Zip Code Radius Search using MySQL

Tuesday, January 22, 2008

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)

Comments 22

Jack Burkhalter (9 Feb, 07:26 PM)

YOUR ARE THE MAN!!! THIS IS WHAT I HAVE BEEN LOOKING FOR FOR OVER A YEAR!!

Brett (12 Feb, 05:31 PM)

Thanks for posting your code. It looks like it should work, but I probably did something wrong. Even after changing the table and column names to match my own zip db's field and table names, I can't managed to get past this mySQL error:

#1312 - PROCEDURE geocode.GetNearbyZipCodes can't return a result set in the given context

Any ideas?

Brett (13 Feb, 11:42 AM)

Found the problem: apparently phpmyadmin does not connect to the mysql server with the proper multi statements flag. I am able to use this function now directly from php, just not phpmyadmin. Thanks again. For ref, here is the connect string that works for me:

define('MYSQL_MULTI_SELECT_FLAG', 65536);
mysql_connect( $this->dbHost, $this->dbUser, $this->dbPass, false, MYSQL_MULTI_SELECT_FLAG );

Credit: http://bobfield.blogspot.com/2006/09/php-and-mysql-stored-procedures.html

Brett (13 Feb, 01:58 PM)

One last thing: I modified your code slightly to order the result set of nearby zips by distance, all else is same:

DROP PROCEDURE IF EXISTS `GetNearbyZipCodes`$$
CREATE DEFINER=`root`@`localhost` 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 lat,lon into lat1,long1 FROM zip_code WHERE zip_code = zipbase;
SELECT zip_code,lat,lon,GetDistance(lat1,long1,B.lat,B.lon) as dist
FROM zip_code AS B
WHERE
B.lat BETWEEN lat1-(range*rangeFactor) AND lat1+(range*rangeFactor)
AND B.lon BETWEEN long1-(range*rangeFactor) AND long1+(range*rangeFactor)
AND GetDistance(lat1,long1,B.lat,B.lon) <= range ORDER BY dist;
END$$

Thanks again for posting this blog! It saved me weeks of dev time :)

Brett (13 Feb, 02:52 PM)

Me again to spam your blog ;)

This might be useful to readers, its a further modification of your code that allows you to store the result set from the sProc to a temporary table. You can then reference the data with selects as a normal table (with joins and all) for the duration of the sql connection in php. Simply alter the create table part of the proc to suit whatever needs you have:

DROP PROCEDURE `GetNearbyZipCodes`//
CREATE DEFINER=`root`@`localhost` 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 lat,lon into lat1,long1 FROM zip_code WHERE zip_code = zipbase;
CREATE TEMPORARY TABLE `#tmpzip` (
zip_code INT,
lat FLOAT(9),
lon FLOAT(9),
dist FLOAT(7,2)
);
INSERT INTO `#tmpzip` (zip_code,lat,lon,dist)
SELECT zip_code,lat,lon,GetDistance(lat1,long1,B.lat,B.lon) as dist
FROM zip_code AS B
WHERE
B.lat BETWEEN lat1-(range*rangeFactor) AND lat1+(range*rangeFactor)
AND B.lon BETWEEN long1-(range*rangeFactor) AND long1+(range*rangeFactor)
AND GetDistance(lat1,long1,B.lat,B.lon) <= range ORDER BY dist;
END

Example Use:
$query1="Call GetNearbyZipCodes(yourzip,20);"
->execute 1st query, resource is bool value

$query2="SELECT DISTINCT D.name, Z.zip_code, Z.dist FROM `someDB`.`directory` AS D, `geocode`.`#tmpzip` AS Z WHERE D.zip = Z.zip_code ORDER BY Z.dist"

->execute query2, resource is a new table of names from your directory sorted by zip distance joined from the temp table. Just add whatever other fields you need to pull by radius to the second query after D.name

Jay (14 Feb, 04:15 AM)

Great piece of information.

Can you let me know if I can execute the stored procedure as part of a "Where" clause? For example, I have a table called users that has the following columns: user_id, zipcode.

Is it possible to do something like:

$sql = "SELECT * FROM users WHERE zipcode IN(GetNearbyZipCodes(92126, 30))"

Brett (14 Feb, 10:34 AM)

Jay,

Your query should work with no problems. Just make sure your master zip table and your user table reside in the same db that the stored function and proc are loaded into.

I like keeping the zip stuff in its own db, which requires join syntax and a db user with privs on each db, but that is entirely optional.

Jon (18 Feb, 06:39 PM)

Wow. This would be so awesome. I got 2/3rds of the way there before realizing my hosting doesn't allow MySQL stored functions, though.

Can anyone point me in a good direction to get past that? Stored procedures are OK.

Sorry - I've been researching two days now but I'm still a Stored procedure / function newby.

Thanks

Jon (18 Feb, 08:36 PM)

Julie (2 Apr, 01:48 AM)

How can I fetch all the results after executing the procedure??
Plz help he, its very urgent!!

Geoserv (17 Apr, 10:23 AM)

STUMBLED!

I have only recently gotten seriously into SQL, this tutorial was a bit over my head, but will bookmark and re-read later.

Thanks.

VOTED for you at:
http://www.newsdots.com/tutorials/goondocks-zip-code-radius-search-using-mysql/

Gabe Sumner (18 Apr, 11:45 AM)

Thanks Geoserv!

Jon Gales (24 Apr, 02:25 PM)

I couldn't get this working until I removed the space around the abs() call in your function. Changing "abs (" to "abs(" made all the difference. I'm using MySQL 5.0.45.

Matthew W (24 Apr, 02:57 PM)

Clever, but you could do a lot better than this.

(1) (the main point) this query requires a full table scan, calculating the distance for every single point - O(n) and SLOW for a big set of geo-data.

Instead, you should be using mysql's spatial data types and an RTREE index:

http://dev.mysql.com/doc/refman/5.1/en/optimizing-spatial-analysis.html

If you store lat and long, you can calculate an appropriate bounding rectangle which will contain your radius, and use this to restrict your query using the RTREE index. Then an extra where clause can check for the precise radius within these results.

(2) the earth's curvature is not relevant at small scales - you can simplify your distance function a fair bit you take advantage of this to ditch the spherical trig. It becomes just the pythagorean formula with a latitude-dependent scaling factor of the longitude.

ThomW (25 Apr, 07:11 AM)

Matthew W is correct.

I ran into an application that worked like your demo with 10k records in the database. It was RIDICULOUSLY slow because it was doing trig on every record.

I simplified it just like Matthew W suggests by using a bounding box to return a rough recordset and ran those results through a trig function to make it a little more accurate, though it wasn't really necessary for what we were trying to do.

Gabe Sumner (25 Apr, 12:30 PM)

I appreciate the 2 comments above.

For my own needs I was dealing only with Phoenix, Arizona zip codes. I, personally, removed zip codes relating to other regions from the database. This certainly improves the execution-time drastically. This may not be a solution available to others though.

Matthew W and ThomW, do either of you have SQL you could share which demonstrates better technique? If you post it I will link to it. Alternately you can send it to me & I will post it (giving you full credit, of couse).

Either way, thanks for your comments.

soumya (2 May, 01:19 PM)

what is rangefactor for?

Djaru (16 May, 01:01 PM)

Sorry I am a bit new at this, but this what I am trying to use to get results into a page, what am I doing wrong?
$zip_query = "CALL GetNearbyZipCodes(30318, 5)";

$result = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_array($result)){

{
print ''.$zip_result['zipcode'].'"';

}
}
?>

Djaru (19 May, 08:57 AM)

Djaru (19 May, 08:59 AM)

Actually, I found out that my host will not alow execute permissions, does anyone know of a good host that will?

Eric (20 May, 07:06 AM)

This was a life saver! In case anyone needs to put this into MS Access, here's the code I used. I'm no Access expert so it may be a bit obtuse. Just make sure to remove any DB entries with a blank lat/long.

---Query ---
SELECT B.ZIP, B.City, B.State, B.County, B.Type
FROM ZIP_CODES AS B
WHERE (((B.Lat) Between (SELECT TOP 1 DupeA.Lat FROM ZIP_CODES AS DupeA WHERE DupeA.Zip = FORMS!FindZip!txtZip)-(CDbl([FORMS]![FindZip]![txtRadius])*0.014457) And (SELECT TOP 1 DupeB.Lat FROM ZIP_CODES AS DupeB WHERE DupeB.Zip = FORMS!FindZip!txtZip)+(CDbl([FORMS]![FindZip]![txtRadius]*0.014457))) AND ((B.Long) Between (SELECT TOP 1 DupeC.Long FROM ZIP_CODES AS DupeC WHERE DupeC.Zip = FORMS!FindZip!txtZip)-(CDbl([FORMS]![FindZip]![txtRadius]*0.014457)) And (SELECT TOP 1 DupeD.Long FROM ZIP_CODES AS DupeD WHERE DupeD.Zip = FORMS!FindZip!txtZip)+(CDbl([FORMS]![FindZip]![txtRadius]*0.014457))) AND ((GetDistance((SELECT TOP 1 DupeE.Lat FROM ZIP_CODES AS DupeE WHERE DupeE.Zip = FORMS!FindZip!txtZip),(SELECT TOP 1 DupeF.Long FROM ZIP_CODES AS DupeF WHERE DupeF.Zip = FORMS!FindZip!txtZip),[B].[Lat],[B].[Long]))<=CDbl([FORMS]![FindZip]![txtRadius])))
ORDER BY B.ZIP;

--- VB Module Code ---
Function GetDistance(lat1 As Double, lon1 As Double, _
lat2 As Double, lon2 As Double) As Double
Dim x As Double
Dim pi As Double

pi = 3.14159265358979
x = Math.Sin(lat1 * pi / 180) * Math.Sin(lat2 * pi / 180) + _
Math.Cos(lat1 * pi / 180) * Math.Cos(lat2 * pi / 180) * _
Math.Cos(Math.Abs((lon2 * pi / 180) - (lon1 * pi / 180)))
x = Math.Atn((Math.Sqr(1 - x ^ 2)) / x)

x = (1.852 * 60 * ((x / pi) * 180)) / 1.609344

GetDistance = x

End Function

anupam (5 Jul, 01:44 AM)

thanks a lot brother

Categories

Tags

Archive

Zip Code Radius Search using MySQL

Tuesday, January 22, 2008

Comments 22

Post a comment!