Skip to main content
`Ctrl`+`K`

# route links table structure

The route_links table holds information on the links of a route.

transit_link identifies the GTFS transit links for the route

pattern_id is an unique pattern for the route

seq identifies the sequence of the stops for a trip

from_stop identifies the stop the vehicle is departing

to_stop identifies the next stop the vehicle is going to arrive

distance identifies the distance (in meters) the vehicle travel between the stops

Field | Type | NULL allowed | Default Value
--- | --- | --- | ---
transit_link | INTEGER | NO |
pattern_id | INTEGER | NO |
seq | INTEGER | NO |
from_stop | INTEGER | NO |
to_stop | INTEGER | NO |
distance | INTEGER | NO |
geometry | LINESTRING | YES |

(* - Primary key)

The SQL statement for table and index creation is below.

    CREATE TABLE IF NOT EXISTS route_links (
         transit_link            INTEGER    NOT NULL,
         pattern_id              INTEGER    NOT NULL,
         seq                     INTEGER    NOT NULL,
         from_stop               INTEGER    NOT NULL,
         to_stop                 INTEGER    NOT NULL,
         distance                INTEGER    NOT NULL,
         FOREIGN KEY(pattern_id) REFERENCES "routes"(pattern_id) deferrable initially deferred,
         FOREIGN KEY(from_stop)  REFERENCES "stops"(stop_id) deferrable initially deferred
         FOREIGN KEY(to_stop)    REFERENCES "stops"(stop_id) deferrable initially deferred
    );

    create UNIQUE INDEX IF NOT EXISTS route_links_stop_id ON route_links (pattern_id, transit_link);

    select AddGeometryColumn( 'route_links', 'geometry', 4326, 'LINESTRING', 'XY');

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