Forum Discussion

Henrique_Quint's avatar
Henrique_Quint
Icon for Helper I rankHelper I
1 year ago
Solved

Distance (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 c...
Need Help
  • danextian's avatar
    danextian
    1 year ago

    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
danextian's avatar
danextian
Icon for Super User rankSuper User
1 year ago

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_Quint's avatar
    Henrique_Quint
    Icon for Helper I rankHelper I
    1 year ago

    Hi, 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?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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