[GIS] MySQL to GeoJSON – Formatting Issue

geojsonleafletMySQL

I'm trying to make a select on a mysql table and then write it out to a geojson file. I'm getting close but there are a few issues. Here's what I have so far: 

<?php
require("dbinfo.php");

// Opens a connection to a mySQL server
$connection=mysql_connect ($hostname, $username, $password);
if (!$connection) {
  die('Not connected : ' . mysql_error());
}

// Set the active mySQL database
$db_selected = mysql_select_db($database, $connection);
if (!$db_selected) {
  die ('Can\'t use db : ' . mysql_error());
}

// json output
$query = 'SELECT * FROM markers LIMIT 5';
$dbquery = mysql_query($query);
// $rows = array();
// while($r = mysql_fetch_assoc($dbquery)) {
//  $coords = $rows['lng'] . ", ";
//     // $rows[] = $r;
// }

if(! $dbquery )
{
  die('Could not get data: ' . mysql_error());
}

// Parse the dbquery into geojson 
// ================================================
// ================================================
// Return markers as GeoJSON
$geojson = array(
    'type'      => 'FeatureCollection',
    'features'  => $feature
 );

while($row = mysql_fetch_assoc($dbquery)) {
    $feature = array(
        'type' => 'Feature', 
      'geometry' => array(
        'type' => 'Point',
        'coordinates' => array($row['lng'], $row['lat'])
            ),
      'properties' => array(
            'name' => $row['name'],
        'comment' => $row['comment']
            )
        );
    array_push($geojson, $feature);
};
mysql_close($connection);

// // Return routing result
    header("Content-Type:application/json",true);
    //header("Location:map.html");
    echo json_encode($geojson);
?>

The result I get is:

{"type":"FeatureCollection","features":null,"0":{"type":"Feature","geometry":{"type":"Point","coordinates":["-122.677811","45.569889"]},"properties":{"name":"Bear on a Box","comment":"Bear on a box."}},"1":{"type":"Feature","geometry":{"type":"Point","coordinates":["-122.653770","45.542503"]},"properties":{"name":"test","comment":"aksdhvah"}},"2":{"type":"Feature","geometry":{"type":"Point","coordinates":["-122.675400","45.515690"]},"properties":{"name":"Bike Art","comment":"Bike pile."}},"3":{"type":"Feature","geometry":{"type":"Point","coordinates":["-122.688789","45.517735"]},"properties":{"name":"Goose","comment":"lipsumLorem ipsum dolor sit amet, consectetur adipiscing elit. Nam ut urna dolor. Sed iaculis velit quis nis"}},"4":{"type":"Feature","geometry":{"type":"Point","coordinates":["-122.649132","45.513401"]},"properties":{"name":"Goose","comment":"lorem ipsum"}}}

So, the issue is that the "features" item does not hold the rest of the info as an array. It remains 'null'. Then, it appears the array position is written into the result which I don't want either. Lastly, I need to get rid of the quotes around the coordinates so it can be read properly by Leaflet's GeoJSON constructor. I'm flailing with arrays and probably not getting it. I mean, does everything that gets pushed into an array have to be surrounded by quotes?

Best Answer

I haven't been coding in PHP lately but I think you're just not layering your data structures properly. You are pushing the $feature onto $geoson every time. Try making 'features' an array like so:

$features = array();
$geojson = array(
    'type'      => 'FeatureCollection',
    'features'  => $features
 );

Then push each $feature in your loop onto $features instead of $geojson.

array_push($features, $feature);
instead of
array_push($geojson, $feature);

The quotes. I'm guessing your lat and lon are strings in the database so they show up as strings in your json . You should be able to cast them to a floating point number. see https://stackoverflow.com/questions/481466/php-string-to-float

Related Question