Forum Discussion
Helper IDistance (in kilometers) between adresses
Hello everyone,
I'm currently in a pickle, I have two tables.
One that has all my stores info and their adresses
The other one has delivery adresses for every sale.
What I want to do is calculate the distance between both adresses and return the distance in kilometers!
You can use the Haversine formula to calculate the distance between two geo coordinates (must be a pair of latitude and longitude).
Distance in KM = // Haversine Formula VAR EarthRadiusKm = 6371.0 VAR Lat1 = RADIANS ( SELECTEDVALUE ( 'FromLocation'[Latitude] ) ) VAR Lat2 = RADIANS ( SELECTEDVALUE ( ToLocation[Latitude] ) ) VAR Lon1 = RADIANS ( SELECTEDVALUE ( 'FromLocation'[Longitude] ) ) VAR Lon2 = RADIANS ( SELECTEDVALUE ( ToLocation[Longitude] ) ) VAR DeltaLat = Lat2 - Lat1 VAR DeltaLon = Lon2 - Lon1 VAR a = POWER ( SIN ( DIVIDE ( DeltaLat, 2 ) ), 2 ) + COS ( Lat1 ) * COS ( Lat2 ) * POWER ( SIN ( DIVIDE ( DeltaLon, 2 ) ), 2 ) VAR c = 2 * ASIN ( SQRT ( a ) ) RETURN EarthRadiusKm * c
9 Replies
- danextian
Super User
You can use the Haversine formula to calculate the distance between two geo coordinates (must be a pair of latitude and longitude).
Distance in KM = // Haversine Formula VAR EarthRadiusKm = 6371.0 VAR Lat1 = RADIANS ( SELECTEDVALUE ( 'FromLocation'[Latitude] ) ) VAR Lat2 = RADIANS ( SELECTEDVALUE ( ToLocation[Latitude] ) ) VAR Lon1 = RADIANS ( SELECTEDVALUE ( 'FromLocation'[Longitude] ) ) VAR Lon2 = RADIANS ( SELECTEDVALUE ( ToLocation[Longitude] ) ) VAR DeltaLat = Lat2 - Lat1 VAR DeltaLon = Lon2 - Lon1 VAR a = POWER ( SIN ( DIVIDE ( DeltaLat, 2 ) ), 2 ) + COS ( Lat1 ) * COS ( Lat2 ) * POWER ( SIN ( DIVIDE ( DeltaLon, 2 ) ), 2 ) VAR c = 2 * ASIN ( SQRT ( a ) ) RETURN EarthRadiusKm * c
Henrique_QuintHi, danextian !
This formula worked. But is there an easier way to get latitude and logitude based on adresses? I'm doing manually but there is more than one thousand deliveries since the beggining of the year.
Any ideas on how to do it?Hi Henrique_Quint,
- If you're comfortable with Python, you can use libraries like geopy or requests to call geocoding APIs and convert multiple addresses into latitude and longitude in batches.
- If you prefer using Power Automate, you can set up a flow to automate the process of geocoding addresses using an API.
If you like coding and want more control over the process, Python is a powerful option.
If you're more comfortable with no-code solutions and are already using the Microsoft ecosystem, Power Automate is an excellent choice. It allows you to create workflows without writing code.
Regards,
Vinay Pabbu
Hi Henrique_Quint,
we would like to follow up to see if the solution provided by the super user resolved your issue. Please let us know if you need any further assistance.
If our super user response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.Regards,
Vinay Pabbu- lbendlin
Which distance? As the crow flies (Great Circle Distance) or road route distance? Do you have latitude/longitude?
- Henrique_Quint
Road route or radius distance is enough! I don't have the exact latitude/longitude, just adresses. I could get it but I would have to work a little bit in python to get it
- lbendlin
you will have more luck using a routing service API like Google Maps or similar - but you would have to precalculate that for each combination of store and delivery location. Most of them are not free, but you wouldn't have to calculate that often as the distance only changes when there is contruction, closures or detours.
