.. _pattern_mapping_transit_data_model:

**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

.. csv-table:: 
   :header: "Field", "Type", "NULL allowed", "Default Value"
   :widths:    30,     20,         20,          20

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