ARC/INFO Functions in SQL – ERASE, INTERSECT, IDENTITY, UNION

Today we are beginning another four-part session on the classical ESRI topological overlay functions of Erase, Intersect, Identity, and Union.  If you were an old ARC/INFO user, this is what made you get into GIS in the first place.  In fact, in the 1980s, if someone had a GIS the first thing you asked them was “can you do an INTERSECT?”  If you are not familiar with this, please, please, please visit the ESRI online help to learn about the functions here.

OK, a little name dropping here…..

In 1990 I was in Redlands, CA benchmarking ARC/INFO.  We had a really “difficult” task for them: overlay 25,000 hexagons with 25,000 triangles.  They were running it on a Sun  Sparcstation 1 – so, back then, this was very difficult.  They started the task before lunch, and it was done when we got back – very impressive.  After the two day benchmark was over, we were off to Ottawa to visit with GeoVision to run the benchmark there.  As we were leaving, Jack Dangermond grabbed my arm and said “you make them do the intersection test“.  I curtly nodded, and he pointed his finger at me and reiterated “no, I’m serious, you make them do it“.

Lo and behold, when the time came to run the test, the President of GeoVision acknowledged that based on their timing estimation, it would take around 3 days to complete the task – so they opted not to do it.  Back then, ARC/INFO was the only software that could do a task like that.

Now of course, other software products can do these tests, but I don’t think there is anything as fast as ARC/INFO doing overlays on a COVERAGE.

I have a Manifold .map file located here.  I’ve attempted to create the same shapes as is illustrated in the ESRI user manual.  Following the order in the ESRI manual, lets tackle ERASE first.

I did some heads-up digitizing of the box and the square used in the ESRI help file:

squarebox

The SQL for performing the ERASE is:

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

That’s it!  The result looks like this:

erase

Now, we haven’t done anything with the attributes, and this post has gotten a little long due to my earlier self-aggrandizing statements.  So I’ll tell you what – before we do INTERSECT in the next post, I will address the attributes.

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