leaflet调用mysql_PHP和MySQL以及Leaflet API

我在这里打了一个关于使用PHP& amp; MySQL与Leaflet API.我开始使用PHP& MYSQL几个月前,我是那个领域的新手,但我愿意学习,所以请给我一些关于我的问题的指示.

所以,我正在尝试使用PHP从MySQL表中获取标记并使用Leaflet API进行渲染

首先,我用一些数据创建了MySQL表：

-- phpMyAdmin SQL Dump

-- version 4.4.6

-- http://www.phpmyadmin.net

--

-- Host: localhost

-- Generation Time: Jan 17, 2016 at 08:36 PM

-- Server version: 5.6.24

-- PHP Version: 5.6.8

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";

SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;

/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

/*!40101 SET NAMES utf8 */;

--

-- Database: `web_gis`

--

CREATE DATABASE IF NOT EXISTS `web_gis` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

USE `web_gis`;

-- --------------------------------------------------------

--

-- Table structure for table `baza`

--

DROP TABLE IF EXISTS `baza`;

CREATE TABLE IF NOT EXISTS `baza` (

`id` int(11) NOT NULL,

`operator` varchar(100) NOT NULL,

`lokacija` varchar(100) NOT NULL,

`x` float NOT NULL,

`y` float NOT NULL,

`prijavljen` date NOT NULL

) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

--

-- Dumping data for table `baza`

--

INSERT INTO `baza` (`id`, `operator`, `lokacija`, `x`, `y`, `prijavljen`) VALUES

(1, 'Tele 2', 'OiV stup Hum na Sutli', 46.2135, 15.672, '2016-01-14'),

(2, 'T-Mobile HR', 'OiV stup Straža', 46.2179, 15.6999, '2016-01-03'),

(3, 'T-Mobile HR', 'Lupinjak', 46.2016, 15.7412, '2016-01-23'),

(4, 'T-Mobile HR', 'Klenovec Humski 89\\1', 46.2169, 15.7268, '2016-01-01');

--

-- Indexes for dumped tables

--

--

-- Indexes for table `baza`

--

ALTER TABLE `baza`

ADD PRIMARY KEY (`id`);

--

-- AUTO_INCREMENT for dumped tables

--

--

-- AUTO_INCREMENT for table `baza`

--

ALTER TABLE `baza`

MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=8;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;

/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

接下来,这是我的PHP(bazneStanice_geojson.php)代码,我从这个网站复制并调整了一点(更改了base的名称更精确)：

/**

* Title: SQLite to GeoJSON (Requires https://github.com/phayes/geoPHP)

* Notes: Query a SQLite table or view (with a WKB GEOMETRY field) and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc. Use QGIS to OGR to convert your GIS data to SQLite.

* Author: Bryan R. McBride, GISP

* Contact: bryanmcbride.com

* GitHub: https://github.com/bmcbride/PHP-Database-GeoJSON

*/

# Connect to SQLite database

$conn = new PDO('mysql:host=localhost;dbname=web_gis','neven','gis');

# Build SQL SELECT statement and return the geometry as a GeoJSON element

$sql = 'SELECT *, x AS x, y AS y FROM baza';

# Try query or error

$rs = $conn->query($sql);

if (!$rs) {

echo 'An SQL error occured.\n';

exit;

}

# Build GeoJSON feature collection array

$geojson = array(

'type' => 'FeatureCollection',

'features' => array()

);

# Loop through rows to build feature arrays

while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {

$properties = $row;

# Remove x and y fields from properties (optional)

unset($properties['x']);

unset($properties['y']);

$feature = array(

'type' => 'Feature',

'geometry' => array(

'type' => 'Point',

'coordinates' => array(

$row['x'],

$row['y']

)

),

'properties' => $properties

);

# Add feature arrays to feature collection array

array_push($geojson['features'], $feature);

}

header('Content-type: application/json');

echo json_encode($geojson, JSON_NUMERIC_CHECK);

$conn = NULL;

// print_r($geojson);

?>

这是脚本代码块. (skripta_mysql.js)我必须指出我成功渲染了地图,我唯一缺少的是MySQL表中的点/标记.

var karta = L.tileLayer('https://api.tiles.mapbox.com/v4/{id}/{z}/{x}/{y}.png?access_token=pk.eyJ1IjoibWFwYm94IiwiYSI6IjZjNmRjNzk3ZmE2MTcwOTEwMGY0MzU3YjUzOWFmNWZhIn0.Y8bhBaUMqFiPrDRW9hieoQ', {

maxZoom: 18,

attribution: 'Map data © OpenStreetMap contributors, ' +

'CC-BY-SA, ' +

'Imagery © Mapbox',

id: 'mapbox.streets'

});

bazne_stanice = new L.geoJson(null, {

pointToLayer: function (feature, latlng) {

return L.marker(latlng, {

});

},

onEachFeature: function (feature, layer) {

if (feature.properties) {

var content = '

'

' + 'ID' + '' + feature.properties.operator + '' + '' +

'

' + 'Name' + '' + feature.properties.lokacija + '' + '' +

'

' + 'Address' + '' + feature.properties.y + '' + '' +

'

' + 'Town' + '' + feature.properties.prijavljen + '' + '' +

'

layer.bindPopup(content);}}

});

$.getJSON("bazneStanice_geojson.php", function (data) {

bazne_stanice.addData(data);

});

var map = L.map('map', {

center: [46.15796, 15.75336],

zoom: 9,

layers: [karta, bazne_stanice]

});

var baseLayers = {

"Podloga": karta

};

var overlays = {

"Bazne stanice": bazne_stanice

};

L.control.layers(baseLayers, overlays).addTo(map);

当我打印变量$geojson;从PHP代码我得到这样的数组

Array

(

[type] => FeatureCollection

[features] => Array

(

[0] => Array

(

[type] => Feature

[geometry] => Array

(

[type] => Point

[coordinates] => Array

(

[0] => 46.2135

[1] => 15.672

)

)

[properties] => Array

(

[id] => 4

[operator] => Tele 2

[lokacija] => OiV stup Hum na Sutli

[prijavljen] => 2016-01-14

)

)....

公平地说,我有点困惑.我真的不知道我错过了什么,一步一步地继续,但仍然没有任何运气渲染标记.在$geojson变量中有问题吗？

我应该在[0]行中获得组合坐标吗？

像这样的东西？

[coordinates] => Array

(

[0] => 46.2135, 15.672

)

============================

更新：

当我试图回声

echo json_encode($geojson, JSON_NUMERIC_CHECK);

我没有得到任何结果

如果我没弄错的话,我应该得到这样的东西

{ "type": "Feature", "properties": { "Operator": "T-Mobile HR", "Lokacija": "Poljana Sutlanska 8, Zagorska Sela", "Prijavljen": "21.12.2010.", "Odjavljeno": null }, "geometry": { "type": "Point", "coordinates": [ 431501.48819855711, 5110280.408429144 ] } }

解决方法:

我找到了解决问题的方法.问题出在符号(č,ž,š,ć,đ..)中,如果任何给定属性有一个符号,则此代码不起作用：

echo json_encode($geojson, JSON_NUMERIC_CHECK);

为了防止破坏代码,我必须在PDO连接上放置UTF-8以检查数据库字符串是否为UTF-8.

而不是这个：

$conn = new PDO('mysql:host=localhost;dbname=web_gis','neven','gis');

所以,我插入了这样的东西：

$conn = new PDO('mysql:host=localhost;dbname=web_gis;charset=utf8','neven','gis',array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'));

它就像一个魅力.

希望能帮助到你！

标签：php,mysql,leaflet,geojson

来源： https://codeday.me/bug/20190706/1396994.html