transit vehicle links table structure#
Transit_Vehicle_links logs all the links traversed by a transit vehicle when completing a transit trip
Field |
Type |
NULL allowed |
Default Value |
Foreign key |
Description |
---|---|---|---|---|---|
object_id |
INTEGER |
NO |
Transit_Vehicle(object_id) |
The Transit_Vehicle whose trajectory is being described (foreign key to the Transit_Vehicle table) |
|
index |
INTEGER |
NO |
Position of link in trajectory |
||
value_transit_vehicle_trip |
INTEGER |
NO |
0 |
Duplicative to object_id - TODO REMOVE |
|
value_transit_vehicle_stop_sequence |
INTEGER |
NO |
0 |
Duplicative to index - TODO REMOVE |
|
value_link |
INTEGER |
NO |
0 |
The link which makes up this part of the overall path (foreign key to Link table) |
|
value_dir |
INTEGER |
NO |
The direction of travel on the link {0: a->b, 1: b->a} |
||
value_link_type |
INTEGER |
NO |
0 |
Link type as defined by enum. Values at value_link_type. |
|
value_Est_Arrival_Time |
INTEGER |
NO |
0 |
Estimated arrival time of vehicle at link in seconds (units: seconds) |
|
value_Act_Arrival_Time |
INTEGER |
NO |
0 |
Actual arrival time of vehicle at link in seconds (units: seconds) |
|
value_Est_Departure_Time |
INTEGER |
NO |
0 |
Estimated departure time of vehicle at link in seconds (units: seconds) |
|
value_Act_Departure_Time |
INTEGER |
NO |
0 |
Actual departure time of vehicle at link in seconds (units: seconds) |
|
value_Est_Dwell_Time |
REAL |
YES |
0 |
Estimated time in seconds spent waiting at stop (units: seconds) |
|
value_Act_Dwell_Time |
REAL |
YES |
0 |
Actual time in seconds spent waiting at stop (units: seconds) |
|
value_Est_Travel_Time |
REAL |
YES |
0 |
Estimated travel time in seconds to traverse link (units: seconds) |
|
value_Act_Travel_Time |
REAL |
YES |
0 |
Actual travel time in seconds to traverse link (units: seconds) |
|
value_Boardings |
INTEGER |
NO |
0 |
Number of traveler boardings logged by vehicle on this trip |
|
value_Alightings |
INTEGER |
NO |
0 |
Number of traveler alightings logged by vehicle on this trip |
|
value_Seated_Load |
INTEGER |
NO |
0 |
Number of seated occupants while vehicle traversed this link |
|
value_Seated_Capacity |
INTEGER |
NO |
0 |
Number of total seats available |
|
value_Standing_Load |
INTEGER |
NO |
0 |
Number of standing occupants while vehicle traversed this link |
|
value_Standing_Capacity |
INTEGER |
NO |
0 |
Total standing space available in number of persons |
|
value_start_position |
REAL |
YES |
0 |
Cumulative value in meters of distance traversed in trajectory as logged at beginning of link (units: meters) |
|
value_exit_position |
REAL |
YES |
0 |
Cumulative value in meters of distance traversed in trajectory as logged at end of link (units: meters) |
|
value_length |
REAL |
YES |
0 |
Lenght of link in meters (units: meters) |
|
value_speed |
REAL |
YES |
0 |
Speed that vehicle traversed the link for this trip in meters per second (units: m/s) |
(* - Primary key)
The SQL statement for table and index creation is below.
CREATE TABLE "Transit_Vehicle_links" (
"object_id" INTEGER NOT NULL,
"index" INTEGER NOT NULL,
"value_transit_vehicle_trip" INTEGER NOT NULL DEFAULT 0,
"value_transit_vehicle_stop_sequence" INTEGER NOT NULL DEFAULT 0,
"value_link" INTEGER NOT NULL DEFAULT 0,
"value_dir" INTEGER NOT NULL,
"value_link_type" INTEGER NOT NULL DEFAULT 0,
"value_Est_Arrival_Time" INTEGER NOT NULL DEFAULT 0,
"value_Act_Arrival_Time" INTEGER NOT NULL DEFAULT 0,
"value_Est_Departure_Time" INTEGER NOT NULL DEFAULT 0,
"value_Act_Departure_Time" INTEGER NOT NULL DEFAULT 0,
"value_Est_Dwell_Time" REAL NULL DEFAULT 0,
"value_Act_Dwell_Time" REAL NULL DEFAULT 0,
"value_Est_Travel_Time" REAL NULL DEFAULT 0,
"value_Act_Travel_Time" REAL NULL DEFAULT 0,
"value_Boardings" INTEGER NOT NULL DEFAULT 0,
"value_Alightings" INTEGER NOT NULL DEFAULT 0,
"value_Seated_Load" INTEGER NOT NULL DEFAULT 0,
"value_Seated_Capacity" INTEGER NOT NULL DEFAULT 0,
"value_Standing_Load" INTEGER NOT NULL DEFAULT 0,
"value_Standing_Capacity" INTEGER NOT NULL DEFAULT 0,
"value_start_position" REAL NULL DEFAULT 0,
"value_exit_position" REAL NULL DEFAULT 0,
"value_length" REAL NULL DEFAULT 0,
"value_speed" REAL NULL DEFAULT 0,
CONSTRAINT "object_id_fk"
FOREIGN KEY ("object_id")
REFERENCES "Transit_Vehicle" ("transit_vehicle_trip")
ON DELETE CASCADE)
Enums#
The following enums are used in this table.
value_link_type#
Value |
Description |
---|---|
0 |
FREEWAY |
1 |
ON_RAMP |
2 |
OFF_RAMP |
3 |
EXPRESSWAY |
4 |
ARTERIAL |
5 |
LOCAL |
6 |
EXTERNAL |
7 |
BIKEWAY |
8 |
WALK |
9 |
LIGHT_RAIL |
10 |
RAIL |
11 |
COMMUTER_RAIL |
12 |
BUS |
13 |
FERRY |
14 |
CABLE_TRAM |
15 |
AERIAL_LIFT |
16 |
FUNICULAR |
17 |
TROLLEY_BUS |
18 |
MONO_RAIL |