SQL Server distance search

Question

I have a Microsoft SQL Server database with a table of Locations. Each location has its address and latitude and longitude coordinates.

In my application, the user can input a zipcode and we return a list of close by locations. This is my approach. a) Using a zipcode DB I search the lat,lon for the zipcode (this is the center point). b) I run a search like this

SELECT Position_ID, distance(pos_lon,pos_lat,zip_lon,zip_lat) dist 
FROM Positions
ORDER BY dist

"distance" is a function that calculates the distance between two points.

The problem is that as my location DB increases the time to run these searches is starting to grow.

Is there a better approach?

Answer 1

If you're using SQL Server 2008, you probably want to look into the geography column type, the STDistance function and spatial indexes.

• Spatial Indexing Overview
• Geography Methods Supported by Spatial Indexes
• How to: Create a Spatial Index

Answer 2

I would do a calculation of the box surrounding your zip code at the specified distance to get the lat/lon values for the four corners. Then do a simple comparison of your position values to select those which fall inside the square. This way, you don't have to calculate the distance from your zip code to every point in your db for every search.

Answer 3

Which version of SQL server? If 2008 have a look at the spatial type.

http://www.microsoft.com/sqlserver/2008/en/us/spatial-data.aspx

EDIT: Also limiting that query with a where would probably help as you likely don't want to go to far in any particular direction.