How to select centroid of multiple nearby points with same attribute

Question

This question is very similar to #131823, that is not solved yet.

I want to merge several points like bus stops into one single point in the centroid of all selected points. The bus stops have a attribute "name". The problem is that these names are not necessarily unique worldwide, so there needs to be a spatial limit if several points can be combined to a MultiPoint. This done, we can calculate the centroid with PostGIS: http://www.postgis.org/docs/ST_Centroid.html

How can we solve this problem with Postgis functions?

This page has some interesting leads: http://gis.stackexchange.com/questions/11567/spatial-clustering-with-postgis — thibautg, Oct 03 '15 at 15:43
that's old doc page by the new docs are at - http://postgis.net/docs/ST_Centroid.html — Regina Obe, Oct 03 '15 at 18:34

score 5 · Answer 1 · edited Apr 13 '17 at 12:33

If you have access to PostGIS 2.2rc1, you can use ST_ClusterWithin to group together points that are no more than a specified distance from another point with the same name. The query would look something like this:

SELECT name, ST_Centroid(clustr), ST_NumGeometries(clustr) AS num_points FROM
    (SELECT name, unnest(ST_ClusterWithin(geom, 0.01)) AS clustr FROM points
     GROUP BY name) sq;

where 0.01 is the tolerance distance.

With PostGIS 2.1 or earlier, the approaches used to solve this problem could potentially be useful - you'd just replace the intersection tests with distance tests.

PostGIS 2.2.0 should be out next week. – Regina Obe Oct 03 '15 at 18:35 — Regina Obe, Oct 03 '15 at 18:35

How to select centroid of multiple nearby points with same attribute

1 Answers1