Calculating distance

[armyguydave69]

Okay, this is probably a lame question. I have a search page which which needs to be sorted by distance when the results come back from mysql. What's the easiest way to do this since mysql orders the results?

heres the code i wanna try to use:

function CalculateDistance($lat1, $lon1, $lat2, $lon2, $distanceType) 
{ 

  $theta = $lon1 - $lon2; 
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
  $dist = acos($dist); 
  $dist = rad2deg($dist); 
  $miles = $dist * 60 * 1.1515;
  $distanceType = strtoupper($distanceType);

  if ($distanceType == "K") 
  {
    //return distance in kilometers
    return ($miles * 1.609344); 
  } 
     else 
     {
          //return distance in miles
          return $miles;
     }
}

Any suggestions?

[edwinhermann]

You'll need to do the distance calculation in mySQL. It's not that hard - I've taken your code and run a quick test on my server.
The only thing is you haven't said what your database tables are like, so you'll need to modify the table name, table columns and the WHERE condition accordingly:

$units_multiplier = (strtoupper($distanceType) == "K") ? 1.609344 : 1.0;
$sql = "SELECT lat1, lon1, lat2, lon2, ( $units_multiplier * 60 * 1.1515 * DEGREES( ACOS( SIN(RADIANS(lat1)) * SIN(RADIANS(lat2)) +  COS(RADIANS(lat1)) * COS(RADIANS(lat2)) * COS(RADIANS(lon1 - lon2)) )) ) dist FROM db_table WHERE (...) ORDER BY dist ASC";

Here's the output from my test directly from the mySQL database:

SELECT lat1, lon1, lat2, lon2, ( 1.0 * 60 * 1.1515 * DEGREES( ACOS( SIN( RADIANS( lat1 ) ) * SIN( RADIANS( lat2 ) ) + COS( RADIANS( lat1 ) ) * COS( RADIANS( lat2 ) ) * COS( RADIANS( lon1 - lon2 ) ) ) ) ) dist
FROM test
ORDER BY dist ASC 

lat1	lon1	lat2	lon2	dist 
67	28	67.5	30	63.62514789654
44	36	45	36.5	73.351606232911
61.5	60	62	57.5	88.745649991595
21.5	50	22.5	52.5	174.41105163118
38	21.5	41	21.5	207.27
27	58	27	54.5	215.45183608755
23	64.5	22.5	69	288.77814017427
72	68.5	54	53	1325.6352829963

You've stated that you're already using database calls, so I haven't shown any further code on reading the DB results and displaying that - I assume you know that part.

Also, I haven't checked your actual mathematical logic - I assumed you've done your calculations correctly in PHP and converted it to mySQL code.