# Vincenty's formulae to calculate in MySQL and PHP

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

• using the code from joel lawheads book learning geospatial analysis with python I get the following values with the vincenty formula: 0.21057333441532497 meter 0.6908573963757381 feet maybe you can port the code to mysql
– Kurt
Oct 1, 2016 at 5:57
• More info about the book and the code can you find here: python 2: packtpub.com/application-development/… python 3: packtpub.com/application-development/…
– Kurt
Oct 1, 2016 at 6:47
• Python might be good choice but our server doesnt allow other then PHP and MySQL.So we have to opt for MySQL cuz of performance Oct 2, 2016 at 2:04

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;
\$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;
\$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;

//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);
//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;
?>
``````
• How can i solve above problem Oct 1, 2016 at 1:03
• @Midavalo see edited answer Oct 1, 2016 at 11:52
• Actually i am trying to do queries in MySQL just because to save server load and time.In PHP case first i need to retrieve around 1000s of latitude and longitude pairs then compare each value to others then send back to MySQL to get details of location.So its time consuming and increase server load.So how can i run above Equirectangular in MySql and how its different from Vincenty Formula.Is Equirectangular is accurate as Vincenty Formula.If no then how its different in speed. Oct 2, 2016 at 2:01
• @Midavalo At the small distances you require the type of spheroid of the earth is not important, Vincenty v Haversine. Oct 2, 2016 at 12:09