Benjamin Becquet

GeoJSON export in PostgreSQL without PostGIS

Here is a handy SQL query I used at work to export geographic point data as GeoJSON from a plain, "spatially-dumb" PostgreSQL database. By that, I mean without the awesome PostGIS extension which offers native GeoJSON support.

It makes use of high-level JSON functions that appeared in PostgreSQL 9.4, so this assumes you use at least this version.

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', json_agg(json_build_object(
    'type', 'Feature',
    'geometry', json_build_object(
      'type', 'Point',
      'coordinates', json_build_array(p.longitude, p.latitude),
      -- assuming your coordinates are stored like that.
    'properties', json_build_object(
      'address', p.address
      -- etc. Those are just examples of fields in the table.
FROM my_places p;

With this indenting and thanks to the simple function signatures (lists of alternating keys and values), you can see the final JSON shape directly in the query. The json_agg function is an SQL aggregate (like avg, min, etc.) which reduces multiple values to a single JSON array.

Saved in a UTF-8 text file, the result is a valid GeoJSON FeatureCollection of Point features. It can be opened in QGIS,, and a multitude of other tools.