Geek Answers Handbook

PostGis clustering with another aggregate

I would like to calculate a cluster of points and for each cluster get the sum of a certain attribute (say, the sum of the estimates of each point in the cluster)

I have already managed to create clusters using ST_ClusterWithin , but I can not calculate the sum.

Here is what I tried:

 SELECT sum(score), unnest(ST_ClusterWithin(coordinates, 0.1)) AS cluster FROM locations GROUP BY cluster;

But I get the following ERROR: aggregate functions are not allowed in GROUP BY error ERROR: aggregate functions are not allowed in GROUP BY

If I delete GROUP BY , I will get a valuation amount for all locations, which I don't want (I want a sum for locations in the cluster)

+7
postgresql postgis
source share
2 answers

This is a difficult task, and the st_clusterwithin api does not seem well designed for what should be commonplace.

The only solution I could find was to return to clusters as follows:

 SELECT SUM(score), cluster FROM locations, ( SELECT unnest(ST_ClusterWithin(coordinates, 0.1)) AS cluster FROM locations ) as location_clustered WHERE ST_Contains(ST_CollectionExtract(cluster, 1), coordinates) GROUP BY cluster;

Edit: I changed ST_CollectionHomogenize to ST_CollectionExtract(<geometrycollection>, 1) (select 1 for the point, 2 for the linestring and 3 for the polygon) as suggested in this answer: https://gis.stackexchange.com/questions/195915/ due to this error: https://trac.osgeo.org/postgis/ticket/3569

Do not ask me why you cannot do ST_Contains(<geometrycollection>, <geometry>) ; We need to convert to a multipoint connection, which is valid as an argument.

Meta: This question would be great for https://gis.stackexchange.com/

+3
source share

In PostGIS 2.3, you can use the ST_ClusterDBSCAN function (choosing the third parameter comes down to hierarchical clustering), which directly returns the corresponding cluster index:

 WITH stat AS ( SELECT score, ST_ClusterDBSCAN(coordinates, 0.1, 1) OVER () AS cluster_id FROM tmp_locations ) SELECT cluster_id, SUM(score) FROM stat GROUP BY cluster_id ORDER BY cluster_id
0
source share

More articles:


All Articles