Skip to main content
`Ctrl`+`K`

# routes table structure

The routes table holds information on the available transit routes for a specific day. This table information comes from the GTFS file routes.txt. You can find more information about the routes table here.

pattern_id is an unique pattern for the route

route_id identifies a route

route identifies the name of a route

agency_id identifies the agency for the specified route

shortname identifies the short name of a route

longname identifies the long name of a route

description provides useful description of a route

route_type indicates the type of transportation used on a route

pce indicates the passenger car equivalent for transportation used on a route

seated_capacity indicates the seated capacity of a route

total_capacity indicates the total capacity of a route

Field | Type | NULL allowed | Default Value
--- | --- | --- | ---
pattern_id* | INTEGER | NO |
route_id | INTEGER | NO |
route | TEXT | NO |
agency_id | INTEGER | NO |
shortname | TEXT | YES |
longname | TEXT | YES |
description | TEXT | YES |
route_type | INTEGER | NO |
pce | NUMERIC | NO | 2.0
seated_capacity | INTEGER | YES |
total_capacity | INTEGER | YES |
geometry | MULTILINESTRING | YES |

(* - Primary key)

The SQL statement for table and index creation is below.

    CREATE TABLE IF NOT EXISTS routes (
         pattern_id      INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
         route_id        INTEGER  NOT NULL,
         route           TEXT     NOT NULL,
         agency_id       INTEGER  NOT NULL,
         shortname       TEXT,
         longname        TEXT,
         description     TEXT,
         route_type      INTEGER  NOT NULL,
         pce             NUMERIC  NOT NULL DEFAULT 2.0,
         seated_capacity INTEGER,
         total_capacity  INTEGER,
         FOREIGN KEY(agency_id) REFERENCES agencies(agency_id) deferrable initially deferred
    );

    select AddGeometryColumn( 'routes', 'geometry', 4326, 'MULTILINESTRING', 'XY');

    select CreateSpatialIndex( 'routes' , 'geometry' );