PostGis clustering with other aggregate

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)

---

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/

---

使用PostGIS 2.3，人们可以从ST_ClusterDBSCAN函数中获益（第三个参数的选择将其减少为层次聚类），该函数直接返回相应的聚类索引：

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

链接地址: http://www.djcxy.com/p/32176.html

上一篇: 在Zuul代理的背后，Spring重定向url问题

下一篇: PostGis与其他聚合集群