We have a set of thousands of points and want to group all of those which are within 100m distance from each other (to get the centroid from each group). A first idea was to build 100m buffers around each point and to union/group all intersecting/overlapping polygons to one (but there can also be non-overlapping polygons within one cluster, see screenshot) – after that, we could simply calculate the centroid from the merged polygons. But I do not know how to do that in Postgis (in QGIS there is a simple tool called 'dissolve' that merges overlapping polygons, but we want to automatize the process)
Best Answer
You can group points using either the recursive query or PL/PLGSQL procedure described in the answers to this question. Just substitute
ST_DWithinforST_Intersects/ST_Touches, as appropriate.If you're comfortable trying something experimental, you could build PostGIS with purpose-built functions to solve this problem: see the ticket on trac (code available on github)
Update January 2016: The "experimental" functions described above are no longer experimental;
ST_ClusterWithinandST_ClusterIntersectingare available in PostGIS 2.2. I highly recommend PostGIS 2.2.1, which includes a very important performance fix for these functions.