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:
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?