Merge buffers PostGIS

Question

I have a points layer arround which I created a buffer of 10m. What I want to do is merge the buffers which intersects each other and the point layer.

I did this to be able to create polygon arround the points which are within 10 m.

CREATE OR REPLACE FUNCTION getbuffered() RETURNS SETOF sige.buffer AS
$BODY$
DECLARE
    poly record;
    pts record;
    intersects boolean;
    geom_poly geometry(MultiPolygon,32628);
    geom_fusion geometry(MultiPolygon,32628); 
BEGIN
    FOR pts IN SELECT * FROM sige.object_elevation LOOP
        FOR poly IN SELECT * FROM sige.buffer LOOP
            intersects := (select true from sige.buffer,sige.object_elevation
                            where poly.id<>pts.id and st_intersects(poly.geom,pts.geom));
                if true then
                geom_poly := (select geom from sige.buffer where orig_fid = pts.id);
                end if;
         END LOOP;
    END LOOP;

END
$BODY$
LANGUAGE 'plpgsql' ;

In this function I am iterating in points layer, then in buffer layer. then I select the features from buffer which have a different id from the points ones and intersect them. If the result is true I want to merge them.

I executed my function that I'm presuming correct but I get this :

ERROR: more than one line returned by a subquery used as an expression
CONTEXT: SELECT SQL statement (select true from sige.buffer, sige.object_elevation
             where poly.id <> pts.id and st_intersects (poly.geom, pts.geom))
PL / pgsql function getbuffered (), line 10 to assignment

What am I'm missing?

Answer 1

"I did this to be able to create polygon around the points which are within 10 m." - I'll give it a shot at what I think you want to do, that is "clustering all your points with a cluster distance of 10m and create a convex hull around each cluster"...

To simply draw the minimal enclosing geometry around each cluster, you can do the following, using ST_ClusterWithin and ST_ConvexHull:

WITH
  clst AS (
    SELECT ST_ClusterWithin(<geometry_column>, 10) arr
    FROM <your_point_table>
  )

SELECT a.cluster_id,
       ST_ConvexHull(ST_CollectionHomogenize(a.geom)) AS geom
FROM clst,
     unnest(clst.arr) WITH ORDINALITY a(geom, cluster_id)

Note: this will return the minimal enclosing geometry of each cluster - that can also be a POINT geometry for a single point that is his own cluster, or a LINESTRING geometry for two points being a cluster! It's a little tricky (some say unadvisable) to work with mixed geometry types in the same table.

As an option, you could only select those clusters that are actually enclosed by a POLYGON:

WITH
  clst AS (
    SELECT ST_ClusterWithin(<geometry_column>, 10) arr
    FROM <your_point_table>
  ),

  clst_encl AS (
    SELECT a.cluster_id,
           ST_ConvexHull(ST_CollectionHomogenize(a.geom)) AS geom
    FROM clst,
         unnest(clst.arr) WITH ORDINALITY a(geom, cluster_id)
  )

SELECT *
FROM clst_encl
WHERE GeometryType(geom) = 'POLYGON'

with the minor backdraw that the extracted array's ordinals I used as cluster_id are not strictly consecutive due to rows being omitted.

You could also opt for simply assigning the cluster_id to your original points:

WITH
  clst AS (
    SELECT ST_ClusterWithin(<geometry_column>, 10) arr
    FROM <your_point_table>
  ),

  clst_ext AS (
    SELECT a.cluster_id,
           ST_CollectionHomogenize(a.geom) AS geom
    FROM clst,
         unnest(clst.arr) WITH ORDINALITY a(geom, cluster_id)
  )

SELECT ce.cluster_id,
       pt.*
FROM <your_point_table> AS pt
JOIN clst_ext AS ce
  ON pt.<geometry_column> && ce.geom

with the minor backdraw that this obviously returns no polygons...

Does that sound like what you need?


EDIT:

As @dbaston thankfully pointed out and I keep forgetting, ST_ClusterDBSCAN is way better suited here;

SELECT ST_ClusterDBSCAN(pt.<geometry_column>, 10, 1) OVER() AS cluster_id,
       *
FROM <your_point_layer> AS pt

to assign the cluster_ids to your points;

SELECT cluster_id,
       ST_ConvexHull(ST_Collect(geom)) AS geom
FROM (
  SELECT ST_ClusterDBSCAN(<geometry_column>, 10, 1) OVER() AS cluster_id,
         <geometry_column> AS geom
  FROM <your_point_layer>
) AS cluster
GROUP BY cluster_id
ORDER BY cluster_id

to get the minimal enclosing geometry of each cluster.