Search code examples
phpmysqljsonformattinggeojson

Formatting json to geojson via PHP

I am trying to get some data that I have called from a mySQL database to correctly display in a GeoJSON format. Here's some of my PHP code:

$data = array(); //setting up an empty PHP array for the data to go into

if($result = mysqli_query($db,$query)) {
  while ($row = mysqli_fetch_assoc($result))
  {
    $data[] = $row;
  }
}

$jsonData =json_encode($data);
$original_data = json_decode($jsonData, true);
$coordinates = array();
foreach($original_data as $key => $value) {
    $coordinates[] = array($value['latitude'], $value['longitude']);
}
$new_data = array(
    'type' => 'FeatureCollection',
    'features' => array(array(
        'type' => 'Feature',
    'properties' => array('time' => $value['time']),
    'geometry' => array('type' => 'Point', 'coordinates' => $coordinates),
    ),
    ),
);

$final_data = json_encode($new_data, JSON_PRETTY_PRINT);
print_r($final_data);

I've managed to get my results to look like this so far: enter image description here

But I need them to look like this so that every set of coordinates has its own "type" and "properties" key-value pair:

enter image description here

I've already found some help with this issue here, but I just can't manage to get over this last formatting hurdle...

Solution

  • Instead of building coordinates, you need to build features:

    $data = array(); //setting up an empty PHP array for the data to go into

if($result = mysqli_query($db,$query)) {
  while ($row = mysqli_fetch_assoc($result))
  {
    $data[] = $row;
  }
}

$jsonData =json_encode($data);
$original_data = json_decode($jsonData, true);
$features = array();
foreach($original_data as $key => $value) {
    $features[] = array(
        'type' => 'Feature',
        'properties' => array('time' => $value['time']),
        'geometry' => array(
             'type' => 'Point', 
             'coordinates' => array(
                  $value['latitude'], 
                  $value['longitude'], 
                  1
             ),
         ),
    );
}
$new_data = array(
    'type' => 'FeatureCollection',
    'features' => $features,
);

$final_data = json_encode($new_data, JSON_PRETTY_PRINT);
print_r($final_data);