Spatial index is slow when trying to find all the points within a range of a geocode.

来源：互联网发布：windows10软件字体模糊编辑：程序博客网时间：2024/05/17 10:53

You might need to use an Index hint (i.e. WITH(INDEX( [INDEX_NAME] ))

Select top 100 ci.Geocode.STDistance(@g), ci.CIOI  from CustomerInformation WITH(INDEX(IX_CI_Geocode))ci where ci.Geocode.Filter(@region) = 1 order by ci.Geocode.STDistance(@g) asc

http://stackoverflow.com/questions/6461496/spatial-index-is-slow-when-trying-to-find-all-the-points-within-a-range-of-a-geo

my example

DECLARE
@search_longitude FLOAT='121.78364585795',
@search_latitude FLOAT='31.2351032520358',
@radius Float=100
DECLARE @search_position geography = geography::Point(@search_latitude, @search_longitude, 4326)

--DECLARE @search_position geography = geography::Point(@search_latitude, @search_longitude, 4326).STBuffer(@radius)
SELECT TOP 50 *
FROM Locations l WITH(INDEX(IX_Stores_Location))
WHERE l.Location is not null and l.Location.STDistance(@search_position) <= @radius

--l.Location.Filter(@search_position)=1