Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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? Thanks in advance for any help.

share|improve this question

3 Answers 3

up vote 3 down vote accepted

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 http://stackoverflow.com/questions/481466/php-string-to-float

share|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

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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