Program to Search Within Radius of Zip Code

I'm looking for a program (php/mysql) to search within a certain radius of a zip code. Any suggestions?

My client has a database of retail stores in many cities. The website visitor would enter a zip code, select a category (clothing, furniture, etc.) and a list of stores within a certain radius of that zip code is created. This isn't something I can produce because the program must "know" what zip codes are near each other.

There are several ready-made scripts that will do this (for example: http://www.code322.com) but my client would like visitors to have the option of entering a zip code or city name. I can't find a program that accepts zip codes or city names, only zip codes.

See Wedding Wire for an example of this search function: http://www.weddingwire.com/

Thanks, Gary


WebStudioWestAsked:
Who is Participating?
 
SimplifiedComputersCommented:
The WHILE statement is actually executed after the first result is pulled.  So the query "loads" up the first row, executes the DO statement, and then the WHILE grabs the next line and starts over.

I've also made a couple of updates to improve the accuracy of the "city, state" searches.  If you ask for a city and state that has multiple zip codes then your results can be off by several miles, e.g. Chicago, Miami,  NYC, San Diego, etc...  Those cities will have upwards of 15-20 zip codes and will range several miles apart.  I've updated the search to pull all of the zip codes for a given city, state combo and then average the latitude and longitude to provide a more balanced result.  Still, for the most accurate distance results it's still best to search by zip code.

I've attached a sample output by populating the STORES table with data from weddingwire.com.
zipcode-radius-stores.gif
0
 
Ray PaseurCommented:
Gary, the logic works this way.  

1. You geolocate the client.  You can do this via zip code or city name using one of the popular geocoder services.  More on this to follow...

2. You geolocate all your stores.  You have probably already done this, and you have the stores' latitude and longitude pairs stored in your data base.  A lat/lon pair is called a "geolocation" and the pair is always given with latitude first and longitude second.  For most terrestrial activity you can ignore the altitude.  It is part of GPS data but not needed for most ground-based applications

3. You create some "space" around the client by setting upper and lower limits on the client's geolocation data.  For example, if the client is located at 39.102 / -104.879 you would set the boundaries at + / - 0.4 on each of those numbers.

4. (Pidgin code) CREATE TEMPORARY TABLE `nearby` distance decimal(10,6) SELECT lat, lon, storename FROM from the store's data base WHERE store.lat BETWEEN upper and lower latitudes AND store.lon BETWEEN upper and lower longitudes ENGINE=MEMORY.

5. Iterate over the `nearby` table using the Haversine formula to determine the "as-the-crow-flies" distance between the client and the selected stores.  UPDATE `nearby` inserting the computed distance

6. SELECT FROM `nearby` ORDER BY distance -- and now you have your closest stores listed first.
0
 
Ray PaseurCommented:
Visit my web site here:
http://www.carpool2camp.org/demo_class_Geocoder.php

You can put in any part of the address - even just a city name - and test the two most popular geocoders from Yahoo! and Google.  After you have made a test, scroll to the bottom of the demo script and click on the link to get the source code.

To use these geocoders you will need to get your own API keys.  They are free, with a simple registration required.  You can hit Yahoo up to 5,000 times a day, and Google up to 15,000 times a day.

Beware of ambiguous city names.  If all you put in is "Springfield" you may not find the correct Springfield.  "Denver" will find Colorado, but that will not be true for all cities.  However if you have a city+state combo, you will get the right data almost all the time.
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Ray PaseurCommented:
This function will compute the distance between two lat/lon pairs.

Here are a couple of links to help you get started:
http://developer.yahoo.com/maps/rest/V1/geocode.html
http://code.google.com/apis/maps/documentation/services.html

Please let me know if you have any questions,

~Ray
function compute_distance($from_lat, $from_lon, $to_lat, $to_lon) 
{ // SEE http://en.wikipedia.org/wiki/Haversine_formula
	$from_lat	= floatval($from_lat);
	$from_lon	= floatval($from_lon);
	$to_lat	= floatval($to_lat);
	$to_lon	= floatval($to_lon);
	$dist	= acos( sin(deg2rad($from_lat))
              * sin(deg2rad($to_lat))
              + cos(deg2rad($from_lat))
              * cos(deg2rad($to_lat))
              * cos(deg2rad($from_lon - $to_lon)) );
	$dist	= rad2deg($dist);
	$miles	= (float) $dist * 69.0;
	if (is_nan($dist)) return FALSE; // NOT A NUMBER ?
	return round($miles,2); // KM = MILES * 1.61
}

Open in new window

0
 
SimplifiedComputersCommented:
Here's what I've done:
1.  Created a "stores" table containing name, address, phone number, city, state, etc...
2.  Created a local ZIP code table (not hard to find on the web).
3.  Wrote a PHP script will read the user's input as either city and state or zip code.  It will also ask the user to specify the distance.  Then it will find the latitude and longitude.  Next, it finds all of the stores within the specified distance.  It will also sort the results by closest store and output it on the page.
<?php require_once('Connections/zipcodes.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
	function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
	{
	  $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
	
	  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
	
	  switch ($theType) {
		case "text":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
		  break;    
		case "long":
		case "int":
		  $theValue = ($theValue != "") ? intval($theValue) : "NULL";
		  break;
		case "double":
		  $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
		  break;
		case "date":
		  $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
		  break;
		case "defined":
		  $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
		  break;
	  }
	  return $theValue;
	}
}
 
$colname_getZipCode = "-1";
if (isset($_GET['place'])) {
  $colname_getZipCode = $_GET['place'];
}
 
if (is_numeric($_GET['place'])) {
	
	mysql_select_db($database_zipcodes, $zipcodes);
	$query_getZipCode = sprintf("SELECT * FROM zipcodes WHERE zipcode = %s", GetSQLValueString($colname_getZipCode, "int"));
	$getZipCode = mysql_query($query_getZipCode, $zipcodes) or die(mysql_error());
	$row_getZipCode = mysql_fetch_assoc($getZipCode);
	$totalRows_getZipCode = mysql_num_rows($getZipCode);
} else {
	$cityvalue = substr($colname_getZipCode, 0, strpos($colname_getZipCode, ","));
	$statevalue = substr($colname_getZipCode, -2);
	mysql_select_db($database_zipcodes, $zipcodes);
	$query_getZipCode = sprintf("SELECT * FROM zipcodes WHERE city LIKE %s AND state LIKE %s LIMIT 1", GetSQLValueString($cityvalue, "text"), GetSQLValueString($statevalue, "text"));
	$getZipCode = mysql_query($query_getZipCode, $zipcodes) or die(mysql_error());
	$row_getZipCode = mysql_fetch_assoc($getZipCode);
	$totalRows_getZipCode = mysql_num_rows($getZipCode);
}
 
mysql_select_db($database_zipcodes, $zipcodes);
$query_getAllStores = "SELECT * FROM stores";
$getAllStores = mysql_query($query_getAllStores, $zipcodes) or die(mysql_error());
$row_getAllStores = mysql_fetch_assoc($getAllStores);
$totalRows_getAllStores = mysql_num_rows($getAllStores);
 
echo "You are searching from <strong>".ucwords(strtolower($row_getZipCode['city'])).", ".strtoupper($row_getZipCode['state'])." ".strtoupper($row_getZipCode['zipcode'])."</strong>";
//For testing
//$row_getZipCode['latitude'].", ".$row_getZipCode['longitude'];
 
$stores = array();
 
do {
	
	$from_lat	= floatval($row_getAllStores['latitude']);
	$from_lon	= floatval($row_getAllStores['longitude']);
	$to_lat	= floatval($row_getZipCode['latitude']);
	$to_lon	= floatval($row_getZipCode['longitude']);
	$dist	= acos( sin(deg2rad($from_lat))
              * sin(deg2rad($to_lat))
              + cos(deg2rad($from_lat))
              * cos(deg2rad($to_lat))
              * cos(deg2rad($from_lon - $to_lon)) );
	$dist	= rad2deg($dist);
	$miles	= (float) $dist * 69.0;
	if (is_nan($dist)) return FALSE; // NOT A NUMBER ?
	$miles = round($miles); // KM = MILES * 1.61
	
	if ($miles<=$_GET['distance']) {
		$storenum = $row_getAllStores['num'];
		$storename = $row_getAllStores['name'];
		$stores[] = array (0=> round($miles), 1=> $storenum);
		array_multisort($stores);
	}
	/*
	foreach ($fruits as $key => $val) {
	    echo "fruits[" . $key . "] = " . $val . "\n";
	}
	*/
 
} while ($row_getAllStores = mysql_fetch_assoc($getAllStores));
 
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Zipcode distances</title>
</head>
 
<body>
<br>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="get">
Find stores within <input type="text" name="distance" value="<?php echo $_GET['distance']; ?>" size="4" /> miles of <input type="text" name="place" value="<?php echo $_GET['place']; ?>" size="20" />
<input type="submit" value="Search" />
</form>
<br>
<br>
 
<?php
$len = sizeof($stores);
$i = 0;
if ($len > 0) {
	do {
	mysql_select_db($database_zipcodes, $zipcodes);
	$query_getStore = "SELECT * FROM stores WHERE num = ".$stores[$i][1];
	$getStore = mysql_query($query_getStore, $zipcodes) or die(mysql_error());
	$row_getStore = mysql_fetch_assoc($getStore);
	$totalRows_getStore = mysql_num_rows($getStore);
	
	echo ucwords(strtolower($row_getStore['name']))."<br>";
	echo ucwords(strtolower($row_getStore['address']))."<br>";
	echo ucwords(strtolower($row_getStore['city'])).", ".strtoupper($row_getStore['state'])." ".$row_getStore['zipcode']."<br>";
	echo $row_getStore['phone']."<br>";
	echo "<strong>".$stores[$i][0]." miles</strong><br><br>";
	$i++;
	} while ($i<$len);
}
?>
</body>
</html>
<?php
mysql_free_result($getZipCode);
 
mysql_free_result($getAllStores);
 
if ($len > 0) {
	mysql_free_result($getStore);
}
?>

Open in new window

0
 
Ray PaseurCommented:
Without reading all the code, I would say that looks like a fairly good algorithm to me!  

But have a look at this code, which is at line 54 and above the do-loop.  Doesn't that usage of mysql_fetch_assoc effectively discard the the first row of the results set?
mysql_select_db($database_zipcodes, $zipcodes);
$query_getAllStores = "SELECT * FROM stores";
$getAllStores = mysql_query($query_getAllStores, $zipcodes) or die(mysql_error());
$row_getAllStores = mysql_fetch_assoc($getAllStores);
$totalRows_getAllStores = mysql_num_rows($getAllStores);
 
echo "You are searching from <strong>".ucwords(strtolower($row_getZipCode['city'])).", ".strtoupper($row_getZipCode['state'])." ".strtoupper($row_getZipCode['zipcode'])."</strong>";
//For testing
//$row_getZipCode['latitude'].", ".$row_getZipCode['longitude'];
 
$stores = array();
 
do {

Open in new window

0
 
Ray PaseurCommented:
Zip codes are not exactly geo=locations.  They are postal carriers routes.  In cities, they tend to be small.  Out west they may be very large.  But they are close enough for this kind of application.

Depending on where you got your zip codes, you may want to scrub the data.  I have found a LOT of inaccuracies in most of the freely available data sets.

Good luck with it, ~Ray
0
 
WebStudioWestAuthor Commented:
Thanks for the great info. I'll need a couple days before I can work on this and post results.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.