Processing dbscan by group

Question

I am new to PostGIS and I need to do DBSCAN by commune.
I have the base dir_v2:

id_dir
geom
commune

I need to process clustering at least 1000 points in a radius of 1000 meters per commune but it doesn't work
CREATE TABLE cob_fibra_cluster_2 AS (
  SELECT id_dir,geom,
    ST_ClusterDBscan (ST_Transform (geom,32719),1000,1000) OVER () AS id_cluster
  FROM dir_v2 
  GROUP BY commune);

JGH · Answer

DBScan should run by commune directly, and not on the entire table + grouped by commune.

The window function must be configured. Note that the cluster ID will start at 0 again for each different commune. You may also want to filter for NULL cluster ID (i.e. points that are not part of a cluster)

SELECT * FROM (
  SELECT id_dir, commune, geom,
         ST_ClusterDBscan (ST_Transform (geom,32719),1000,1000) OVER (PARTITION BY commune) AS id_cluster
  FROM dir_v2) dc
WHERE dc.id_cluster IS NOT NULL;

Processing dbscan by group

One Answer

Add your own answers!

Ask a Question