Determining Zip Code Proximity

So I got a request from one of our development officers the other day. They wanted to be able to perform prospect reports based on zip code, or a range of zip codes. So I started investigating, and found numerous resources thanks to the members of the coldfusion community on the cf-talk mailing list.

First, someone pointed me to http://www.teamredline.com/, a company that sells zip code databases for $5. The database contains city, state, state abbreviation, zip code, latitude, and longitude. I forked out the $5 and downloaded the data, and then imported it into SQL Server.

Russ also provided a SQL Server function that would determine the distance between two points on the globe, and here it is:

CREATE FUNCTION [dbo].[getDistance]
(
@lat1 numeric(9,6),
@lon1 numeric(9,6),
@lat2 numeric(9,6),
@lon2 numeric(9,6)
)
RETURNS NUMERIC( 10, 5 )
AS
BEGIN
DECLARE @x decimal(20,10)
DECLARE @pi decimal(21,20)
SET @pi = 3.14159265358979323846
SET @x = sin( @lat1 * @pi/180 ) * sin( @lat2 * @pi/180 ) + cos(
@lat1 *@pi/180 ) * cos( @lat2 * @pi/180 ) * cos( abs( (@lon2 * @pi/180) -
(@lon1 *@pi/180) ) )
SET @x = atan( ( sqrt( 1- power( @x, 2 ) ) ) / @x )
RETURN ( 1.852 * 60.0 * ((@x/@pi)*180) ) / 1.609344
END

He also provided a method of querying based on zip code that used a cross join. Several other users had recommended modifying his query to restrict the number of zip codes it compares by looking for latitude between A-X and A+X where A is the latitude of the origin zip code, and X is some factor of degrees that would contain the search radius. You can do the same for longitude (though the further north you go, the wider the area you end up including because the longitude is only 69 degrees per mile at the equator... but enough math! Here's the query, cfquery style:

SELECT
z2.CITY, z2.ABBR, z2.ZIPCODE
FROM
ADSPRD.dbo.zipcodes z
cross join ADSPRD.dbo.zipcodes z2
WHERE
z.zipCode=<cfqueryparam cfsqltype="cf_sql_char" value="#zipcode#">
AND z2.LATITUDE BETWEEN z.latitude-(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*0.014457*2) and z.latitude+(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*0.014457*2)
AND z2.LONGITUDE BETWEEN z.longitude-(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*0.014457*2) and z.longitude+(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*0.014457*2)
and ADSPRD.dbo.getDistance(z.Latitude, z.longitude,z2.latitude, z2.longitude)
<= <cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">;

But I found an even faster way, as follows. It uses some Transact-SQL statements, but gets the same results without the cross join.

DECLARE @lat1 decimal(5,2);
DECLARE @long1 decimal(5,2);
DECLARE @rangeFactor decimal(7,6);
SET @rangeFactor = 0.014457;
SELECT @lat1 = LATITUDE, @long1 = LONGITUDE from ADSPRD.dbo.zipcodes where zipcode = <cfqueryparam cfsqltype="cf_sql_char" value="#zipcode#">;

SELECT B.CITY, B.ABBR, B.zipcode
FROM ADSPRD.dbo.zipcodes B
WHERE
B.LATITUDE BETWEEN @lat1-(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*@rangeFactor) and @lat1+(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*@rangeFactor)
AND B.LONGITUDE BETWEEN @long1-(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*@rangeFactor) and @long1+(<cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">*@rangeFactor)
AND ADSPRD.dbo.getDistance(@lat1,@long1,B.latitude,B.longitude)
<= <cfqueryparam cfsqltype="cf_sql_smallint" value="#range#">;

The difference in query execution time is pretty minimal in terms of actual milliseconds, but it is significant, anywhere from 2x to 3x faster, depending on the radius of the area you're looking in.

UPDATE - Mysql GetDistance Function

goondocs.com took the above code and produced a MySQL function for the same purpose. Since links in comments in my blog don't do anything for search engine visibility due to the nofollow attribute, I'm posting the link up here as an update:

MySQL function to compute the distance between two zip codes