Creating GeoJson in php from MySql for use with the MapBox API

Question

What I'm trying to do is very simple; get the marker data from the Mysql table using my PHP code, convert it to geoJson (used by MapBox), send this geoJson to javascript, and then fill this marker on my map.

I read the following two links very carefully, which apparently contain all the information I need to solve this problem, but I'm not sure what I am missing.

Here you can see an example of how to fill markers from geoJson into a MapBox;

Here you can see how to create geoJson from the MySql table;

My sql table looks 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'); 

So, here is my php code where I get data from the database and create 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 check it out if "echo json_encode ($ marker)" here is 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]"}}}; 

Now here 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> 

And finally, the full HTML, which 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> 

So what am I missing? one thing I notice is that the output of my geoJson has "in the geometry array, and the one used in the MapBox example does not work;

 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] } }] }; 

Can anyone help me? there were no real complications, I think it's just a data formatting problem or a way to send GeoJson to JS.

Thanks in advance!

I just noticed that this line;

 var geoJson = <?php echo json_encode($marker,JSON_NUMERIC_CHECK); ?>; 

it should be:

 var geoJson = <?php echo json_encode($geojson,JSON_NUMERIC_CHECK); ?>; 

and in this case the result:

  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); }); 

Answer 1

Take a look at this: https://github.com/bmcbride/PHP-Database-GeoJSON

You are returning an array instead of Json data. This is what 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; ?> 

Answer 2

You are close and on the right track here:

one thing I notice is that the output of my geoJson has "in the geometry array, while the example used in the MapBox example does not:

Yes, you need to make these quotes go away! Looking at your sample, you get a string, not an array, for the key value of the "coordinates" in your array.

I'm not sure why this is so, 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); } 

Answer 3

Check this line

// GeoJSON representing two point functions

var geoJson =;

// Pass the functions and the custom factory function to the map

Try adding quotes around php output

var geoJson = '';