PostGIS Geometry Rows → GeoJSON FeatureCollection

Use this skill when your data already has a PostGIS geometry column (typically geom) and you need to serve it to a web map as GeoJSON.

This is the canonical pattern:

output WGS84 (EPSG:4326) for clients
return a FeatureCollection
keep properties as “all columns minus geom” (or explicitly selected fields)

When to use

You have a table with geom (geometry) and other columns
You are building or updating an API endpoint that returns map layers
You want consistent GeoJSON structure and coordinate system

Core rules

Serve geometry in EPSG:4326 for web clients
GeoJSON output should be a FeatureCollection
Properties should not include raw geom (remove it or select explicitly)

Canonical SQL pattern (FeatureCollection)

SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', COALESCE(jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(ST_Transform(t.geom, 4326))::jsonb,
      'properties', (to_jsonb(t) - 'geom')
    )
  ), '[]'::jsonb)
) AS geojson
FROM my_table t
WHERE t.geom IS NOT NULL;

Notes

COALESCE(..., '[]') ensures empty results return "features": [] (not null)
ST_Transform(..., 4326) ensures map-safe coordinates
to_jsonb(t) - 'geom' keeps properties clean

Selecting only certain properties (recommended for payload size)

If a table has many columns, explicitly build properties:

SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', COALESCE(jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(ST_Transform(t.geom, 4326))::jsonb,
      'properties', jsonb_build_object(
        'id', t.id,
        'name', t.name,
        'status', t.status
      )
    )
  ), '[]'::jsonb)
) AS geojson
FROM my_table t
WHERE t.geom IS NOT NULL;

Returning one feature by id

Use this pattern for a single geometry row:

SELECT jsonb_build_object(
  'type', 'Feature',
  'geometry', ST_AsGeoJSON(ST_Transform(t.geom, 4326))::jsonb,
  'properties', (to_jsonb(t) - 'geom')
) AS feature
FROM my_table t
WHERE t.id = $1
  AND t.geom IS NOT NULL;

Guardrails for map endpoints

Always require filtering (bbox, ids, or a sensible limit) for large tables
Consider enforcing a maximum feature count
Prefer explicit properties for large payloads
If you frequently serve this layer, consider simplifying geometry upstream (separate skill)

Common mistakes

Serving geometry in a projected SRID (client renders nonsense)
Returning raw geom in properties
Forgetting COALESCE and returning "features": null
Returning too many features (browser crash / huge payload)

geojson-postgis