[GIS] Create dissolved buffer from multi-geometry (Union by shared attribute and spatial intersection)

buffermultipartpostgis

I've to create dissolved buffers from multi-point input features. In the example below, the input table contains 4 features. Feature #2 consists of two point geometries. After creating a buffer, I get 4 polygon geometries:

enter image description here

Is there a way to group the result? The buffers of the points #1 and #2 are dissolved and should be a single multi-polygon feature (a).

What I've done so far:

-- collect all buffers to a single multi-polygon feature
-- dissolve overlapping polygon geometries
CREATE TABLE public.pg_multibuffer AS SELECT
    row_number() over() AS gid,
    sub_qry.*
FROM (SELECT
    ST_Union(ST_Buffer(geom, 1000, 8))::geometry(MultiPolygon, /*SRID*/) AS geom
FROM
public.multipoints)
AS sub_qry;

EDIT:

-- create sample geometries

CREATE TABLE public.multipoints (
gid serial NOT NULL,
geom geometry(MultiPoint, 31256),
CONSTRAINT multipoints_pkey PRIMARY KEY (gid)
);

CREATE INDEX sidx_multipoints_geom
ON public.multipoints
USING gist
(geom);

INSERT INTO public.multipoints (gid, geom) VALUES
(1, ST_SetSRID(ST_GeomFromText('MultiPoint(12370 361685)'), 31256)),
(2, ST_SetSRID(ST_GeomFromText('MultiPoint(13520 360880, 19325 364350)'), 31256)),
(3, ST_SetSRID(ST_GeomFromText('MultiPoint(11785 367775)'), 31256)),
(4, ST_SetSRID(ST_GeomFromText('MultiPoint(19525 356305)'), 31256));

Best Answer

Starting with some random points, in an attept to imitate those in the OP's image, where the first two spatially intersect, then the 2nd and 3rd have the same attribute id (2), with a couple of other points that neither spatially intersect nor have the same attribute, the following query produces 3 clusters:

WITH 
  temp (id, geom) AS 
     (VALUES (1, ST_Buffer(ST_Makepoint(0, 0), 2)),
        (2, ST_Buffer(ST_MakePoint(-0.7,0.5), 2)),
        (2, ST_Buffer(ST_MakePoint(10, 10), 2)), 
        (3, ST_Buffer(ST_MakePoint(-2, 12), 2)), 
        (4, ST_Buffer(ST_MakePoint(5, -6), 2))),
 unions(geoms) AS 
      (SELECT ST_Union(geom) FROM temp GROUP BY id),
 clusters(geoms) AS 
      (SELECT ST_CollectionExtract(unnest(ST_ClusterIntersecting(geoms)), 3) 
         FROM unions),
 multis(id, geoms) AS 
      (SELECT row_number() over() as id, geoms FROM clusters)
 SELECT ST_UNION(d.geom) FROM 
      (SELECT id, (ST_DUMP(geoms)).geom FROM multis) d GROUP BY id;

There are several steps here:

  1. use ST_Union, grouping by id, to first group by attribute
  2. use ST_ClusterIntersecting to combine those from same group that intersect spatially
  3. add an id to each of the clusters (table multis) -- trying to do this directly in the ClusterIntersecting leads to all geometries getting an id of 1
  4. Union the dumped geometries from step 2, grouping by the id from step 3 -- this is the dissolve part. This causes the two overlapping polygons in your cluster A, to be joined together, rather than being overlapping, as they are at the end of step 2.

Rather long, but it works (and, I am sure there is a shorter way).

Using the WKT tool in QGIS, (and discovering how awful I am with the editing tools) produces clusters like the following, where you can see the cluster your- labelled as a, is all together -- ie, one colour.

enter image description here

If you put an ST_AsText round the final, ST_UNION(d.geom), then you can see the results directly.

EDIT following more information in the comments: As you are starting with points you will need to incorporate the buffer into my original solution -- which I put in the temp CTE at the start to mimic your diagram. It would be easier to add the buffer in the unions CTE, so you can do all the geometries at once. So, using a buffer distance of 1000, as an example, the following now returns 3 clusters, as expected.

WITH temp(id, geom) AS 
  (VALUES 
      (1, ST_SetSRID(ST_GeomFromText('MultiPoint(12370 361685)'), 31256)),   
      (2, ST_SetSRID(ST_GeomFromText('MultiPoint(13520 360880, 19325 364350)'), 31256)),                                                
      (3, ST_SetSRID(ST_GeomFromText('MultiPoint(11785 367775)'), 31256)),
      (4, ST_SetSRID(ST_GeomFromText('MultiPoint(19525 356305)'), 31256))
),                                              
unions(geoms) AS 
  (SELECT st_buffer(ST_Union(geom), 1000) FROM temp GROUP BY id),
clusters(geoms) AS 
  (SELECT ST_CollectionExtract(unnest(ST_ClusterIntersecting(geoms)), 3) 
     FROM unions),
multis(id, geoms) AS 
  (SELECT row_number() over() as id, geoms FROM clusters)
SELECT id, ST_UNION(d.geom) FROM 
  (SELECT id, (ST_DUMP(geoms)).geom FROM multis) d GROUP BY id;
Related Question