ARC/INFO Functions – UNION
We are going to conclude our overlay posts with the ARC/INFO UNION command. This should be a lot easier than it actually is. I think the problem is that Manifold may have a bug in the ClipSubract command. What I discovered was that the ClipSubract clause only seems to work on the first geometry in a layer. With that error, it would appear that we are dead in the water. However, if Manifold’s ClipSubract only works on the first geometry, well, we can manipulate the query to pass ClipSubract a single geometry. To do that, we use the UnionAll command to take all the geometries in one layer and union them together into a single geometry.
So, the ARC/INFO UNION command in SQL needs three things:
1. The intersection of the two layers (to get the intersection of the two layers) 2. The clipsubtract of layer 1 and layer 2 (to get the part of layer 1 not intersecting layer 2) 3. The clipsubract of layer 2 and layer 1 (to get the part of layer 2 not intersecting layer 1)
But, we are still not out of the woods yet. We need to attach the attributes to the layers. To do that, we have to fake out Manifold a little to think that we actually have a unique ID for the the subtract layer. The ClipSubract clause correctly clips the geometries, but it will not return the ID for the subtract layer since we UNIONEDed the entire thing into a single geometry. So, to fake Manifold out, we return a value of 0 as the id field for the layer.
SELECT * FROM ( SELECT ClipIntersect(circle.id,rectangles.id) AS g, circle.id AS cid, rectangles.id AS rid FROM circle, rectangles UNION ALL SELECT Clipsubtract(circle.[Geom (I)],(SELECT UnionAll(id) FROM rectangles)) AS g, circle.id AS cid, 0 AS rid FROM circle UNION ALL SELECT ClipSubtract(rectangles.[Geom (I)],(SELECT unionall(id) FROM circle)) AS g, 0 AS cid, rectangles.id AS rid FROM rectangles ) LEFT JOIN [circle] ON circle.id = cid LEFT JOIN [rectangles] ON rectangles.id = rid