Zip Code Search Radius Calculation

zenaphex

I have experimented with some of the distance calculation I have found on some development sites for zip codes.

I can understand that the calculations aren't entirely accurate but the results I am getting are way off.

I punch in my zip code and set a radius of 30 miles. When I submit I get two zip codes from Oklahoma. I live in Texas right along the southeast coast of Houston and Oklahoma shouldn't be coming up. I get a bunch more zip codes within Texas and I have a hunch some of those are not within 30 mile radius.

Here is my PHP code:

<?php
	function great_circle_distance($lat1, $lat2, $lon1, $lon2)
	{
		define('EARTH_RADIUS', 3959);

	/* Convert all the degrees to radians */
	$lat1 = deg_to_rad($lat1);
	$lon1 = deg_to_rad($lon1);
	$lat2 = deg_to_rad($lat2);
	$lon2 = deg_to_rad($lon2);

	/* Find the deltas */
	$delta_lat = $lat2 - $lat1;
	$delta_lon = $lon2 - $lon1;

	/* Find the Great Circle distance */
	$temp = pow(sin($delta_lat/2.0), 2) + cos($lat1) * cos($lat2) * pow(sin($delta_lon/2.0), 2);

	$distance = EARTH_RADIUS * (2 * atan2(sqrt($temp), sqrt(1-$temp)));

	return $distance;
}

function deg_to_rad($deg)
{
	$radians = 0.0;

	$radians = $deg * M_PI/180.0;

	return($radians);
}

$sql = @mysql_connect('blah', 'blah', 'blah')
	or die("<p>Unable to connect to MySQL server.<br>\nReason: " . mysql_error() . "</p>\n");

@mysql_select_db('somedb')
	or die("<p>Unable to select database.<br>\nReason: " . mysql_error() . "</p>\n");

$zipcode = 77777;
$state = 'TX';
$radius = 30;

$result = @mysql_query("SELECT longitude, latitude FROM locations WHERE zipcode='$zipcode' AND state='$state'", $sql)
	or die("<p>Unable to query database.<br>\nReason: " . mysql_error() . "</p>\n");

echo "<p>\n";
if (mysql_num_rows($result) == 1) {
	$row = mysql_fetch_array($result);

	$lon1 = $row[0];
	$lat1 = $row[1];

	printf("\tLongitude: %f, Latitude: %f<br>\n", $lon1, $lat1);

	$result = @mysql_query("SELECT * FROM locations", $sql)
		or die("<p>Unable to query database.<br>\nReason: " . mysql_error() . "</p>\n");

	if (mysql_num_rows($result) > 0) {
		echo "<p>\n";
		while ($row = mysql_fetch_array($result)) {
			$distance = great_circle_distance($lat1, $row[3], $lon1, $row[2]);
			if ($distance <= $radius)
				echo "Match - ZipCode: $row[0], State: $row[1], Longitude: $row[2], Latitude: $row[3], Distance: $distance miles<br>\n";
		}
		echo "</p>\n";
	}
	else
		echo "<p>No results found.</p>\n";
}
else
	echo "<p>No single result found.</p>\n";
echo "</p>\n";

mysql_free_result($result);
mysql_close($sql);
?>

I changed the set zip code for privacy reasons. The only thing that is not accurate are the latitude and longitude decimals I imported into my mysql database. I set the lat/lon as floats and they got rounded by like .000005 or so. I don't see how this could cause Oklahoma to show up when it's hundreds of miles away from me.

I plan on migrating the distance calculation to a SQL statement but what's up with this PHP code? Any ideas please?

Thanks in advance.

sneakyimp

When the zip codes are returned, what does your result say about how far away they are? If it says they are less than 30 miles, either your data (longitude, latitude of zip codes) is wrong or your calculation is wrong.

I haven't looked in detail at your code but i'm curious what the results say.

zenaphex

I attached the results for a 30 mile radius since it was too long to fit in message. The first line is my longitude/latitude. The rest of the lines are the radius results. You will notice the first two Oklahoma results. One of the results is 0 miles which is my zip code.

zenaphex

I just reimported the census data into another table for testing purposes. This time instead of setting lon/lat as FLOATs I set them as DOUBLEs. The DOUBLEs show accurate number data(as FLOATs will round off the decimals) though the search results still come out incorrect. So I know now it isn't my imported census data. It must be the calculation portion. I ripped this code from someone elses posted code so it may have some flaws in it.

AstroTeg

I took a quick look and your data I think is fine. The algorithm you're using has issues.

For testing (and testing only), you can play with this:

http://www.axtime.com/zip/geodist_multi.php

I assembled that for some database querying I needed to do a while ago. It doesn't have the most complete zip collection and the zip data is from the census folks meaning its not based on postal zips. But you might find it handy for testing your algorithm out.

The method I'm using can be found in this post: http://www.phpbuilder.com/board/showthread.php?s=&threadid=10272986&highlight=geodist

zenaphex

If your posting uses C code then no way. I'll find a fix for mine eventually as I want to stick to PHP/MySQL strictly. Thanks though.

sneakyimp

a 0 mile distance is correct for your zip code. (longitude and latitude difference are zero).

my last post got chopped. if your script says that those far away zip codes are within 30 miles, either your data is wrong or your calculation is wrong.

alter your script to show you lat & long of the zips returned. perform the calculation in a spreadsheet or make another PHP script that just does the greatcircle distance. once you are CERTAIN your great circle distance calculation is correct, then make sure your data is correct.

AstroTeg

Originally posted by zenaphex
If your posting uses C code then no way. I'll find a fix for mine eventually as I want to stick to PHP/MySQL strictly. Thanks though.

You might have missed the beauty of the C code that was posted.

The C code plugs into MySQL! You do NO calculations at the SQL or PHP level. The C code adds a function to MySQL's SQL. The web address I gave above uses it and I've got to say its pretty quick.

zenaphex

Oh man do I feel stupid. I got my calculation resolved. The problem was that I had my latitude and longitude reversed. Someone slap me please. 😃 Anyhoos, I got it fixed up and found this simpler formula for my distance calculation.

$distance = 69.1 * sqrt( pow($lat2 - $lat1, 2) + 0.6 * pow($lon2 - $lon1, 2) );

I read that this formula was easier to process and provided same but less accurate results. I compared and there wasn't much difference in accuracy so I stuck with this simpler formula.

Radical!