[SOLVED] Zip Code Lookup is slow

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

[SOLVED] Zip Code Lookup is slow

Post by jwalsh »

Hi,

I'm using a simple zip code database, with about 42,000 rows. I've written a function to take an entered zip code, and compare those latitude and longitude values to those of each row in the database to calculate the distance between them. If the distance returned is less than the given radius, it should add that zipcode to the array.

Mine seems to keep getting stuck in an infinate loop, (or is taking many hours to complete, and I've never finished it). I have compared my code to others on the net which do this function in less than a second.

Any ideas?

Code: Select all

function InRadius($zip, $radius) {
		// Get The Lon and Lat for the entered zip code
		$this->GetByZip($zip);
		
		// Loop Through Query, and check distance
		$DataLookup = New MySQL;
		$query = "SELECT * FROM zipcode ORDER BY zipcode";
		$i = 0;
		while ($row = $DataLookup->queryTOobject($query)) {
			$rowlat = $row->latitude;
			$rowlon = $row->longitude;
			$dist = Distance($this->lat, $this->lon, $rowlat, $rowlon);
			if ($dist <= $radius) {
				$zipsarr[$i] = $row->zipcode;
			}
		}
		
	}
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Try something like this:

When I find the source where I found this I'll post it here :)

Code: Select all

function inRadius($zip,$radius)
{
	
	$query="SELECT LATITUDE,LONGITUDE,STATE FROM dbName.ZIPCODES WHERE ZIP_CODES='$zip'";
	
	$result = mysql_query($query);

	if(mysql_num_rows($result) > 0) {
	
		$row = mysql_fetch_array($result, MYSQL_ASSOC);
		$lat=$row["LATITUDE"];
		$lon=$row["LONGITUDE"];
		$st = $row["STATE"];

		$query="SELECT DISTINCT(ZIP_CODES) as zip
			FROM dbName.ZIPCODES 
		WHERE 
			STATE = '$st'
		and
(POW((69.1*(LONGITUDE-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(LATITUDE-\"$lat\")),\"2\"))<($radius*$radius) ";
		

		$result = mysql_query($query);
		
		if(mysql_num_rows($result) > 0) {
			while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
				$return_val[] => $row['zip'];
			}
			
		
		
		return $return_val;                
		}
	} else {
		return "Zip Code not found";
	}
} // end func
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

Woo Hoo - I found it:

(PDF) http://www.phparch.com/issuedata/articles/article_9.pdf

Great article for doing distance calc.
User avatar
jwalsh
Forum Contributor
Posts: 202
Joined: Sat Jan 03, 2004 4:55 pm
Location: Cleveland, OH

RESOLVED

Post by jwalsh »

I found a number of resources online, but this seemed to be the most straight forward. That class is great...

http://www.micahcarrick.com/v2/content/view/4/3


Thanks,

Josh
Post Reply