Points → GeoJSON (PostGIS)

Use this skill when you have tabular data (rows or JSON) that include latitude/longitude fields and you need a GeoJSON FeatureCollection for map display.

This skill documents the canonical raw SQL pattern for building GeoJSON from point data in PostGIS.

When to use

• You have rows like { id, lat, lng, ... }
• You want GeoJSON directly from Postgres
• You want predictable geometry and property handling

Input assumptions

• Latitude and longitude are stored as numeric or castable to numeric
• Longitude comes first in point construction
• Geometry is output in WGS84 (EPSG:4326)

Canonical SQL pattern

From JSON input

Use when your API passes rows as a JSON array.

WITH data_rows AS (
  SELECT jsonb_array_elements($1::jsonb) AS row_data
)
SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_Point(
          (row_data->>'lng')::float,
          (row_data->>'lat')::float
        )
      )::jsonb,
      'properties', row_data - 'lat' - 'lng'
    )
  )
)
FROM data_rows;

From a table

Use when latitude/longitude are stored in columns.

SELECT jsonb_build_object(
  'type', 'FeatureCollection',
  'features', jsonb_agg(
    jsonb_build_object(
      'type', 'Feature',
      'geometry', ST_AsGeoJSON(
        ST_Point(lng, lat)
      )::jsonb,
      'properties', to_jsonb(t) - 'lat' - 'lng'
    )
  )
)
FROM my_table t;

Coordinate rules (critical)

• GeoJSON coordinates are [longitude, latitude]
• Latitude range: -90..90
• Longitude range: -180..180

Most incorrect maps come from swapped coordinates.

Output shape

• type: "FeatureCollection"
• features: array of GeoJSON Feature objects
• Each feature has:
  • geometry.type = "Point"
  • geometry.coordinates = [lng, lat]
  • properties = remaining fields

Implementation notes

• Use ST_Point(lng, lat) (not ST_MakePoint(lat, lng))
• ST_AsGeoJSON(... )::jsonb avoids string parsing downstream
• Removing coord fields keeps properties clean and map-safe

Guardrails

• Filter rows with missing or invalid coordinates before conversion
• Enforce feature limits for browser safety
• Explicitly select properties if payload size matters