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.