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 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?

 

 

Need Help

4 Replies

  • v-caliao-msft's avatar
    v-caliao-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

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

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