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',
        'id', p.id,
        '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(
          'name', p.name,
          '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, geojson.io, and a multitude of other tools.