Jump to content

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


nita

Recommended Posts

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.

 

Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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);
Link to comment
Share on other sites

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 by nita
Link to comment
Share on other sites

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 by nita
Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by nita
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.