Designing a Star Schema for OSM Features Jump to heading#

-- A star schema for OSM features. Written for DuckDB; the shape transfers.

CREATE TABLE dim_class (
    class_key      INTEGER PRIMARY KEY,
    class          VARCHAR NOT NULL,      -- 'transportation'
    subclass       VARCHAR NOT NULL,      -- 'road'
    detail         VARCHAR NOT NULL,      -- 'residential'
    primary_key_tag VARCHAR NOT NULL,     -- 'highway'
    primary_value  VARCHAR NOT NULL       -- 'residential'
);

CREATE TABLE dim_area (
    area_key       INTEGER PRIMARY KEY,
    osm_relation_id BIGINT,
    name           VARCHAR NOT NULL,
    admin_level    SMALLINT NOT NULL,
    parent_area_key INTEGER,              -- the containing area, or NULL
    country_code   VARCHAR(2) NOT NULL
);

CREATE TABLE dim_snapshot (
    snapshot_key   INTEGER PRIMARY KEY,
    extract_date   DATE NOT NULL,
    source_digest  VARCHAR NOT NULL,      -- ties back to provenance
    sequence_number BIGINT
);

CREATE TABLE fact_feature (
    feature_key    VARCHAR PRIMARY KEY,   -- the surrogate, never an OSM id
    snapshot_key   INTEGER NOT NULL REFERENCES dim_snapshot,
    class_key      INTEGER NOT NULL REFERENCES dim_class,
    area_key       INTEGER REFERENCES dim_area,   -- smallest containing area
    osm_type       VARCHAR NOT NULL,      -- kept for traceability, not as a key
    osm_id         BIGINT NOT NULL,
    osm_version    INTEGER NOT NULL,
    -- Promoted attributes: present on most features, filtered on constantly.
    name           VARCHAR,
    addr_street    VARCHAR,
    addr_housenumber VARCHAR,
    -- Geometric measures.
    geom           GEOMETRY NOT NULL,
    area_m2        DOUBLE,                -- NULL for lines and points
    length_m       DOUBLE,                -- NULL for points and areas
    -- Everything else, without exception. No tag is ever dropped.
    tags           MAP(VARCHAR, VARCHAR) NOT NULL
);

-- Partition and cluster for the filters analysts actually apply.
CREATE INDEX idx_feature_area  ON fact_feature (area_key, class_key);
CREATE INDEX idx_feature_geom  ON fact_feature USING RTREE (geom);

from __future__ import annotations

import logging

import duckdb

logging.basicConfig(level=logging.INFO, format="%(levelname)s: %(message)s")
logger = logging.getLogger("osm.warehouse.star")

# Primary tag -> (class, subclass). Detail is the tag value itself.
CLASS_MAP: dict[str, tuple[str, str]] = {
    "highway": ("transportation", "road"),
    "railway": ("transportation", "rail"),
    "building": ("built", "building"),
    "landuse": ("land", "landuse"),
    "natural": ("land", "natural"),
    "amenity": ("poi", "amenity"),
    "shop": ("poi", "shop"),
    "waterway": ("water", "waterway"),
}
# Checked in order: the first matching key decides the class.
PRIORITY = ("highway", "railway", "waterway", "building", "amenity", "shop",
            "landuse", "natural")


def classify(tags: dict[str, str]) -> tuple[str, str, str, str, str] | None:
    """Resolve a feature's primary tag into the class hierarchy."""
    for key in PRIORITY:
        value = tags.get(key)
        if not value:
            continue
        klass, subclass = CLASS_MAP[key]
        return (klass, subclass, value, key, value)
    return None      # no primary tag: the feature is not a thing we model


def assign_area(conn: duckdb.DuckDBPyConnection) -> None:
    """Precompute the smallest containing area, once per load.

    Doing this here rather than per query is the single largest performance
    decision in the schema: a point-in-polygon join against a country's
    boundaries costs orders of magnitude more than a foreign key lookup.
    """
    conn.execute("""
        UPDATE fact_feature f
        SET area_key = (
            SELECT a.area_key
            FROM dim_area a
            JOIN area_geometry g ON g.area_key = a.area_key
            WHERE ST_Contains(g.geom, ST_Centroid(f.geom))
            ORDER BY a.admin_level DESC        -- smallest containing area
            LIMIT 1
        )
        WHERE f.area_key IS NULL
    """)
    missing, = conn.execute(
        "SELECT COUNT(*) FROM fact_feature WHERE area_key IS NULL").fetchone()
    if missing:
        # Features outside every mapped boundary: coastal, offshore, or a gap.
        logger.warning("%d feature(s) fall outside every area", missing)


def null_rate_report(conn: duckdb.DuckDBPyConnection) -> None:
    """Which promoted columns are earning their place?"""
    for column in ("name", "addr_street", "addr_housenumber"):
        rate, = conn.execute(
            f"SELECT 1.0 - COUNT({column}) * 1.0 / COUNT(*) FROM fact_feature"
        ).fetchone()
        verdict = "consider demoting" if rate > 0.8 else "keep"
        logger.info("%-18s %5.1f%% null — %s", column, rate * 100, verdict)


if __name__ == "__main__":
    logger.info("classify, load, assign areas, then review the null rates")