According to Microsoft docs, spatial indexes will be used with geography types on the following methods when they appear at the beginning of a comparison predicate with a
Only geometry types' methods (restricted list) will trigger use of spatial index in
JOIN ... ON, so change your code to use
WHERE geog1.STIntersects(geog2) = 1 and that should improve speed.
I also recommend taking advice in g2server's answer and add the following for filtering and add spatial index on it
ALTER TABLE [dbo].[T_POLYGON] ADD SimplePolysGeog AS
you could then have a query like the following (i wrote this post quickly and have not yet tested, this is just something to try because I saw that your query and the highest posted answers use JOIN ON spatial op = 1 which will not use a spatial index):
FROM T_Polygon p2
WHERE p2.coords.STIntersects(t.coords) = 1),
FROM T_PIN t
FROM T_POLYGON p
WHERE t.coords.STIntersects(p.SimplePolysGeog) = 1) = 1
FYI: The above does not work if
SimplePolysGeog end up overlapping (as in a pin can be in two simplified geogs, just ran this on people in precincts in a state and since normal polys share boundary, the bounding boxes overlapped), so in most use cases, it will throw an error that subquery returned more than one result.
From MS Docs' Spatial Indexes Overview:
Geography Methods Supported by Spatial Indexes
Under certain conditions, spatial indexes support the following set-oriented geography methods: STIntersects(),STEquals(), and STDistance(). To be supported by a spatial index, these methods must be used within the WHERE clause of a query, and they must occur within a predicate of the following general form:
To return a non-null result, geography1 and geography2 must have the same Spatial Reference Identifier (SRID). Otherwise, the method returns NULL.
Spatial indexes support the following predicate forms:
Queries that use Spatial Indexes
Spatial indexes are only supported in queries that include an indexed spatial operator in the WHERE clause. For example syntax such as:
[spatial object].SpatialMethod([reference spatial object]) [ = | < ] [const literal or variable]
The query optimizer understands the commutativity of spatial operations (that
@a.STIntersects(@b) = @b.STInterestcs(@a) ). However, the spatial index will not be used if the beginning of a comparison does not contain the spatial operator (for example
WHERE 1 = spatial op will not use the spatial index). To use the spatial index, rewrite the comparison (for example
WHERE spatial op = 1).
The following query will work if
;WITH cte AS
INNER JOIN T_POLYGON
ON T_PIN.COORD.STIntersects(T_POLYGON.SimplePolysGeog) = 1
INNER JOIN cte
ON T_PIN_PIN_ID = cte.PIN_ID
where cte.[COORD].STIntersects(T_PIN.COORD) = 1