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			indicates the type of transit mode served by this route, see transit_modes table or GTFS reference for definitions
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
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,
    seated_capacity INTEGER,
    design_capacity INTEGER,
    total_capacity  INTEGER,
    number_of_cars  INTEGER DEFAULT 0,

    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' );