Trip Fact Table

Column

Description

Example

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