I have been searching for a few days for a SQL query that would allow me to create a GeoJSON that would give me the outline of polygons in my database.
That first query is working correctly, it permits to get 1 Geojson Object with all the multipolygons in only one cell.
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(
the_geom
)::json,
'properties', json_build_object(
'name', name
)
)
)
)
FROM layer
Now i'm trying to get the same GeoJson object but with Linestring instead of multipolygons. It seems that the function ST_ExteriorRing() doesn't work with the function json_agg() ? I don't know what I have to change to make it works ... Here is the last query i tried, which doesn't work :
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(
json_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(
ST_ExteriorRing( -- get the exterior lines of multipolygons
ST_GeometryN(
the_geom,
generate_series(
1,
ST_NumGeometries(the_geom)
)
)
)
)::json,
'properties', json_build_object(
'name', name
)
)
)
)
FROM layer
If i suppress the json_agg() function it works fine, but it this case i don't get a single cell in output. If i supress the ExteriorRing() function it works also fine, but I have MultiPolygons and not LineString like i would like ...
Any ideas how to solve that problem ?
I found it months later, so here is the answer :
SELECT json_build_object(
'type', 'FeatureCollection',
'features', json_agg(feature)
) as geojson
FROM (
SELECT json_build_object(
'type', 'Feature',
'geometry', ST_AsGeoJSON(
ST_ExteriorRing(
ST_GeometryN(
ST_Union(the_geom),1
)
)
)::json
) as feature
FROM layer
) t