Identify groups of stacked geometries in PostGIS

Question

I need to create a column in my PostGIS table with values identifying different groups of stacked/duplicate geometries, such that one group of stacked geometries might get a value of 1, another group a value of 2, and so on. I've come across this post but it indicates how to delete duplicates while saving one, but I do not want to delete the duplicates. I also do not just want to identify duplicates with a boolean value, as this would not distinguish between groups.

Answer 1

1. Make a set of unique geometries by deleting duplicates (following the example you have already found - create as a new table).
2. In the new table, create an attribute to uniquely identify each polygon (you could do this as part of step one by adding a serial field).
3. Use the new table to do a spatial join on the original table. this will add the new unique polygon id to the original data and thereby every one of your duplicate geometries in a given 'stack' or group will get the id.