Classic ARC/INFO Commands in PostGIS: INTERSECT

Thinking back, the ARC/INFO INTERSECT command is probably where I fell in love with GIS. I think what I loved more than the function itself was the circle and squares they used in the old ARC/INFO user manual. The new user manual sort of replicates that, but not as nicely as the original I'm afraid:

Once again, I digitized my own example to look like Esri's:

Let's look at the query to perform the topological intersection of the two layers.

The key here is using the PostGIS ST_Intersection command. You can see the ST_Intersection used in line 3, along with a RIGHT JOIN. Like we've done in previous examples, you can omit the RIGHT JOIN, and explicitly select fields from both layers.

But what's up with lines 5 and 8? Well, like any computer program, PostGIS is going to do whatever you ask, and won't ask any questions. The inner portion of this query in lines 2-4 intersects every area with every other area. So, if two polygons don't intersect one another, PostGIS is going to perform the operation any way and simply return a null value. In fact, it's going to return a lot of null values. So, we include ST_Intersects in the WHERE clause so that the computationally expensive ST_Intersection command won't get kicked off for those geometries. In Line 9, we are adding another WHERE clause that makes sure a null geometry isn't returned.

The final result looks like this:

with a table of two records: