4. Database

The project uses a PostgreSQL database named import_db for reproducibly imported stops, routes, problems, and supporting metadata.

The database is designed to be fully rebuilt from source files on every import run. That keeps the web application aligned with the latest pipeline output and avoids drift between intermediate CSVs and the live UI.

High-Level Architecture

We use PostgreSQL with PostGIS for spatial queries. Schema changes are managed through Alembic migrations in migrations/versions/.

Configuration

The import pipeline configures the database connection in matching_and_import_db/database/session.py:

DATABASE_URI = os.getenv('DATABASE_URI', '...')
engine = create_engine(DATABASE_URI)

The Flask application initializes the same database in backend/app.py, so the importer and web app operate on the same schema.

Docker Setup

The PostgreSQL container initializes the database on first boot. Import runs then migrate the schema to HEAD and repopulate the import tables from scratch.

Schema Overview

The stops_matched table is the central table for map rendering. It provides a single row per visible stop marker and links out to ATLAS details, OSM details, route membership, and derived problems.

Why Some Relationships Use Logical Joins

The StopsMatched ORM model defines its relationships to AtlasStop and OsmNode using explicit SQLAlchemy join conditions rather than database-level foreign keys:

atlas_stop_details = db.relationship(
    'AtlasStop',
    primaryjoin='StopsMatched.sloid == AtlasStop.sloid',
    foreign_keys='AtlasStop.sloid',
    uselist=False,
)

That choice is intentional for the central stop-linking layer because:

1. Unmatched ATLAS rows have osm_node_id = NULL, and unmatched OSM rows have sloid = NULL.
2. The importer can bulk-load the stop tables independently before higher-level relationships are consumed by the web layer.
3. The application can treat sloid and osm_node_id as natural linkage keys without introducing an extra stop-link table.

This does not mean the schema avoids foreign keys entirely. Other tables, such as problems, osm_stop_members, osm_route_tags, and route membership tables, do use database-level FKs where cascade behavior is desirable and the parent entity is canonical.

Table Reference

Table: stops_matched

The central table representing every map marker: matched pairs, unmatched ATLAS stops, and unmatched OSM nodes.

Table: atlas_stops

ATLAS-specific attributes keyed by sloid.

Table: osm_nodes

OSM-specific stop attributes keyed by osm_node_id.

osm_nodes stores node-level stop attributes and duplicate metadata only. OSM ways that behave like stops are imported as pseudo-nodes and can be identified by the way_ prefix in osm_node_id.

Table: osm_stops

Canonical OSM stop-unit table used for counting and filtering semantics.

Table: osm_stop_members

Membership rows mapping raw OSM nodes to canonical stop units.

Table: problems

Detected stop-level issues linked to stops_matched.

Table: atlas_routes

Canonical ATLAS route entities imported from GTFS-derived CSVs.

Table: atlas_route_directions

Per-route direction summaries for ATLAS routes.

Table: osm_routes

Canonical OSM route relations.

Table: osm_route_tags

Key-value tags preserved for each imported OSM route relation.

Table: route_atlas_stops

Ordered mapping of ATLAS stops to ATLAS routes.

Table: route_osm_stops

Ordered mapping of OSM stop members to OSM route relations.

Table: routes_matched

Cross-dataset route matches between ATLAS routes and OSM route relations.

Table: route_problems

Route-level problems derived after route import and route matching.

Indexes

We maintain several indexes to support the web app's query patterns.

Spatial Index

CREATE INDEX idx_stops_geom_gist ON stops_matched USING GIST (geom);

This supports fast viewport queries such as /api/data bounding-box requests.

Stop and Problem Indexes

OSM Stop-Unit Indexes

Route Indexes

Related Documentation

• 4.1 Import Process — How matching_and_import_db/database/importer.py rebuilds and repopulates the import database