I have a table with points in it, and I want to cluster them. I had created a separate table to hold the clusters, but I ended up copying all the data from the original table into the new cluster table. Instead, I would like to add a cluster id (cid) column to the original table, and update it with the cluster id. I tried

INSERT INTO spatial_data (cid) 
SELECT st_clusterdbscan(geog::geometry, eps := 0.01, minPoints := 5) over() as cid 
FROM spatial_data;

but it added a lot of new rows that were empty except for the cluster id.

I suspect I need to use the update command instead, but I don't know how to write the WHERE clause.

What is the correct way to populate the new cluster id column?


You can do this. First, create the column in your table.

ADD COLUMN cid integer;

Then update your table based on your query. When you do your DBScan query. Make sure you select an id field that can be matched back to the points.

SET cid = subquery.cid
FROM (SELECT id, st_clusterdbscan(geom, eps := 0.01, minPoints := 5) over() as cid 
      FROM schema.point) AS subquery
WHERE point.id = subquery.id;
  • 2
    You may want to include the UPDATE spatial_data statement – JGH Mar 6 '19 at 16:59
  • Do I need to add an id field, or is it safe to match each individual field like so, WHERE point.f1 = subquery.f1 and point.f2 = subquery.f2 and ... – Craeft Mar 7 '19 at 14:17
  • 1
    You shouldn't need to add an id field, that was just an example I used. Your f1 field is fine if it's unique. – Edmond Mar 7 '19 at 20:51

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.