Page 1 of 1

Distance in a query

Posted: Wed Apr 26, 2006 10:13 pm
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?

Posted: Wed Apr 26, 2006 11:02 pm
by feyd
yes, it's possible.

Posted: Thu Apr 27, 2006 12:01 am
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)";

Posted: Thu Apr 27, 2006 12:19 am
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

Posted: Thu Apr 27, 2006 12:21 am
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\") ";