10

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?

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.