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

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\") ";