Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
@Bnaya
Copy link

Bnaya commented May 24, 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

@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