Haversine formula to find distance between two lat/long points
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)
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:
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.
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.
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!
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. (: