Создание GeoJson в php из MySql для использования с API-интерфейсом MapBox

То, что я пытаюсь сделать, очень просто; получить данные маркера из таблицы Mysql с помощью моего PHP-кода, преобразовать это в geoJson (используется MapBox), отправить этот geoJson в javascript, а затем заполнить этот маркер на моей карте.

Я очень внимательно читал следующие две ссылки, которые, по-видимому, содержат всю информацию, которая мне нужна для решения этой проблемы, но я не уверен, что мне не хватает.

Здесь вы можете увидеть пример того, как заполнять маркеры из geoJson в MapBox;

Здесь вы можете увидеть, как создать geoJson из таблицы MySql;

Моя таблица 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-код, где я получаю данные из БД и создаю 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, DC', '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); }); 

 <?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; ?> не <?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; ?> не <?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 = 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); }