topic Haversine formula to find distance between two lat/long points in QlikView

Haversine formula to find distance between two lat/long points

rothtd — Thu, 28 Apr 2011 18:30:23 GMT

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

Haversine formula to find distance between two lat/long points

johnw — Fri, 29 Apr 2011 01:01:37 GMT

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.

Haversine formula to find distance between two lat/long points

rothtd — Fri, 29 Apr 2011 14:43:51 GMT

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

Re: Haversine formula to find distance between two lat/long points

Fri, 22 Jul 2011 07:25:59 GMT

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

Haversine formula to find distance between two lat/long points

Mon, 10 Oct 2011 07:44:47 GMT

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

Re: Haversine formula to find distance between two lat/long points

Fri, 07 Mar 2014 13:26:50 GMT

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.

Re: Haversine formula to find distance between two lat/long points

Mon, 25 Jan 2016 21:15:13 GMT

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!

Re: Haversine formula to find distance between two lat/long points

eelianx88 — Fri, 12 Feb 2016 04:01:41 GMT

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

Re: Haversine formula to find distance between two lat/long points

Wed, 30 Nov 2016 15:06:47 GMT

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

Re: Haversine formula to find distance between two lat/long points

Thu, 01 Dec 2016 20:23:29 GMT

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

Re: Haversine formula to find distance between two lat/long points

johnw — Fri, 02 Dec 2016 01:23:26 GMT

Not correct, and yes we can calculate using two tables. Easiest way is to just move most of the calculation to the front end, but if performance becomes a problem, there are other ways to handle it. See attached.

Re: Haversine formula to find distance between two lat/long points

Sun, 04 Dec 2016 14:38:32 GMT

Thank you so much John again you solved my problem

Re: Haversine formula to find distance between two lat/long points

Fri, 09 Dec 2016 18:02:24 GMT

Hi John,

I'm struggling with an issue john, the first thought in my mind is you, who can help me out. who can help in any issue

i have data as below

based on your inputs i have calculated distance between two latitude and longitudes

1, i need to calculate the distance between each employee to every office (this is achieved with your previous inputs)

the main part is

i want three input boxes as below

when i put value in distance inputbox = 0.7 all the offices which are less than 0.7 km to employee should be dispalyed

when i put value in office strength inputbox = 100 all the offices stregth which are less than 100 should be dispalyed

when i put value in employee salary rank inputbox = 15 top 15 ranked employees should be displayed

all the above should be displayed in a straight table

as below

output_1

employee emp_lat emp_lang office strength employee salary count(offices <0.7)

1 x y 50(which is less than 100 ) 10 (rank 1) 12(varies)

2 z w 40(which is less than 100 ) 8 (rank 2) 1

i want for a particular employee how many offices are below 0.7 km(what ever the value provided in input box), whose office strength is less than 100(what ever the value provided in inputbox) and the employee salary by rank (provided in the input box)

Re: Haversine formula to find distance between two lat/long points

johnw — Fri, 09 Dec 2016 21:51:16 GMT

Calculate distance as shown before. Add a Rank field for the salary. Trigger actions to make selections based off the input boxes. See attached.

Re: Haversine formula to find distance between two lat/long points

Sat, 10 Dec 2016 11:48:56 GMT

Hi John Thank you for the reply

my requiremnt is a staright table

as below

based on the input boxes values

i have defined two variables and a set expression as below

i'm using emp_id as dimension

this is my expression in staraight table

count({<Distance={"<$(vdistance_office)"}, office_strength={"<$(v_strength)"}>}office_id)

i'm not getting the exat results and my data is associated as below

Re: Haversine formula to find distance between two lat/long points

johnw — Fri, 16 Dec 2016 01:38:57 GMT

Well, that's very different from the approach I took, but I think it should work just fine. You should count distinct office_id, not just office_id, but I don't know if that's the problem. Hard to debug what I can't see.

Re: Haversine formula to find distance between two lat/long points

ingo_lsg — Thu, 19 Sep 2019 11:01:00 GMT

Hi,

the formula runs without errors, but it doesn't calculate the measure.

Which format for longitude lattitude is necessary?

Ingo

Re: Haversine formula to find distance between two lat/long points

ingo_lsg — Thu, 19 Sep 2019 11:16:28 GMT

Heres a screenshot of my data

Re: Haversine formula to find distance between two lat/long points

LZucco — Sat, 28 Dec 2019 12:18:57 GMT

Hello Chong Ee Lian,

Could you please give more details about the load procedure?

I could not understand what you have done on introduce an additional recnum() column for the table in the data load editor.

Thanks in advance + regards

Re: Haversine formula to find distance between two lat/long points

kevinpintokpa — Sat, 21 Aug 2021 00:41:10 GMT

Hi, this is an old thread, but I needed a solution and have written some code using the Vicenty formula if anyone finds its useful. I stole @johnw 's code above.

For ease of understanding (and copy and paste), I broke the QlikView Load statement into 3 preceding loads:

Trace Calculating distance on a sphere; // This is a series of preceding loads, executed in the order Step 1... Step 2... NoConcatenate Distances: // Step 3: Convert the raw distance into convenient common units Load *, Round ( [Distance Vicenty], 0.1 ) as [Distance Vicenty km], Round ( [Distance Vicenty] * 1000 ) as [Distance Vicenty meters], Round ( [Distance Vicenty] * 0.6213712, 0.1 ) as [Distance Vicenty miles], ; // Step 2: Calculate the distance using the Vicenty formula and radian fields Load *, // Vincenty formula atan2( sqrt( sqr(cos(Lat2Radians)*sin(Lon2Radians-Lon1Radians)) + sqr(cos(Lat1Radians)*sin(Lat2Radians)-sin(Lat1Radians)*cos(Lat2Radians)*cos(Lon2Radians-Lon1Radians)) ), sin(Lat1Radians)*sin(Lat2Radians) + cos(Lat1Radians)*cos(Lat2Radians)*cos(Lon2Radians-Lon1Radians) ) * 6371 as [Distance Vicenty] ; // Step 1: Generate radian fields // Replace Lat1, Lat2, Lon1 and Lon2 with the name of your fields Load *, (Lat1 * Pi() / 180) as Lat1Radians, (Lat2 * Pi() / 180) as Lat2Radians, (Lon1 * Pi() / 180) as Lon1Radians, (Lon2 * Pi() / 180) as Lon2Radians Resident TestLocations ;

This results in some extra fields but they can be dropped easily and this is much easier to understand. I have four actual places as test data and the QVW attached calculates the Vicenty distance to the same end location (Salt Lake City airport).

The file DistanceCalculations.qvw is attached.