Classic ARC/INFO Commands in PostGIS: UNION

Having walked through the ARC/INFO INTERSECT and IDENTITY command, we'll now move on to UNION. In this case, we keep the polygons of the input layer and those of the output layer that intersect it. We'll follow the same shapes that Esri users in their example.

Once again, I digitized my own example to look like Esri's:

Let's look at the query to perform the topological union of the two layers. This one is trickier than the somewhat complicated IDENTITY command. We need to determine what UNION actually is. Truth be told, it is an intersection of two layers and the difference from layer A->B and another difference for layer B->A

Similar to what we did with IDENTITY, we'll write multiple queries to assemble the topological union. Let's look at each one in turn:

Lines 1-6: This is the ST_Intersection that we did in the previous post. So, that gets us the intersection of the circle and rectangles. We also grab whatever fields from rectangles and circle that we want.

Lines 10-16: Hold on to your hats - this gets tricky. Here we'll use ST_Difference to get the parts of circles that do not intersect the rectangles. But, we need to "dissolve" the polygons of the rectangles so that we can get the portion of the circle that is not intersecting the rectangles. And, we know that the ST_Difference is going to completely exclude the rectangles, so we simply null out the attributes for the rectangle fields.

Lines 20-26: I'm guessing you see where we're going with this. This is identical to lines 10-16 but instead, we are doing an ST_Difference on rectangles as the focus layer. So, once again, since we know that there are no circle polygons being returned, we will null those out.

So think about what we have - this intersection and the difference:

By unioning the two queries together we get the final results:

and we have the resulting table of 5 records: