how to find the total distance traveled, based on geo points stored in database

Question

Using an android app I am sending latitude and longitude values to database.
This is my database

Now in webpage I need to show the total distance traveled by K.Prathibha.N

I've used Google Maps Direction API to find the distance but it is accepting 'id=2' as origin and 'id=5' as destination. But my motto is to find the total distance travelled i.e., id_2 to id_3 to id_4 to id_5.

I am using while loop here.

Code snippet:

<?php
          $params_string="";
            $pname = $_POST['name']; //name is passed from a dropdown
            $query = "SELECT * FROM information WHERE mobile=? ORDER BY id DESC ";      
            $result = $dbConnection->prepare($query);
            $result->execute(array($pname));                                        
            if($result->rowCount() >0)
            {
                $params = array();

                while($row = $result->fetch())
                {
                    $mobile = $row['mobile'];
                    $latitude = $row['latitude'];
                    $cname = $row['name'];  
                    $longitude = $row['longitude'];
                    $lat = "17.4136846";    
                    $long = "78.49228289999996";

                    $params = array("origin" => $latitude.",".$longitude,"destination" => $lat.",".$long,"sensor"   => 'true',"units" => 'imperial');       

//Join parameters into URL string
    foreach($params as $var => $val)
    {
    $params_string .= '&' . $var . '=' . urlencode($val); 
    }           

                }
            }

    // Request URL
    $url = "http://maps.googleapis.com/maps/api/directions/json?".ltrim($params_string, '&');

    // Make our API request
    $curl = curl_init();
    curl_setopt($curl, CURLOPT_URL, $url);
    curl_setopt($curl, CURLOPT_RETURNTRANSFER, 1);
    $return = curl_exec($curl);
    curl_close($curl);

    // Parse the JSON response
    $directions = json_decode($return);

    // Show the total distance 

    print('<p><strong>Total distance:</strong> ' . ceil(($directions->routes[0]->legs[0]->distance->text)* 1.609344) .' KM'. '</p>');

    ?>

Now,How to pass the destination values here.

My task is to pass database values to variables lat and long....Just for example i've passed manual values..how to fetch database values simultaneously so as origin gets id 2 and destination gets id 3 values

Example-If it is accepting

origin as id-2 then destination should be id-3 next

origin as id-3 then destination should be id-4 and so on

at the end give total distance travelled on that particular day based on the available geo points.

Struggling with this from more than 3 days.

Any kind of suggestions would be of great help.

Letishaletitia

Answer 1

Well, You can do like this.

For eg.

  $qry = mysql_query("select *  from store");
    $num = mysql_num_rows($qry);
    while($row = mysql_fetch_array($qry)){
    $lat[] = $row['lat'];
    $long[] =$long['long'];
    }
    for($i=0;$i<$num;$i++){
    $distance=distance($lat[$i],$long[$i],$lat[$i+1],$long[$i+1],"k");
    }

    function:

    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.60934);
            } else if ($unit == "N") {
              return ($miles * 0.8684);
            } else {
              return $miles;

Vimen

thanx again...ok..let me try this technique...i think it will match the requirement.. – Letishaletitia

not working @user2763731.. $lat[$i+1],$long[$i+1] these 2 commands are not accepting the 2nd row values..tried to implement ur technique in many ways..but no progress :( – Letishaletitia

how come??r u using the same logic which u gave here..or any thing u've added – Letishaletitia

Notice: Undefined offset: 2 in /opt/lampp/htdocs/Prathibha/superadmin_cp/direct.php on line 304 --- line 304::: $distance=distance($lat[$i],$long[$i],$lat[$i+1],$long[$i+1],"k"); – Letishaletitia

i am getting this notice but i am getting result also. these are just notice. to disappear these just put error_reporting(0); at the top. – Vimen

there is just one error just change to $long[] =$long['long']; from $long[] =$row['long']; – Vimen

its not all accepting the 2nd row values..when i echo it,it is displaying all the records but it is not passing 2nd row as destination...even if i give manual lat and long it is giving same error – Letishaletitia

After a lot of hard-work completed this..and achieved the distance but am getting 600m difference in actual distance and calculated distance – Letishaletitia

Answer 2

You can use call this function in your while loop continuosly

 $latitude = $row['latitude'];
 $longitude = $row['longitude'];
 $lat = "17.4136846";    
 $long = "78.49228289999996";

$distance=distance($latitude,$longitude,$lat,$long,"k");

and the function is

    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.60934);
        } else if ($unit == "N") {
          return ($miles * 0.8684);
        } else {
          return $miles;
        }
    }
//remember you pass last parameter in function k. so it you you the   //distance in kilometer

Vimen

Thank you so much for ur time and attention..but my task is to pass database values to variables lat and long,,..for clarity i've passed manual values..how to fetch database values simultaneously so as origin gets id 2 and destination gets id 3 values – Letishaletitia