[GIS] Vincenty’s formulae to calculate in MySQL and PHP

distancegeolocationhaversineMySQL

I was reading Haversine formula on wikipedia and at the end of article its state that "More accurate methods that consider the Earth's ellipticity are given by Vincenty's formula and the other formulas in the geographical distance article.". Now It made me think that Vincenty's formula has better scope when calculating distance between 2 pair of latitude and longitude (Distance between two points ).

So i need to calculate minimum distance between 2 points in FEET

My questions are

1) Is Vincenty Formula the better option to calculate distance between 2 points than Haversine formula when generating latitude and longitude from Android Device

2) If Vincenty Formula is the better option then how to calculate distance between 2 points in FEET in MySQL

3) For example:Latitude 28.5312119 Longitude 77.2569046 and Latitude 28.5312138 Longitude 77.2569046 . these locations is generated from same location (with few feet difference).As you can see longitude is same but latitude is bit different in last decimals places only.

When i calculate these two location with Haversine formula i got 0 meters difference,When i know difference is few meters.So how can i achieve results in Feet with Vincenty Formula to get actual difference between these two locations. Please Also Read

Best Answer

With points as close as these we can ignore what type of spheroid the earth is and use the eqirectangular projection Pythagoras formula is all that is required.

The following two functions are in PHP.

function Equirectangular($lat1,$lng1,$lat2,$lng2){
$lng1 = abs($lng1);
$lng2 = abs($lng2);
$alpha = $lng2-$lng1;
$x = deg2rad($alpha) * cos(deg2rad($lat1+$lat2)/2);
$y = deg2rad($lat1-$lat2);
$R = 6372.8*1000; // gives d in km
$distance = sqrt($x*$x + $y*$y) * $R;
return $distance;
}



function Haversine($lat1,$lng1,$lat2,$lng2) {
  $deltaLat = $lat2 - $lat1 ;
  $deltaLng = $lng2 - $lng1 ;
  $earthRadius = 6372.8 *1000; // 3959 in miles 6371 in meters.
  $alpha = $deltaLat/2;
  $beta = $deltaLng/2;
  $a = sin(deg2rad($alpha)) * sin(deg2rad($alpha)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * sin(deg2rad($beta)) * sin(deg2rad($beta)) ;
  $c = 2 * atan2(sqrt($a), sqrt(1-$a));
  $distance = $earthRadius * $c;
  return $distance;
  }

As you can Equirectangular() is simpler than Haversine()and can be used for small distances. I have multiplied earth radius by 100 to convert to metres.

Result. Equirectangular 0.211513 m Haversine 0.211513 m The following code can be modified to suit your database

PDOPyth.php
Use PDO with parameters to query database using Equirectangular approximation formula for range.
Save exceptions to file.
02/03/2016
*/
require("dbinfo2.php");

//Create coordinates
$lat = 52;
$lng =-2;
$radius  = 25;


//Connect to database
$dbh = new PDO("mysql:host=$host;dbname=$database", $username);
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
try {
    // Prepare statement
    //SELECT (SQRT( POW(RADIANS(lng1 -lng2), 2) + POW(RADIANS(lat1 - lat2)* COS(RADIANS(lat1 - lat2)/2),2  ))*6372
    $stmt1 = $dbh->prepare("SELECT  name, lat, lng, ( SQRT( POW(RADIANS(? - lng), 2) + POW(RADIANS(? - lat)* COS(RADIANS(? - lat)/2),2  ))*6372 ) AS distance FROM gbstn HAVING distance < ? ORDER BY distance ");
    // Assign parameters
    $stmt1->bindParam(1,$lng);
    $stmt1->bindParam(2,$lat);
    $stmt1->bindParam(3,$lat);
    $stmt1->bindParam(4,$radius);
    //Execute query
    $stmt1->setFetchMode(PDO::FETCH_ASSOC);
    $stmt1->execute();
    $data = array();
    // Iterate through the rows, adding nodes for each
    while($row = $stmt1->fetch()) {
        $data[] = $row;

    }
echo $lat ." ".$lng;
echo "<BR>" ;
echo json_encode($data);
}


catch(PDOException $e) {
    echo "I'm sorry I'm afraid you can't do that.". $e->getMessage() ;// Remove or modify after testing 
    file_put_contents('PDOErrors.txt',date('[Y-m-d H:i:s]').", mapSelect.php, ". $e->getMessage()."\r\n", FILE_APPEND);  
 }
//Close the connection
$dbh = null; 
?>
Related Question