Review application A.1
Database & bundle import
On this page
The review application keeps everything it shows in one PostgreSQL database with PostGIS, import_db: stops and their matches, GTFS identities, routes and problems. This chapter describes the tables. How a result bundle gets into them is explained in A1.1 Bundle import and publication.
The tables hold a snapshot: the projection of one bundle. Each import validates a new bundle, loads it into a private staging schema and then replaces all snapshot tables in one transaction, so the database never mixes two runs. dataset_publication records the manifest of the published run and changes in the same transaction.
Alembic migrations (migrations/) own the table structure; backend/importing/ writes the rows; the engine never connects to this database. Table and column names such as atlas_stops and sloid come from the Swiss deployment; for another dataset they hold its namespaced source records (A3).
Tables
Stops and identities
| Table | One row per |
|---|---|
stops_matched |
Map entry: a matched pair, an effectively matched two-sided stop middle, an unmatched ATLAS platform, an unmatched OSM element or a disused OSM element (stop_type: matched, effectively_matched, atlas_unmatched, osm_unmatched, osm_disused), with its match type, both positions, distance and a display point (geom, spatially indexed) |
atlas_stops |
ATLAS platform, with its name, platform code, operator and duplicate group |
atlas_operators |
Operator of ATLAS platforms, by abbreviation |
osm_nodes |
OSM element, with its projected tags, every tag (osm_tags) and, for a disused element, its lifecycle prefix (osm_lifecycle, such as disused) |
osm_stops, osm_stop_members |
OSM stop unit (single, pair or two-sided stop), and which elements belong to it with which role; disused elements belong to none |
gtfs_stops_raw |
GTFS stop of the feed; uic_number is empty for feeds without UIC station numbers |
gtfs_stop_identity_resolution |
Resolution of a GTFS stop_id to a SLOID (E1.2) |
problems |
Problem attached to a stops_matched row, with type and priority |
osm_nodes.osm_tags (JSONB) is a deferred column (db.deferred in backend/models.py): the map and list queries that join osm_nodes never fetch or decode it. Only single-stop endpoints read it (A2.1).
An absent JSONB value is SQL NULL, never the JSON value null: the models declare JSONB(none_as_null=True), and the COPY import writes None as NULL. IS NULL and IS NOT NULL therefore select the rows without and with a value, for example the ATLAS platforms in a duplicate group (duplicate_group_sloids IS NOT NULL).
Routes
| Table | One row per |
|---|---|
gtfs_line_families, osm_route_relations |
Raw GTFS route and OSM route relation; the only raw route tables kept |
line_families, itineraries, stop_calls |
The normalized route model (E3): line, direction or variant, ordered stop |
line_family_matches, itinerary_matches |
Engine route matches |
itinerary_best_overlaps |
Comparable itinerary, with its best counterpart of the other source |
itinerary_best_overlaps is a review aid that the importer derives from the bundle's route rows, not an engine result; A2.6 Variant stop overlap explains what it stores and how it is computed.
Relationships
The diagram shows the database foreign keys. o| on the parent side marks a nullable key. Unique constraints limit each GTFS stop to one resolution row and each OSM element to one stop unit. stops_matched refers to atlas_stops and osm_nodes through sloid and osm_node_id without database foreign keys; those joins exist only in the ORM, so the diagram omits them.
What lives outside the snapshot
dataset_publication is not replaced by an import. Runtime state, such as pipeline status and export tasks, is kept in files, not in the database (A3).