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:


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

// // Return routing result
    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?


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

| improve this answer | |

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

Cheers Henrik

| improve this answer | |

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().

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.