Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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:

What is the best way to join lots of small polygons to form a larger polygon?

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?

EDIT:

I find a solution by myself:

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 I can access geometries and transform it into geoJSON. Adjacent geometries are merged and I get all other geometries which are also not included in merged polygon.

I would answer this as an answer but I do not have enough reputation to answer my own question.

So hope it helps other who read this.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.