Over the years I have implemented a number of various "near me" style searches using third-party zip code databases and a long, complicated formula to find the distance between two different latitudes and longitudes. Well, starting with SQL Server 2008, it is possible to get the same result, with a lot less effort, following two simple steps In this post I will walk through things step by step.
Assumptions
Before starting with this, I will assume that you already have a table of information with Zip Code latitude/longitude data. Something similar to the data available from Melissa Data. If you do not, the key here is to be able to obtain a latitude and longitude for each entry.
Initial Setup - Prime the Records
Now, the key to this is actually to modify the records that you are going to be searching on. For example, if I have a listing of customer addresses and I want to calculate distances between them, I would be modifying my customer information.
What we want to do is to add a new column to these records, which is of the type "GEOGRAPHY" this is a new data type added in SQL Server 2008 and exposes the functionality that we will be using later in this post to calculate distance. A GEOGRAPHY column stores the latitude and longitude data as a single data element rather than having two separate columns for each.
To set the value for a geography column is very simple, simply use the below function call
geography::STPointFromText('POINT(' + CAST(@StreetLongitude as varchar(20)) + ' '
+ CAST(@StreetLatitude as varchar(20)) + ')', 4326)The above is using two parameters that happen to be floats that store the latitude and longitude., you can modify if you have the values in another format. The key here is that we store the records in our database with this information together.
Make the Distance Query
Following more historical methods for doing distance calculations, we would be forced to create a complex query that uses a number of trigonometric functions to get our distance amount. If we wanted to query and find records where two geography points are less than or equal to 25 miles apart we can use the following simple where clause.
StreetGeo.STDistance(@MyLocation)/1609.344 <= 25This assumes that "StreetGeo" is the column in the database that we are checking and @MyLocation is the geography representation of the location we are searching from! The only piece of magic here is that we have to divide the result by 1609.344 to get the result in miles.
Conclusion
Overall I hope that this has been helpful, it is sure a lot easier than doing the more involved distance queries. I'm still benchmarking performance of this method to see which is faster but initial results are looking good. Feel free to share comments below.