bredn Tracker

Re: Distance calculation with list of coordinates

bredn — Wed, 08 Nov 2017 15:29:02 GMT

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.

Re: Distance calculation with list of coordinates

bredn — Tue, 07 Nov 2017 16:13:56 GMT

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.

Re: Distance calculation with list of coordinates

bredn — Mon, 06 Nov 2017 08:32:09 GMT

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.

Distance calculation with list of coordinates

bredn — Fri, 03 Nov 2017 14:16:16 GMT

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?