How to use ST_Contains() with ST_ClusterWithin()

Question

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.

Answer 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