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

Question

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:

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));

Answer 1

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.

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;

Answer 2

One way to do this is to ST_Union all of the buffers together, ST_Dump the result to get the components of the resulting polygon, and join with ST_Intersects back to the input points to found out how many/which points made up each cluster.

This can be done without requiring a join by grouping the points together before calling ST_Buffer. For two points to be located within the same dissolved buffer, they must be reachable by hops between points of a distance less than eps. This is just a minimum-linkage clustering problem, which can be solved using ST_ClusterDBSCAN:

SELECT
  cluster_id,
  ST_Union(ST_Buffer(geom, 1000)) AS geom,
  count(*)                        AS num_points,
  array_agg(point_id)             AS point_ids
FROM (
  SELECT
    point_id,
    ST_ClusterDBSCAN(geom, eps := 2000, minpoints := 1) OVER() AS cluster_id ,
    geom
  FROM points) sq
 GROUP BY cluster_id;

Note that this won't produce exactly the same result as the buffer-first method, because PostGIS buffers aren't perfect circles and two points 1000m apart may not be connected by two 500m buffers.

Answer 3

According to this answer you want to do ST_DUMP within your subquery.

Something like this:

-- 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_Dump(ST_Union(ST_Buffer(geom, 1000, 8))::geometry(MultiPolygon, /*SRID*/)) AS geom
FROM
public.multipoints)
AS sub_qry;

The reason why is that ST_UNION returns a dissolved multipolygon of all the features, and ST_DUMP breaks this out into the individual polygon features (which were dissolved).