How to Export PostGIS Data to GeoJSON: 4 Methods Compared

Which method to use

There are four common ways to push a PostGIS query result out as GeoJSON. None is wrong — they're optimal in different situations.

Detailed walkthroughs below.

Method 1: ST_AsGeoJSON in SQL

PostGIS ships with ST_AsGeoJSON, which returns a GeoJSON Geometry object as text. Wrap it in json_build_object and json_agg to produce a complete FeatureCollection in one query:

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', json_agg(
    json_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(geom)::json,
      'properties', json_build_object(
        'id', id,
        'name', name,
        'population', population
      )
    )
  )
) AS geojson
FROM cities
WHERE country = 'DE';

The result is a single text cell containing the full FeatureCollection. To pull it into a file:

psql -At -c "SELECT json_build_object(...) FROM cities" gis > out.geojson

The -At flags strip column headers and alignment.

Pros: runs entirely in SQL, easy to embed in application code, fine-grained control over which columns go where.

Cons: the whole result is materialised in server memory. Fine for 10,000 features; punishing for a million.

Method 2: ogr2ogr

The GDAL one-liner for the same job:

ogr2ogr -f GeoJSON out.geojson \
  PG:"host=localhost dbname=gis user=me" \
  -sql "SELECT id, name, population, geom FROM cities WHERE country='DE'"

ogr2ogr streams from the database cursor to the file. Memory usage is bounded; you can export tables with millions of rows.

Useful additions:

• -t_srs EPSG:4326 to reproject if the source is in another CRS (GeoJSON requires WGS 84).
• -lco RFC7946=YES to enforce RFC 7946 strict mode.
• -lco COORDINATE_PRECISION=6 to truncate to ~10 cm precision.

Pros: streams, scales, supports every PostGIS-side filter you can write in SQL.

Cons: another tool in the pipeline; requires GDAL installed.

Method 3: psql + ST_AsGeoJSON + \COPY

For server-side bash scripts where ogr2ogr isn't available, psql plus \COPY is the Postgres-native streaming export:

psql -d gis <<'SQL'
\COPY (
  SELECT json_build_object(
    'type', 'Feature',
    'geometry', ST_AsGeoJSON(geom)::json,
    'properties', json_build_object('id', id, 'name', name)
  )
  FROM cities
  WHERE country='DE'
) TO '/tmp/features.ndjson'
SQL

This produces NDJSON — one JSON Feature per line. To assemble a single FeatureCollection:

{
  echo '{"type":"FeatureCollection","features":['
  paste -sd ',' /tmp/features.ndjson
  echo ']}'
} > out.geojson

Pros: uses only Postgres-side tools. Streams. Great for cron jobs on database hosts.

Cons: the FeatureCollection assembly is a shell hack. NDJSON intermediate is fine for many web tools but not strictly GeoJSON.

Method 4: QGIS DB Manager

For exploratory work or when you want to see the result on a map first:

1. Open QGIS, Database → DB Manager.
2. Connect to your PostGIS instance.
3. Run the query in the SQL window.
4. Tick Load as new layer to inspect the result.
5. Once loaded as a layer, Layer → Export → Save Features As → GeoJSON.

The DB Manager runs the query on the server, materialises the result client-side, lets you sanity-check on the map, and writes a clean GeoJSON file.

Pros: visual confirmation before export. Easy to iterate on the query.

Cons: GUI-only. Not scriptable. Loads everything into client memory.

Picking by situation

• One-off exploration: QGIS DB Manager.
• Embedded in app code (Node/Python/Go): ST_AsGeoJSON in SQL.
• Scheduled export, large table: ogr2ogr in cron.
• Database host with no GDAL: psql + \COPY.

Pitfalls to avoid

• Forgetting to reproject. PostGIS lets you store data in any SRID; GeoJSON demands WGS 84. Always reproject with ST_Transform(geom, 4326) (in SQL) or -t_srs EPSG:4326 (ogr2ogr) on export.
• Empty FeatureCollection. If json_agg returns over an empty result set, you get null in the features slot, which is not valid GeoJSON. Wrap in COALESCE(json_agg(...), '[]'::json).
• Quoting attribute names with special characters. PostGIS column names map directly to GeoJSON property names. If a column is named Country Name (with a space), it round-trips fine to JSON but breaks many downstream tools. Alias to safe names in your SELECT.

A complete realistic example

Export all buildings in a bounding box, transformed to WGS 84, at 6-decimal precision:

ogr2ogr -f GeoJSON -t_srs EPSG:4326 \
  -lco COORDINATE_PRECISION=6 \
  buildings.geojson \
  PG:"host=localhost dbname=gis user=me" \
  -sql "SELECT id, height, geom FROM buildings WHERE ST_Intersects(geom, ST_MakeEnvelope(13.3, 52.4, 13.5, 52.6, 4326))"

Streams from PostGIS, reprojects on the fly, truncates precision on write. One line, scales to a million features.