0

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?

  • not sure if I get this right...you basically want to cluster points that are within 10m of each other and create a convex hull around each of those clusters? if so, ST_ClusterWithin should be the better choice than a buffer aproach (using buffers for anything distance related is usually not a good idea) – ThingumaBob Mar 7 '18 at 10:57
  • Please be sure to always specify the exact PostgreSQL database release and PostGIS version within each question. – Vince Mar 7 '18 at 12:46
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.

  • 1
    unnest(clst.arr) with ordinality, sweet. I have absolutely no idea if this answers the question or not, but it is a fabulous answer to some question :-) – John Powell Mar 7 '18 at 14:23
  • @ThingumaBob I tried your method, and seems similiar to point aggregation in ArcGIS. This doesn't cluster all points. I have some points which are in a distance of 2, 7 meters are not clustered. I want all points which are separated by 10 m max be grouped on a polygon. – zakaria mouqcit Mar 7 '18 at 14:32
  • You can use row_number () over ())::integer to populate a sequential id col – Vince Mar 7 '18 at 14:34
  • @Vince Of course, thanks, but I was just adding to the inital query, so the second one is a mere mockup to exclude non-polygons. I´ll add a better one ASAP (since then the whole ordinal extraction is only overhead)... – ThingumaBob Mar 7 '18 at 14:42
  • 1
    Definitely use ST_ClusterDBSCAN if you want to assign IDs back to the original points; see gis.stackexchange.com/a/273613/18189 – dbaston Mar 7 '18 at 15:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.