# 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

## 4 thoughts on “ARC/INFO Functions – UNION”

• Yes I did, and redirected them to this post.

1. Hi Art, yes I think there is a bug in the ClipSubtract function in that it does not return polygons from the layer to be clipped which are disjoint with the 2nd layer. However, the ClipSubtract function does appear to work on more than 1 geometry as long as the polygons touch. For example, if there are 3 rectangle in the rectangle layer with 2 of the rectangles overlapping the circle in the circle layer and 1 rectangle which is disjoint then the following query will return the 2 rectangles clipped to the circle but will not return the rectangle which does not overlap the circle:
SELECT
[rectangles].ID,
[circle].ID,
ClipSubtract(rectangles.[ID], circle.[ID]) as g
FROM [rectangles] INNER JOIN [circle]
on Touches (rectangles.[ID], circle.[ID]);

• Just a correction to my previous post: I meant to say “the query returns the 2 rectangles with the circle area clipped out, but will not return the rectangle which does not touch the circle (the disjoint rectangle).