I am looking for a way to spatially clusters thousands of datapoints (potentially millions) based on distance, such that each cluster contains less than 5000 points.
This is a similar question to Problems with ST_ClusterDBSCAN cluster sizes . I would like to build upon the provided answer by using WITH RECURSIVE to automatically continue splitting clusters until they are all bellow a size.
This is the query I came up with (not complete):
WITH RECURSIVE clusterize(cid, csize, autopoi_ids, eps) AS ( SELECT cid, csize, unnest(poi_ids) as poi_id, eps FROM ( SELECT cid, count(*) as csize, array_agg(id) as poi_ids, 0.05 as eps FROM ( SELECT id, ST_ClusterDBSCAN(geometry, eps := 0.05, minpoints := 3) over () AS cid FROM stats_autopoistat ) clusters GROUP BY cid ) q UNION ALL SELECT cid, csize, unnest(poi_ids) as poi_id, eps FROM ( SELECT cid, count(*) as csize, array_agg(id) as poi_ids, ( SELECT eps/2.0 FROM clusterize LIMIT 1 )/2.0 as eps FROM ( SELECT id, (SELECT max(cid) FROM clusterize) + ST_ClusterDBSCAN(geometry, eps := ( SELECT eps/2.0 FROM clusterize LIMIT 1), minpoints := 0) over () AS cid FROM clusterize WHERE csize > 5000 ) clusters GROUP BY cid ) q ) SELECT * -- here filter out non-max cids for each poi_id FROM clusterize limit 1000
However, it seems I am unable to refer to the recursive CTE inside a subquery, as Postgres complains with:
ERROR: recursive reference to query "clusterize" must not appear within a subquery LINE 15: ..., array_agg(id) as poi_ids, ( SELECT eps/2.0 FROM clusterize...
I would like to know if this can even be come with WITH RECURSIVE given the limitations I encountered above.
The reason I want to accomplish this within Postgres and not Python is that the number of points to cluster will continue increasing. The table already has about 1 million rows, and I would like to avoid loading all this data into Python.