Filter clusters in PostGIS by size

Question

I am clustering points in a table based on their Mercator distance (~10km, it doesn't have to be that precise, so I'm fine with projection distortion). What I want to achieve is to only query the clusters which contain multiple points.

I have come up with this query:

select * from 
(SELECT unnest(ST_ClusterWithin(ST_Transform(geom, 3857), 10000)) gc
FROM mils) f 
where ST_NumGeometries(gc) > 1;

Which does exactly what I want. However, this will do the clustering first on the whole table, and only then filter on the results.

My question is: is this effective enough / the way it should be done, or is there a more efficient way of filtering clusters on the fly?

Please note that the table is not that big, its size is usually between 500 and 10000, tops. Also, I have added a spatial index on the column geom.

Answer 1

For tables of those sizes it won't matter much, but you should use ST_ClusterDBSCAN instead, using the minpoint parameter to control clustering; as a window function, it should also be faster and can be used to cluster by multiple attributes (specify in the ... OVER(PARTITION BY ...) ... clause).

SELECT *,
       ST_ClusterDBSCAN(ST_Transform(geom, <srid>), 10000, 2) OVER() AS clst_id
FROM mils;

will assign clst_ids as a new column while keeping the original table structure. Set the minpoint parameter accordingly to only create clusters (and assign ids only to those points, all others will be assigned NULL) with that minimum point density (2 in above example, suiting your request). Note that I left out the SRID; don't use WebMercator for anything distance related, project to the correct UTM Zone or an other suitable projection that fits your area.