Classic ARC/INFO Commands in PostGIS: IDENTITY

Having walked through the ARC/INFO INTERSECT command, we'll now move on to IDENTITY. 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 identity of the two layers. This one is much more complicated than the simple INTERSECT command.

While the SQL looks complex, it's really two queries, unioned together, that are almost identical.

Lines 1-7: 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 11-17: This is using ST_Difference that gets the parts of rectangles that do not intersect the circle. And again, we are grabbing the columns we want. UNION ALL requires that the two tables have the same fields (we've used the RIGHT JOIN in the past, and that is certainly acceptable, but this is easier to conceptualize. One thing we know about the ST_Difference command is that we don't have anything returned from the circle layer, so we simply assign those fields a null value.

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

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

along with the associated attribute table: