A1 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](https://github.com/openTdataCH/stop_sync_osm_atlas/blob/main/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).

Related pages

Data update in progress
Elapsed: -- ETA: -- Phase: idle