Short answer

The reliable way to export PostGIS to GeoJSON is ogr2ogr from GDAL, reprojecting to EPSG:4326 and emitting RFC 7946 output:

ogr2ogr -f GeoJSON faults.geojson \
  PG:"dbname=geo host=localhost user=reader" \
  -sql "SELECT id, name, dip, geom FROM faults WHERE region = 'NW'" \
  -t_srs EPSG:4326 \
  -lco RFC7946=YES \
  -lco COORDINATE_PRECISION=6

GeoJSON is defined to be in WGS84 longitude/latitude, so anything not already in EPSG:4326 must be reprojected, not merely relabelled. The two operations are not the same, and conflating them is the most common way to ship data that lands in the wrong place.

SRID label vs reprojection: the critical distinction

PostGIS geometries carry an SRID. Two functions touch it and they do completely different things:

  • ST_SetSRID(geom, 4326) changes only the label. The numbers stay identical. Use it when the data is genuinely in 4326 but the SRID was lost (showing as 0), for example after an import that dropped the .prj.
  • ST_Transform(geom, 4326) runs an actual coordinate transformation through PROJ, moving every vertex from the source CRS to 4326.

If your table is in, say, UTM 31N (EPSG:32631) with coordinates like 448000, 5412000, calling ST_SetSRID(geom, 4326) tells the system those are degrees, placing the feature off the coast of Africa near (448000°, 5412000°), which is nonsense. You need ST_Transform. Check first:

SELECT Find_SRID('public', 'faults', 'geom');   -- declared SRID
SELECT ST_SRID(geom), ST_AsText(ST_Centroid(geom)) FROM faults LIMIT 1;

If the SRID is 0, decide whether the metadata is missing (set it) or wrong, before exporting.

RFC 7946 and why it matters

The current GeoJSON standard, RFC 7946 (2016), mandates WGS84, right-hand-rule winding for polygons, and bounded coordinates. Older GDAL output predates it. Passing -lco RFC7946=YES makes ogr2ogr produce spec-compliant geometry, which avoids polygon fill bugs in MapLibre and Leaflet caused by inconsistent ring winding. It also strips a crs member that some old clients misread. If you need to keep a non-4326 CRS (generally a mistake for GeoJSON, but occasionally required for a specific tool), use -lco RFC7946=NO deliberately and document why.

Controlling file size

Raw GeoJSON is verbose. Three levers, applied during export, usually cut size by more than half without harming a web map:

  • Coordinate precision. -lco COORDINATE_PRECISION=6 keeps six decimal places of longitude/latitude, about 0.11 m at the equator, which is finer than most web maps need. Default GDAL output can write 15 digits, most of which are noise.
  • Column selection. Export only the attributes the client needs via the -sql SELECT. Wide tables with internal bookkeeping columns bloat every feature.
  • Geometry simplification. For overview layers, ST_SimplifyPreserveTopology(geom, 10) (tolerance in the geometry's units; reproject to metres first or simplify in 3857) removes redundant vertices while keeping shared boundaries intact. Plain ST_Simplify can tear topology between adjacent polygons.

Above roughly 5–10 MB, stop fighting GeoJSON. Gzip helps in transit, but for genuinely large layers switch to FlatGeobuf (-f FlatGeobuf, streamable and indexed) or generate vector tiles instead.

A repeatable, scriptable workflow

  1. Prototype on a sample. Append LIMIT 100 to the -sql query and inspect the output before running the full table. This catches CRS, encoding, and column problems cheaply.
  2. Pin the connection and query in a script so the export is reproducible from a clean database state:
#!/usr/bin/env bash
set -euo pipefail
PGCONN="PG:dbname=geo host=localhost user=reader"
ogr2ogr -f GeoJSON faults_4326.geojson "$PGCONN" \
  -sql "SELECT id, name, dip, ST_Transform(geom, 4326) AS geom FROM faults" \
  -lco RFC7946=YES -lco COORDINATE_PRECISION=6 -lco WRITE_BBOX=YES

Here the reprojection lives in SQL; you could equally drop ST_Transform and use -t_srs EPSG:4326. Pick one approach and be consistent. WRITE_BBOX=YES adds a top-level bbox, which speeds some clients.

  1. Validate (next section) every time, not just the first.
  2. Log the source table version, query, and GDAL version alongside the output so the artefact is traceable.

Validation: never trust an exit code

ogr2ogr returning 0 only means it wrote a file, not that the file is correct. Check:

# Feature count matches the source
psql -d geo -c "SELECT count(*) FROM faults;"
ogrinfo -so -al faults_4326.geojson | grep "Feature Count"

# Extent is in degrees (-180..180, -90..90) and over the right area
ogrinfo -so -al faults_4326.geojson | grep Extent

# CRS is WGS84
ogrinfo -so -al faults_4326.geojson | grep -i crs

Then open the file in QGIS over a basemap and confirm features land where they should. For polygons, check ST_IsValid in the source first (SELECT count(*) FROM faults WHERE NOT ST_IsValid(geom);) and repair with ST_MakeValid if needed, because invalid geometry can export with subtly broken rings.

Common pitfalls and why they happen

  • Using ST_SetSRID when transformation is required. The data looks fine in attribute tables but plots in the ocean. It happens because both functions mention SRID, so they get confused.
  • Forgetting the WGS84 requirement. Exporting a 3857 or UTM GeoJSON without RFC7946 produces a file some web clients silently mis-handle.
  • Trusting the exit code. A query that returns zero rows still writes a valid, empty file.
  • Excessive coordinate precision. Fifteen decimal places triples file size for no cartographic benefit.
  • Plain ST_Simplify across shared borders, which opens slivers between adjacent units; use ST_SimplifyPreserveTopology.

Bathyl perspective

We script PostGIS-to-GeoJSON exports so they are rerunnable, version-logged, and validated every time, because a GeoJSON is only as trustworthy as its CRS handling and its feature count. The goal is a small, spec-clean file that a web map can consume and that any analyst can trace back to the exact query that produced it.

Related reading

Sources