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?


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.

| improve this answer | |
  • PostGIS 2.2.0 should be out next week. – LR1234567 Oct 3 '15 at 18:35

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.