transit trips table structure

transit trips table structure#

The Transit_Trips table holds the complete list of all transit services operating, for which one can override the information on capacity coming from route/pattern. There is also information on whether the vehicle is articulated or if it has multiple cars (applicable to rail), but both of these fields default to 0 in case of regular bus services.

The transit trip ID can be traced back to the pattern, route and agency directly through the encoding of their trip_id, as explained in the documentation for the Transit_Agencies table.

Table Structure#

Field

Type

NULL allowed

Default Value

Foreign key

Description

trip_id*

INTEGER

NO

ID of the individual trip in the format AARRRRPPPPTTTT (Agency, Route, Pattern, Trip)

trip

TEXT

YES

Trip identifier as shown in the GTFS feed

dir

INTEGER

NO

Direction of the trip

pattern_id

INTEGER

NO

Transit_Patterns(pattern_id)

ID of the pattern this trip refers to

seated_capacity

INTEGER

YES

NULL

Seated capacity of the vehicles operating this trip. Overrides the information on the transit_patterns table

design_capacity

INTEGER

YES

NULL

Design capacity of the vehicles operating this trip. Overrides the information on the transit_patterns table

total_capacity

INTEGER

YES

NULL

Total capacity of the vehicles operating this trip, actually used in POLARIS as opposed to design_capacity. Overrides the information on the transit_patterns table

is_artic

INTEGER

YES

0

number_of_cars

INTEGER

YES

0

Number of train cars operating this trip. 0 for regular bus services. Used to calculate the train capacities, not directly used in POLARIS

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE IF NOT EXISTS "Transit_Trips" (
    trip_id         INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    trip            TEXT,
    dir             INTEGER NOT NULL,
    pattern_id      INTEGER NOT NULL,
    seated_capacity INTEGER DEFAULT NULL,
    design_capacity INTEGER DEFAULT NULL,
    total_capacity  INTEGER DEFAULT NULL,
    is_artic        INTEGER DEFAULT 0,   -- @ Whether the vehicle is articulated or not. 1 for articulated, 0 for non-articulated. Used to calculate bus capacities, not directly used in POLARIS
    number_of_cars  INTEGER DEFAULT 0,

    FOREIGN KEY(pattern_id) REFERENCES Transit_Patterns(pattern_id) deferrable initially deferred
);