transit links table structure

This table includes the hyper links connecting all pairs of consecutive stops for every transit pattern in the model.

Table Structure

Field	Type	NULL allowed	Default Value	Foreign key	Description
transit_link*	INTEGER	YES			Link ID of the unidirectional transit service link
pattern_id	INTEGER	NO		Transit_Patterns(pattern_id)	ID of the pattern in the format AARRRRPPPP0000 (Agency, Route, Pattern). For every from_node, to_node, pattern_id tuple, we generate a unique transit link
from_node	INTEGER	NO		Transit_Stops(from_node)	starting node for the link, the node being a transit stop/station from the GTFS
to_node	INTEGER	NO		Transit_Stops(to_node)	ending node for the link, the node being a transit stop/station from the GTFS
length	REAL	NO			length of the link in meters
type	INTEGER	NO			indicates the type of transit mode served at this link, see transit_modes table or GTFS reference for definitions
geo	LINESTRING	YES

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE IF NOT EXISTS "Transit_Links" (
    transit_link  INTEGER           PRIMARY KEY,
    pattern_id    INTEGER NOT NULL,
    from_node     INTEGER NOT NULL,
    to_node       INTEGER NOT NULL,
    length        REAL    NOT NULL,
    type          INTEGER NOT NULL,

    FOREIGN KEY(to_node) REFERENCES Transit_Stops(stop_id) deferrable initially deferred,
    FOREIGN KEY(from_node) REFERENCES Transit_Stops(stop_id) deferrable initially deferred,
    FOREIGN KEY(pattern_id) REFERENCES Transit_Patterns(pattern_id) deferrable initially deferred
);

CREATE INDEX IF NOT EXISTS transit_links_from_node ON Transit_Links (from_node);
CREATE INDEX IF NOT EXISTS transit_links_to_node ON Transit_Links (to_node);
CREATE UNIQUE INDEX IF NOT EXISTS transit_links_unique_idx ON Transit_Links (pattern_id, from_node, to_node);

SELECT AddGeometryColumn( 'Transit_Links', 'geo', SRID_PARAMETER, 'LINESTRING', 'XY');
SELECT CreateSpatialIndex( 'Transit_Links' , 'geo' );