Zip Code Radius Search using MySQL

I had a project that required me to get a list of zip codes within a certain distance from a given zip code.

First, you are going to need to get a database that maps zip codes to longitude & latitude.

Zip code databases:

  1. Free from: http://www.populardata.com/downloads.html
  2. 5$ from http://www.teamredline.com/zc/

Next, you need to create a MySQL function to calculate distances between two longitudes and latitudes:

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

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:

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

Lastly you need to make a call to this stored procedure:

CALL GetNearbyZipCodes( '85254' , 30
Advertisements
Posted in Uncategorized. Tags: . 1 Comment »

One Response to “Zip Code Radius Search using MySQL”

  1. bob Says:

    “SET x = atan( ( sqrt( 1- power( x, 2 ) ) ) / x );” can be simplified to “SET x = acos( x );”


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

%d bloggers like this: