Order by Distance

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
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Order by Distance

Post by Benjamin »

Can someone please show me how to order by distance? I found the following query but I'm not sure how to add the order by clause to it.

Code: Select all

$query="SELECT * FROM zipData WHERE (POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\"))<($radius*$radius) ";
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

Code: Select all

order by POW((69.1*(lon-\"$lon\")*cos($lat/57.3)),\"2\")+POW((69.1*(lat-\"$lat\")),\"2\")
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Thank you!
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Ok, I modified my query as you recommended but I might have misunderstood. This is what I have.

Code: Select all

AND (POW((69.1*(Longitude-"-93.261385")*cos(37.148949/57.3)),"2")+POW((69.1*(Latitude-"37.148949")),"2"))<(1*1) ORDER BY POW((69.1*(Longitude-"-93.261385")*cos(37.148949/57.3)),"2")+POW((69.1*(Latitude-"37.148949")),"2") ASC Limit 0,300
This query is returning results that are over 1 mile away, when it should not be.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

sounds like a problem in your math.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

For a zip code script the distance needs to be calculated outside of the query. The query usually returns the data related to listings within a circumference to the users zip code. There are an infinite number of points within the circle. The distance to each is something that can be calculated in PHP after the points are returned from the database. What I have done is, after getting all the points in the circle returned from the query, during the while loop of assigning the fetch_array to my own array, I calculate distance and add that to my array.

Here's a snippet from a class I had written to do this. If you want, I can post the whole class or you can PM me if you'd like. Hope it helps...

PS, To sort this you can use whichever array sorting functions best suits your array to sort by the 'distance' field.

Code: Select all

<?php
function results_in_radius($userZip, $radius, $count_only = 0)
{
	global $db;

	$sql = "SELECT c.*, z." . $this->zipColState . " AS zstate 
			FROM " . $this->tblClientData . " c INNER JOIN " . $this->tblZipData . " z ON c.member_zipcode = z." . $this->zipColZip . "
			WHERE (
			pow(
				(69.1*(z." . $this->zipColLon . "-(" . $this->get_longitude($userZip) . ")))*cos(" . $this->get_latitude($userZip) . "/57.3),2)+pow((69.1*(z." . $this->zipColLat . "-" . $this->get_latitude($userZip) . ")),2)
			) < (" . $this->radius . " * " . $this->radius . ")"; 

	if ( !($result = $db->sql_query($sql)) )
	{
		return false;
	}
	
	if ($count_only) 
	{
		$result_count = $db->sql_numrows($result);
		return $result_count;
	}
	else 
	{
		$counter = 0;
		while ( $row = $db->sql_fetchrow($result))
		{
			$row['distance'] = $this->set_distance($userZip, $row['member_zipcode']);
			$this->searchResults[$counter] = $row;
			$counter++;
		}
		
		return $this->searchResults;
	}
			
	return true;
}

function set_distance($userZip, $clientZip)
{
	$this->userLat = $this->get_latitude($userZip);
	$this->userLon = $this->get_longitude($userZip);
	$this->clientLat = $this->get_latitude($clientZip);
	$this->clientLon = $this->get_longitude($clientZip);
	
	
	$this->userLat = $this->deg_to_rad($this->userLat);
	$this->userLon = $this->deg_to_rad($this->userLon);
	
	$this->clientLat = $this->deg_to_rad($this->clientLat);
	$this->clientLon = $this->deg_to_rad($this->clientLon);
	
	$delta_lat = $this->clientLat - $this->userLat;
	$delta_lon = $this->clientLon - $this->userLon;
	
	/* Find the Great Circle distance */
	$dist_factor = pow(sin($delta_lat/2.0),2) + cos($this->userLat) * cos($this->clientLat) * pow(sin($delta_lon/2.0),2);
	$earth_radius = 3956;
	$distance = $earth_radius * 2 * atan2(sqrt($dist_factor),sqrt(1-$dist_factor));
	$this->distance = $distance;
	return $this->distance;
	
}
?>
Last edited by RobertGonzalez on Fri Mar 17, 2006 10:43 am, edited 1 time in total.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Found the problem was an OR where there should have been an AND much earlier in the query.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Thanks Everah, I appreciate the help. I am using the query to avoid pulling records outside of a certain distance to make it more efficient. Once the results are returned I use a function to calculate the distance.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Post by Weirdan »

was it working properly before the addition of ORDER BY clause? ;)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

I didn't test it before the clause because I hadn't written it yet. It's a dynamic query that is rather large, (over 1000 lines or code to create it), and there are some rogue OR statements in it that I need to resolve. So sometimes it works and sometimes it doesn't, but I know how to fix it so I am good to go on this one.
Post Reply