在 php 中从 MySql 创建一个 GeoJson 以与 MapBox javascript API 一起使用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17775627/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a GeoJson in php from MySql to use with MapBox javascript API
提问by Marcelo Grebois
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.
我想做的很简单；使用我的 PHP 代码从 Mysql 表中获取标记数据，将其转换为 geoJson（由 MapBox 使用），将该 geoJson 发送到 javascript，然后将这些标记填充到我的地图中。
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.
我一直在非常仔细地阅读以下两个链接，其中显然包含解决此问题所需的所有信息，但我不确定我错过了什么。
Hereyou can see an example on how to populate markers from a geoJson in MapBox;
在这里，您可以看到有关如何从 MapBox 中的 geoJson 填充标记的示例；
Hereyou can see how to create a geoJson from a MySql table;
在这里你可以看到如何从一个 MySql 表创建一个 geoJson；
My sql table look like this, here is the creation code plus some dummy data;
我的sql表是这样的，这里是创建代码加上一些虚拟数据；
-- 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');
So here is my php code, where I get the data from the DB and create a GeoJson.
所以这是我的 php 代码，我从数据库中获取数据并创建一个 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']);
}
?>
Just to test this, if a "echo json_encode($marker)" here is a sample output;
只是为了测试这个，如果这里的“echo json_encode($marker)”是一个示例输出；
{"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]"}}};
Now on this is what the javascript code looks like;
现在这就是 javascript 代码的样子；
<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>
And finally the complete HTML that actually shows the map, but not the markers;
最后是实际显示地图的完整 HTML，但不显示标记；
<?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>
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;
那么，我错过了什么？我注意到的一件事是我的 geoJson 的输出在几何数组中有 "，而 MapBox 示例中使用的则没有；
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]
}
}]
};
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.
谁能帮我？没有任何真正的复杂性，我认为这只是数据格式问题或我将 GeoJson 发送到 JS 的方式。
Thanks in advance!
提前致谢！
I've just notice that this line;
我刚刚注意到这一行；
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
should be;
应该;
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);
});
回答by YoungStacker
Have a look at this: https://github.com/bmcbride/PHP-Database-GeoJSON
看看这个：https: //github.com/bmcbride/PHP-Database-GeoJSON
You are returning array instead of Json data. This is what it should look like
您正在返回数组而不是 Json 数据。这就是它的样子
<?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;
?>
回答by arh1
You're close, and are on the right track here:
你已经接近了，并且在正确的轨道上：
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;
我注意到的一件事是我的 geoJson 的输出在几何数组中有 "，而 MapBox 示例中使用的则没有；
Yes, you need to make those quotes go away! Looking at your sample output you're getting a string rather than an array for the value of the 'coordinates' key in your array.
是的，你需要让这些报价消失！查看您的示例输出，您将获得一个字符串而不是数组中“坐标”键值的数组。
I'm not sure why this is the case based on your sample code, but something like this should work:
根据您的示例代码，我不确定为什么会出现这种情况，但是这样的事情应该可行：
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysql_fetch_assoc($dbquery)){
$marker = array(
'type' => 'Feature',
'properties' => array(
'title' => $row['name'],
'marker-color' => '#f00',
'marker-size' => 'small'
),
'geometry' => array(
'type' => 'Point',
'coordinates' => array(
$row['lat'],
$row['lng']
)
)
);
array_push($geojson['features'], $marker);
}
回答by Besi
Check this line
检查这一行
// The GeoJSON representing the two point features
var geoJson = ;
// Pass features and a custom factory function to the map
// 代表两点特征的 GeoJSON
var geoJson = ;
// 将特性和自定义工厂函数传递给地图
Try to add quotes around the php output
尝试在 php 输出周围添加引号
var geoJson = '';
var geoJson = '';
回答by Ivan Cayabyab
$marker = array(
'type' => 'Feature',
//'features' => array(
//'type' => 'Feature',
'properties' => array(
'title' => $row->name,
'marker-color' =>'#0F8C29',
'marker-size' => 'small'
//'url' =>
),
"geometry" => array(
'type' => 'Point',
'coordinates' => array(
$row->lng,
$row->lat
)
)
//)
);
array_push($geojson['features'], $marker);