Forum Discussion

bredn's avatar
bredn
Frequent Visitor
8 years ago

Distance calculation with list of coordinates

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 t...
Need Help
bredn's avatar
bredn
Frequent Visitor
8 years ago

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's avatar
bredn
Frequent Visitor
8 years ago

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.