从MySql在php中创建GeoJson以与MapBox javascript API一起使用 [英] Creating a GeoJson in php from MySql to use with MapBox javascript API
问题描述
我想做的事情很简单;使用我的PHP代码从Mysql表中获取marker的数据，将其转换为geoJson（由MapBox使用），将geoJson发送到javascript，然后将这些标记填充到我的地图中。
What I'm trying to do is very simple; get marker's data from a Mysql table with my PHP code, convert that that to a geoJson ( used by MapBox ), send that geoJson to javascript and then populate those marker into my map.
我一直在仔细阅读以下两个链接，显然包含我需要解决此问题的所有信息，但我不确定我缺少什么。
I've been reading very carefully the following two links, that apparently contains all the info I need to resolve this, but I'm not sure what I'm missing.
这里你可以看到关于如何从MapBox中的geoJson填充标记的示例;
Here you can see an example on how to populate markers from a geoJson in MapBox;
这里你可以看到如何从MySql表创建一个geoJson;
Here you can see how to create a geoJson from a MySql table;
我的sql表看起来像这样，这里是创建代码加上一些虚拟数据;
My sql table look like this, here is the creation code plus some dummy data;
-- phpMyAdmin SQL Dump
-- version 4.0.4.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 21, 2013 at 03:26 PM
-- Server version: 5.5.32-cll-lve
-- PHP Version: 5.5.0
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
--
-- Database: `pgmq`
--
-- --------------------------------------------------------
--
-- Table structure for table `mapa`
--
CREATE TABLE IF NOT EXISTS `mapa` (
`contrato` int(11) NOT NULL,
`name` varchar(60) NOT NULL,
`address` varchar(80) NOT NULL,
`lat` float(10,6) NOT NULL,
`lng` float(10,6) NOT NULL,
`type` varchar(30) NOT NULL,
PRIMARY KEY (`contrato`),
UNIQUE KEY `contrato` (`contrato`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
--
-- Dumping data for table `mapa`
--
INSERT INTO `mapa` (`contrato`, `name`, `address`, `lat`, `lng`, `type`) VALUES
(217, 'DIVANIR BRASIL DA SILVA', '47 joao carolino da silva, guaruja - sao paulo, brazil', -23.950968, -46.289585, '11'),
(233, 'ADEMIR VIEIRA', '180 dois j, guaruja - sao paulo, brazil', -23.932041, -46.206879, '11'),
(241, 'MARIA CECILIA DOS SANTOS', '81 professor carvalho pinto, guaruja - sao paulo, brazil', -23.946516, -46.290428, '11'),
(252, 'ELIETE COSTA SANTOS', '387 maria de melo rodrigues, guaruja - sao paulo, brazil', -23.667521, -46.747810, '11'),
(271, 'ROBERTO SANTOS COUTO', '62 tapajos, guaruja - sao paulo, brazil', -23.949146, -46.284588, '11'),
(275, 'UMBERTO FERREIRA SOUZA NETO', '88 tapajos, guaruja - sao paulo, brazil', -23.949162, -46.284821, '11'),
(276, 'SERGIO RICARDO DOS SANTOS', '418 joana menezes de mello faro, guaruja - sao paulo, brazil', -23.994600, -46.256866, '11'),
(278, 'MARIA APARECIDA NUNES', '80 andre reboucas, guaruja - sao paulo, brazil', -23.945040, -46.297462, '11'),
(285, 'WALTECES SOUZA DA CONCEICAO', '298 maranhao, guaruja - sao paulo, brazil', -23.942638, -46.304131, '11'),
(286, 'ROBERTO AUGUSTO DE JESUS SOUZA', '38 dois c caic cinquenta e cinco , guaruja - sao paulo, brazil', -23.994600, -46.256866, '11');
所以这是我的PHP代码，我从数据库中获取数据并创建一个GeoJson。 / p>
So here is my php code, where I get the data from the DB and create a GeoJson.
<?php
$connect = mysql_connect("localhost","user","password");
$mapa = "SELECT * FROM pgmq.mapa ";
$dbquery = mysql_query($mapa,$connect);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'features' => array(
'type' => 'Feature',
'properties' => array(
'title' => "".$row[name]."",
'marker-color' => '#f00',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row[lat],
$row[lng]
)
)
)
);
array_push($geojson['features'], $marker['features']);
}
?>
为了测试这个，如果这里的echo json_encode（$ marker）是一个样本输出;
Just to test this, if a "echo json_encode($marker)" here is a sample output;
{"type":"Feature","features":{"type":"Feature","properties":{"title":"DIVANIR BRASIL DA SILVA","marker-color":"#f00","marker-size":"small"},"geometry":{"type":"Point","coordinates":"[-23.950968, -46.289585]"}}};
现在这就是javascript代码的样子;
Now on this is what the javascript code looks like;
<script>
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
</script>
最后是实际显示地图的完整HTML，但不是标记;
And finally the complete HTML that actually shows the map, but not the markers;
<?php
$connect = mysql_connect("localhost","user","pass");
$mapa = "SELECT * FROM pgmq.mapa ";
$dbquery = mysql_query($mapa,$connect);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'features' => array(
'type' => 'Feature',
'properties' => array(
'title' => "".$row[name]."",
'marker-color' => '#f00',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row[lat],
$row[lng]
)
)
)
);
array_push($geojson['features'], $marker['features']);
}
?>
<!DOCTYPE html>
<html>
<head>
<meta name='viewport' content='width=device-width, initial-scale=1.0, maximum-scale=1.0, user-scalable=no' />
<script src='//api.tiles.mapbox.com/mapbox.js/v1.3.1/mapbox.js'></script>
<link href='//api.tiles.mapbox.com/mapbox.js/v1.3.1/mapbox.css' rel='stylesheet' />
<style>
body { margin:0; padding:0; }
#map { position:absolute; top:0; bottom:0; width:100%; }
</style>
</head>
<body>
<div id='map'></div>
<script>
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
</script>
</body>
</html>
那么，我错过了什么？我注意到的一件事是我的geoJson的输出在几何数组中，而在MapBox示例中使用的那个没有;
So, what am I missing? one thing I notice is that the output of my geoJson has " in the geometry array, while the one used in the MapBox example don't;
var geoJson = {
type: 'FeatureCollection',
features: [{
type: 'Feature',
properties: {
title: 'Washington, D.C.',
'marker-color': '#f00',
'marker-size': 'large',
url: 'http://en.wikipedia.org/wiki/Washington,_D.C.'
},
geometry: {
type: 'Point',
coordinates: [-77.03201, 38.90065]
}
},
{
type: 'Feature',
properties: {
title: 'Baltimore, MD',
'marker-color': '#f00',
'marker-size': 'large',
url: 'http://en.wikipedia.org/wiki/Baltimore'
},
geometry: {
type: 'Point',
coordinates: [-76.60767, 39.28755]
}
}]
};
任何人都可以帮助我吗？没有任何真正的复杂性，我认为这只是一个数据格式化问题或我将GeoJson发送给JS的方式。
Can anyone help me? there hasn't any real complication, i think it's just a data formatting problem or the way that I'm sending the GeoJson to the JS.
提前致谢！
我刚注意到这一行;
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
应该是;
var geoJson = <?php echo json_encode($geojson,JSON_NUMERIC_CHECK); ?>;
在这种情况下结果是;
and in that case the result is;
var map = L.mapbox.map('map', 'examples.map-20v6611k').setView([-23.948714, -46.295508], 1);
// The GeoJSON representing the two point features
var geoJson = ;
// Pass features and a custom factory function to the map
map.markerLayer.setGeoJSON(geoJson);
map.markerLayer.on('click', function(e) {
e.layer.unbindPopup();
window.open(e.layer.feature.properties.url);
});
推荐答案
看看这个： https://github.com/bmcbride/PHP-Database-GeoJSON
您正在返回数组而不是Json数据。它应该是什么样子
You are returning array instead of Json data. This is what it should look like
<?php
/*
* Title: MySQL Points to GeoJSON
* Notes: Query a MySQL table or view of points with x and y columns and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.
* Author: Bryan R. McBride, GISP
* Contact: bryanmcbride.com
* GitHub: https://github.com/bmcbride/PHP-Database-GeoJSON
*/
# Connect to MySQL database
$conn = new PDO('pgsql:host=localhost;dbname=mypostgisdb','myusername','mypassword');
# Build SQL SELECT statement including x and y columns
$sql = 'SELECT *, x AS x, y AS y FROM mytable';
/*
* If bbox variable is set, only return records that are within the bounding box
* bbox should be a string in the form of 'southwest_lng,southwest_lat,northeast_lng,northeast_lat'
* Leaflet: map.getBounds().pad(0.05).toBBoxString()
*/
if (isset($_GET['bbox']) || isset($_POST['bbox'])) {
$bbox = explode(',', $_GET['bbox']);
$sql = $sql . ' WHERE x <= ' . $bbox[2] . ' AND x >= ' . $bbox[0] . ' AND y <= ' . $bbox[3] . ' AND y >= ' . $bbox[1];
}
# 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;
?>
这篇关于从MySql在php中创建GeoJson以与MapBox javascript API一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
