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 quite a bit in PHP and MySQL for GIS. While researching the topic, I had a hard time finding some of the 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, VP and Architect of Highbridge.

Douglas Karr

Douglas Karr is the founder of the Martech Zone and a recognized expert on digital transformation. Douglas has helped start several successful MarTech startups, has assisted in the due diligence of over $5 bil in Martech acquisitions and investments, and continues to launch his own platforms and services. He's a co-founder of Highbridge, a digital transformation consulting firm. Douglas is also a published author of a Dummie's guide and a business leadership book.

Related Articles

78 Comments

  1. Thank you very much for sharing. This was an easy copy and paste job and works great. You’ve saved me alot of time.
    FYI for anyone porting to C:
    double deg2rad(double deg) { return deg*(3.14159265358979323846/180.0); }

  2. Very nice piece of posting – worked very nice – I only had to change the name of the table holding the lat-long. It works pretty fast to.. I have a reasonably small number of lat-longs (< 400) but I think this would scale nicely. Nice site too – i have just added it to my del.icio.us account and will check back regularly.

    1. Glad to help out, rails friend!

      Now I’m in search of an ‘in Polygon’ PHP function that will take an array of sequenced latitude and longitude coordinates and figure out if another point is within or outside of the polygon.

  3. Thanks so much for sharing this code. It saved me a lot of development time. Also, thanks to your readers for pointing out that a HAVING statement is necessary for MySQL 5.x. Very helpful.

  4. Hello,

    Another question. Is there a formula for NMEA strings like the one below ?

    1342.7500,N,10052.2287,E

    $GPRMC,032731.000,A,1342.7500,N,10052.2287,E,0.40,106.01,101106,,*0B

    Thanks,
    Harry

  5. I also found that WHERE did not work for me. Changed it to HAVING and everything works perfect. At first i didnt read the comments and rewrote it using a nested select. Both will work just fine.

  6. Incredibly helpful, thank you very much! I was having some problems with the new “HAVING”, rather than “WHERE”, but once I read the comments here (after about half an hour of grinding my teeth in frustration =P), I got it working nicely. Thank you ^_^

  7. Keep in mind that a select statement like that will be very computationally intense and therefore slow. If you have a lot of those queries, it can bog things down quite quickly.

    A much less intense approach is to run a first (crude) select using a SQUARE area defined by a calculated distance i.e. “select * from tablename where latitude between lat1 and lat2 and longitude between lon1 and lon2”. lat1 = targetlatitude – latdiff, lat2 = targetlatitude + latdiff, similar with lon. latdiff ~= distance / 111 (for km), or distance/69 for miles since 1 degree of latitude is ~ 111 km (slight variation since earth is slightly oval, but sufficient for this purpose). londiff = distance / (abs(cos(deg2rad(latitude))*111)) — or 69 for miles (you can actually take a slightly larger square in order to account for variations). Then take the result of that and feed it into the radial select. Just don’t forget to account for out-of-bounds coordinates – i.e. the range of acceptable longitude is -180 to +180 and the range of acceptable latitude is -90 to +90 — in case your latdiff or londiff runs outside this range. Note that in most cases this may not be applicable since it only affects calculations over a line through the pacific ocean from pole to pole, though it does intersect part of chukotka and part of alaska.

    What we accomplish by this is a significant reduction in the number of points against which you make this calculation. If you have a million global points in the database distributed roughly evenly and you want to search within 100 km, then your first (fast) search is of an area 10000 sq km and will probably yield about 20 results (based on even distribution over a surface area of about 500M sq km), which means that you run the complex distance calculation 20 times for this query instead of a million times.

      1. Fantastic advice! I actually worked with a developer who wrote a function that pulled the inside square and then a recursive function that made ‘squares’ around the perimeter to include and exclude the remaining points. The result was an incredibly fast result – he could evaluate millions of points in microseconds.

        My approach above is definitely ‘crude’ but capable. Thanks again!

        1. Doug,

          I have been trying to use mysql and php to evaluate whether a lat long point is within a polygon. Do you know if your developer friend published any examples on how to accomplish this task. Or do you know any good examples. Thanks in advance.

  8. Hi everyone this is my test SQL statement:

    SELECT DISTINCT area_id, (
    (
    (
    acos( sin( ( 13.65 * pi( ) /180 ) ) * sin( (
    `lat_dec` * pi( ) /180 ) ) + cos( ( 13.65 * pi( ) /180 ) ) * cos( (
    `lat_dec` * pi( ) /180 )
    ) * cos( (
    ( 51.02 - `lon_dec` ) * pi( ) /180 )
    )
    )
    ) *180 / pi( )
    ) *60 * 1.1515 * 1.609344
    ) AS distance
    FROM `post_codes` WHERE distance <= 50

    and Mysql is telling me that distance, doesn’t exist as a column, i can use order by, i can do it without WHERE, and it works, but not with it…

  9. This is great, however it is just as the birds fly. It would be great to try and incorporate the google maps API to this somehow (maybe using roads etc.) Just to give an idea using a different form of transportation. I still have yet to make a simulated annealing function in PHP that would be able to offer an efficient solution to the traveling salesman problem. But I think that I may be able to reuse some of your code to do so.

  10. Good article! I found a lot of articles describing how to compute distance between two points but I was really looking for the SQL snippet.

  11. 2 days of research to finally find this page that solves my problem. Looks like i better bust out my WolframAlpha and brush up on my maths. The change from WHERE to HAVING has my script in working order. THANK YOU

  12. I wish this was the first page i’d found on this. After trying many different commands this was the only one to work properly, and with minimal changes needed to fit my own database.
    Thanks a lot!

  13. I wish this was the first page i’d found on this. After trying many different commands this was the only one to work properly, and with minimal changes needed to fit my own database.
    Thanks a lot!

  14. thanks for posting this helpful article,  
    but for some reason I’d like to ask
    how to get the distance between coords inside mysql db and coords inserted to php by user?
    for  more clearly describe:
    1.user have to insert [id] for selecting specified data from db and user itself’s coords
    2.the php file get the target data (coords) using [id] and then calculate distance between user and target point

    or can just simply get distance from the code below?

    $qry = “SELECT *,(((acos(sin((“.$latitude.”*pi()/180)) * sin((`Latitude`*pi()/180))+cos((“.$latitude.”*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((“.$longitude.”- `Longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance FROM `MyTable` WHERE distance >= “.$distance.”       >>>>can I “take out” the distance from here?
    thanks again,
    Timmy S

  15. ok, everything I have tried is not working. I mean, what I have works, but the distances are way off.

    Could anybody possibly see what is wrong with this code?

    if(isset($_POST[‘submitted’])){    $z = $_POST[‘zipcode’];    $r = $_POST[‘radius’];    echo “Results for “.$z;       $sql = mysql_query(“SELECT DISTINCT m.zipcode, m.MktName,m.LocAddSt,m.LocAddCity,m.LocAddState,m.x1,m.y1,m.verified,z1.lat,z2.lon,z1.city,z1.state    FROM mrk m, zip z1, zip z2    WHERE m.zipcode = z1.zipcode    AND z2.zipcode = $z    AND (3963 * acos( truncate( sin( z2.lat / 57.2958 ) * sin( m.y1 / 57.2958 ) + cos( z2.lat / 57.2958 ) * cos( m.y1 / 57.2958 ) * cos( m.x1 / 57.2958 – z2.lon / 57.2958 ) , 8 ) ) ) <= $r ")        or die(mysql_error());        while($row = mysql_fetch_array( $sql )) {            $store1 = $row['MktName']."”;            $store = $row[‘LocAddSt’].””;            $store .= $row[‘LocAddCity’].”, “.$row[‘LocAddState’].” “.$row[‘zipcode’];            $latitude1 = $row[‘lat’];            $longitude1 = $row[‘lon’];            $latitude2 = $row[‘y1’];            $longitude2 = $row[‘x1’];            $city = $row[‘city’];            $state = $row[‘state’];            $dis = getnew($latitude1, $longitude1, $latitude2, $longitude2, $unit = ‘Mi’);           // $dis = distance($lat1, $lon1, $lat2, $lon2);            $verified = $row[‘verified’];                                        if($verified == ‘1’){                    echo “”;                    echo “”.$store.””;                            echo $dis . ” mile(s) away”;                    echo “”;                }                else {                    echo “”.$store.””;                            echo $dis . ” mile(s) away”;                    echo “”;                }        }}

    my functions.php code
    function getnew($latitude1, $longitude1, $latitude2, $longitude2, $unit = ‘Mi’) {     $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 ‘Mi’: break; case ‘Km’ : $distance = $distance * 1.609344; } return (round($distance,2)); }

    Thank you in advance

  16. Hey Douglas, great article. I found your explanation of the geographical concepts and the code really interesting. My only suggestion would be to space and indent the code for display (like Stackoverflow, for example). I understand that you want to conserve space, but conventional code spacing / indentation would make it a lot easier for me, as a programmer, to read and dissect. Anyhow, that’s a small thing. Keep up the great work.

  17. it seems faster (mysql 5.9) to use twice the formula in the select and where:
    $formula = “(((acos(sin((“.$latitude.”*pi()/180)) * sin((`Latitude`*pi()/180))+cos((“.$latitude.”*pi()/180)) * cos((`Latitude`*pi()/180)) * cos(((“.$longitude.”- `Longitude`)*pi()/180))))*180/pi())*60*1.1515*1.609344)”;
    $sql = ‘SELECT *, ‘.$formula.’ as distance FROM table WHERE ‘..$formula.’ <= '.$distance;

  18. Thanks a lot for shear this article.it’s very helpful.
    PHP was at first created as a simple scripting platform called “Personal Home Page”. Nowadays PHP (the short for Hypertext Preprocessor) is an alternative of the Microsoft’s Active Server Pages (ASP) technology.

    PHP is an open source server-side language which is used for creating dynamic web pages. It can be embedded into HTML. PHP is usually used in conjunction with a MySQL database on Linux/UNIX web servers. It is probably the most popular scripting language.

  19. I found above solution not working properly.
    I need to change to :

    $qqq = “SELECT *,(((acos(sin((“.$latitude.”*pi()/180)) * sin((`latt`*pi()/180))+cos((” . $latitude . “*pi()/180)) * cos((`latt`*pi()/180)) * cos(((” . $longitude . “- `longt`)*pi()/180))))*180/pi())*60*1.1515) as distance FROM `register` “;

  20. Hello, please i will really need your help on this.

    I made a get request to my web-server
    53.47792 = $latitude
    -2.23389 = $longitude
    and 20 = the distance i want to retrieve

    However using you formula, it retrieves all rows in my db

    $results = DB::select( DB::raw(“SELECT *, (((acos(sin((“.$latitude.”pi()/180)) * sin((latpi()/180))+cos((“.$latitude.”pi()/180)) * cos((latpi()/180)) * cos(((“.$longitude.”- lng)pi()/180))))180/pi())601.1515*1.609344) as distance FROM markers HAVING distance >= “.$distance ));

    [{“id”:1,”name”:”Frankie Johnnie & Luigo Too”,”address”:”939 W El Camino Real, Mountain View, CA”,”lat”:37.386337280273,”lng”:-122.08582305908,”distance”:16079.294719663},{“id”:2,”name”:”Amici’s East Coast Pizzeria”,”address”:”790 Castro St, Mountain View, CA”,”lat”:37.387138366699,”lng”:-122.08323669434,”distance”:16079.175940152},{“id”:3,”name”:”Kapp’s Pizza Bar & Grill”,”address”:”191 Castro St, Mountain View, CA”,”lat”:37.393886566162,”lng”:-122.07891845703,”distance”:16078.381373826},{“id”:4,”name”:”Round Table Pizza: Mountain View”,”address”:”570 N Shoreline Blvd, Mountain View, CA”,”lat”:37.402652740479,”lng”:-122.07935333252,”distance”:16077.420540582},{“id”:5,”name”:”Tony & Alba’s Pizza & Pasta”,”address”:”619 Escuela Ave, Mountain View, CA”,”lat”:37.394012451172,”lng”:-122.09552764893,”distance”:16078.563225154},{“id”:6,”name”:”Oregano’s Wood-Fired Pizza”,”address”:”4546 El Camino Real, Los Altos, CA”,”lat”:37.401725769043,”lng”:-122.11464691162,”distance”:16077.937560795},{“id”:7,”name”:”The bars and grills”,”address”:”24 Whiteley Street, Manchester”,”lat”:53.485118865967,”lng”:-2.1828699111938,”distance”:8038.7620112314}]

    I want to retrieve just rows with 20 miles but it brings all rows. Please what am i doing wrong

  21. I’m looking for a similar query but stepped up a bit – in short this is to group all coordinates within 2 miles of each coordinate and then count how many coordinates in each group and output only one group that has the most coordinates – even if you have more than one group among groups that have the most number of coordinates – simply output the random group from the groups with same largest number –

What do you think?

This site uses Akismet to reduce spam. Learn how your comment data is processed.

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.