Skip to content

Instantly share code, notes, and snippets.

@brambow
Last active April 25, 2023 15:44
Show Gist options
  • Star 36 You must be signed in to star a gist
  • Fork 6 You must be signed in to fork a gist
  • Save brambow/889aca48831e189a62eec5a70067bf8e to your computer and use it in GitHub Desktop.
Save brambow/889aca48831e189a62eec5a70067bf8e to your computer and use it in GitHub Desktop.
PostGIS query to build a GeoJSON FeatureCollection
SELECT json_build_object(
'type', 'FeatureCollection',
'crs', json_build_object(
'type', 'name',
'properties', json_build_object(
'name', 'EPSG:4326'
)
),
'features', json_agg(
json_build_object(
'type', 'Feature',
'id', {id}, -- the GeoJson spec includes an 'id' field, but it is optional, replace {id} with your id field
'geometry', ST_AsGeoJSON(geom)::json,
'properties', json_build_object(
-- list of fields
'field1', field1,
'field2', field2
)
)
)
)
FROM yourtable; --replace with your table name
@4xle
Copy link

4xle commented Aug 2, 2019

Is it possible to insert an entire FeatureCollection into geography field?
For example:
https://raw.githubusercontent.com/idoivri/israel-municipalities-polygons/master/zvulun/zvulun.geojson

You can separate out each of the features and convert them to a geography record per each row. You could store the entire feature collection in one row as a geography type as one massive multi-geometry record, but that's ill-advised because you'd likely hamstring most common PostGIS queries.

@Bnaya
Copy link

Bnaya commented Aug 18, 2019

How would it look like?
Merging the polygons?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment