Forum Discussion
How do I get distance between two gps rows
Hi,
I have a table with informations about cars locations. (carID, entry_datetime, latitude, longtitude)
And I want to make another column with distance between current row and the previous one based on entry_datetime for same carID.
I know how to count distance between two gps locations, but I dont know how to get these values into one formula.
Can you help me please?
Thank you!
Hi Anonymous ,
Is this what you want to achieve? :
If so, you can try this measure:
DifferenceKm = VAR currentLat = MIN ( T[Lat] ) VAR currentLng = MIN ( T[Lng] ) VAR prevLat = CALCULATE ( MIN ( T[Lat] ), FILTER ( ALL ( T ), T[DateTime] < SELECTEDVALUE ( T[DateTime] ) && T[CarID] = SELECTEDVALUE ( T[CarID] ) ) ) VAR prevLng = CALCULATE ( MIN ( T[Lng] ), FILTER ( ALL ( T ), T[DateTime] < SELECTEDVALUE ( T[DateTime] ) && T[CarID] = SELECTEDVALUE ( T[CarID] ) ) ) VAR p = DIVIDE ( PI (), 180 ) VAR A = 0.5 - COS ( ( prevLat - currentLat ) * p ) / 2 + COS ( currentLat * p ) * COS ( prevLat * p ) * ( 1 - COS ( ( prevLng - currentLng ) * p ) ) / 2 VAR result = 12742 * ASIN ( ( SQRT ( A ) ) ) RETURN COALESCE ( IF ( NOT ISBLANK ( prevLat ), result ), "" )If not, please, provide a table with data example and demonstrate the result to be achieved.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.
2 Replies
- ERD
Community Champion
Hi Anonymous ,
Is this what you want to achieve? :
If so, you can try this measure:
DifferenceKm = VAR currentLat = MIN ( T[Lat] ) VAR currentLng = MIN ( T[Lng] ) VAR prevLat = CALCULATE ( MIN ( T[Lat] ), FILTER ( ALL ( T ), T[DateTime] < SELECTEDVALUE ( T[DateTime] ) && T[CarID] = SELECTEDVALUE ( T[CarID] ) ) ) VAR prevLng = CALCULATE ( MIN ( T[Lng] ), FILTER ( ALL ( T ), T[DateTime] < SELECTEDVALUE ( T[DateTime] ) && T[CarID] = SELECTEDVALUE ( T[CarID] ) ) ) VAR p = DIVIDE ( PI (), 180 ) VAR A = 0.5 - COS ( ( prevLat - currentLat ) * p ) / 2 + COS ( currentLat * p ) * COS ( prevLat * p ) * ( 1 - COS ( ( prevLng - currentLng ) * p ) ) / 2 VAR result = 12742 * ASIN ( ( SQRT ( A ) ) ) RETURN COALESCE ( IF ( NOT ISBLANK ( prevLat ), result ), "" )If not, please, provide a table with data example and demonstrate the result to be achieved.
If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.