I had a project that required me to get a list of zip codes within a certain distance from a given zip code.
Here is a web-based tool that does what I'm describing.
I found a
great post on how to do this using ColdFusion . I converted the SQL found on this page to work with MySQL and have posted it on this page.
First, you are going to need to get a database that maps zip codes to longitude & latitude. I personally purchased this data for $5 from the following web site:
http://www.teamredline.com/zc/
Update: Heyrog posted on StumbleUpon that you can get the zip code database for free from:
http://www.populardata.com/downloads.html
Next, you need to create a MySQL function to calculate distances between two longitudes and latitudes:
| DELIMITER $$ |
| |
| DROP FUNCTION IF EXISTS `GetDistance`$$ |
| |
| CREATE FUNCTION `GetDistance`( |
| lat1 numeric (9,6), |
| lon1 numeric (9,6), |
| lat2 numeric (9,6), |
| lon2 numeric (9,6) |
| ) RETURNS decimal (10,5) |
| 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 $$ |
| |
| DELIMITER ; |
You will now need to create a MySQL stored procedure that accepts a zip code and a radius and returns a list of zip codes that fall within the given radius:
| DELIMITER $$ |
| |
| DROP PROCEDURE IF EXISTS `GetNearbyZipCodes`$$ |
| |
| CREATE PROCEDURE `GetNearbyZipCodes`( |
| zipbase varchar (6), |
| range numeric (15) |
| ) |
| BEGIN |
| DECLARE lat1 decimal (5,2); |
| DECLARE long1 decimal (5,2); |
| DECLARE rangeFactor decimal (7,6); |
| SET rangeFactor = 0.014457; |
| SELECT latitude,longitude into lat1,long1 FROM tbl_zipcodes WHERE zipcode = zipbase; |
| SELECT B.zipcode |
| FROM tbl_zipcodes AS B |
| WHERE |
| B.latitude BETWEEN lat1-(range*rangeFactor) AND lat1+(range*rangeFactor) |
| AND B.longitude BETWEEN long1-(range*rangeFactor) AND long1+(range*rangeFactor) |
| AND GetDistance(lat1,long1,B.latitude,B.longitude) <= range; |
| END $$ |
| |
| DELIMITER ; |
Lastly you need to make a call to this stored procedure:
| CALL GetNearbyZipCodes( '85254' , 30) |