2

I need to write a spatial query which will return centroids of clusters which are within a bounding box or polygon.

The query I have now is:

SELECT
    ST_NumGeometries(unnest(ST_ClusterWithin(ST_PointFromText(centroid), 10))) AS venueCount,
    ST_AsText(ST_Centroid(unnest(ST_ClusterWithin(ST_PointFromText(centroid), 10)))) AS clusterCentroid
FROM venue_locations
WHERE ST_MakeEnvelope (...) ~ ST_Centroid(unnest(ST_ClusterWithin(ST_PointFromText(centroid), 10)));

This, however, gives me the error:

ERROR: aggregate functions are not allowed in WHERE

If I replace WHERE with HAVING, I get the following error:

ERROR: argument of HAVING must not return a set

How can I write this to be efficient for large databases?

EDIT: to clear up any confusion, what I am trying to accomplish it to get clustered points from the database which are in a specific extent.

  • It is very unclear what you are trying to do with the WHERE clause. The clustering algorithms implicitly do that for, so you are repeating logic (as well as returning a set from the WHERE, as the query planner is telling you. – John Powell Oct 27 '17 at 8:20
1

It's unclear to me what you are trying to do here, but if you need to do an unnest, you are better off using ST_ClusterDBScan if you have PostGIS 2.3+. I'm also not clear why you have two calls to ST_ClusterWithin each with different distance.

http://postgis.net/docs/ST_ClusterDBSCAN.html

WITH c AS (SELECT ST_ClusterDBScan(centroid,100,10) AS cluster_num, vl.geom
    FROM venue_locations AS vl
)
SELECT COUNT(c.geom), cluster_num, ST_Centroid(ST_Collect(c.geom)) AS mgeom
FROM c
GROUP BY cluster_num
HAVING ST_MakeEnvelope (...) && ST_Centroid(ST_Collect(c.geom)) ;

You might also want to consider using ST_GeometricMedian instead of ST_Centroid. ST_GeometricMedian is also new in PostGIS 2.3

http://postgis.net/docs/ST_GeometricMedian.html

  • The two different distances are a copying mistake. I’m going to edit the question to make it more clear what I am trying to do. – Dino Prašo Oct 26 '17 at 16:42

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.