[GIS] Merge adjacent polygons and return all (adjacent and not adjacent) as Multipolygon

geojsonmergepolygonpostgresql

I have a postgres database with administrative boundaries and their geometry.
Each of those boundaries have an ident number.

What I want to achieve:

First I want to select all rows which are starting with a particular zip code.
For example:

SELECT * FROM "post" WHERE "post"."ident" LIKE '101%'

All of those Geometries which are adjacent should be merged and those which are not should also be returned.

I have tried those statements:

This one I have from this answer:

Joining lots of small polygons to form larger polygon using PostGIS?

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_UNION(geom) from t;

But this statement returns only those polygons which are adjacent but others are not included

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ((ST_Dump(ST_UNION(geom)))) from t;

I get 2 rows, and it seems that this could be what I want, but I cannot get the result als ST_AsText.

Is there a statement which returns all polygons merged where necessary and return it ideally as geoJSON or maybe as text?

Best Answer

OP's answer:

with t as (
select (st_dump(geom)).geom 
from "post"
where "post"."ident" LIKE '593%'
) select ST_AsGeoJSON((ST_Dump(ST_UNION(geom))).geom) from t;

So you can access geometries and transform it into geoJSON. Adjacent geometries are merged and you get all other geometries which are also not included in merged polygon.

Related Question