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)

  • 3
    You also asked this on StackOverflow -- in general, I believe cross-posting on multiple Stack Exchange sites is discouraged.
    – Erica
    Feb 5 '15 at 11:52

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.