locked
Spatial index not working here? RRS feed

  • Question

  • I'm using Sql Server 2012 Standard Edition. 

    I am getting the error: The query processor could not produce a query plan for a query with a spatial index hint.  Reason: Spatial indexes do not support the comparator supplied in the predicate.  Try removing the index hints or removing SET FORCEPLAN

    I have a temp table with a geography spatial index. Here is my query:

    SELECT
    	m.LocationNumber,
    	closest.LocationNumber
    FROM @MultiFieldLocationNumbers m
    CROSS APPLY (
    	SELECT TOP 1
    		#officialLocations.LocationNumber
    	FROM
    		#officialLocations WITH(INDEX(SpatialIndex)) 
    	INNER JOIN
    		RigData.dbo.Location l ON m.LocationNumber = l.LocationNumber  
    	WHERE #officialLocations.Geography.STDistance(l.Geography) IS NOT NULL
    	ORDER BY #officialLocations.Geography.STDistance(l.Geography) ASC
    ) closest


    Please help!



    • Edited by MikeTG22 Tuesday, March 12, 2013 12:53 PM
    Tuesday, March 12, 2013 2:44 AM

All replies

  • You're not using any predicates that would make use of a spatial index (STDistance, STContains, STIntersects etc.), so there's no reason (and, in fact, it's incorrect) to force the use of a spatial index.

    Get rid of the WITH(INDEX(SpatialIndex))


    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    Tuesday, March 12, 2013 5:52 PM
    Answerer
  • Thanks for the response tanoshimi, but how is my WHERE clause not making use of the spatial index? It uses STDistance...

    My query uses the same structure as the one on the MSDN page that describes how to use spatial indexes with the nearest neighbor query: http://msdn.microsoft.com/en-us/library/ff929109.aspx

    Am I missing something?




    • Edited by MikeTG22 Tuesday, March 12, 2013 6:49 PM
    Tuesday, March 12, 2013 6:45 PM
  • I fixed it, but I don't know why this fixes it. I had to remove the INNER JOIN from the subquery, and place it in the outer query, like so:

    SELECT
    	m.LocationNumber,
    	closest.LocationNumber
    FROM @MultiFieldLocationNumbers m
    INNER JOIN
    	RigData.dbo.Location l ON m.LocationNumber = l.LocationNumber  
    CROSS APPLY (
    	SELECT TOP 1
    		#officialLocations.LocationNumber
    	FROM
    		#officialLocations WITH(INDEX(SpatialIndex)) 
    	WHERE #officialLocations.Geography.STDistance(l.Geography) IS NOT NULL
    	ORDER BY #officialLocations.Geography.STDistance(l.Geography) ASC
    ) closest


    When I did that, the error no longer happened. I guess this is a restriction of the nearest neighbor execution when trying to use a spatial index.


    • Edited by MikeTG22 Tuesday, March 12, 2013 7:35 PM
    Tuesday, March 12, 2013 7:35 PM
  • Ah sorry - my mistake. Should probably have put my glasses on before trying to reply!

    twitter: @alastaira blog: http://alastaira.wordpress.com/ | Pro Spatial with SQL Server 2012

    Tuesday, March 12, 2013 11:02 PM
    Answerer