Code Explainer | Power BI
Distance Calculation using Haversine
This code calculates the distance between two points on the Earth's surface using latitude and longitude coordinates. It utilizes the Haversine formula to accurately calculate the distance, taking into account the Earth's curvature. The calculated
![Empty image or helper icon](https://fs.enterprisedna.co/library/data-mentor/cover-images/fVxpl8yt5hKq1yWCqGWWIBWe8UiWkFr1whfm5WAs.webp)
This Query related with thread "Geographical Distance Calculation in DAX using Haversine"
Prompt
Answer
Code Explanation
The provided code calculates the distance between two points on the Earth's surface using latitude and longitude coordinates.
- It starts by declaring and initializing variables to store the latitude and longitude values of the two points:
lat1
is assigned the value of the latitude of the first point from the 'Table' column 'Latitude1' in radians.lon1
is assigned the value of the longitude of the first point from the 'Table' column 'Longitude1' in radians.lat2
is assigned the value of the latitude of the second point from the 'Table' column 'Latitude2' in radians.lon2
is assigned the value of the longitude of the second point from the 'Table' column 'Longitude2' in radians.
- The code then calculates the differences in latitude and longitude:
dlat
represents the difference in latitude between the two points (lat2 - lat1).dlon
represents the difference in longitude between the two points (lon2 - lon1).
- The code proceeds to calculate the distance between the two points using the Haversine formula, which is based on trigonometry.
- The formula calculates the "great circle distance" between two points on a sphere (in this case, the Earth).
a
is calculated as the sum of the following terms:- The square of the sine of half the latitude difference (SIN(dlat/2) * SIN(dlat/2)).
- The product of the cosine of the first latitude, the cosine of the second latitude, and the square of the sine of half the longitude difference (COS(lat1) * COS(lat2) * SIN(dlon/2) * SIN(dlon/2)).
c
is calculated as the arctangent of the square root of a divided by the square root of (1 - a) (2 * ATAN2(SQRT(a), SQRT(1-a))).- Finally, the
distance
variable is calculated as the product of c and the Earth's radius in kilometers (6371).
- The calculated distance, stored in the
distance
variable, is then returned as the output of the calculation.
Key Concepts
RADIANS
function is used to convert degrees to radians, which is a widely used unit of measurement in trigonometry calculations.- Haversine formula is used to calculate the distance between two points on a sphere, such as the Earth's surface.
- The formula takes into account the curvature of the Earth, making it more accurate for long distances compared to other distance calculation methods.
Additional Example
Here's an example of how the code can be used in practice:
Distance =
VAR lat1 = RADIANS(37.7749) -- Latitude of point 1 in degrees
VAR lon1 = RADIANS(-122.4194) -- Longitude of point 1 in degrees
VAR lat2 = RADIANS(34.0522) -- Latitude of point 2 in degrees
VAR lon2 = RADIANS(-118.2437) -- Longitude of point 2 in degrees
VAR dlat = lat2 - lat1
VAR dlon = lon2 - lon1
VAR a =
SIN(dlat/2) * SIN(dlat/2) +
COS(lat1) * COS(lat2) *
SIN(dlon/2) * SIN(dlon/2)
VAR c = 2 * ATAN2(SQRT(a), SQRT(1-a))
VAR distance = c * 6371 -- Earth radius in kilometers
RETURN distance
This example calculates the distance between San Francisco (latitude 37.7749, longitude -122.4194) and Los Angeles (latitude 34.0522, longitude -118.2437) and returns the result. The output would be the approximate distance between the two cities in kilometers.
Description
This code calculates the distance between two points on the Earth's surface using latitude and longitude coordinates. It utilizes the Haversine formula to accurately calculate the distance, taking into account the Earth's curvature. The calculated distance is returned as the output.
More Code Explainers
![Apache Flink Code Explainer](https://fs.enterprisedna.co/library/icons/flink.png)
![Apache Pig Code Explainer](https://fs.enterprisedna.co/library/icons/apache_pig.png)
![Azure Data Factory Code Explainer](https://fs.enterprisedna.co/library/icons/azure-data-factory.png)
![C/C++ Code Explainer](https://fs.enterprisedna.co/library/icons/c_c_plus.png)
![CouchDB Code Explainer](https://fs.enterprisedna.co/library/icons/couchdb.png)
![DAX Code Explainer](https://fs.enterprisedna.co/library/icons/power-bi.png)
![Excel Code Explainer](https://fs.enterprisedna.co/library/icons/excel.png)
![Firebase Code Explainer](https://fs.enterprisedna.co/library/icons/firebase.png)
![Google BigQuery Code Explainer](https://fs.enterprisedna.co/library/icons/google_bigquery.png)
![Google Sheets Code Explainer](https://fs.enterprisedna.co/library/icons/google_sheets.png)
![GraphQL Code Explainer](https://fs.enterprisedna.co/library/icons/graphql.png)
![Hive Code Explainer](https://fs.enterprisedna.co/library/icons/hive.png)
![Java Code Explainer](https://fs.enterprisedna.co/library/icons/java.png)
![JavaScript Code Explainer](https://fs.enterprisedna.co/library/icons/javascript.png)
![Julia Code Explainer](https://fs.enterprisedna.co/library/icons/julia.png)
![Lua Code Explainer](https://fs.enterprisedna.co/library/icons/lua.png)
![M (Power Query) Code Explainer](https://fs.enterprisedna.co/library/icons/m.png)
![MATLAB Code Explainer](https://fs.enterprisedna.co/library/icons/matlab.png)
![MongoDB Code Explainer](https://fs.enterprisedna.co/library/icons/mongodb.png)
![Oracle Code Explainer](https://fs.enterprisedna.co/library/icons/oracle.png)
![PostgreSQL Code Explainer](https://fs.enterprisedna.co/library/icons/postgresql.png)
![Power BI Code Explainer](https://fs.enterprisedna.co/library/icons/power-bi.png)
![Python Code Explainer](https://fs.enterprisedna.co/library/icons/python.png)
![R Code Explainer](https://fs.enterprisedna.co/library/icons/r.png)
![Redis Code Explainer](https://fs.enterprisedna.co/library/icons/redis.png)
![Regex Code Explainer](https://fs.enterprisedna.co/library/icons/regex.png)
![Ruby Code Explainer](https://fs.enterprisedna.co/library/icons/ruby.png)
![SAS Code Explainer](https://fs.enterprisedna.co/library/icons/sas.png)
![Scala Code Explainer](https://fs.enterprisedna.co/library/icons/scala.png)
![Shell Code Explainer](https://fs.enterprisedna.co/library/icons/shell.png)
![SPSS Code Explainer](https://fs.enterprisedna.co/library/icons/spss.png)
![SQL Code Explainer](https://fs.enterprisedna.co/library/icons/sql.png)
![SQLite Code Explainer](https://fs.enterprisedna.co/library/icons/sqlite.png)
![Stata Code Explainer](https://fs.enterprisedna.co/library/icons/stata.png)
![Tableau Code Explainer](https://fs.enterprisedna.co/library/icons/tableau.png)
![VBA Code Explainer](https://fs.enterprisedna.co/library/icons/vba.png)