quote:

---

Originally posted by ED_KING

I have a query being ran against a table of about 7 Million and when i run it on our SQL server 64bit 24proc 128GB ram attached to a 2 EqualLogic SAN it takes about 8 minutes. So after creating the Spatial Index i would think it would run much faster. I have done stuff like this before and had to use a HINT for the spatial index which made things take less than a second. However, on this query which i'm trying to find the nearest lat\long to another lat\long i use a HINT because without it, the query takes forever it gives me this error:

Msg 8635, Level 16, State 4, Line 5
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.


Here is the query for testing:
declare @point_a geography
set @point_a=GEOGRAPHY::Point(33.7876,-118.17,4326)

select TimeZone FROM tblTimeZoneLatLong with(index(geoindex1))
WHERE Location.STDistance(@point_a) = (SELECT MIN(Location.STDistance(@point_a))
FROM tblTimeZoneLatLong with(index(geoindex1)))


Thanks! I'm just stuck because my only other option is to pick the TOP 1 of the TimeZones where the lat and long is within a certain amount of meters, but not all my Lat and Longs will be within the number i give so it just won't work.

cheers

-ED

---