XML Feeds
Have 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/
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 ZipCodes(
- [Country] [VARCHAR](2) NULL,
- [ZipCode] [VARCHAR](5) NULL,
- [City] [VARCHAR](200) NULL,
- [STATE] [VARCHAR](50) NULL,
- [StateAbbreviation] [VARCHAR](2) NULL,
- [County] [VARCHAR](50) NULL,
- [Unused1] [VARCHAR](5) NULL,
- [Unused2] [VARCHAR](1) NULL,
- [Latitude] [DECIMAL](8,5) NULL,
- [Longitude] [DECIMAL](8,5) NULL,
- [Unused3] [VARCHAR](1) 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.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.