postgis - How to union polygons and add attribute values of combined output features

I have a layer containing 10 polygons all overlapping at various locations.

Each polygon has a value of '1' in a column called "value"

How do I Union the polygons (rows) together such that the values in the "value" column are added for each output polygon?

What I'm after is the ability to add the values from the columns of the input features such that a combined overlay is produced. What I mean is, where 8 polygons overlap within the feature, the output union geometry has a value of "8".

And yes, this can be done using raster, but is there a way to do this using vector and SQL Spatial?

I would like to to this using SQL Spatial, ie. using PostGIS or SQLite.

There are discussions on this site on how to Union polygons together, but how to combine values from the inputs is something I cannot find.

Answer

This probably won't work in practice, but it does in theory:

• ST_DumpRings to get all the linework from your polygons


• ST_Union the rings to get a single set of boundaries


• ST_Polygonize the linework to get a complete set of areas bounded by the linework


• ST_PointOnSurface the areas to get points


• ST_Intersects the points with the original polygons and Count(*) to find out how many original polygons overlap the areas

The first three steps are about extracting what would be called the "union" in ESRI terms or the "overlay" in topology terms of the inputs. As a pure spatial operation, it doesn't retain any information about the input polygons other than their bounds.

The second two steps are about joining the new spatial layer back to the old one to extract information from it. In this case, the number of times each old polygon overlaps one of the new ones.