我想做的事情非常简单;用我的PHP代码从Mysql表中获取标记的数据,将其转换为geoJson(MapBox使用),将该geoJson发送到javascript,然后将这些标记填充到我的地图中。
我一直在仔细阅读下面的两个链接,其中显然包含了我解决这个问题所需的所有信息,但我不确定我遗漏了什么。
在这里,您可以看到一个关于如何在MapBox中从geoJson填充标记的示例;
在这里,您可以看到如何从MySql表创建geoJson;
我的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');
这是我的php代码,我从DB中获取数据并创建一个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)"是一个示例输出;
{"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代码的样子;
<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,它实际上显示了地图,但没有显示标记;
<?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示例中使用的输出没有;
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的方式。
提前感谢!
我刚刚注意到这句话;
var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>;
应该是;
var geoJson = <?php echo json_encode($geojson,JSON_NUMERIC_CHECK); ?>;
在这种情况下,结果是;
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数据。这就是的样子
<?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;
?>
你很接近,并且在正确的轨道上:
我注意到的一件事是,geoJson的输出在几何数组中有",而MapBox示例中使用的输出没有;
是的,你需要让那些名言消失!查看示例输出,您得到的是数组中"坐标"键值的字符串,而不是数组。
基于您的示例代码,我不确定为什么会出现这种情况,但类似的东西应该会起作用:
$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);
}
检查此行
//表示两点特征的GeoJSON
var geoJson=;
//将功能和自定义工厂功能传递到地图
尝试在php输出周围添加引号
var geoJson=";
$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);
最终答案:
<?php
define('DB_SERVER','localhost');
define('DB_USER','root');
define('DB_PASS' ,'');
define('DB_NAME', 'pgmq');
$connect = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME);
$mapa = "SELECT * FROM mapa ";
$dbquery = mysqli_query($connect,$mapa);
$geojson = array( 'type' => 'FeatureCollection', 'features' => array());
while($row = mysqli_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['lng'],
$row['lat']
)
)
);
array_push($geojson['features'], $marker);
}
header('Content-type: application/json');
echo json_encode($geojson, JSON_NUMERIC_CHECK);
?>
我相信,它会帮助其他人。您可以在以下位置验证响应:https://geojsonlint.com/