Distance calculation with list of coordinates

[bredn]

Hi,

I currently have a map of several vehicles and their routes throughout the day. The map is plotted by using these coordinates and filtering on the car ID. I need to mention that I do have a timestamp of when the coordinates were sent. The coordinates come at random times and there is no ordering of events by car ID (meaning the list of coordinates contain all IDs at different times).

Now I want to calculate the total distance the car has driven during the day, and visualise this. I have tried multiple formulas and guides I found on this forum, but with no luck. Do any of you know how to do this?

Thanks

-------

Edit:

I duplicated my lon/lat columns, and artificially removed the first entry, thus to create a simulated starting/end-point. After having done this i tried the following formula:

Distanse =
VAR R = 6371 
VAR Pi = 3,141592 

VAR p1='Spørring1'[latitude]*Pi/180
VAR q1='Spørring1'[longitude]*Pi/180
VAR p2='Spørring1'[lat2]*Pi/180
VAR q2='Spørring1'[lon2]*Pi/180

VAR DeltaP = ABS(p1-p2) 
VAR DeltaQ = ABS(q1-q2) 

RETURN
IF(
     p1 = BLANK()||p2=BLANK()||q1=BLANK()||q2=BLANK()
;BLANK()
;ACOS(SIN(p1)*SIN(p2)+COS(p1)*COS(p2)*COS(DeltaQ))*R
)

When trying to execute this formula I get the following error message: An argument of function ACOS either has the wrong datatype, or is too large or small.

All the columns have the same datatype, and I dont see why they would be too large or small, any idea why this occurs?

[v-caliao-msft]

@bredn,

You can just return the value 
SIN(p1)*SIN(p2)+COS(p1)*COS(p2)*COS(DeltaQ)
and check if it has the correct data type or is too large or small.

Regards,

Charlie Liao

[bredn]

@v-caliao-msft

Thanks for the reply,

When returning the formula without ACOS I get a value returned, so its not a datatype issue. 

Do you know how I could get this to work for each vehicle and their total distance? I am now getting the same total value for all vehicles, eventhoug they have not driven the same distance.

[bredn]

EDIT and update:

I have created a table that ranks each incoming event mapped to its vechicle, so that I can get all the events ordered by device. The table and code for creating it looks like this: 

Rank = RANKX (
    FILTER (
        'Tracker Status Variables';
        'Tracker Status Variables'[device_id] = EARLIER ( 'Tracker Status Variables'[device_id] )
    );
    RANKX (
        FILTER (
            'Tracker Status Variables';
            'Tracker Status Variables'[device_id] = EARLIER ( 'Tracker Status Variables'[device_id] )
        );
        'Tracker Status Variables'[eventenqueuedutctime];
        ;
        DESC
    )
        + DIVIDE (
            RANKX (
                FILTER (
                    'Tracker Status Variables';
                    'Tracker Status Variables'[device_id] = EARLIER ( 'Tracker Status Variables'[device_id] )
                );
                'Tracker Status Variables'[eventenqueuedutctime];
                ;
                ASC
            );
            (
                COUNTROWS (
                    FILTER (
                        'Tracker Status Variables';
                        'Tracker Status Variables'[device_id] = EARLIER ( 'Tracker Status Variables'[device_id] )
                    )
                )
                    + 1
            )
        )
)

My current distance calculation formula looks like this:

VINCENTY = ACOS(COS(RADIANS(90-'Tracker Status Variables'[latitude]))
*COS(RADIANS(90-'Tracker Status Variables'[lat2]))+
SIN(RADIANS(90-'Tracker Status Variables'[latitude]))*
SIN(RADIANS(90-'Tracker Status Variables'[lat2]))*
COS(RADIANS('Tracker Status Variables'[lon2]-'Tracker Status Variables'[longitude])))*6371

lat2 and lon2 represent copies of the original latitude/longitude data, with the first record removed. The hope was that this would allow me to create a distance calculation, and order it by ID. Sadly this is not working. Do any of you know what i could do here? Feel like im at my wits end, any help would be greatly appreciated.

[bredn]

EDIT and Update:

Like I mentioned in the earlier update, I added a copy of the original lat/on columns. Today I tried adding some logic to this, so that it would skip the first entry, thus creating a synthetic start/stop point. The code for this is below (there are several ways of doing this, but this is the approach I went with).

LongitudeLookup = LOOKUPVALUE('Tracker Status Variables'[longitude];'Tracker Status Variables'[device_id];'Tracker Status Variables'[device_id];'Tracker Status Variables'[Rank];'Tracker Status Variables'[Rank]+1)

This uses the previously created rank variable.

The result of this is that I get accurate distances per day. However, this only works for the previous day. For data being transmitted now, the latest entrypoint gets treated as 0, thus the distance is reported to being the radius of the earth (i.e some 6300 KM).

I choosing to edit this post further in case someone else has a similar problem, hopefully this can help in some way.