Finding Zipcodes in close proximity on MS SQL

I’ve had to research this and do it over again a few times. Figured this time I would document it. This works fine under Microsoft SQL Server 2005 with a reasonable response time, and seems a lot simpler then other solutions I’ve seen.

First get the data from:
http://www.census.gov/geo/www/gazetteer/places2k.htm

After a little massaging you should have a table with three columns, zipcode, lat, and long.

Doing a quick search gets you this equation to give you distance between two points:

acos(sin(lat1)*sin(lat2)+cos(lat1)*cos(lat2)*cos(long1-long2))

From there this is the SQL query you will need:

select 
	a.ZipCode,
	a.lat,
	a.long,
	b.ZipCode,
	b.lat,
	b.long,
	acos(sin(radians(a.lat)) * sin(radians(b.lat)) + cos(radians(a.lat)) * cos(radians(b.lat)) * cos(radians(a.long)-radians(b.long))) * 3956 as Distance
from
	ZipCodes a
	join ZipCodes b on acos(sin(radians(a.lat)) * sin(radians(b.lat)) + cos(radians(a.lat)) * cos(radians(b.lat)) * cos(radians(a.long)-radians(b.long))) * 3956 < 5
where
	a.ZipCode = '19146'

If you want it in km use 6367;
If you want it in nautical miles use 3438.
If you want it in miles use 3956 .

The number 5 is the minimum distance to filter on. What is displayed here seems accurate enough for most uses.

Good luck!

This entry was posted in personal, tech. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s