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?

Improve this question
2
  • 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) Commented Mar 7, 2018 at 10:57
  • Please be sure to always specify the exact PostgreSQL database release and PostGIS version within each question. Commented Mar 7, 2018 at 12:46

1 Answer 1

Reset to default
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.

Improve this answer
11
  • 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 :-) Commented Mar 7, 2018 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. Commented Mar 7, 2018 at 14:32
  • You can use row_number () over ())::integer to populate a sequential id col Commented Mar 7, 2018 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)... Commented Mar 7, 2018 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 Commented Mar 7, 2018 at 15:18

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.