Haversine formula to find distance between two lat/long points

[rothtd]

I figured I would take a moment to share a code snippet that uses the Haversine formula to calculate the distance between two lat/long points.

// use the 'haversine' formula to calculate distance between two lat/long points for each record
// The Haversine formula can be broken into multiple pieces or steps - I chose to do the entire formula
// in one step. This makes it more difficult to read/understand, but allows QlikView to do it all at once
// when reading a record. If I broke it up, then QlikView would have to examine each record for each step of the
// process increasing time and decreasing efficiency.
// formula notes:
// http://www.movable-type.co.uk/scripts/latlong.html
// convert degrees to radians: rads=degrees*pie / 180
// radius of earth = 6371km (constant)

CalcDistances:
noconcatenate
load
 zipcode,
 state,
 country,
 Latitude1,
 Longitude1,
 weatherStationID,
 Latitude2,
 Longitude2,
 // the Haversine formula
 (2 *
 (atan2(
 sqrt((sqr(sin(((Latitude2 * Pi() / 180) - (Latitude1 * Pi() / 180)) / 2))
 + ((cos((Latitude1 * Pi() / 180)) * cos((Latitude2 * Pi() / 180)))
 * sqr(sin(((Longitude2 * Pi() / 180) - (Longitude1 * Pi() / 180)) / 2))
 )
 )),
 sqrt((1-(
 sqr(sin(((Latitude2 * Pi() / 180) - (Latitude1 * Pi() / 180)) / 2))
 + ((cos((Latitude1 * Pi() / 180)) * cos((Latitude2 * Pi() / 180)))
 * sqr(sin(((Longitude2 * Pi() / 180) - (Longitude1 * Pi() / 180)) / 2))
 )
 ))
 )))) * 6371 as Distance_km
resident AllPossibleZipWeatherStationCombinations;

Example of input/output:

- Trevor

[johnw]

In many cases, you can probably get away with this:

,acos(sin(Latitude *pi()/180)*sin(Latitude2*pi()/180)
+cos(Latitude *pi()/180)*cos(Latitude2*pi()/180)
*cos(Longitude2*pi()/180 -Longitude*pi()/180))*6371 as Distance

But reading Wikipedia, if you can have very short distances, the Haversine formula is better. But as long as we're shooting for accuracy, it indicates that the Haversine formula has similar numerical errors for points on near exact opposite sides of the earth. Likely much more rare in real data sets than two points close together, but we might as well handle everything. It recommends the spherical case of the Vincenty formula:

,atan2(sqrt(sqr(cos(Latitude2*pi()/180)
*sin(Longitude2*pi()/180-Longitude*pi()/180))
+sqr(cos(Latitude *pi()/180)*sin(Latitude2*pi()/180)
-sin(Latitude *pi()/180)*cos(Latitude2*pi()/180)
*cos(Longitude2*pi()/180 -Longitude*pi()/180)))
,sin(Latitude *pi()/180)*sin(Latitude2*pi()/180)
+cos(Latitude *pi()/180)*cos(Latitude2*pi()/180)
*cos(Longitude2*pi()/180 -Longitude*pi()/180))*6371 as Distance

I haven't checked it carefully, but it seems to produce the right results for a few sample points that admittedly don't test the difficult boundary conditions.

Both of us could probably make our formulas more efficient and easier to read by just adding the Latitude and Longitude in radians to our table temporarily.

Edit: Yeah, that does of course make everything much simpler. See below and attached. I can see that the simple arccosine formula does have problems with points right next to each other. Interestingly, your Haversine formula doesn't match the one on Wikipedia. It's mathematically equivalent I'm sure, but so are all of them.

// arccosine: problem when points are very close together
,acos(sin(Lat1)*sin(Lat2)+cos(Lat1)*cos(Lat2)*cos(Lon2-Lon1))*6371 as "Distance"

// Haversine: problem when points are very close to opposite sides of the earth
,atan2(sqrt(sqr(sin((Lat2-Lat1)/2))+cos(Lat1)*cos(Lat2)*sqr(sin((Lon2-Lon1)/2)))
,sqrt(1-(sqr(sin((Lat2-Lat1)/2))
+cos(Lat1)*cos(Lat2)*sqr(sin((Lon2-Lon1)/2)))))*2*6371 as "Distance 2"

// Vincenty: no problems (?)
,atan2(sqrt(sqr(cos(Lat2)*sin(Lon2-Lon1))
+sqr(cos(Lat1)*sin(Lat2)-sin(Lat1)*cos(Lat2)*cos(Lon2-Lon1)))
,sin(Lat1)*sin(Lat2)+cos(Lat1)*cos(Lat2)*cos(Lon2-Lon1))*6371 as "Distance 3"

Also, an amusing tidbit now that I'm reading the site you linked to:

"...it is probably worth, in most situations, using either the simpler law of cosines or the more accurate ellipsoidal Vincenty formula in preference to haversine!"

Edit 2: Hmmm, well, if we take the Vincenty results as the right answers, both the Arccosine and Haversine approaches are ever so slightly off for points that are ever so slightly different from exact opposite ends of the earth. I didn't notice that at first. It's the distance between points 4 and 5. The formulas disagree by about 1 mm. Hardly an issue in practice, of course.

[rothtd]

Your insight is greatly appreciated. Thanks for your comments, John.

[#4]

Hi John,

I've a list latitude and longitude that is used in a scatter chart and superimposed on google maps. Is there a way to calculate distance between any two coordinates that I select?

Regards,

Rahul

[#5]

Hi John,

I am new to using Google maps on Qlikview. I had a task on hand which required me to plot a set of streets/cities of US on the google maps and then find out the distance of the plotted points from the nearest respective highways.

I was able to import my data on Qlikview and have the streets plotted on the google map. But I see that I cannot drop points on the nearest highways and then get the distance between the two as we can in Google maps.

Is this feature possible/ available on Qlikview?

Any help would be of great assisstance!

Many Thanks,

Ritika

[#6]

Hi Trevor

I went searching the community as a long shot and found gold. Thank you very much for the post - it has saved me hours.

K

[#7]

John,

This is super helpful. I'm looking to use this formula converted to miles, so I looked up the radius of the earth in miles. My question is whether to use miles or nautical miles. I don't fully understand nautical miles, but it sounds like it is the standard for navigating and charting (which is sort of what we're doing here). It appears you are using the straight line radius. Any thoughts or comments?

This is a great explanation already, so thanks for sharing!

[eelianx88]

Hi Raul,

this is how i get it done in a simple expression by introducing an additional recnum() column for the table in the data load editor. With the GetSelectedCount() function, this will calculate the distance between two selected geographical coordinates.

I had also added some 'prompts' and format settings. Hope this helps. (:

Thank You and have a nice day (:

Best Regards,
Chong Ee Lian

New to Qlik

// Start of Sample Expression

if(GetSelectedCount(OUTLET,true())=2,

Num(

(ACOS(COS(pi()/180*(90-(MAX({<RECNUM={"$(=Max(RECNUM,1))"}>} LAT))))

*COS(pi()/180*(90-(MAX({<RECNUM={"$(=Max(RECNUM,2))"}>} LAT))))

+

SIN(pi()/180*(90-(MAX({<RECNUM={"$(=Max(RECNUM,1))"}>} LAT))))

*SIN(pi()/180*(90-(MAX({<RECNUM={"$(=Max(RECNUM,2))"}>} LAT))))

*COS(pi()/180*((MAX({<RECNUM={"$(=Max(RECNUM,1))"}>} LONG))-(MAX({<RECNUM={"$(=Max(RECNUM,2))"}>} LONG)))))*6371)

,'#,##0.##', '.' , ',' )

& 'km'

,if(GetSelectedCount(OUTLET,true())>2,'You Have Selected More than 2 Outlets. Please Select Only 2.'

,if(GetSelectedCount(OUTLET,true())=1,'For Distance Calculations, Please Select 2 Outlets.'

,'No Selection')))

//Assumption 1: --- Earth's Radius=6371km

//Assumption 2: --- Degrees to Radian Conversion --- Rad=(Deg* PI/180)

//Final Output will be in X.XX KM,

//Using Harvesine formula to calculate the distance between two selected geographical coordinates.

//LAT: latitude, LONG: longitude

[#9]

Hi John,

I have a data as below

Tanle1

Employee  latitude  longitude

Arnold           121          130

Beck               150           72

Table 2

Factory    f_latitude  f_longitude

A                211.7          179.5

B                 129             150

I want to calculate the distance from Arnold to both the factories A and B 

the distance from Beck to both the factories A and B

Is it possible to do and there is no relation between the two tables

[#10]

Hi John

i have data in two different tables as follows

emp-tab:

emp_id, lat1, long1
1, 22.32, 114.17
2, 22.28, 114.16

factory_tab:

Fact_ID, lat2, long2
1, 22.42544, 114.23134
2, 22.29540, 114.26860

i'm getting the end results as

is this correct

and can't we calculate the distance if the data is in two different tables