How to create a GeoJSON feature collection in PostGIS

It’s often useful to convert rows of PostGIS geo data to GeoJSON. There’s a builtin function, ST_AsGeoJSON, that I had naively assumed could only do something with a geometry column, like this:

SELECT ST_AsGeoJSON(geom) FROM my_table;

This returns each row as a simple geometry — a point, a linestring, or a polygon — like this:

{"type": "Point", "coordinates": [-21.2, 64.1]}

Since PostGIS v3.0.0 (released 20 October 2019) if you want to include other columns as properties, you can:

SELECT
  ST_AsGeoJSON(t.*)
FROM
  my_table AS t

The ‘t.*’ and ‘AS t’ are important, because they cause PostgreSQL to return a record rather than a row. Without that you get an error. If you try a plain SELECT ST_AsGeoJSON(*) FROM my_table, PostgreSQL will complain:

ERROR: function st_asgeojson() does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.

But if you use the correct syntax then Postgres will return each row as a GeoJSON feature, like this:

{
  "type": "Feature",
  "geometry": {
    "type": "Point",
    "coordinates": [-21.2, 64.1]
  },
  "properties": {
    "id": 12345,
    "underground": false
  }
}

Now we have rows of GeoJSON features — but what about if we want to combine these into a single GeoJSON feature collection? Surprisingly easy:

SELECT
  json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(t.*)::json)
  )
FROM
  my_table AS t

If you want to transform your data into another spatial reference system, then with a little more typing you can:

SELECT
  json_build_object(
    'type', 'FeatureCollection',
    'features', json_agg(ST_AsGeoJSON(t.*)::json)
  )
FROM (
  SELECT
    col1,
    col2,
    ST_Transform(geom, 4326)
  FROM
    my_table
) AS t

I haven’t yet found a way to do that without having to enumerate every column, but this is still much easier to remember than the method required with PostGIS v2.5 or earlier. More information can be found on Paul Ramsey’s blog post, Waiting for PostGIS 3: ST_AsGeoJSON(record).

• PostgreSQL
• PostGIS
• GeoJSON