pattern mapping table structure

The pattern_mapping table holds information on the stop pattern for each route.

pattern_id is an unique pattern for the route

seq identifies the sequence of the stops for a trip

link identifies the link_id in the links table that corresponds to the pattern matching

dir indicates the direction of travel for a trip

Field	Type	NULL allowed	Default Value
pattern_id*	INTEGER	NO
seq	INTEGER	NO
link	INTEGER	NO
dir	INTEGER	NO
geometry	LINESTRING	YES

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE IF NOT EXISTS pattern_mapping (
     pattern_id  INTEGER    NOT NULL,
     seq         INTEGER    NOT NULL,
     link        INTEGER    NOT NULL,
     dir         INTEGER    NOT NULL,
     PRIMARY KEY(pattern_id, "seq"),
     FOREIGN KEY(pattern_id) REFERENCES routes (pattern_id) deferrable initially deferred,
     FOREIGN KEY(link) REFERENCES route_links (link) deferrable initially deferred
);

SELECT AddGeometryColumn( 'pattern_mapping', 'geometry', 4326, 'LINESTRING', 'XY');

SELECT CreateSpatialIndex( 'pattern_mapping' , 'geometry' );