Using a Where Statement in a SQL Spatial Join

I had a question the other day about how to incorporate a where statement into a spatial join (STIntersects).  Unfortunately, the examples I had previously posted didn’t cover that topic.  Well, here is an example of querying two tables, using the geometry data type in each, along with a where statement, to find all results that meet the defined spatial conditions:

select PP.*, SP.*
from Populated_Places PP
inner join States_Provinces SP with(Index(geom_sidx))
on PP.geom.STIntersects(SP.geom) = 1
where SP.NAME_0 = 'United States of America' 
and SP.NAME_1 = 'Massachusetts'

Let’s breakdown this query:

• The query starts by calling all the columns from two different tables, a table with point data called Populated_Places, and a table containing polygon data called State_Provinces.
• The query then performs an inner join between the two tables, In the inner join I specifically call the spatial index from the States_Provinces table.  I call this index because I want to use it, and because query optimizer may skip it, as it does with many spatial indexes.
• In the ON statement I set up the STIntersects statement between the point table and the polygon table, only returning the records that intersect.  This is done by setting the Boolean requirement at the end of the STIntersects statement to 1.  I also make sure to set the correct column in each table that contains the spatial  datatype, which in this case is called geom for both tables.
• Now, here comes the where statement,which is about as vanilla as vanilla can be.  In the where statement I simply limit the polygons that are eligible for the query based on some defined values.

This type of query will also work with line or polygon features, as demonstrated in the following picture.  In this example I query all the roads that intersect a polygon defined in the where statement.  You will notice that I have the spatial results tab open to display the intersecting line features.

This is a pretty simple example, but if you have never worked in this medium it can be a little confusing at first.  Have any more spatial sql questions?  Let me know.  I need to keep sharp.