Code Explanation: Distance Calculation Using Haversine Formula

The provided code snippet calculates the distance between two geographical points on Earth using the Haversine formula. The formula accounts for the spherical shape of the Earth. Below is a breakdown of the code structure and functionality.

Code Breakdown

Language

This code is written in DAX (Data Analysis Expressions), commonly used in Power BI, Azure Analysis Services, and SQL Server Analysis Services.

Variable Definitions

1. Convert Degrees to Radians
   The geographic coordinates are generally given in degrees. The RADIANS function converts these degrees into radians, which are necessary for trigonometric calculations.

   VAR lat1 = RADIANS('Table'[Latitude1])
   VAR lon1 = RADIANS('Table'[Longitude1])
   VAR lat2 = RADIANS('Table'[Latitude2])
   VAR lon2 = RADIANS('Table'[Longitude2])

2. Calculate Differences
   These variables represent the differences in latitude and longitude between the two points.

   VAR dlat = lat2 - lat1
   VAR dlon = lon2 - lon1

Haversine Formula Calculation

3. Intermediate Variable a
   This calculation is derived from the Haversine formula. It takes into account the sine of half the difference in latitude and longitude to compute a value crucial for determining the distance.

   VAR a =
       SIN(dlat/2) * SIN(dlat/2) +
       COS(lat1) * COS(lat2) *
       SIN(dlon/2) * SIN(dlon/2)

4. Calculate Variable c
   The variable c is derived using the ATAN2 function, which computes the arctangent of the quotient of its arguments. This variable represents the angular distance in radians.

   VAR c = 2 * ATAN2(SQRT(a), SQRT(1-a))

5. Final Distance Calculation
   Here, the distance is calculated by multiplying the angular distance c by the Earth's radius (approximately 6371 kilometers). This gives the final distance between the two points in kilometers.

   VAR distance = c * 6371 -- Earth radius in kilometers

Return Statement

6. Return the Calculated Distance
   The final distance value is returned as the result of the calculation.

   RETURN distance

Key Concepts Explained

• Radians vs. Degrees: Radians are a unit of angle measurement based on the radius of a circle, a necessary unit for the trigonometric functions used in this calculation.
• Haversine Formula: A formula that determines the distance between two points on the surface of a sphere, considering the spherical shape of the Earth. It is particularly useful for calculating distances over large curved surfaces.
• ATAN2 Function: This function computes the angle from the x-axis to a point described by the coordinates (x,y) in a two-dimensional space, thus enabling calculations involving two angular measurements.

Additional Examples

To reinforce the understanding of the Haversine formula, consider the following code structure that could be adapted:

Distance = 
VAR lat1 = RADIANS(34.052235) -- Latitude of location 1 (Los Angeles)
VAR lon1 = RADIANS(-118.243683) -- Longitude of location 1
VAR lat2 = RADIANS(40.712776) -- Latitude of location 2 (New York)
VAR lon2 = RADIANS(-74.005974) -- Longitude of location 2
-- Compute remaining variables and the distance as shown earlier.

This example computes the distance between Los Angeles and New York using hard-coded latitude and longitude values instead of table references.

Conclusion

The provided DAX code effectively calculates the distance between two geographical locations using the Haversine formula. Each section of the code has a specific purpose that contributes to the final output, enhancing understanding of both distance calculation and DAX functions. For further learning on DAX and distance calculations, consider exploring resources available through the Enterprise DNA Platform.