I have a table Points with shape column for points of geography type, and a spatial index on the column to improve performance..

CREATE TABLE  Points(id VARCHAR(10) PRIMARY KEY, shape geography);
CREATE SPATIAL INDEX SIndx_Points_shape ON Points(shape);

I am trying to retrieve all intersecting pairs of points from the Points table with polygon shapes from shape column of type geometry in another table named Grids . Grids table also has a spatial index to optimize spatial queries.

INSERT INTO Intersections(point_id ,grid_id )
SELECT P.id , G.id
FROM Points P
INNER JOIN Grids G
ON G.SHAPE.STIntersects(geometry::STGeomFromWKB(P.shape.STAsBinary(),4326)) = 1

However, the execution plan indicates a Clustered Index seek on the Grids table, but it doesn't seem to use the spatial index on the Points table, which might affect query performance.

I attempted to use a query hint to specify the index, like this:

OPTION (TABLE HINT(P, INDEX (SIndx_Points_shape )))

but it throws an error

Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN.

Any ideas on why the query optimizer does not use the spatial index on the Points table and how to ensure that the optimizer utilizes the index for better performance.

Update:

• Tried using Points table with shape column of type geometry and compare directly so that the types are compatible. Produced same exact execution plan and time, with Clustered Index seek on the Grids table only

SELECT P.id , G.id
FROM Points P
INNER JOIN Grids G
ON G.SHAPE.STIntersects(P.shape) = 1

• Attempted an index hint on Points table column, Execution plan now only does Clustered Index seek on Points table replacing Grids table index.
• Attempted an index hint on both indices, it throws same error.

OPTION (TABLE HINT (G, INDEX(S7434_idx)), TABLE HINT (P, INDEX(SIndx_Points_shape)))

spatial index on Grids table

CREATE SPATIAL INDEX [S7434_idx] ON Grids ( [SHAPE] )USING GEOMETRY_AUTO_GRID WITH (BOUNDING_BOX =(-120.005720158, 25.837266505, -92.408500871, 49.00098643), CELLS_PER_OBJECT = 8, PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

--