PostGis clustering with other aggregate

Question

I would like to compute cluster of points and, for each cluster to get the sum of a specific attribute (let say, the sum of the score of each point in the cluster)

I already managed to build clusters using ST_ClusterWithin but I cannot compute 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 ERROR: aggregate functions are not allowed in GROUP BY

If I remove the GROUP BY, I get the sum of the score for all locations, which is not what I want (I want the sum for locations in the cluster)

Try wraping it with another select, and do the group by in the outer select .... SELECT sum,cluster FROM(YOUR QUERY) group by cluster — sagi, Feb 18 '16 at 13:39
Can't get it working. In order to sum in my outer query, I need either to group or to aggregate the score attribute in my inner query (because ST_Clusterwithin is already an aggregate function) — Chris, Feb 18 '16 at 13:51

score 6 · Accepted Answer · edited Apr 13 '17 at 12:33

This is a tricky one and the st_clusterwithin api doesn't seem well designed for what should be a common case.

The only solution I could find was to rejoin back on the 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've changed ST_CollectionHomogenize to ST_CollectionExtract(<geometrycollection>, 1) (Pick 1 for point, 2 for linestring and 3 for polygon) as suggested in this answer: https://gis.stackexchange.com/questions/195915/ because of this bug: https://trac.osgeo.org/postgis/ticket/3569

Don't ask me why you can't do ST_Contains(<geometrycollection>, <geometry>); We need to convert to a multipoint which is permissable as an argument.

Meta: this question would have been a great match for https://gis.stackexchange.com/

Should that be "FROM locations," instead of "FROM location," in the outer select? — Vesanto, Nov 22 '16 at 14:28

score 2 · Answer 2 · answered Feb 03 '17 at 18:50

With PostGIS 2.3, one might profit from the ST_ClusterDBSCAN function (the choice of the third parameter reduces it to hierarchical clustering) which returns directly 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

PostGis clustering with other aggregate

2 Answers2