Select records from a SQL Server database using geography and distance

Question

I am using the SQL Server geography datatype to store the location of records in my database. I would like to select all records within a given distance of a given location:

DECLARE @location AS geography = geography::Point(@latitude, @longitude, 4326)
DECLARE @distance AS INT = 10000

SELECT * 
FROM records
WHERE records.location.STDistance(@location) <= @distance

With a couple of dozen records in my test database this runs pretty quickly and I don't have any issues, but I know that WHERE clause is running STDistance against all records in my database, and once I have thousands of records, it's going to slow to a crawl.

Is there a better way to do this? Maybe create some sort of region and first select data in neighboring regions?

Answer 1

You most definitely want to set up a spatial index as @Twelfth recommends. You also want to do your search on a range, not distance to take better advantage of the spatial index.

DECLARE @location AS geography = geography::Point(@latitude, @longitude, 4326)
DECLARE @distance AS INT = 10000
DECLARE @range AS geography = @location.STBuffer(@distance)

SELECT * 
FROM records
WHERE records.location.STIntersects(@Range) = 1