How to Export PostGIS Data to GeoJSON: 4 Methods Compared

Which method to pick

Four common ways to push PostGIS query results out as GeoJSON. None is wrong — they're optimal in different situations.

Walkthroughs below.

Method 1: ST_AsGeoJSON in SQL

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

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

To pull it into a file:

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

-At strips column headers and alignment so the output is raw JSON.

Pros: runs entirely in SQL. Easy to embed in app code or API endpoints. Fine-grained control over which columns map where.

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

Method 2: ogr2ogr with the PG: driver

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 straight to the file. Memory usage is bounded; tables with millions of rows export cleanly.

Useful additions:

• -t_srs EPSG:4326 to reproject if the source uses another SRID.
• -lco RFC7946=YES for strict RFC 7946 mode.
• -lco COORDINATE_PRECISION=6 to truncate precision and halve file size.

For credentials, prefer the PGPASSWORD env var or a ~/.pgpass file over putting the password in the connection string.

Pros: streams, scales, supports any SQL filter you can express.

Cons: another tool in the pipeline. Requires GDAL installed on the export host.

Method 3: QGIS DB Manager

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

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 on the map.
5. Once it loads, Layer → Export → Save Features As → GeoJSON.

The DB Manager runs the query server-side, 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 RAM.

Method 4: COPY to CSV + post-process

For locked-down database hosts where GDAL isn't installed and ogr2ogr is unavailable:

psql -d gis <<'SQL'
\COPY (
  SELECT
    id, name, population,
    ST_X(geom) AS lon,
    ST_Y(geom) AS lat
  FROM cities
  WHERE country='DE'
) TO '/tmp/cities.csv' WITH (FORMAT CSV, HEADER);
SQL

Then post-process the CSV into GeoJSON with a small Python script, ogr2ogr on another machine, or a stream processor like jq.

For non-point geometries, use ST_AsText(geom) to emit WKT instead:

\COPY (SELECT id, name, ST_AsText(geom) AS wkt FROM regions) TO '/tmp/r.csv' CSV HEADER;

The CSV then needs -oo GEOMETRY=AS_WKT on the destination side to be read as spatial.

Pros: uses only Postgres-side tools. Streams to disk. Good for cron jobs on hardened hosts.

Cons: two-step pipeline with an intermediate file. Geometry encoding is on you.

Picking by situation

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

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 becomes null. json_agg over an empty result returns null in the features slot, which is not valid GeoJSON. Wrap with COALESCE(json_agg(...), '[]'::json).
• Column names with special characters. PostGIS column names round-trip into GeoJSON property names verbatim. A column called Country Name (with a space) works but breaks downstream tools. Alias to safe names in your SELECT.
• Credentials in shell history. Don't put the password in PG:"... password=...". Use PGPASSWORD or .pgpass.

A complete realistic example

Export all buildings in a bounding box, reprojected to WGS 84, at 6-decimal precision, streamed straight to file:

ogr2ogr -f GeoJSON -t_srs EPSG:4326 \
  -lco COORDINATE_PRECISION=6 -lco RFC7946=YES \
  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))"

One line, streams from PostGIS, scales to a million features.