mysql - Querying SQL using Php and outputting geojson for a GeoPolygon -

output geojson geo polygon in php stored in mysql database

i have tried many things including geophp plugin https://geophp.net/geos.html no success far, tried using st_asgeojson no use

$sql = "select st_asgeojson(st_geomfromtext('coordinates')) buildings id = 1"; 

https://dev.mysql.com/doc/refman/5.7/en/spatial-geojson-functions.html

i trying achieve output similar

"shape":{"type":"polygon","coordinates":[[[53.294974,-6.426631],[53.294847,-6.426419],[53.294289,-6.426888],[53.294326,-6.427194],[53.294974,-6.426631]]]} 

this insert statement -

insert `mrp_buildings` (   `buildingid`, `companyid`, `name`, `nooffloors`,   `coordinates`, `city`, `country`, `address`, `phone`,   `email`) values (     '1', '1', 'my sample building', '4',     polyfromtext('polygon((33.294974 -2.426631, 53.294847 -6.426419,       73.294289 -6.426888, 13.294326 -6.427194, 43.294974 -6.426631,       33.294974 -2.426631))'), 'dublin', 'ireland', 'mayor street', '089449 8500', 'email@example.ie '); 

any suggestions -

take db row, craft array json keys, json_encode().

of course, way have stored coordinates messy, need turn $coordinates array so:

$coordinates = [     [         55.123456,         0.123456,     ],     [        //etc     ] 

in order that, need rid of stuff don't need first:

$string = $row['coordinates']; /* polygon((33.294974 -2.426631, 53.294847 -6.426419,       73.294289 -6.426888, 13.294326 -6.427194, 43.294974 -6.426631,       33.294974 -2.426631)) */  $string = str_replace('polygon', '', $string); /* ((33.294974 -2.426631, 53.294847 -6.426419,   73.294289 -6.426888, 13.294326 -6.427194, 43.294974 -6.426631,   33.294974 -2.426631)) */   $string = str_replace('(', '', $string);  $string = str_replace(')', '', $string); // remove brackets   $coords = explode(',' $string); 

at point you'll have array of coordinates, both x & y 1 value separated space, need explode too.

$coordinates = []; foreach ($coords $co) {     $ex = explode(' ', $co);     $coordinates[] = [         $ex[0], $ex[1];     ]; } 

finally can create array json encode:

$array = []; //empty array  $array['shape'] = [     'type' => 'polygon',     'coordinates' => $coordinates ];  $json = json_encode($array); 

i hope helps! if can, try storing coordinates in db without additional stuff save hassle!