Compute distances based on LatLong data

[Anonymous]

Hi @Greg_Deckler , @parry2k , @ImkeF , @TomMartens 

I need an expert opinion on this. 

I have a table with the following data.

UserID

ShopCode

ShopName

Visit Date

Visit Time

Latitude

Longitude

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 Order	Lat1	Long1	Lat2	Long2
5	9.8765065	76.4876413	9.8758951	76.4875258
1	9.9798904	76.317211	9.9798904	76.317211
7	9.9250371	76.5226294	9.8982384	76.3837396
2	9.9798904	76.317211	9.949854	76.3415586
8	9.8982384	76.3837396	9.9020063	76.3853692
6	9.8758951	76.4875258	9.9250371	76.5226294
4	9.9011156	76.3913691	9.8765065	76.4876413
3	9.949854	76.3415586	9.9011156	76.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 Order	Lat1	Long1	Lat2	Long2	Distance
5	9.8765065	76.4876413	9.8758951	76.4875258	0.07
1	9.9798904	76.317211	9.9798904	76.317211	-
7	9.9250371	76.5226294	9.8982384	76.3837396	15.50
2	9.9798904	76.317211	9.949854	76.3415586	4.27
8	9.8982384	76.3837396	9.9020063	76.3853692	0.46
6	9.8758951	76.4875258	9.9250371	76.5226294	6.68
4	9.9011156	76.3913691	9.8765065	76.4876413	10.90
3	9.949854	76.3415586	9.9011156	76.3913691	7.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.

[Syndicate_Admin]

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

[PaulDBrown]

@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

[FrankAT]

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)

[Greg_Deckler]

@Anonymous Check you formula against the one that I created, "Going the Distance". https://community.powerbi.com/t5/Quick-Measures-Gallery/Going-the-Distance/m-p/963267#M423

I would expect that the distance calculated using lat/long should be less than the actual distance traveled.

[Anonymous]

@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.