MySQL to GeoJSON - Formatting Issue

Question

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?

Answer 1

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

Answer 2

I had the same issue. Try this, worked for me:

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

and change the array_push to:

array_push($geojson['features'], $feature);

Answer 3

You are pushing each $feature onto $geojson without index, while already having two associative indexes ('type' and 'features'), so each is going in as an ordinary numerically-indexed item (0, 1, 2…). Then your indexes are showing up because you've now got an associative array instead of just a normal numerical one.

Try something like (pseudo-code):

1. $features = array();
2. Each loop: $features[] = array('type' => 'Feature'…
3. $geojson['features'] = $features

Then your final array will only have implicit numbered indexes (that's what $var[] does) and you shouldn't get the indexes coming out in your json_encode().