transit routes table structure

The transit routes correspond to the routes table in GTFS feeds, but this table includes only those routes for which that are active services for the for which services have been imported. Descriptive information, as well as capacity is included in this table, although the latter can be overwritten if information is provided in the transit_patterns or transit_trips tables.

The routes can be traced back to the 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
route_id*	INTEGER	NO			ID of the route in the format AARRRR00000000 (Agency, Route)
route	TEXT	NO			ID of the route as defined in the GTFS
agency_id	INTEGER	NO		Transit_Agencies(agency_id)	ID of the agency serving the route
shortname	TEXT	YES			short name of the route as seen in the GTFS
longname	TEXT	YES			long name of the route as seen in the GTFS
description	TEXT	YES			description of the route as seen in the GTFS
type	INTEGER	NO			collapsed GTFS route_type (basic 0-12) served by this route, see transit_modes table for definitions
route_type_gtfs	INTEGER	YES			raw GTFS route_type as seen in routes.txt before collapsing extended route types to basic ones. The raw value is not used by POLARIS simulation; it is stored only so a GTFS export after editing remains consistent with the GTFS originally imported.
seated_capacity	INTEGER	YES			Seated capacity of the vehicles operating this route.
design_capacity	INTEGER	YES			Design capacity of the vehicles operating this route.
total_capacity	INTEGER	YES			Total capacity of the vehicles operating this route, actually used in POLARIS as opposed to design_capacity.
number_of_cars	INTEGER	YES	0		Number of train cars operating this route. 0 for regular bus services. Used to calculate the train capacities, not directly used in POLARIS
route_url	TEXT	YES			URL of a web page about this route, as in routes.route_url. Not used by POLARIS simulation; stored only so a GTFS export after editing remains consistent with the GTFS originally imported.
route_color	TEXT	YES			background route color (six-digit hex without leading #) as in routes.route_color. Not used by POLARIS simulation; stored only so a GTFS export after editing remains consistent with the GTFS originally imported.
route_text_color	TEXT	YES			legible text color over route_color (six-digit hex without leading #) as in routes.route_text_color. Not used by POLARIS simulation; stored only so a GTFS export after editing remains consistent with the GTFS originally imported.
route_sort_order	INTEGER	YES			rendering order for the route, as in routes.route_sort_order. Not used by POLARIS simulation; stored only so a GTFS export after editing remains consistent with the GTFS originally imported.
continuous_pickup	INTEGER	YES			routes.txt continuous_pickup: 0 continuous, 1 none, 2 phone agency, 3 coordinate with driver. Not used by POLARIS simulation; stored only so a GTFS export after editing remains consistent with the GTFS originally imported.
continuous_drop_off	INTEGER	YES			routes.txt continuous_drop_off: 0 continuous, 1 none, 2 phone agency, 3 coordinate with driver. Not used by POLARIS simulation; stored only so a GTFS export after editing remains consistent with the GTFS originally imported.
geo	MULTILINESTRING	YES

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE IF NOT EXISTS Transit_Routes(
    route_id            INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
    route               TEXT     NOT NULL,
    agency_id           INTEGER  NOT NULL,
    shortname           TEXT,
    longname            TEXT,
    description         TEXT,
    "type"              INTEGER  NOT NULL,
    route_type_gtfs     INTEGER,
    seated_capacity     INTEGER,
    design_capacity     INTEGER,
    total_capacity      INTEGER,
    number_of_cars      INTEGER DEFAULT 0,
    route_url           TEXT,
    route_color         TEXT,
    route_text_color    TEXT,
    route_sort_order    INTEGER,
    continuous_pickup   INTEGER,
    continuous_drop_off INTEGER,
    FOREIGN KEY(agency_id) REFERENCES Transit_Agencies(agency_id) deferrable initially deferred
);
select AddGeometryColumn( 'Transit_Routes', 'geo', SRID_PARAMETER, 'MULTILINESTRING', 'XY');
select CreateSpatialIndex( 'Transit_Routes' , 'geo' );