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!