How to UPDATE a column with cluster IDs in PostGIS?

Question

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?

Answer 1

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

ALTER TABLE point
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;