TIMSS 6: Zip Codes in Radius

Those of you using TCMS might have seen that you can search for customers within a given distance of a selected zip code when pulling directories.

Want to know how that works? Well there is a table called APP_GEO_INFO in TIMSS 6 that has the longitude and latitude of zip codes. If you know the longitude and latitude of two zip codes, you can calculate the distance between them (go here for the mathematical theory, or here).

I don't know where the data in this table came from or when the last time it was updated, but it seems to be fairly accurate. You can find places on the Internet where you can buy this data, or subscribe for updates.

So if you wanted to find all the zip codes with in 200 miles (driving distance) of your conference (in 20036), you could use this query to do it (formula from http://jan.ucc.nau.edu/~cvm/latlongdist.html):

select p1.zipcode
from app_geo_info p1, app_geo_info p2
where p2.zipcode='20036'
and acos(cos(p1.lat_rad)*cos(p2.lat_rad)
*cos(p1.long_rad)*cos(p2.long_rad)
+cos(p1.lat_rad)*sin(p1.long_rad)
*cos(p2.lat_rad)*sin(p2.long_rad)
+sin(p1.lat_rad)*sin(p2.lat_rad))*3963.1<=200

And you can use this to find all your customers within 200 miles to send them a special announcement about your upcoming conference.

Or if you wanted to create a function:

CREATE FUNCTION DBO.ZipDist(@Zip1 AS NVARCHAR(10), @Zip2 AS NVARCHAR(10))
RETURNS FLOAT AS
BEGIN
DECLARE @lat1 as FLOAT
DECLARE @long1 as FLOAT
DECLARE @lat2 as FLOAT
DECLARE @long2 as FLOAT
DECLARE @distance as FLOAT
SET @lat1 = (select lat_rad from app_geo_info where zipcode=@Zip1)
SET @long1 = (select long_rad from app_geo_info where zipcode=@Zip1)
SET @lat2 = (select lat_rad from app_geo_info where zipcode=@Zip2)
SET @long2 = (select long_rad from app_geo_info where zipcode=@Zip2)
SET @distance = acos(cos(@lat1)*cos(@lat2)*cos(@long1)*cos(@long2)+cos(@lat1)*sin(@long1)*cos(@lat2)*sin(@long2)+sin(@lat1)*sin(@lat2))*3963.1
RETURN @distance
END

NOTE: Some of the zip codes were missing a leading zero in my table

Applies to: TIMSS6