How to force sql server to use the spatial index?

Question

I'm beginner with SQL Server (2012). I created a spatial index, and I'm trying to force SQL Server to use that index to solve the query.

Here's what I have:

DECLARE @g geography

SET @g = geography::STPointFromText('POINT(-79.25161 43.735965)', 4326)

SELECT *

FROM CUSTOMER

WITH(INDEX(idx_Geography))

where HomeGeog.STDistance(@g)

The spatial index (idex_Geography) is created for HomeGeog.

I'm getting this error: Msg 102, Level 15, State 1, Line 5 Incorrect syntax near '('.

I'm not sure where the error is...

Thanks for you help!

Answer 1

The issue is with your use of STDistance rather than the index. This function calculates the distance between two points so you need to replace HomeGeog with a feature rather than the name of the table.

E.g. @g.STDistance(@g) should work (but always return 0)

What are you trying to return from your query?

See http://msdn.microsoft.com/en-us/library/bb933952.aspx for more details on how to use the STDistance function.