Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Program to Search Within Radius of Zip Code

Posted on 2009-02-13
8
Medium Priority
?
3,043 Views
Last Modified: 2013-12-12
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


0
Comment
Question by:WebStudioWest
  • 5
  • 2
8 Comments
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23641225
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23641266
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23641327
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 1

Expert Comment

by:SimplifiedComputers
ID: 23642747
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 23645125
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
 
LVL 1

Accepted Solution

by:
SimplifiedComputers earned 1000 total points
ID: 23645202
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
 
LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 1000 total points
ID: 23645211
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
 

Author Comment

by:WebStudioWest
ID: 23660524
Thanks for the great info. I'll need a couple days before I can work on this and post results.
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
This video teaches viewers how to create their own website using cPanel and Wordpress. Tutorial walks users through how to set up their own domain name from tools like Domain Registrar, Hosting Account, and Wordpress. More specifically, the order in…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Suggested Courses
Course of the Month20 days, 19 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question