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

  1. id_dir
  2. geom
  3. 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);
  • 1
    what doesnt work? do you get an error? – ziggy Nov 26 '19 at 14:43
  • ERROR: la columna «dir_v2.id_dir» debe aparecer en la cláusula GROUP BY o ser usada en una función de agregación LINE 2: (SELECT id_dir,geom, ^ SQL state: 42803 Character: 45 – Marcelo Esteban Lagos Bustaman Nov 26 '19 at 14:45
  • in english please – ziggy Nov 26 '19 at 14:52
  • ERROR: The column "dir_v2.id_dir" must appear in the GROUP BY clause or be used in a LINE 2 aggregation function: (SELECT id_dir, geom, ^ SQL state: 42803 Character: 45 - – Marcelo Esteban Lagos Bustaman Nov 26 '19 at 14:53

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 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;
  • I need the dbscan to be processed by commune. that in each commune perform the clustering – Marcelo Esteban Lagos Bustaman Nov 26 '19 at 15:31
  • @MarceloEstebanLagosBustaman that is exactly what my answer is doing. Have you tried it? – JGH Nov 26 '19 at 15:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.