transit transfers table structure

The Transit_Transfers table holds the transfer rules between stops, routes and trips for each agency, as found in the GTFS transfers.txt file.

Per the latest GTFS Schedule specification, transfers may now reference not only stops but also specific routes and trips for both endpoints, allowing feed authors to express linked trips and route-aware transfer constraints.

The table is not used by POLARIS simulation itself. It is stored only so that a GTFS export after editing remains consistent with the GTFS originally imported.

Table Structure

Field	Type	NULL allowed	Default Value	Foreign key	Description
transfer_id*	INTEGER	NO			surrogate primary key for the transfer rule.
agency_id	INTEGER	YES		Transit_Agencies(agency_id)	ID of the agency that produced this transfer rule (for traceability).
from_stop_id	INTEGER	YES		Transit_Stops(from_stop_id)	Polaris stop_id of the transfer origin (resolved from transfers.from_stop_id).
to_stop_id	INTEGER	YES		Transit_Stops(to_stop_id)	Polaris stop_id of the transfer destination (resolved from transfers.to_stop_id).
from_route_id	INTEGER	YES		Transit_Routes(from_route_id)	Polaris route_id of the transfer origin route, when transfers.from_route_id is provided.
to_route_id	INTEGER	YES		Transit_Routes(to_route_id)	Polaris route_id of the transfer destination route, when transfers.to_route_id is provided.
from_trip_id	INTEGER	YES		Transit_Trips(from_trip_id)	Polaris trip_id of the transfer origin trip, when transfers.from_trip_id is provided.
to_trip_id	INTEGER	YES		Transit_Trips(to_trip_id)	Polaris trip_id of the transfer destination trip, when transfers.to_trip_id is provided.
from_stop	TEXT	YES			raw GTFS from_stop_id, kept for traceability when the resolved id cannot be derived.
to_stop	TEXT	YES			raw GTFS to_stop_id, kept for traceability when the resolved id cannot be derived.
from_route	TEXT	YES			raw GTFS from_route_id, kept for traceability.
to_route	TEXT	YES			raw GTFS to_route_id, kept for traceability.
from_trip	TEXT	YES			raw GTFS from_trip_id, kept for traceability.
to_trip	TEXT	YES			raw GTFS to_trip_id, kept for traceability.
transfer_type	INTEGER	NO	0		transfers.transfer_type: 0 recommended, 1 timed, 2 minimum time required, 3 not possible, 4 in-seat transfer, 5 must re-board.
min_transfer_time	INTEGER	YES			minimum number of seconds for the transfer (transfers.min_transfer_time).

(* - Primary key)

The SQL statement for table and index creation is below.

CREATE TABLE IF NOT EXISTS Transit_Transfers(
    transfer_id       INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    agency_id         INTEGER,
    from_stop_id      INTEGER,
    to_stop_id        INTEGER,
    from_route_id     INTEGER,
    to_route_id       INTEGER,
    from_trip_id      INTEGER,
    to_trip_id        INTEGER,
    from_stop          TEXT,
    to_stop            TEXT,
    from_route         TEXT,
    to_route           TEXT,
    from_trip          TEXT,
    to_trip            TEXT,
    transfer_type     INTEGER NOT NULL DEFAULT 0,
    min_transfer_time INTEGER,
    FOREIGN KEY(agency_id) REFERENCES Transit_Agencies(agency_id) deferrable initially deferred,
    FOREIGN KEY(from_stop_id) REFERENCES Transit_Stops(stop_id) deferrable initially deferred,
    FOREIGN KEY(to_stop_id) REFERENCES Transit_Stops(stop_id) deferrable initially deferred,
    FOREIGN KEY(from_route_id) REFERENCES Transit_Routes(route_id) deferrable initially deferred,
    FOREIGN KEY(to_route_id) REFERENCES Transit_Routes(route_id) deferrable initially deferred,
    FOREIGN KEY(from_trip_id) REFERENCES Transit_Trips(trip_id) deferrable initially deferred,
    FOREIGN KEY(to_trip_id) REFERENCES Transit_Trips(trip_id) deferrable initially deferred
);
CREATE INDEX IF NOT EXISTS idx_polaris_transit_transfers_from_stop ON Transit_Transfers (from_stop_id);
CREATE INDEX IF NOT EXISTS idx_polaris_transit_transfers_to_stop ON Transit_Transfers (to_stop_id);
CREATE INDEX IF NOT EXISTS idx_polaris_transit_transfers_type ON Transit_Transfers (transfer_type);