unistake Posted January 4, 2016 Share Posted January 4, 2016 Hi all, I am trying to calculate the straight line distance between two points by using their co-ordinates on each row from a mysql db that satisfies the query. It works fine, except the last for loop below calculates $lat[$x+1],$long[$x+1] to have blank fields as they no longer exist in the database therefore i have invalid distances calculated. My goal of this script is to find all the routes a particular day a flight attendant is working, by searching my db, rosters, then gathering the departure and arrival airport from this table. With these airports, I then extract their co-ordinates from the table, airports, and use the function below to calculate the distance. Hope it makes sense what I am trying to do below!! <?php function distance($lat1, $lon1, $lat2, $lon2, $unit) { $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; $unit = strtoupper($unit); if ($unit == "K") { return ($miles * 1.609344); } else if ($unit == "N") { return ($miles * 0.8684); } else { return $miles; } } $today = '2016-01-04'; $sql = "SELECT * FROM rosters WHERE Code = 'TESTUSER' AND SectorDate = '$today' ORDER BY BeginTime ASC"; $result = mysqli_query($cxn,$sql) or die ("Cant get rosters distance."); $dep = array(); $arr = array(); $lat = array(); $long = array(); while($row=mysqli_fetch_array($result)) { $dep[] = $row['Dep']; $arr[] = $row['Arr']; } echo 'Number of flights: '.count($dep).'<br />'; foreach($dep as $key => $value) { $sql = "SELECT * FROM airports WHERE IATA = '$value'"; $result = mysqli_query($cxn,$sql) or die ("Cant get airport coordinates."); while($row=mysqli_fetch_array($result)) { $lat[] = $row['Latitude']; $long[] = $row['Longitude']; } } $max = count($dep); for ($x = 0; $x <= $max; $x++) { echo $dep[$x].' - '.$arr[$x].'<br />'; echo floor(distance($lat[$x],$long[$x],$lat[$x+1],$long[$x+1], "N")) . "nm<br />"; } ?> Quote Link to comment Share on other sites More sharing options...
mac_gyver Posted January 4, 2016 Share Posted January 4, 2016 (edited) $x <= $max; the problem is the above comparison in your for(){} loop. $max is the count of the items in the array. if there are 10 items in the array, $max will be 10, but the array indexes would be 0-9. you would want to use $x < $max; next, don't run queries inside of loops. this a performance killer. edit: run one JOINed query that gets the related data all at once. i would get a unique list of all the departure and arrival airports. then implode these into a comma separated list of quoted-string values, then use WHERE IATA IN(the list would go here) in ONE query to get all the lat/long values in one query. edit: also, if i read what you are doing correctly, you would need to get the lat/long for the final arrival airport. your current code is only getting the lat/long for the departure airports. see the related edit i also made in the above paragraph. Edited January 4, 2016 by mac_gyver Quote Link to comment Share on other sites More sharing options...
requinix Posted January 4, 2016 Share Posted January 4, 2016 First, the condition should have been $x because $dep[$max] is one too far since the array keys will be 0 through $max-1. What he said. The problem is the loop is going too far, right? So don't make it go that far. for ($x = 0; $x and then output the final lat/long pair since the loop didn't reach it. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.