Find no of trips using Latitude and Longitude

[prateekraina]

Hello,

I have a scenario wherein I need to find out no of trips made by a driver based on Latitude and Longitude data which I receive.

I have a fixed Source Address, whenever the car leaves that address and comes back, it means one complete trip.

Following is the sample data. Assume that my source address coordinates are Latitude - LA1 and Longitude - LO1

Timestamp	Latitude	Longitude	Trips
3/15/18 11:00 AM	LA1	LO1
3/15/18 11:15 AM	LA1	LO1	1
3/15/18 11:30 AM	LA2	LO2
3/15/18 11:45 AM	LA3	LO3
3/15/18 12:00 PM	LA1	LO1
3/15/18 12:15 PM	LA1	LO1
3/15/18 12:30 PM	LA1	LO1	2
3/15/18 12:45 PM	LA2	LO2
3/15/18 1:00 PM	LA3	LO3
3/15/18 1:15 PM	LA4	LO4
3/15/18 1:30 PM	LA1	LO1
3/15/18 1:45 PM	LA1	LO1
3/15/18 2:00 PM	LA1	LO1
3/15/18 2:15 PM	LA1	LO1
3/15/18 2:30 PM	LA1	LO1
3/15/18 2:45 PM	LA1	LO1	3
3/15/18 3:00 PM	LA3	LO3
3/15/18 3:15 PM	LA3	LO3
3/15/18 3:30 PM	LA3	LO3
3/15/18 3:45 PM	LA1	LO1
3/15/18 4:00 PM	LA1	LO1
3/15/18 4:15 PM	LA1	LO1

As per above data, no of trips is 3. Can anyone suggest how to achive this calculation?

Prateek Raina

[TomMartens]

Hey,

this is an interesting question, but I have to admit that I'm not able to fully understand the source data.

My first understanding was:

if LA/LO from the immediate previous row (ordered by the Timestamp) and grouped by driver id are the same this would count as trip, but looking at the source data, this understanding is not correct.

Second guess is whenver, the next LA/LO does not  match with the "home address" a trip counted. This seems valid, but I'm also unsure about the first trip.

Can you plesase confirm that this rule is valid (per driver / per day):

A trip is counted when the next position is not the homeposition 

Regards

Tom

[prateekraina]

Hi @TomMartens,

I was not able to properly create grid in this post.

1 trp = No of rows between bold LA1 and LO1 including those rows.

"Can you plesase confirm that this rule is valid (per driver / per day):

A trip is counted when the next position is not the homeposition" - i will be getting data of only one driver at once so i did not group it on driver.

In some scenarios trip would have been completed and still I will be receiving the home coordinates, so that would not come under my trip. So we cannot say for sure that a trip is counted when the next position is not the home position.

Prateek Raina

[Phil_Seamark]

Hi @prateekraina

This calculated column gets close.  It puts 1's in, instead of a cumulative total.  I'm guessing that running a SUM over this column would be more useful

Trips = 
VAR SourceLat = "LA1"
VAR SourceLng = "LO1"
VAR NextTimeStamp = 
    MINX(
        FILTER(
            'Table1',
            'Table1'[Timestamp] > EARLIER('Table1'[Timestamp])
            ),
        'Table1'[Timestamp]
        )
VAR NextLat = MINX(FILTER('Table1',Table1[Timestamp] = NextTimeStamp),'Table1'[Latitude])
VAR NextLng = MINX(FILTER('Table1',Table1[Timestamp] = NextTimeStamp),'Table1'[Longitude])
VAR Result =  
    IF (
        'Table1'[Latitude] = SourceLat && 
        'Table1'[Longitude] = SourceLng && 
        'Table1'[Latitude] <> NextLat &&
        'Table1'[Longitude] <> NextLng && 
        NOT ISBLANK(NextLat) ,1 , BLANK())
        
RETURN Result

        

[prateekraina]

Hi @Phil_Seamark,

I tried this, it works well.

Can you please tell if it is possible to group this result by another column "DriverID".

I tried to filter the resulting measure (i.e. Sum of Trips) by a specific DriverID but then it shows incorrect no of Trips as the logic is applied on Timestamp,Lat and Long across all the drivers.

Kindly let me know if it can be done.

Please see the below image for better understanding.
Also it would be great to somehow see trip duration for a driver.
Example: Driver D1's first trip is from 11:15 AM to 12:00 PM

Prateek Raina

[Phil_Seamark · Accepted solution]

HI @prateekraina

Please try this modified calculated column to allow for DriverID

Trips = 
VAR SourceLat = "LA1"
VAR SourceLng = "LO1"
VAR DriverID = 'Table1'[DriverID]
VAR NextTimeStamp = 
    MINX(
        FILTER(
            'Table1',
            'Table1'[Timestamp] > EARLIER('Table1'[Timestamp])
            && 'Table1'[DriverID] = DriverID
            ),
        'Table1'[Timestamp]
        )
VAR NextLat = MINX(FILTER('Table1',Table1[Timestamp] = NextTimeStamp && 'Table1'[DriverID] = DriverID),'Table1'[Latitude])
VAR NextLng = MINX(FILTER('Table1',Table1[Timestamp] = NextTimeStamp && 'Table1'[DriverID] = DriverID),'Table1'[Longitude])
VAR Result =  
    IF (
        'Table1'[Latitude] = SourceLat && 
        'Table1'[Longitude] = SourceLng && 
        'Table1'[Latitude] <> NextLat &&
        'Table1'[Longitude] <> NextLng && 
        NOT ISBLANK(NextLat) ,1 , BLANK())
        
RETURN Result

        

[prateekraina]

Hi @Phil_Seamark,

Thank you very much for the help.

Lastly, can you please tell if there is any way to filter the data by Driver ID and get his/her trip duration?

Prateek Raina

[Phil_Seamark]

I can look at this tomorrow.  I have to prepare to present a DPG webinar later this evening and I should focus on that.

[prateekraina]

Hi @Phil_Seamark,

I just integrated this column in my actual development report which has around 36K rows. This calculated column thorows memory error.

Upon investigating, I found out that It is happening beacuse of following condition written in bold:

VAR NextTimeStamp = 
    MINX(
        FILTER(
            'Table1',
            'Table1'[Timestamp] > EARLIER('Table1'[Timestamp])
            && 'Table1'[DriverID] = DriverID
            ),
        'Table1'[Timestamp]
        )

Removing it creates the column successfully but then gives incosistent result 😞

I suspect EARLIER is causing this error. Is there any other way to do this?

[prateekraina]

@Phil_Seamark

Any luck?

[prateekraina]

Yes, sure. Thanks a lot.

[prateekraina]

Hi @Phil_Seamark,

Thanks for your suggestion, i will try this out and let you know. Appreciate the help.

Prateek Raina