I am trying to split polylines by points which have a small buffer around them. Currently I have over 370,000 lines and 320,000 nodes and the query is running really slowly (I've left it for 3 days and it still hasn't completed). I have tried forcing a spatial index using with (Index(SI_tempPD)) but I get the following error:
"The query processor could not produce a query plan for a query with a spatial index hint. Reason: Could not find required binary spatial method in a condition. Try removing the index hints or removing SET FORCEPLAN."
Below is the snippet of code that I'm trying to run when I get the error:
BEGIN INSERT INTO TempLines ( [linenum] ,[ogr_geometry] )
SELECT lines.[linenum] ,lines.[ogr_geometry].STDifference(points.[ogr_geometry].STBuffer(0.005))
AS ogr_geometry FROM dbo.TemplineData AS lines with(Index(SI_tempPD)) INNER JOIN dbo.[TemplineNodes]
AS points ON lines.[ogr_geometry].STIntersection(points.[ogr_geometry]).STDistance(points.[ogr_geometry]) < 1
WHERE (lines.[linenum] <> points.[linenum]) END
Is there anyway i can speed up the query?
I also have a clustered primary key. The execution plan shows that a filter takes up 36% of the cost and the insert takes up 64%
I'm using SQL Server 2008 (SQL server Management studio 10.50.1600.1)
Best Answer
You must follow the rules. If your query does not hit every point in Microsoft's list, the spatial index will not be used by the query.
See https://msdn.microsoft.com/en-us/library/ff929109.aspx
One thing you are doing wrong: You must include in your WHERE clause a test that either excludes distances calculations that return null or has blah.STDistance(blah) < somenumber. It must be the first condition in the where clause. Putting it in the JOIN ON clause won't work.