Trip Fact Table

driver_dimension

Maps directly to the Driver dimension table using the driver_[COLUMN NAME] naming convention. For example driver_last_name to query the last_name column of the Driver dimension table.

SELECT 
 "start_time" AS "trip start time",
 "end_time" AS "trip end time",
 "total_distance" AS "trip distance",
 "driver_first_name" AS "first name",
 "driver_last_name" AS "last name"
FROM 
 "trip" 
WHERE 
 "total_distance" is not null
GROUP BY 
 "start_time", "end_time", "total_distance", 
 "driver_first_name", "driver_last_name"
ORDER BY 
 "total_distance" DESC
LIMIT
 10;

vehicle_dimension

Maps directly to the Vehicle dimension table using the vehicle_[COLUMN NAME] naming convention. For example vehicle_model to query the model column of the Vehicle dimension table.

SELECT 
 "start_time" AS "trip start time",
 "end_time" AS "trip end time",
 "total_distance" AS "trip distance",
 "vehicle_year" AS "vehicle year",
 "vehicle_make" AS "vehicle make"
FROM 
 "trip" 
WHERE 
 "total_distance" is not null
GROUP BY 
 "start_time", "end_time", "total_distance", 
 "vehicle_year", "vehicle_make"
ORDER BY 
 "total_distance" DESC
LIMIT
 10;

start_marker

The ID of the marker at which this trip began. Maps to the Marker dimension table.

end_marker

The ID of the marker at which this trip ended. Maps to the Marker dimension table.

start_day

The day on which this trip began as a concatenated year, month and day integer [YYYYMMDD]

20140122

start_time

The time at which this trip began.

end_day

The day on which this trip ended.

end_time

The time at which this trip ended.

duration

The duration of the trip, in seconds.

60

total_distance

Total distance traveled in this trip, in the user's default distance units (miles or kilometers).

49635

fitted_route_distance

The distance value obtained by route fitting the trip points, in the user's default distance units (miles or kilometers).

fuel_used

The amount of fuel used in this trip, in the user's default volume units (gallons or liters).

30

drive_duration

The driving duration of this trip, in seconds.

3600

working_duration

The working duration of this trip, in seconds.

9769

on_site_duration

The on-site duration of this trip, in seconds.

24000

preidle_duration

The pre-idle state duration of this trip, in seconds.

1800

postidle_duration

The post-idle state duration of this trip, in seconds.

50

pto_duration

The duration of PTO (Power Take Off, for off-board pony engine applications such as boom arms and pumps) use in this trip, in seconds.

600

hard_accel

The number of hard acceleration events recorded during this trip.

3

hard_brake

The number of hard braking events recorded during this trip.

6

seatbelt_violations

The number of seatbelt violation events recorded during this trip.

2

speeding_over_posted

The number of speeding over posted violation events recorded during this trip.

4

speeding_excessive

The number of excessive speeding events recorded during this trip.

1

engine_time

The engine run-time duration during this trip, in seconds.

16800

non_productive_preidle_duration

The duration of the trip spent in a non-productive pre-idle state, in seconds.

390

non_productive_postidle_duration

The duration of the trip spent in a non-productive post-idle state, in seconds.

86400

direct_route_distance

The distance in meters generated when routing directly from the first point of the trip to the last point.

9730

direct_route_duration

The time in seconds when routing (using estimated vehicle speed) directly from the first point of the trip to the last point.

8653

start_odo

The vehicle odometer reading at the start of the trip in the user's default distance units (miles or kilometers).

37979

end_odo

The vehicle odometer reading at the end of the trip on in the user's default distance units (miles or kilometers).

381492

dtc_count

The number of diagnostic trouble codes (DTCs) logged during the trip.

0

completed

Whether the trip was marked as complete. True if completed, false if not.

True

custom_metrics

eid

speeding_over_posted_without_edits

The number of events triggered by speeding over the posted speed limit by 10MPH or more on links without a speed limited edited using the SVACS speed limit editor.

5

is_deleted

idle_over_ten_minutes

The number of events triggered by idling for over ten minutes

2