Discussion Board for collaboration related to QlikView App Development.
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
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.
Thank you so much John again you solved my problem
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)
Calculate distance as shown before. Add a Rank field for the salary. Trigger actions to make selections based off the input boxes. See attached.
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
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.
Hi,
the formula runs without errors, but it doesn't calculate the measure.
Which format for longitude lattitude is necessary?
BR
Ingo
Heres a screenshot of my data
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
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.