Zip Distance

[Tsukiyomi]

I'm working on a search engine for a site where businesses can register for a listing, then people can search for listings in their zip code or within however many miles of their zip code.

 

I have a database that I filled with all the zip codes in the US and their latitude and longitude.  I also have a function that I'm using to calculate the distance between two sets of latitude and longitude, here is the one I'm using.

 

function distance($lat1, $lon1, $lat2, $lon2, $unit='M') {

  $theta = $lon1 - $lon2;
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
  $dist = acos($dist);
  $dist = rad2deg($dist);
  $miles = $dist * 60 * 1.1515;
  switch (strtoupper($unit)) {
  	case 'K':
	$ret = $miles * 1.609344;
	break;
case 'N':
	$ret = $miles * 0.8684;
	break;
default:
	$ret = $miles;
  }
  return $ret;
}

 

My question is if there is any more efficient way of comparing the distance between the zip they enter and the one in the database than running through ALL the rows in the table for each search and comparing the latitude and longitude from that record to the entered zip.

 

Any help would be greatly appreciated.

[Lytheum]

$firstzip =  $_POST['zipcode1'];
$secondzip = $_POST['zipcode2'];

$query = "SELECT * FROM table_name WHERE zip_code = '$firstzip'";
$query = mysql_query($query) or die("MySQL Error");
$row = mysql_fetch_array($query);
$latitude1 = $row['latitude'];
$longitude1 = $row['longitude'];
//Gets info from database about the first zipcode entered (lati, long, etc)

$query2 = "SELECT * FROM table_name WHERE zip_code = '$secondzip'";
$query2 = mysql_query($query2) or die("MySQL Error");
$row2 = mysql_fetch_array($query2);
$latitude2 = $row2['latitude'];
$longitude2 = $row2['longitude'];
//Gets info from database about the second zipcode entered (lati, long, etc)

 

 

That will get your $lat1, $lon1, $lat2, and $lon2.

 

Am I on the right path??

 

[hitman6003]

I think this will work....I haven't tested it because I don't have a database that contains zips along with lat and long...if you can tell me where to get one, I'd be willing to play with it...I've never played with this stuff before so it's entertaining.

 

Anyway, it returns an array of the 10 closest zips and their distance...

 

//lat and long passed to func are the ones selected by the user
function distance($lat_of_zip_selected, $long_of_zip_selected, $unit = 'M') {
$sin_la = sin(deg2rad($lat_of_zip_selected));
$cos_la = cos(deg2rad($lat_of_zip_selected)); 

//lat = the latitue column in your table
//long = the longitude column in your table
//zip = the zip code column in your table
//zip_locations = the name of your table

$query = "SELECT zip, (ACOS(RADIANS(( SIN(RADIANS(lat)) * $sin_la + COS(RADIANS(lat)) * $cos_la * COS(RADIANS( ($long_of_zip_selected - long) )) ))) * 60 * 1.1515) AS distance_miles " .
	     "FROM zip_locations ORDER BY distance_miles DESC LIMIT 10";


$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
	$miles = $row['distance_miles'];

	switch (strtoupper($unit)) {
		case 'K':
			$ret = $miles * 1.609344;
			break;
		case 'N':
			$ret = $miles * 0.8684;
			break;
		default:
			$ret = $miles;
	}

	$data[$row['zip']] = $miles;
}

return $data;
}

[Tsukiyomi]

Thanks for the help guys, I'll try these out and see if they work.

 

hitman6003, here is where I got the information I put in my database:

 

http://spatialnews.geocomm.com/newsletter/2000/jan/zipcodes.html

 

I downloaded this text file then wrote a php file to open it, read it then break it up so it could throw the information in the database.

[hitman6003]

I had to tweak the query some, but this works...

 

function distance($lat_of_zip_selected, $long_of_zip_selected, $unit = 'M') {
global $database_conn;

$lat = $lat_of_zip_selected;
$lon = $long_of_zip_selected;

$query = "SELECT zip, state, (DEGREES(ACOS((SIN(RADIANS(`lat`)) * SIN(RADIANS($lat))) + COS(RADIANS(`lat`)) * COS(RADIANS($lat)) * COS(RADIANS( `long` - $lon )))) * 60 * 1.1515) AS distance_miles " .
	     "FROM zip_codes ORDER BY distance_miles ASC LIMIT 10";

$result = mysql_query($query) or die(mysql_error());
while ($row = mysql_fetch_assoc($result)) {
	$miles = $row['distance_miles'];

	switch (strtoupper($unit)) {
		case 'K':
			$ret = $miles * 1.609344;
			break;
		case 'N':
			$ret = $miles * 0.8684;
			break;
		default:
			$ret = $miles;
	}

	$data[$row['zip']] = $miles;
}

return $data;
}

 

Took me 30 minutes to realize that the last operation conducted was rad2deg, rather than deg2rad...which was throwing off the DEGREES and RADIANS calls in the SQL...oops