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?