1

I have a mysql spatial database ('mydb1') of one table ('pk'), i mean by spatial the fact that i obtained the database from a point shapefile using ogr2ogr tool. one row of this 'pk' table, is like:

 OGR_FID   SHAPE       CODIF    Position_X   Position_Y    Nom   Status
 1     [GEOMETRY-25o]  PAC182854  398235.38    414569.24    G-31   Vert

I need to output the geoJSON file from this spatial mysql table. I downloaded geoPHP library and also MySQL to GeoJSON script which i adapted to my database settings, like below:

<?php
/**
* Title:   MySQL to GeoJSON (Requires https://github.com/phayes/geoPHP)
* Notes:   Query a MySQL table or view and return the results in GeoJSON format, suitable for use in OpenLayers, Leaflet, etc.
* Author:  Bryan R. McBride, GISP
* Contact: bryanmcbride.com
* GitHub:  https://github.com/bmcbride/PHP-Database-GeoJSON
*/

# Include required geoPHP library and define wkb_to_json function
include_once('geoPHP/geoPHP.inc');
function wkb_to_json($wkb) {
    $geom = geoPHP::load($wkb,'wkb');
    return $geom->out('json');
}

# Connect to MySQL database
$conn = new PDO('mysql:host=localhost;dbname=mydb1','root','admin');

# Build SQL SELECT statement and return the geometry as a WKB element
$sql = 'SELECT *, AsWKB(SHAPE) AS wkb FROM pk';

# Try query or error
$rs = $conn->query($sql);
if (!$rs) {
    echo 'An SQL error occured.\n';
    exit;
}

# Build GeoJSON feature collection array
$geojson = array(
   'type'      => 'FeatureCollection',
   'features'  => array()
);

# Loop through rows to build feature arrays
while ($row = $rs->fetch(PDO::FETCH_ASSOC)) {
    $properties = $row;
    # Remove wkb and geometry fields from properties
    unset($properties['wkb']);
    unset($properties['SHAPE']);
    $feature = array(
         'type' => 'Feature',
         'geometry' => json_decode(wkb_to_json($row['wkb'])),
         'properties' => $properties
    );
    # Add feature arrays to feature collection array
    array_push($geojson['features'], $feature);
}
header('Content-type: application/json');
//write to json file
$fp = fopen('pk_geojson.json', 'w');
fwrite($fp, json_encode($geojson));
fclose($fp);
$conn = NULL;
?>

But when i execute the code on the browser and open the output geojson file, i don't find the longitude and latitude values of each feature. i get instead, X and Y values another time, like so:

{"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[398235.3882,414569.2462]},"properties":{"OGR_FID":"1","codif":"PAC182854","position_x":"398235.388200","position_y":"414569.246200","nom":"G-31","point_kilo":"182000","status":"Vert"}}]}

in the geometry property like you can see, i get {"type":"Point","coordinates":[398235.3882,414569.2462]} so another time x, y values that i have in the property "properties" ("position_x", "position_y"), while i need to have instead longitude and latitude values, in order to add this geojson file to my map on openlayers as vector layer. I notice that in the starting, i had a x,Y excel file that i converted with arcmap to the shapefile from which i built mysql database using ogr2ogr tool. So, is it a problem of the way i made the shapefile or what's wrong exactly i have to fix to get a proper geojson file?

  • Your GeoJSON file is proper (but not a FeatureCollection with one Point, use single Feature) but the crs is clearly not WSG84 or EPSG:4326 , therefore the projection of your table is also not WSG84. You need to change the crs of the table. Moreover MySQL has the ST_AsGeoJSON function. – gene Oct 6 '16 at 15:12
  • @gene Thanks for your response. first, i illustrated in my question just one feature of the geojson file, there are another ones, it's long so i didn't want to show all the features, but you are right. Then, where should i precise crs EPSG:4326 WGS84? is it in the import of X,Y excel file on arcmap? (because effectively i choose EPSG:26191 related to morocco? And how to use ST_AsGeoJSON in mysql? i dont know. Thks. – drissrais Oct 6 '16 at 16:08
  • Cross-posted as stackoverflow.com/q/39853381/820534 – PolyGeo Jan 16 '17 at 11:59

Your Answer

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

Browse other questions tagged or ask your own question.