Distance calculation based on multiple locations (lat, lon) including starting/ finishing point

[nita]

I would like to calculate the total distance of driving beetween multiple locations (loop), including the distance (starting point (garage) - first location sarting point) and (last location finishig point - finishing point (garage)). Example: (Garage + D1) + (D1 + D2) + (D2 + E1) + (E1 + E2) + E2 + Garage)

I'm having a problem with the correct looping. Here's my simplified code:

 <?
$driver = 5;

     $result2 = mysql_query("SELECT * FROM test WHERE id='$driver' LIMIT 1") or die(mysql_error());
     while($row2 = mysql_fetch_array( $result2 )) {
         $lon=$row2['lon'];
         $lat=$row2['lat'];
    echo "$lon, $lat";
     }

   $result = mysql_query("SELECT * FROM test1 WHERE driver='$driver'") or die(mysql_error());  
    while($row = mysql_fetch_array( $result )) {

         $lon1=$row['lon1'];
         $lat1=$row['lat1'];
         $lon2=$row['lon2'];
         $lat2=$row['lat2'];

        //////////  distance between driver address and starting address    
        $distancecalc = (3958*3.1415926*sqrt(($lat-$lat1)*($lat-$lat1) + cos($lat/57.29578)*cos($lat1/57.29578)*($lon-$lon1)*($lon-$lon1))/180);
        //////////  distance between statring address and finishing address  - multiple adsresses
        $distancecalc1 = $distancecalc1 + (3958*3.1415926*sqrt(($lat2-$lat1)*($lat2-$lat1) + cos($lat2/57.29578)*cos($lat1/57.29578)*($lon2-$lon1)*($lon2-$lon1))/180);
        //////////  distance between finishing address and driver address
        $distancecalc2 = (3958*3.1415926*sqrt(($lat2-$lat)*($lat2-$lat) + cos($lat2/57.29578)*cos($lat/57.29578)*($lon2-$lon)*($lon2-$lon))/180);

        $distancetotal = $distancecalc + $distancecalc1 +$distancecalc2;

        echo "$distancecalc<br>
        $distancecalc1<br>
        $distancecalc2<br>";
    }
    echo "$distancetotal";
  ?> 

I'm aware that code posted above doesnt't do what it meant to .. i just want to keep it clear.. there is some things i tried but no correct resoults.

I would appreciate some help on this one.

Thank you very much.

 

[mac_gyver]

here are some hints -

 

1) you need to write a function to calculate the distance between points so that you don't need to copy/paste/change that line of code everywhere you are using it

 

2) you need to define the steps that solve the problem before you can write any code to do it. see the following outline/pseudo code -

// get Garage location for the driver ($lat,$lon)
 
// get and loop over the route points for the driver (rows must be retrieved in the correct order)

$first_pass = true;  // flag to detect the first row inside the loop
$distance = 0; // accumulate the distance
while($row = mysql_fetch_assoc($result)){

    // assign $lat1,$lon1,$lat2,$lon2
 
    // calculate the distance from the Garage to the first point of the first row
    if($first_pass){
        $distance += calc_distance($lat,$lon,$lat1,$lon1);
        $first_pass = false;
    }

    // calculate the distance for each row (segment) in the route
    $distance += calc_distance($lat1,$lon1,$lat2,$lon2);
}
 
// calculate the distance from the second point of the last row to the Garage
$distance += calc_distance($lat2,$lon2,$lat,$lon);

[ignace]

https://github.com/jsor/Geokit/blob/master/src/Geokit/Calc.php

 

This class shows you how you can calculate the distance between 2 lat/lon points. Use the distanceHaversine method.

[mac_gyver]

a slightly different method to accomplish the same steps (runs faster when there are many rows) -

// get Garage location for the driver ($lat,$lon)

// get the route points for the driver (rows must be retrieved in the correct order)

$distance = 0; // accumulate the distance

// fetch the first row from the result set and get the first point ($lat1,$lon1)

// calculate the distance from the Garage to the first point of the first row
$distance += calc_distance($lat,$lon,$lat1,$lon1);

mysql_data_seek(0); // reset to the first row of the result set

// loop over the route points for the driver
while($row = mysql_fetch_assoc($result)){

    // assign $lat1,$lon1,$lat2,$lon2
 
    // calculate the distance for each row (segment) in the route
    $distance += calc_distance($lat1,$lon1,$lat2,$lon2);
}
 
// calculate the distance from the second point of the last row to the Garage
$distance += calc_distance($lat2,$lon2,$lat,$lon);

[nita]

Came up with following code:

<?
include "connectdb.php";

$driver = 5;
$datestamp = '2013/05/07';

$result2 = mysql_query("SELECT * FROM drivers WHERE id='$driver' LIMIT 1") or die(mysql_error());
while($row2 = mysql_fetch_array( $result2 )) {
         $lon=$row2['lon'];
         $lat=$row2['lat'];
     }
?>

    <?
    
    $distance = 0; // accumulate the distance
    
$result = mysql_query("SELECT * FROM quotedb WHERE moveday='$datestamp' AND driver='$driver' AND cleared='Not Cleared' AND status='Done' ORDER BY moveday, timeday") or die(mysql_error());  
while($row = mysql_fetch_assoc( $result )) {
    
        $first_pass = true;  // flag to detect the first row inside the loop
         $lon1=$row['lon1'];
         $lat1=$row['lat1'];
         $lon2=$row['lon2'];
         $lat2=$row['lat2'];
        
         echo "$lon, $lat<br>$lon1, $lat1, $lon2, $lat2<br>";
        
         // calculate the distance from the Garage to the first point of the first row
    if($first_pass){
        $distance += (3958*3.1415926*sqrt(($lat-$lat1)*($lat-$lat1) + cos($lat/57.29578)*cos($lat1/57.29578)*($lon-$lon1)*($lon-$lon1))/180);
        $first_pass = false;
    }

    // calculate the distance for each row (segment) in the route
    $distance += (3958*3.1415926*sqrt(($lat2-$lat1)*($lat2-$lat1) + cos($lat2/57.29578)*cos($lat1/57.29578)*($lon2-$lon1)*($lon2-$lon1))/180);
    
        
    }
    
    // calculate the distance from the second point of the last row to the Garage
    $distance +=  (3958*3.1415926*sqrt(($lat2-$lat)*($lat2-$lat) + cos($lat2/57.29578)*cos($lat/57.29578)*($lon2-$lon)*($lon2-$lon))/180);

echo "$distance";

  ?>

Edited May 10, 2013 by nita

[nita]

i will also try your second solution once i get this looping around !!

[nita]

https://github.com/jsor/Geokit/blob/master/src/Geokit/Calc.php

 

This class shows you how you can calculate the distance between 2 lat/lon points. Use the distanceHaversine method.

i will aplay other calculation method for sure. My problem here at the moment is to que up calculations. Thank for a tip.

[nita]

how do i aplay:

$distance += calc_distance($lat1,$lon1,$lat2,$lon2);

should i use function:

function calc_distance

($lat1, $lon1, $lat2, $lon2) {
    return (3958*3.1415926*sqrt(($lat2-$lat1)*($lat2-$lat1) + cos($lat2/57.29578)*cos($lat1/57.29578)*($lon2-$lon1)*($lon2-$lon1))/180);
}

but then what about

$distance += calc_distance($lat2,$lon2,$lat,$lon);

the variables are different ????

Edited May 10, 2013 by nita

[nita]

There is proggres. Code calculates distance beetween garage and starting point of 1st location. Then calculates well distances between starting points and finishing points of locations. Also calculate well distance between dinishing point of the last location and garage.

 

What im missing is: distances between finishig pont of 1st location and starting point of 2nd location and so on ...

 

(Garage + D1) + (D1 + D2) + (D2 + E1) + (E1 + E2) + E2 + Garage)

[mac_gyver]

if you where doing this on paper, how would you find the distance between the last point in one row and the first point in the next row?

 

the point of us helping you, is that you try to do this yourself first, not just to post what you want.

[nita]

guess you right, working on it, manual (on paper thats easy) - missing logic in my code ...

Edited May 10, 2013 by nita

[nita]

Ok. I worked it out. All calculations are done as i wish.  My code below:

<?
include "connectdb.php";
$driver = 5;
$datestamp = '2013/05/07';
$result2 = mysql_query("SELECT * FROM drivers WHERE id='$driver' LIMIT 1") or die(mysql_error());
while($row2 = mysql_fetch_array( $result2 )) {
		 $lon=$row2['lon'];
		 $lat=$row2['lat'];
	 }

$result = mysql_query("SELECT * FROM quotedb WHERE moveday='$datestamp' AND driver='$driver' AND cleared='Not Cleared' AND status='Done' ORDER BY moveday, timeday") or die(mysql_error());  
	$distance = 0; // accumulate the distance
	$first_pass = true;  // flag to detect the first row inside the loop
while($row = mysql_fetch_assoc( $result )) {
		 $lon2a=$lon2;
		 $lat2a=$lat2;
		 $lon1=$row['lon1'];
		 $lat1=$row['lat1'];
		 $lon2=$row['lon2'];
		 $lat2=$row['lat2'];
		 // calculate the distance from the Garage to the first point of the first row
    if($first_pass){
        $distance += (3958*3.1415926*sqrt(($lat-$lat1)*($lat-$lat1) + cos($lat/57.29578)*cos($lat1/57.29578)*($lon-$lon1)*($lon-$lon1))/180);
        $first_pass = false;
    }
    // calculate the distance for each row (segment) in the route
    $distance += (3958*3.1415926*sqrt(($lat2-$lat1)*($lat2-$lat1) + cos($lat2/57.29578)*cos($lat1/57.29578)*($lon2-$lon1)*($lon2-$lon1))/180);
	if ( $lon2a == "" or $lat2a =="" ) {			
		} else {
		// calculate the distance from the second point of the first row to the first point of the next row	
	$distance += (3958*3.1415926*sqrt(($lat2a-$lat1)*($lat2a-$lat1) + cos($lat2a/57.29578)*cos($lat1/57.29578)*($lon2a-$lon1)*($lon2a-$lon1))/180);
		}
	}
	// calculate the distance from the second point of the last row to the Garage
	$distance += (3958*3.1415926*sqrt(($lat2-$lat)*($lat2-$lat) + cos($lat2/57.29578)*cos($lat/57.29578)*($lon2-$lon)*($lon2-$lon))/180);
echo "$distance<br>
";
  ?>

Don't get me wrong, but i think it  can be done more proffesional way.. that is what i try to achive .. and still have a lot to learn - i realize that. Do you guys have some suggestion to improve this piece of code ..(will aplay Haversine method for calculations).  THX

[mac_gyver]

because you are carrying over the end point from the previous row in variables, you can just put the starting Garage point into those variables before the start of the loop and you will no longer need to have any of the $first_pass logic or the if( $lon2a == "" or $lat2a =="") logic. the only things inside of the loop would be the assignment statements for the variables and two $distance += ... statements.

[nita]

improved version:

<?

include "connectdb.php";

$driver = 5;

$datestamp = '2013/05/07';

$result2 = mysql_query("SELECT * FROM drivers WHERE id='$driver' LIMIT 1") or die(mysql_error());

while($row2 = mysql_fetch_array( $result2 )) {

         $garage_lon=$row2['lon'];

         $garage_lat=$row2['lat'];

     }


$result = mysql_query("SELECT * FROM quotedb WHERE moveday='$datestamp' AND driver='$driver' AND cleared='Not Cleared' AND status='Done' ORDER BY moveday, timeday") or die(mysql_error());  


    function calculate_distance($lon1, $lat1, $lon2, $lat2) {

    return (3958 * 3.1415926 * sqrt(($lat2 - $lat1) * ($lat2 - $lat1) + cos($lat2 / 57.29578) * cos($lat1 / 57.29578) * ($lon2 - $lon1) * ($lon2 - $lon1)) / 180);}  


$previous_lon = $garage_lon;

$previous_lat = $garage_lat;

$distance = 0; // accumulate the distance


while($row = mysql_fetch_assoc( $result ))

{

    $lon1 = $row['lon1'];

    $lat1 = $row['lat1'];

    $lon2 = $row['lon2'];

    $lat2 = $row['lat2'];

    if ( $previous_lon && $previous_lat )

    {

	    // calculate the distance from the Garage to the first point of the first row

	    $distance += calculate_distance($lon1, $lat1, $previous_lon, $previous_lat);

    }

    // calculate the distance for each row (segment) in the route

    $distance += calculate_distance($lon1, $lat1, $lon2, $lat2);

    $previous_lon = $lon2;

    $previous_lat = $lat2;

}

// calculate the distance from the second point of the last row to the Garage

$distance += calculate_distance($garage_lon, $garage_lat, $lon2, $lat2);  

    $distance = round($distance,0);

echo "$distance<br>

";

  ?>

Edited May 10, 2013 by nita