Zip Code Radius Search using MySQL

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!