Suppose I have vehicle data of the form:
2016-10-18 17:37:05 GMT vehicle_id="1011" vehicle_distance=185 stop_tag="5239"
2016-10-18 17:39:25 GMT vehicle_id="1009" vehicle_distance=51 stop_tag="4532"
2016-10-18 17:41:37 GMT vehicle_id="1010" vehicle_distance=107 stop_tag="4529"
2016-10-18 17:43:35 GMT vehicle_id="1009" vehicle_distance=104 stop_tag="4532"
2016-10-18 17:45:59 GMT vehicle_id="1011" vehicle_distance=98 stop_tag="5240"
I also have a look-up table that give additional information about stops keyed by tag:
stop_tag,stop_title,stop_id,stop_direction,stop_lat,stop_lon
...
"5239","King St & 4th St","15239","Outbound",37.776270,-122.394170
"5240","King St & 4th St","15240","Inbound",37.776270,-122.394080
...
Each physical stop actually has two stop tags: one for "Inbound" and another for "Outbound," for example, the "King St & 4th St" stop shown. The direction is given by stop_direction
.
Hence, given a log entry such as the first, at 17:37:05, vehicle 1011 is at stop 5239 --- which is King St & 4th St outbound. Then, nearly 9 minutes later, the same vehicle is at stop 5240 --- the same stop, but inbound. Hence, the vehicle had a "layover" of approximately 9 minutes.
For each vehicle, when it changes direction from outbound to inbound (or vice versa) as given by the direction of the stop tag it's at, I want to chart both the average and maximum layover as the Y-axis and either:
vehicle_id
.How can I do this?
Try this
base search vehicle data | lookup lookupfile.csv stop_tag | reverse | streamstats count by vehicle_id stop_direction | stats earliest(_time) as start latest(_time) as end earliest(stop_direction) as dir1 latest(stop_direction) as dir2 by vehicle_id count | where NOT (dir1=dir2) | eval duration=end-stop
You have a typo at the end: should be end-start
Anyway, I took that as a starting point and did:
base search | reverse | streamstats count by vehicle_id stop_direction | stats earliest(_time) as start latest(_time) as end earliest(stop_direction) as dir1 latest(stop_direction) as dir2 by vehicle_id count | where dir1="Outbound" AND dir2="Inbound" | eval layover=(end-start)/60 | chart min(layover) as Min avg(layover) AS Avg max(layover) as Max by vehicle_id
What about part 1 of my request? All vehicles taken together?