Selecting records from a SQL Server database using geography and distance

Question

I use the SQL Server geography data type to store the location of records in my database. I would like to select all records at a given distance from 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 dozens of records in my test database, this is pretty fast and I have no problem, but I know that the WHERE clause uses STDistance for all the records in my database, and as soon as I have thousands of records, it will be slow to be scanned.

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

Answer 1

You definitely want to tune the spatial index, as @Twelfth recommends. You also want to search by range rather than distance to make better use 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