Measure distance in km between two map coordinates

Firstly I’m not a math whiz. What I’ve done here is take a JavaScript function, convert it to Qlik Sense script code and test it works.

The problem… I have two latitude (lat) and longitude (long) coordinates and I wanted to calculate distance between the two as the crow fly’s in km’s. Using google maps I can pick two points, record their coordinates and measure the distance so I know what I’m aiming for. In this example I’m heading from my home to my office (not done that since lockdown) and the magic number is 34.3 km’s

Here I find a solution to the problem, the JavaScript code is easy to follow and I use JSFiddle to check each calculation stage using a console log.

function degreesToRadians(degrees) {
    return degrees * Math.PI / 180;
}

function distanceInKmBetweenEarthCoordinates(lat1, lon1, lat2, lon2) {
    var earthRadiusKm = 6371;

    var dLat = degreesToRadians(lat2-lat1);
    var dLon = degreesToRadians(lon2-lon1);

    lat1 = degreesToRadians(lat1);
    lat2 = degreesToRadians(lat2);

    var a = Math.sin(dLat/2) * Math.sin(dLat/2) +
    Math.sin(dLon/2) * Math.sin(dLon/2) * Math.cos(lat1) * Math.cos(lat2);
    var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a));
    return earthRadiusKm * c;
}

 

I ran the formula first through excel and then Qlik, both had challenges which was because of exponential numbers. if you do it in stages you have to convert it back to decimal (highlighted in Red):

Data:
Load * Inline [
ID, lat1,lon1,lat2,lon2
A,53.4981860,-1.4299190,53.7997620,-1.5442520
];
DataLong:
Load *
        ,6371 * C AS Distance_LongCalc
;
Load *
         ,2 * Atan2( sqrt(A) , sqrt(1-A) ) AS C
;
Load *
         ,num(sin(dLat/2) * sin(dLat/2) +sin(dLon/2) * sin(dLon/2) * cos(Lat1) * cos(Lat2),'(dec)') AS A
;
Load *
        ,((lat2-lat1) * Pi() /180) AS dLat
        ,((lon2-lon1) * Pi() /180) AS dLon

        ,((lat1) * Pi() /180) AS Lat1
        ,((lat2) * Pi() /180) AS Lat2
Resident Data;

 

The short version you don’t have to worry about exponential numbers… although it was handy to start of step by step (as above) before moving to a short version.

DataShort:
Load *

     ,6371 * (2 *
           Atan2( 
                   sqrt((sin(((lat2-lat1) * Pi() /180) /2) * sin(((lat2-lat1) * Pi() /180) /2) +sin(((lon2-lon1) * Pi() /180) /2) * sin(((lon2-lon1) * Pi() /180) /2) * cos(((lat1) * Pi() /180) ) * cos(((lat2) * Pi() /180) )))
               ,  sqrt(1-(sin(((lat2-lat1) * Pi() /180) /2) * sin(((lat2-lat1) * Pi() /180) /2) +sin(((lon2-lon1) * Pi() /180) /2) * sin(((lon2-lon1) * Pi() /180) /2) * cos(((lat1) * Pi() /180) ) * cos(((lat2) * Pi() /180) )))

            ))  AS Distance
Resident Data;

Enjoy!

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s