XML FeedsHave you ever wanted to add functionality that allows you to perform simple radius searches based on Zip Code? For example, Show me all the cities within 20 miles of a certain zip code or show me the 5 closest retail locations. You can easily add this functionality to your database by importing a zip code database and writing a little code. This article will show you where to get zip code data, how to import it to your database, and how to use the data to perform proximity searches.
Denis and I decided to show you how you can do simple radius searches based on Zip Codes, he did the SQL 2008 version here: http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/sql-server-2008-proximity-search-with-th (using the geography data type) and I did the SQL Server 2000/2005 version.
The first thing we need to do is load our data. There are various sources for this data, ranging from free to expensive monthly subscriptions. One source of free data is: http://download.geonames.org/export/zip/
** Note: Based on a comment from Steve_O, the format of the geonames file recently changed. I have no control over those changes, and am not notified when changes occur. If you have difficulty loading the data from the file in to the table, you may need to investigate the structure of the data to see if anything has changed.
Once you have downloaded your data, the next step is to import it in to your database. You can use the following script to do it.
- If Exists(Select *
- From Information_Schema.Tables
- Where Table_Name = 'ZipCodes'
- And Table_Type = 'Base Table')
- Drop Table ZipCodes
- CREATE TABLE [dbo].[ZipCodes](
- [country code] [varchar](2) NULL,
- [postal code] [varchar](20) NULL,
- [place name] [varchar](180) NULL,
- [admin name1] [varchar](100) NULL,
- [admin code1] [varchar](20) NULL,
- [admin name2] [varchar](100) NULL,
- [admin code2] [varchar](20) NULL,
- [admin name3] [varchar](100) NULL,
- [admin code3] [varchar](20) NULL,
- [latitude] [decimal](8, 4) NULL,
- [longitude] [decimal](8, 4) NULL,
- [accuracy] [int] NULL
- )
- DECLARE @bulk_cmd varchar(1000)
- SET @bulk_cmd = 'BULK INSERT ZipCodes
- FROM ''C:\YourFolder\US.txt''
- WITH (FIELDTERMINATOR=''\t'', ROWTERMINATOR = '''+CHAR(10)+''')'
- EXEC(@bulk_cmd)
- Alter Table ZipCodes Drop Column Unused1, Unused2, Unused3
- CREATE CLUSTERED INDEX IX_ZipCodes_Zip ON dbo.ZipCodes(ZipCode, Longitude, Latitude)
- GO
- CREATE NONCLUSTERED INDEX IX_ZipCodes_Longitude_Latitude ON dbo.ZipCodes(Longitude,Latitude, ZipCode)
- GO
- Select * From ZipCodes
Next, we’ll need to add a couple of functions that will allow us to use this data.
The original function that calculates distances has been replaced with this one. There was a flaw in the original version. Based on rounding errors, the calculations fed in to the arc-cosine (acos) function could be greater than 1 or less than -1 which would cause a domain error. To accommodate this flaw, I perform the calculations in multiple steps so that I can catch the values that result in the domain error.
Credit for finding the flaw goes to Chris. Thank you.
- CREATE Function [dbo].[CalculateDistance]
- (@Longitude1 Decimal(8,5),
- @Latitude1 Decimal(8,5),
- @Longitude2 Decimal(8,5),
- @Latitude2 Decimal(8,5))
- Returns Float
- As
- Begin
- Declare @Temp Float
- Set @Temp = sin(@Latitude1/57.2957795130823) * sin(@Latitude2/57.2957795130823) + cos(@Latitude1/57.2957795130823) * cos(@Latitude2/57.2957795130823) * cos(@Longitude2/57.2957795130823 - @Longitude1/57.2957795130823)
- if @Temp > 1
- Set @Temp = 1
- Else If @Temp < -1
- Set @Temp = -1
- Return (3958.75586574 * acos(@Temp) )
- End
- Create Function [dbo].[LatitudePlusDistance](@StartLatitude Float, @Distance Float) Returns Float
- As
- Begin
- Return (Select @StartLatitude + Sqrt(@Distance * @Distance / 4766.8999155991))
- End
- Create Function [dbo].[LongitudePlusDistance]
- (@StartLongitude Float,
- @StartLatitude Float,
- @Distance Float)
- Returns Float
- AS
- Begin
- Return (Select @StartLongitude + Sqrt(@Distance * @Distance / (4784.39411916406 * Cos(2 * @StartLatitude / 114.591559026165) * Cos(2 * @StartLatitude / 114.591559026165))))
- End
Finally, we can begin writing some interesting queries. For example, return a list of zipcodes within 20 miles of zipcode 20013 (Washington, DC).
- -- Declare some variables that we will need.
- Declare @Longitude Decimal(8,5),
- @Latitude Decimal(8,5),
- @MinLongitude Decimal(8,5),
- @MaxLongitude Decimal(8,5),
- @MinLatitude Decimal(8,5),
- @MaxLatitude Decimal(8,5)
- -- Get the lat/long for the given zip
- Select @Longitude = Longitude,
- @Latitude = Latitude
- From ZipCodes
- Where ZipCode = '20013'
- -- Calculate the Max Lat/Long
- Select @MaxLongitude = dbo.LongitudePlusDistance(@Longitude, @Latitude, 20),
- @MaxLatitude = dbo.LatitudePlusDistance(@Latitude, 20)
- -- Calculate the min lat/long
- Select @MinLatitude = 2 * @Latitude - @MaxLatitude,
- @MinLongitude = 2 * @Longitude - @MaxLongitude
- -- The query to return all zips within a certain distance
- Select ZipCode
- From ZipCodes
- Where Longitude Between @MinLongitude And @MaxLongitude
- And Latitude Between @MinLatitude And @MaxLatitude
- And dbo.CalculateDistance(@Longitude, @Latitude, Longitude, Latitude) <= 20
You could also use this for a "store locator". On a website, potential customers could enter their zipcode and you could present a list of top 5 closest stores.
The query shown below assumes you have a Stores table with a ZipCode column.
- Declare @Longitude Decimal(8,5)
- Declare @Latitude Decimal(8,5)
- Select @Longitude = Longitude,
- @Latitude = Latitude
- From ZipCodes
- Where ZipCode = '20013'
- Select Top 5 Stores.StoreName, ZipCodes.City, dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude) As Distance
- From @Stores As Stores
- Inner Join ZipCodes
- On Stores.ZipCode = ZipCodes.ZipCode
- Order By dbo.CalculateDistance(@Longitude, @Latitude, ZipCodes.Longitude, ZipCodes.Latitude)
As you can see, these queries perform very well, and are relatively easy to write.
I haven't used your new function (I will have to do another test) but in SQL 2005 you can also use the CLR functions very effectively for calculations like this:
Alex maybe you should create a post showing how to do it with the CLR 
Thanks for sharing this. I'm trying it out now.
Chris,
I like this and admire what you shared with others. 
Since @Distance is squared, how does one ask for a logitude 5 miles less than the one specified?
Very nice work, you saved me a lot of time.
Hello all,
Great work. Thank you very much. Just incorporated this into a complicated search query and it worked a treat. A++ 
Can Someone tell me what's the exact formula to calculate maximum latitude
Hi George,
Thanks for the code. It worked perfectly. 
This is great! Thanks so much for sharing. 
I cannot thank you enough for posting this...I'm working on an iPhone app to go along with an ASP-driven site that we have and I had no idea how to do this until I was directed to this article. 
I used your setup and things for the most part appear the way they should. However, I have several that don't seem to add up. 
Great job George!
For those who have been asking, the various constants used in George's distance calculation come from:
George,
Thanks for this script. Helped us. 
I am having an issue during the import process. It seems that some of the data cannot be converted to Decimal(8, 5) and the bulk import fails because of this in the Longitude column 
Great work!! It's really nice having something like this...
Great work, and Thank You for posting this!
I was having a problem (Domain Error) with my own query and ran into this site trying to fix it.
IT Professionals are responsible for creating new technology for development. 
Someone could telle me wich values can I use in LatitudePlusDistance and LongitudePlusDistance for calculation with Km? 
I rarely comment on posts but yours has really made life easier. Thank you for posting this. 
This is fantastic, it is fast. So much faster than using a prepopulated distance table of millions or rows.
The idea was really nice and save my time, Excelent!
George,
George,
Works great, thanks for saving me about a week! 
Props for sharing this with the community. Thank you. 
I must be missing something on the first piece. I've downloaded the US zip file & am attempting to bulk insert it into SQL 2008 R2.
George,
Hi George,
Hi Paul, here are a few rough ideas in case you have not got around to figuring it out yet. You can refer to a function by name in a passthrough query (http://support.microsoft.com/kb/303968), so, for example,
Hi remou,
Great post. One question though. I'm trying to get store locater values back for both U.S. and Canada. This process works fine when they are done individually but when I enter a Canadian Postal Code of V5K0A1 (Vancouver) it is telling me that a store located in 98055 (Renton, WA) is thousands of miles away. Any ideas? 
Well shoot! I didn't even think to look at the raw database values. Duh! That appears to be my problem. Apparently the download I received has them incorrect. I will go out to the site you listed and pull them in from there, but here are the values.
This is really helpful. I believe its been asked, but I can't seem to find the answer. Can you explain what the following constants in your functions refer to:
Thanks for the post. I'd like to mention that the file with the Latitude/Longitude had some inaccurate Lat/Lon. I created a console app that will update the lat/lon from Bing Maps if anyone is interested.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.