Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Compute distances based on LatLong data

 

Hi Greg_Deckler , parry2k , ImkeF , TomMartens 

 

I need an expert opinion on this. 

 

I have a table with the following data.

 

UserIDShopCodeShopNameVisit DateVisit TimeLatitudeLongitude

 

There are hundreds of users with thousands of shops/retail outlets. So  I will limit the sample data to one user and one date. So Assume a salesman has visited 8 shops and based on the date/time stamp, I have calculated the order of visits from where to where. The following is a subset of few columns added to the table above for one user and one date.

 

Visit OrderLat1Long1Lat2Long2
59.876506576.48764139.875895176.4875258
19.979890476.3172119.979890476.317211
79.925037176.52262949.898238476.3837396
29.979890476.3172119.94985476.3415586
89.898238476.38373969.902006376.3853692
69.875895176.48752589.925037176.5226294
49.901115676.39136919.876506576.4876413
39.94985476.34155869.901115676.3913691

 

Lat1 & Long1 are the coordinates of the source shop and Lat2 & Long2 are the coordinates of the destination shop. Meaning, the salesman has traveled from Point A to Point B, then from Point B to Point C, and so on...

 

My objective is to calculate the distance traveled by users on a daily basis. An approximation is okay; disregarding the actual route, signals, u-turns and so on that could not be calculated using latitude and longitude data.

 

The formula I have written to calculate the distance is as follows...

 

 

 

 

Distance = 
VAR Latitude1 =
    RADIANS ( dailyvisit_data[Lat1] )
VAR Longitude1 =
    RADIANS ( dailyvisit_data[Long1] )
VAR Latitude2 =
    RADIANS ( dailyvisit_data[Lat2] )
VAR Longitude2 =
    RADIANS ( dailyvisit_data[Long2] )
VAR DeltaOfLongitudes = Longitude2 - Longitude1
VAR DeltaOfLatitudes = Latitude2 - Latitude1
VAR Step1 =
    POWER ( SIN ( DIVIDE ( DeltaOfLatitudes, 2, 0 ) ), 2 )
        + COS ( Latitude1 ) * COS ( Latitude2 )
            * POWER ( SIN ( DIVIDE ( DeltaOfLongitudes, 2, 0 ) ), 2 )
VAR Step2 =
    2 * ASIN ( SQRT ( Step1 ) )
VAR RadiusOfEarthInKMs = 6371
VAR Result = Step2 * RadiusOfEarthInKMs
RETURN
    ROUND ( Result, 3 )

 

 

 

This gave me the following result on the sample data given above.

 

Visit OrderLat1Long1Lat2Long2 Distance 
59.876506576.48764139.875895176.48752580.07
19.979890476.3172119.979890476.317211-  
79.925037176.52262949.898238476.383739615.50
29.979890476.3172119.94985476.34155864.27
89.898238476.38373969.902006376.38536920.46
69.875895176.48752589.925037176.52262946.68
49.901115676.39136919.876506576.487641310.90
39.94985476.34155869.901115676.39136917.69

 

Totaling to 45.57 Kilometers.

 

I asked the user what is the total distance he traveled on this day and the actual distance seems to be 30.97 kilometers. Again this 30.97 kilometers is what is showing in the mobile app the user is using to register the visits. The variance between the distance I have calculated in Power BI and the user's mobile app is nearly 50%. 

 

Is there a way to verify the accuracy of my formula which I have written based on some google searches about calculating distances between Latitude and Longitude coordinate pairs.

 

 

Need Help
Tips and Tricks
Tutorial Requests

5 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler 

       

      I checked the link and the formula you have used. Though there is a difference between the formulas, both are giving the same results. 

  • FrankAT's avatar
    FrankAT
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    what about using maps.google.com to measure the distance between to coordinates?

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

    I'm no expert but...let me try

    A not very geographically accurate calculation could be using the right angle triangle length formula of A^2 + B^2 = C^2

    so the straight line distance, a.k.a radius, (since the earth is flat, as we all know all too well...) would be the square root of (Lat2 -Lat1)^2 + (Long2-Long1)^2 where the difference in coordinates is calculated in whatever distance metric (metres, KM...), using an appropriate conversion factor for both lat and long distances  based on the general geographical whereabouts (if you really need to fine tune the "radius", and can be easily found using two points in the sea with the same lat for lat and the same long for long), especially for the latitude, since the variation is relevant (equator vs. artic circle for example). 

    At least, I think that is something which rings a belI from those 'oh so happy days at school way back when' last century.

    someone please correct me if necessary!

    PS: I don't think the earth's curvature will make that much of a difference over short distances, and it is an approximation after all

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Icon for Administrator rankAdministrator
    4 years ago

    I'm using your formula and your result gives me the same result with an excel formula, so if it works