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.

  • 1
    Your question is rather unclear. As it is now all you would have to do is select SUM(value) along with whatever aggregate function you're using on the polygons. Could you provide the input data and desired output? – Jakub Kania Jul 10 '14 at 23:08
  • 1
    Do you want a count of overlaps for each polygon: i.e feature 1, intersects 8 other features, and that value (8) is recorded in feature 1's attribute table? Or were you wanting something more like map algebra, where intersecting areas are added together to form something like a statistical surface? – GISI Jul 11 '14 at 8:03
  • @GISI I think the latter: "something more like map algebra, where intersecting areas are added together to form something like a statistical surface" Thank you! – DPSSpatial Jul 11 '14 at 14:21

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.

| improve this answer | |
  • It probably won't work in practice because I've found the "decompose to lines, rebuild to polygons" step to be inconsistent in regenerating the same areas as were input. – Paul Ramsey Jul 11 '14 at 14:44
  • Thanks @Paul Ramsey ... I think that's as good as things are going to get... We'll try this and report back. – DPSSpatial Jul 11 '14 at 15:35

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.