[postgis-users] Generating GeoJson files from PostGIS

[Bruce Rindahl]

Just ran into an error creating a GeoJSON file and thought I would share my
fix.


I generate GeoJSON files constantly from PG/PostGIS on a regular schedule.
Typical uses are how much rain has fallen, potential flooding and
earthquakes near me in the past week.  Sometimes the answer is ‘none’ –
i.e. haven’t seen any earthquakes lately.  When this happens, the typical
code I use (https://gist.github.com/brambow/889aca48831e189a62eec5a70067bf8e
)

gives the result

{"type" : "FeatureCollection",  "features" : null}


While this is valid JSON, QGIS and Leaflet will really complain about it.
What they want is

  {"type" : "FeatureCollection",  "features" : [] }

so I just wrap the coalesce function around it like this:


SELECT json_build_object('type', 'FeatureCollection',
 'features', COALESCE(
json_agg(json_build_object('type', 'Feature', 'geometry',
(st_asgeojson(foo.geom))::json,
'properties', json_build_object('bar', foo.bar)))
, '[]')
) AS json_build_object
   FROM foo;


This generates what QGIS and Leaflet are looking for.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20210129/311b4ff8/attachment.html>