PostGIS - Merge buffers geometry in different groups (when intersecting)

2
votes

I've some geometries stored in this table (140k)

CREATE TABLE buffers (pk integer NOT NULL,geom geometry(MultiPolygon,4326),)

- buffers geometries from table "buffers" -

and i would like to create (if possible) a new table (buffersmerged) that will contain the resultants geometries where the previews are grouped just when them are intersecting between each other.

My expected output, it is like when using in QGIS the Buffer(s) tool checking on "dissolve buffer results", but i would like, rather than just a big unique geometry as output, more small groups (one for each group of intersecting geometries)

- this is the expected result -

can you please help me to understand how can I construct a query that allows me to do that? (Postgres + PostGIS enabled) Thank You

Stefano

1
sqlpostgresqlgeometrypostgisqgis

1 Answers

2
votes

Do you need the attributes too?

If not, this query should do the job:

CREATE TABLE buffersmerged AS 
SELECT (ST_dump(ST_union(a.geom))).geom 
FROM buffers a, buffers b 
WHERE ST_intersects(a.geom,b.geom) AND a.id != b.id;