How to calculate Geographical Distance in Qlik

This video shows how to use the longitude and latitude of two distinct points to calculate the distance between those two points in kilometers or miles. We use a concept called Haversine Formula. The video demonstrates how to easily implement this solution in a Qlik app.

Solution Code

// Setting the Haversine Formula parameters

// Earth's radius 

// Use 6371 for Km

// Use 3963 for miles

Let vEarthRadius = 6371; 

Let vEarthRadius_miles = 3963; 

Let vPi = Pi(); // Store Pi value for better performance

Let vRadConv = $(vPi) / 180;

//Create a Cartesian Join:

CartesianJoin:

Load 

	 flight_id,

     aircraft_lat As flight_lat,

     aircraft_long As flight_long

Resident Flights;

Join(CartesianJoin)

SecureLocations:

Load

	Location_id As loc_id,

    location_type,

    [Secure Location] As location_name,

    location_lat As secure_loc_lat,

    location_long  As secure_loc_long    

Resident SecureLocations;

// Use the Haversine formula in Qlik to calculate the distance.

DistanceCalculation:

LOAD

    *,

/*  

	// In Qlik

    $(vEarthRadius) * Acos(

    	(

        	Sin(p_lat * $(vPi) / 180) * Sin(q_lat * $(vPi) / 180)

        ) +

        (

        Cos(p_lat * $(vPi) / 180) * Cos(q_loc_lat * $(vPi) / 180) 

        * Cos((q_long - flight_long) * $(vPi) / 180) 

        )

    ) AS distance_km

*/        

    $(vEarthRadius) * Acos(

    	(

        	Sin(flight_lat * $(vPi) / 180) * Sin(secure_loc_lat * $(vPi) / 180)

        ) +

        (

        Cos(flight_lat * $(vPi) / 180) * Cos(secure_loc_lat * $(vPi) / 180) *

        Cos((secure_loc_long - flight_long) * $(vPi) / 180) 

        )

    ) AS distance_km,

    //Simplified

    $(vEarthRadius_miles) * Acos(

    	(

        	Sin(flight_lat * $(vRadConv) ) * Sin(secure_loc_lat * $(vRadConv) )

        ) +

        (

        Cos(flight_lat * $(vRadConv) ) * Cos(secure_loc_lat * $(vRadConv) ) *

        Cos((secure_loc_long - flight_long) *$(vRadConv) ) 

        )

    ) AS distance_miles

RESIDENT CartesianJoin;

ClosestLocation:

LOAD

    flight_id,

    MIN(distance_km) AS closest_distance_km,

    FIRSTSORTEDVALUE(location_name, distance_km) AS closest_location_name,

    FIRSTSORTEDVALUE(location_type, distance_km) AS closest_location_type

RESIDENT DistanceCalculation

GROUP BY flight_id;

DROP TABLE CartesianJoin;

DROP TABLE DistanceCalculation;