3

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

Mike T
  • 41,085
  • 18
  • 152
  • 203
entalpia
  • 101
  • 1
  • 11

1 Answers1

3

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;
Tom-db
  • 6,528
  • 3
  • 30
  • 44
  • First of all, thanks for your quick reply. No i didn't need any attributes, just the geometry. Once i run your code, I've got a table that was looking good but without any geometry. What i've done to fix this (maybe in not a sexy way), was to create a table with already defined two column (pk, geom) where the second was already defined as POLYGON. And now it works!! Thanks – entalpia Feb 10 '16 at 11:57
  • Strange, by me the statement worked. Maybe can you edit my answer and add your workaround? Don't forget to accept the answer, if it works. – Tom-db Feb 10 '16 at 12:19
  • before edit i'll show you the workaround: >CREATE TABLE buffersmerged (pk serial NOT NULL PRIMARY KEY,geom geometry(POLYGON,4326)); and then: >INSERT INTO buffersmerged (geom) >SELECT (ST_dump(ST_union(a.geom))).geom >FROM buffers a, buffers b >WHERE ST_intersects(a.geom,b.geom) AND a.pk != b.pk; there is probably a way to make a single query, but i don't know how. how to accept the answer? the upper arrow? – entalpia Feb 10 '16 at 15:15
  • You need a.id< b.id instead of a.id != b.id, otherwise you get a double copy of grouped geometries. – Poiana Apuana Jan 07 '17 at 17:30