Distance in a query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Distance in a query

Post by s.dot »

I have a distance formula to calculate the distance between two points. However, it would be easy if I can make a column alias in a query so I could use it in an ORDER BY clause.

Something like the following:

Code: Select all

$result = mysql_query("SELECT `foo`,`bar`,`lat`/`long` AS `distance` FROM `table` ORDER BY `distance` ASC");
I would have the zip code and lat/long points to calculate distance, but is it possible to do the math inside of a query?
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

yes, it's possible.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Code: Select all

// radius is miles
$query = "WHERE (POW((69.1*(Longitude-\"$Longitude\")*cos($Latitude/57.3)),\"2\")+POW((69.1*(Latitude-\"$Latitude\")),\"2\"))<($radius*$radius)";
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

@agtlewis, I have the WHERE part down, I just want to be able to select the distance from a given point, so i can use it as a value in my where clause.

if feyd is correct (i do not doubt) then it will save me a bunch of lines of code.

I found this function in a zip code class that I modified to suit my needs

Code: Select all

function calculate_mileage($lat1,$lat2,$lon1,$lon2,$unit){
      $lat1 = deg2rad($lat1);
      $lon1 = deg2rad($lon1);
      $lat2 = deg2rad($lat2);
      $lon2 = deg2rad($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 = 3956 * 2 * atan2(sqrt($temp),sqrt(1-$temp));

      if($unit == "M"){
	      return round($distance,2);
      }
      
      if($unit == "K"){
	      return round($distance*1.609,2);
      }
      
      return false;
      
}
If I could do something that would return the same result as this, so I could SELECT `thismathcrap` AS `distance`...... and `distance` would be a number like 3.02 to use in my order by clause, then that would be nothing short of magical :-D
Last edited by s.dot on Thu Apr 27, 2006 12:22 am, edited 1 time in total.
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Oh sorry I posted the wrong bit lol.

Code: Select all

$query = "ORDER BY POW((69.1*(Longitude-\"$Longitude\")*cos($Latitude/57.3)),\"2\")+POW((69.1*(Latitude-\"$Latitude\")),\"2\") ";
Post Reply