I'm building a small map application with Leaflet and PostGIS. I'm having an issue similar to this post where using pg_fetch_all is returning the data I want, but buried in an associative array.

When I run the query in PgAdmin I get the results I expect as a feature collection that works on geosjson.io. What I'm having issues is formating the response, in that the query's result raw is just Resource id #3. Using pg_fetch_all returns the GeoJSON object nested into an associative array looking like this:

Array ( [0] => Array ( [row_to_json] => {"type":"FeatureCollection","features":[{"type":"Feature","geometry":{"type":"Point","coordinates":[8.697130556,50.123908333,108.5]},"properties":{"id":3,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_3475.jpeg","type":"Quote"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[8.691429401,50.115307684,105.7]},"properties":{"id":4,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_3025.jpeg","type":"Quote"}},{"type":"Feature","geometry":{"type":"Point","coordinates":[8.694072891,50.119541651,111.5]},"properties":{"id":5,"photo":"/Users/Nizz0k/Desktop/Peng/IMG_1274.jpeg","type":"Drawing and Tag"}}

So, how can I either format the response better, or pull the nested object out of the associative array?

My aim is to get GeoJSON for a Leaflet map. I'm querying a shapefile of single locations that I want some attributes associated.

Here's my query and code snippet:

$query = "SELECT row_to_json(fc)
FROM ( SELECT 'FeatureCollection' As type, array_to_json(array_agg(f)) As features
FROM (SELECT 'Feature' As type
   , ST_AsGeoJSON(lg.geom)::json As geometry
   , row_to_json((SELECT l FROM (SELECT id, photo, type) As l
     )) As properties
  FROM public.\"Peng\" As lg   ) As f )  As fc;";

$result = pg_query($query) or die('Query failed: ' . pg_last_error());
$geodata = pg_fetch_all($result);

How can I both query and output a GeoJSON object? It's unclear to me why the output is getting wrapped in the array this way.


So, the answer is just correctly accessing the data in the array with $geodata[0]['row_to_json'].

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.