Jump to content


Photo

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

calculation distance latitude longitude

  • Please log in to reply
13 replies to this topic

#1 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 01:59 AM

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.

 



#2 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,437 posts

Posted 10 May 2013 - 02:28 AM

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

multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#3 ignace

ignace

    Now mod flavored

  • Moderators
  • 6,249 posts
  • LocationBelgium

Posted 10 May 2013 - 02:57 AM

https://github.com/j...Geokit/Calc.php

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

#4 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,437 posts

Posted 10 May 2013 - 03:16 AM

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

multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#5 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 03:58 AM

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, 10 May 2013 - 04:04 AM.


#6 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 03:58 AM

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



#7 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 04:02 AM

https://github.com/j...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.



#8 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 04:06 AM

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, 10 May 2013 - 04:06 AM.


#9 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 04:37 AM

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)



#10 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,437 posts

Posted 10 May 2013 - 04:55 AM

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.


multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#11 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 05:03 AM

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


Edited by nita, 10 May 2013 - 05:04 AM.


#12 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 05:52 AM

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



#13 mac_gyver

mac_gyver

    Advanced Member

  • Administrators
  • 2,437 posts

Posted 10 May 2013 - 09:08 AM

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.


multi-purpose programming fool and resident naysayer [We try not be negative in replies, but telling someone what they're doing wrong, while staying politically correct, isn't always going to happen.]

#14 nita

nita

    Advanced Member

  • Members
  • PipPipPip
  • 85 posts

Posted 10 May 2013 - 11:00 AM

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, 10 May 2013 - 11:01 AM.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com