Published on

How to Retrieve GeoJSON FeatureCollections and Features With PostGIS Query

Tags

postgissqlgeojson

This article teaches you how to return full GeoJSON FeatureCollection and Feature objects from PostgreSQL/PostGIS queries.

Table of Contents

Overview

Use the query templates below to retrieve FeatureCollections or Features with all or only some selected field values as Feature properties.

Make sure to replace

  • input_table,
  • id_column and
  • geom_column

with your own table/column names.

As the coordinate reference system for all GeoJSON coordinates is a geographic coordinate reference system (WGS 84), you might want to transform your coordinates by replacing ST_AsGeoJSON(geom_column) with ST_AsGeoJSON(ST_Transform(geom_column, 4326)).

The id member of the Feature object is optional by the GeoJSON specification.

FeatureCollection (All Columns As Properties)

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', json_agg(
    json_build_object(
      'type',       'Feature',
      'id',         id_column,
      'geometry',   ST_AsGeoJSON(geom_column)::json,
      -- use the '-' operator to delete unnecessary keys (columns) from the properties object
      'properties', to_jsonb(input_table) - 'id_column' - 'geom_column'
    )
  )
)
FROM input_table

FeatureCollection (Selected Columns As Properties)

SELECT json_build_object(
  'type', 'FeatureCollection',
  'features', json_agg(
    json_build_object(
      'type',       'Feature',
      'id',         id_column,
      'geometry',   ST_AsGeoJSON(geom_column)::json,
      'properties', json_build_object(
        -- list of columns - replace with your own column names
        'column1', column1,
        'column2', column2
      )
    )
  )
)
FROM input_table

Feature (All Columns As Properties)

SELECT json_build_object(
  'type',       'Feature',
  'id',         id_column,
  'geometry',   ST_AsGeoJSON(geom_column)::json,
  -- use the '-' operator to delete unnecessary keys (columns) from the properties object
  'properties', to_jsonb(input_table) - 'id_column' - 'geom_column'
)
FROM input_table

Feature (Selected Columns As Properties)

SELECT json_build_object(
  'type',       'Feature',
  'id',         id_column,
  'geometry',   ST_AsGeoJSON(geom_column)::json,
  'properties', json_build_object(
    -- list of columns - replace with your own column names
    'column1', column1,
    'column2', column2
  )
)
FROM input_table
Discuss on Twitter