transit vehicle links table structure

Transit_Vehicle_links logs all the links traversed by a transit vehicle when completing a transit trip

Table Structure

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

Enum Values Description

Value	Name	Description
0	FREEWAY
1	ON_RAMP
2	OFF_RAMP
3	EXPRESSWAY
4	ARTERIAL
5	LOCAL
6	EXTERNAL
7	BIKEWAY
8	WALK
9	LIGHT_RAIL	GTFS type = 0
10	RAIL	GTFS type = 0
11	COMMUTER_RAIL	GTFS type = 2
12	BUS	GTFS type = 3
13	FERRY	GTFS type = 4
14	CABLE_TRAM	GTFS type = 5
15	AERIAL_LIFT	GTFS type = 6
16	FUNICULAR	GTFS type = 7
17	TROLLEY_BUS	GTFS type = 11
18	MONO_RAIL	GTFS type = 12