The ARC/INFO IDENTITY operation seen here is sometimes confusing because it takes ALL of the Input feature, and the part of the IDENTITY feature that intersects the input feature and merges it into a new feature class. I recreated the features in the ESRI help documentation so that we will work with a feature class called “Circle”, and one called “Rectangles”.
To show how this is done in SQL, we should simply focus on the geometry aspect first, then bring the attributes in later.
SELECT ClipIntersect(circle.id,rectangles.id) g, int(circle.id) AS cid, int(rectangles.id) AS rid FROM circle, rectangles UNION ALL SELECT ClipSubtract(rectangles.id,circle.id) AS g, int(circle.id) AS cid, int(rectangles.id) AS rid FROM circle, rectangles
The IDENTITY operation takes two geometric operations. First, we have to Intersect the two features to find the intersection area. Then, we have to perform a ClipSubract to only include those areas of the input feature. Now, the UNION ALL clause in SQL creates a new table, and sticks it under the previous table – therefore, we must have the same columns in order to do it. The above SQL will create a geometric representation like this one:
So, this is what it should LOOK like, but the real power is that the attributes from the inputs are retained in the output. To do that, we take our above query, and just wrap it in a RIGHT JOIN clause:
SELECT * FROM ( SELECT ClipIntersect(circle.id,rectangles.id) g, int(circle.id) AS cid, int(rectangles.id) AS rid FROM circle, rectangles UNION ALL SELECT ClipSubtract(rectangles.id,circle.id) AS g, int(circle.id) AS cid, int(rectangles.id) AS rid FROM circle, rectangles ) RIGHT JOIN [circle] ON circle.id = cid RIGHT JOIN [rectangles] ON rectangles.id = rid WHERE IsArea(g)
Go ahead, and give it a try with the example I have on my website. Also, don’t forget our idea of spatial is not special. This IDENTITY command isn’t some self contained function for which you have no control – you can always add other interesting clauses in the WHERE statement to select out certain features first, or some other interesting query.
Note: A recent discussion on georeference.org illustrated that a lot of null values were returned. The reason for this is because when we ask to return a ClipIntersect, if two objects don’t intersect, we’ve still asked the query engine to give us that result, and the result is in fact a null value. So, I’ve added one line of code to only return the geometry if it is an area feature – I’ve written this in RED. That is all you need, and it will work with more complicated features.