Creating a GeoJson in php from MySql to use with MapBox javascript API

Question

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.

Here you can see an example on how to populate markers from a geoJson in MapBox;

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

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

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']);
}
?>

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

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>

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>

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

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.

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

Answer 1

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

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;
?>

Answer 2

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;

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

Answer 3

Check this line

// The GeoJSON representing the two point features

var geoJson = ;

// Pass features and a custom factory function to the map

Try to add quotes around the php output

var geoJson = '';

Answer 4

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