Analytics & TestingCRM and Data PlatformsMarketing Tools

Calculate or Query Great Circle Distance Between Points of Latitude and Longitude Using The Haversine Formula (PHP, JavaScript, Java, Python, MySQL, MSSQL Examples)

This month, I’ve been programming in PHP and MySQL for GIS. While researching the topic, I had difficulty finding geographic calculations to find the distance between two locations, so I wanted to share them here.

Flight Map Europe With Great Circle Distance

The simple way of calculating a distance between two points is using the Pythagorean formula to calculate the hypotenuse of a triangle (A² + B² = C²). This is known as the Euclidean distance.

That’s an interesting start, but it doesn’t apply to geography since the distance between lines of latitude and longitude are not equal distances apart. As you get closer to the equator, lines of latitude get further apart. If you use a simple triangulation equation, it may measure distance accurately in one location and wrong in the other because of the curvature of the Earth.

Great Circle Distance

The routes traveled long distances around the Earth are known as the Great Circle Distance. That is… the shortest distance between two points on a sphere differs from the points on a flat map. Combine that with the fact that latitude and longitude lines aren’t equidistant… and you’ve got a difficult calculation.

Here’s a fantastic video explanation of how Great Circles work.

The Haversine Formula

The distance using the curvature of the Earth is incorporated in the Haversine formula, which uses trigonometry to allow for the Earth’s curvature. When you’re finding the distance between 2 places on Earth (as the crow flies), a straight line is actually an arc.

This is applicable in air flight – have you ever looked at the actual map of flights and noticed they are arched? That’s because flying in an arch between two points is shorter than directly to the location.

PHP: Calculate Distance Between 2 Points of Latitude and Longitude

Here’s the PHP formula for calculating the distance between two points (along with Mile vs. Kilometer conversion) rounded to two decimal places.

function getDistanceBetweenPointsNew($latitude1, $longitude1, $latitude2, $longitude2, $unit = 'miles') {
  $theta = $longitude1 - $longitude2; 
  $distance = (sin(deg2rad($latitude1)) * sin(deg2rad($latitude2))) + (cos(deg2rad($latitude1)) * cos(deg2rad($latitude2)) * cos(deg2rad($theta))); 
  $distance = acos($distance); 
  $distance = rad2deg($distance); 
  $distance = $distance * 60 * 1.1515; 
  switch($unit) { 
    case 'miles': 
      break; 
    case 'kilometers' : 
      $distance = $distance * 1.609344; 
  } 
  return (round($distance,2)); 
}

The variables are:

  • $Latitude1 – a variable for your first location’s latitude.
  • $Longitude1 – a variable for your first location’s longitude
  • $Latitude2 – a variable for your second location’s latitude.
  • $Longitude2 – a variable for your second location’s longitude.
  • $unit – the default being miles. This can be updated or passed as kilometers.

Java: Calculate Distance Between 2 Points of Latitude and Longitude

public static double getDistanceBetweenPointsNew(double latitude1, double longitude1, double latitude2, double longitude2, String unit) {
    double theta = longitude1 - longitude2;
    double distance = 60 * 1.1515 * (180/Math.PI) * Math.acos(
        Math.sin(latitude1 * (Math.PI/180)) * Math.sin(latitude2 * (Math.PI/180)) + 
        Math.cos(latitude1 * (Math.PI/180)) * Math.cos(latitude2 * (Math.PI/180)) * Math.cos(theta * (Math.PI/180))
    );
    if (unit.equals("miles")) {
        return Math.round(distance, 2);
    } else if (unit.equals("kilometers")) {
        return Math.round(distance * 1.609344, 2);
    } else {
        return 0;
    }
}

The variables are:

  • latitude1 – a variable for your first location’s latitude.
  • longitude1 – a variable for your first location’s longitude
  • latitude2 – a variable for your second location’s latitude.
  • longitude2 – a variable for your second location’s longitude.
  • unit – the default being miles. This can be updated or passed as kilometers.

JavaScript: Calculate Distance Between 2 Points of Latitude and Longitude

function getDistanceBetweenPoints(latitude1, longitude1, latitude2, longitude2, unit = 'miles') {
    let theta = longitude1 - longitude2;
    let distance = 60 * 1.1515 * (180/Math.PI) * Math.acos(
        Math.sin(latitude1 * (Math.PI/180)) * Math.sin(latitude2 * (Math.PI/180)) + 
        Math.cos(latitude1 * (Math.PI/180)) * Math.cos(latitude2 * (Math.PI/180)) * Math.cos(theta * (Math.PI/180))
    );
    if (unit == 'miles') {
        return Math.round(distance, 2);
    } else if (unit == 'kilometers') {
        return Math.round(distance * 1.609344, 2);
    }
}

The variables are:

  • latitude1 – a variable for your first location’s latitude.
  • longitude1 – a variable for your first location’s longitude
  • latitude2 – a variable for your second location’s latitude.
  • longitude2 – a variable for your second location’s longitude.
  • unit – the default being miles. This can be updated or passed as kilometers.

Python: Calculate Distance Between 2 Points of Latitude and Longitude

Here’s the Python formula for calculating the distance between two points (along with Mile vs. Kilometer conversion) rounded to two decimal places. Credit to my son, Bill Karr, a Data Scientist for OpenINSIGHTS, for the code.

from numpy import sin, cos, arccos, pi, round

def rad2deg(radians):
    degrees = radians * 180 / pi
    return degrees

def deg2rad(degrees):
    radians = degrees * pi / 180
    return radians

def getDistanceBetweenPointsNew(latitude1, longitude1, latitude2, longitude2, unit = 'miles'):
    
    theta = longitude1 - longitude2
    
    distance = 60 * 1.1515 * rad2deg(
        arccos(
            (sin(deg2rad(latitude1)) * sin(deg2rad(latitude2))) + 
            (cos(deg2rad(latitude1)) * cos(deg2rad(latitude2)) * cos(deg2rad(theta)))
        )
    )
    
    if unit == 'miles':
        return round(distance, 2)
    if unit == 'kilometers':
        return round(distance * 1.609344, 2)

The variables are:

  • latitude1 – a variable for your first location’s latitude.
  • longitude1 – a variable for your first location’s longitude
  • latitude2 – a variable for your second location’s latitude.
  • longitude2 – a variable for your second location’s longitude.
  • unit – the default being miles. This can be updated or passed as kilometers.

MySQL: Retrieving All Records Within A Range By Calculating Distance In Miles Using Latitude and Longitude

Using Spatial Data Types in MySQL is a more efficient and convenient way to work with geographical data, including calculating distances between points. MySQL supports Spatial Data Types such as POINT, LINESTRING, and POLYGON, along with spatial functions like ST_Distance.

When you use the ST_Distance function in MySQL with geographical data represented as POINT coordinates, it takes into account the curvature of the Earth’s surface. The spherical model used by ST_Distance employs the Haversine formula. This approximation is suitable for most practical purposes but may introduce slight inaccuracies for very long distances.

Here’s how you can calculate distances between two points using Spatial Data Types:

  1. Create a Table with Spatial Data Type: First, create a table with a POINT column to store geographical points. For example:
CREATE TABLE locations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    coordinates POINT
);

Insert your geographical points into this table using the POINT constructor:

INSERT INTO locations (name, coordinates)
VALUES
    ('Point A', POINT(40.7128, -74.0060)), -- New York City
    ('Point B', POINT(34.0522, -118.2437)); -- Los Angeles
  1. Calculate Distance Using ST_Distance: You can calculate the distance between two points using the ST_Distance function. Here’s an example query to calculate the distance between two points:
SELECT
    id1,
    id2,
    (ST_Distance(coordinates1, coordinates2) / 1609.344) AS distance_in_miles
FROM (
    SELECT
        l1.id AS id1,
        l2.id AS id2,
        l1.coordinates AS coordinates1,
        l2.coordinates AS coordinates2
    FROM
        locations l1,
        locations l2
    WHERE
        l1.id = 1 AND l2.id = 2
) AS distances;

Replace 1 and 2 with the IDs of the two points you want to calculate the distance between.

  1. Result: The query will return the distance between the two points in miles.

Using Spatial Data Types and the ST_Distance function provides a more efficient and accurate way to work with geographical data in MySQL. It also simplifies calculating distances between points, making it easier to manage and query your data.

MySQL: Retrieving All Records Within A Range By Calculating Distance In Kilometers Using Latitude and Longitude

By default ST_Distance returns the distance in meters, so you simply need to update the query for kilometers:

SELECT
    id1,
    id2,
    (ST_Distance(coordinates1, coordinates2) / 1000) AS distance_in_kilometers
FROM (
    SELECT
        l1.id AS id1,
        l2.id AS id2,
        l1.coordinates AS coordinates1,
        l2.coordinates AS coordinates2
    FROM
        locations l1,
        locations l2
    WHERE
        l1.id = 1 AND l2.id = 2
) AS distances;

Microsoft SQL Server Geographic Distance: STDistance

If you’re utilizing Microsoft SQL Server, they offer their own function, STDistance for calculating the distance between two points using the Geography data type.

DECLARE @g geography;  
DECLARE @h geography;  
SET @g = geography::STGeomFromText('LINESTRING(-122.360 47.656, -122.343 47.656)', 4326);  
SET @h = geography::STGeomFromText('POINT(-122.34900 47.65100)', 4326);  
SELECT @g.STDistance(@h);  

Hat tip to Manash Sahoo, founder and senior architect at Ion Three.

Douglas Karr

Douglas Karr is CMO of OpenINSIGHTS and the founder of the Martech Zone. Douglas has helped dozens of successful MarTech startups, has assisted in the due diligence of over $5 bil in Martech acquisitions and investments, and continues to assist companies in implementing and automating their sales and marketing strategies. Douglas is an internationally recognized digital transformation and MarTech expert and speaker. Douglas is also a published author of a Dummie's guide and a business leadership book.

Related Articles

Back to top button
Close

Adblock Detected

Martech Zone is able to provide you this content at no cost because we monetize our site through ad revenue, affiliate links, and sponsorships. We would appreciate if you would remove your ad blocker as you view our site.