SQL Spatial Select Record Where Intersects Another Record

I’ve always been one to query GIS data from with ArcGIS, but last month I found myself needing a query that would produce a table/report output rather than a spatial result (map, layer, etc.) so I decided to get into SQL and figure out if I could do a spatial query.

I had two tables – ‘Tree’ (point) and ‘ServiceArea’ (polygon) in the GIS database. Trying to select all Tree records that intersect a ServiceArea where the value in Tree.ServiceAreaNumber field doesn’t equal the value in the intersecting ServiceArea.ServiceAreaNumber field.

Found that the documentation and other online search results I found were not that helpful – they all wanted me to specify coordinates direct in the query and not pull them from another record.

Figured it out eventually though:

SELECT
    T.TreeID,
    T.ServiceAreaNumber AS RecordedServiceArea,
    S.ServiceAreaNumber AS ActualServiceArea
FROM
    TREE T,
    SERVICEAREA S
WHERE
    T.Shape.STIntersects(S.Shape) = 1 AND T.ServiceAreaNumber != S.ServiceAreaNumber