Review application A.1

Database & bundle import

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

erDiagram atlas_operators o|--o{ atlas_stops : atlas_business_org_abbr gtfs_stops_raw ||--o| gtfs_stop_identity_resolution : stop_id atlas_stops o|--o{ gtfs_stop_identity_resolution : resolved_sloid osm_nodes ||--o{ osm_stops : representative_node_id osm_stops ||--o{ osm_stop_members : osm_stop_id osm_nodes ||--o| osm_stop_members : node_id stops_matched o|--o{ problems : stop_id gtfs_line_families o|--o{ line_families : gtfs_line_id osm_route_relations o|--o{ line_families : representative_relation_id line_families ||--o{ itineraries : line_family_id itineraries ||--o{ stop_calls : itinerary_id atlas_stops o|--o{ stop_calls : source_sloid osm_nodes o|--o{ stop_calls : source_node_id line_families ||--o{ line_family_matches : gtfs_line_family_id line_families ||--o{ line_family_matches : osm_line_family_id line_family_matches o|--o{ itinerary_matches : line_family_match_id itineraries ||--o{ itinerary_matches : gtfs_itinerary_id itineraries ||--o{ itinerary_matches : osm_itinerary_id itineraries ||--o| itinerary_best_overlaps : itinerary_id itineraries o|--o{ itinerary_best_overlaps : best_itinerary_id

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).