January 23, 2012

Simple Zip Code to Zip Code Distance Calculations with SQL Server

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 trigonomic 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 <= 25

This 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.

tags: SQL, Quick Tips
comments powered by Disqus

Content provided in this blog is provided "AS-IS" and the information should be used at your own discretion.  The thoughts and opinions expressed are the personal thoughts of Mitchel Sellers and do not reflect the opinions of his employer.

Content Copyright

Content in this blog is copyright protected.  Re-publishing on other websites is allowed as long as proper credit and backlink to the article is provided.  Any other re-publishing or distribution of this content is prohibited without written permission from Mitchel Sellers.