ARC/INFO Functions in SQL – INTERSECT

I promised you that I would address the attributes.  Remember, our previous query only performed the ClipSubract for the geometries, and did not add any additional attribute columns.  There are two ways to add the attributes.  The simplest way is to just call the columns individually or with a *:

SELECT ClipSubtract(square.[Geom (I)],box.[Geom (I)]) AS g, square.*
FROM square, box

That works fine for this example, but when we start merging attributes from multiple tables, things get a little trickier.  So, we can perform a RIGHT-JOIN on the resulting geometric table to stuff the attributes at the end of the table.  For example, to add the attributes, we simply write:

SELECT * FROM
(
SELECT ClipSubtract(square.[Geom (I)],box.[Geom (I)]) AS g,
square.id AS sid
FROM square, box
)
RIGHT JOIN [square] on square.id = sid

Here we are returning the geometric erase, but also the unique ID value and calling that sid.  The RIGHT JOIN clause takes the the attributes from the original [square] table and links it back in to the resulting geometric result in the inner portion of the query.

OK, now lets do the same thing with INTERSECT (here we will use the CIRCLE and RECTANGLE shapes that are like the ESRI help example:

SELECT * FROM
(
SELECT ClipIntersect(a.id,b.id) AS g, int(a.id) cid, int(b.id) AS rid
FROM circle AS a, rectangles AS b
)
RIGHT JOIN [circle] ON circle.id = cid
RIGHT JOIN [rectangles] ON rectangles.id = rid

Since we are dealing with more than one table, I decided to make things a little more generic and renamed the individual geometry tables a and b so to distinguish them from the original [circle] and [rectangles] tables.  Also, since we are dealing with more than one unique ID, I decided to call the [Circle] ID value cid, and the [Rectangles] ID value rid.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s