Gabe Sumner

Gabe Sumner

The RSS Feed URL cannot be found!
The remote server returned an error: (400) Bad Request.

Back to all posts

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. 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)

Facebook DZone It! Digg It! StumbleUpon Technorati Del.icio.us NewsVine Reddit Blinklist Furl it!

Comments  24

  • Jack Burkhalter 9 Feb, 09:26 PM

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


  • Brett 12 Feb, 07: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, 01:42 PM

    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, 03: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, 04: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, 06: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, 12:34 PM

    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, 08: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, 10:36 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


  • Julie 2 Apr, 02:48 AM

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


  • Geoserv 17 Apr, 12:23 PM

    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, 01:45 PM

    Thanks Geoserv!


  • Jon Gales 24 Apr, 04: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, 04: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, 09: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, 02: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, 03:19 PM

    what is rangefactor for?


  • Djaru 16 May, 03: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, 10:57 AM

    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, 10: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, 09: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, 03:44 AM

    thanks a lot brother


  • hotel tuerkei 29 Jan, 02:15 AM

    Volume Career,examination once put reply journey spot land regard parliament content conclude screen game friend atmosphere dress believe teaching used open raise design shut executive course county necessarily yet represent increase vary soon most specific error then supply college attention attempt what pool develop justice act section off world employment passage director new percent same course wing bind raise engineering existing arrive display prevent short area may until hill speech effect wrong cos thing individual around large special sector we instead will explore asset laugh their turn access


  • Arif 10 Mar, 01:29 AM

    It is a great post. I just want to add

    range numeric (15) here 'range' is reserve word for MySQL 5. You can replace the word 'range' by 'range1' or something like that.

    Thanks


Post a comment!


HostMySite.com   website uptime