Mapping Multi-Modal Fare Zones to PostGIS Polygons

The task this page solves is narrow and unforgiving: given a single automated fare collection (AFC) tap — a latitude/longitude, a UTC timestamp, and a mode of travel — resolve it to exactly one fare zone when bus, light-rail, and microtransit zones physically overlap. It is a concrete implementation step inside Fare Zone Taxonomy Design, the stage that turns a raw tap into a billable, auditable event. Get the point-in-polygon resolution wrong and the failure is not cosmetic: a tap that matches two overlapping polygons at once produces duplicate charges, a tap that matches none silently leaks fare, and either outcome surfaces weeks later as an unreconcilable variance a revenue analyst has to chase by hand.

The inputs here arrive already normalized — origin coordinates and UTC-anchored timestamps come out of Smart Card Schema Mapping, and the coordinates themselves are sanity-checked against live vehicle positions from the GTFS-RT Realtime Sync feed. This page owns only the spatial-plus-temporal resolution and hands its output, a single zone_id and fare tier, downstream to Fare Rule Validation & Calculation Engines for final pricing. The whole thing is a deterministic PostGIS query with a precedence rule bolted on.

The Resolution Rule

Fare zones are not mutually exclusive. A downtown block can sit inside a base district polygon, a transfer-corridor polygon, and an off-peak concession overlay simultaneously. The resolver must collapse that set to one winner deterministically, or reconciliation is impossible. Every polygon therefore carries an integer zone_hierarchy; the winning zone is the valid, containing polygon with the highest hierarchy, and ties break toward the smallest (most specific) polygon by geographic area. Formally, for a tap point $p$ at time $t$ on mode $m$:

$$z^{\star} = \operatorname*{arg\,max}_{z \,\in\, Z(p,\,t,\,m)} \bigl(h_z,\; -A_z\bigr)$$

where $Z(p,t,m)$ is the set of zones whose geometry contains $p$, whose validity window covers $t$, and whose modal_scope includes $m$; $h_z$ is zone_hierarchy; and $A_z$ is the polygon’s area in square metres. That single ordering — hierarchy descending, then area ascending — is the entire tie-break contract, and both the Python resolver and the SQL reconciliation view below implement it identically so edge devices and the warehouse never disagree.

Schema and the Precedence Contract

Before any query runs, the spatial schema has to encode both the geometry and the metadata the rule depends on. Every polygon is stored in GEOMETRY(Polygon, 4326), indexed with GIST for point-in-polygon speed, and carries its own validity window plus a Decimal base_fare so no monetary value ever touches floating point. Overlapping polygons are allowed — they are the normal case — but only if zone_hierarchy makes their precedence explicit.

CREATE TABLE IF NOT EXISTS fare_zones (
    zone_id        UUID PRIMARY KEY,
    fare_tier      VARCHAR(10) NOT NULL,
    valid_from     TIMESTAMPTZ NOT NULL,
    valid_to       TIMESTAMPTZ,
    modal_scope    VARCHAR(20) NOT NULL,
    zone_hierarchy INT NOT NULL DEFAULT 0,
    base_fare      NUMERIC(8, 2) NOT NULL,
    geometry       GEOMETRY(Polygon, 4326) NOT NULL,
    created_at     TIMESTAMPTZ DEFAULT NOW(),
    CONSTRAINT chk_valid_dates CHECK (valid_to IS NULL OR valid_to > valid_from)
);

CREATE INDEX IF NOT EXISTS idx_fare_zones_gist  ON fare_zones USING GIST (geometry);
CREATE INDEX IF NOT EXISTS idx_fare_zones_valid ON fare_zones (valid_from, valid_to);

Treat this table as the single source of truth: any deviation during ingestion cascades into reconciliation discrepancies that are expensive to unwind after settlement.

Ingesting and Repairing Zone Geometry

Raw GIS exports from planning departments rarely meet AFC-grade precision. The pipeline below uses geopandas, shapely, and psycopg2 to normalize the CRS to EPSG:4326, repair invalid topology with a single make_valid pass, enforce precedence, and — critically — quarantine anything unrepairable rather than dropping it, so every rejected polygon stays traceable for a transit-ops audit.

The flow below shows how each polygon moves through CRS normalization, topology repair, and precedence resolution, with unrepairable geometries quarantined:

import logging
import uuid
from typing import Tuple

import geopandas as gpd
import pandas as pd
import psycopg2
from psycopg2.extras import execute_values
from shapely.geometry.base import BaseGeometry
from shapely.validation import make_valid

logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s | %(levelname)s | %(message)s",
    datefmt="%Y-%m-%dT%H:%M:%S%z",
)
logger = logging.getLogger("fare_zone.ingestion")


def ingest_and_validate_zones(
    input_path: str,
    conn_string: str,
    quarantine_table: str = "fare_zones_quarantine",
) -> Tuple[int, int]:
    """Ingest, validate, and upsert fare-zone polygons into PostGIS.

    Returns (successful_upserts, quarantined_records).
    """
    try:
        gdf = gpd.read_file(input_path)
        if gdf.empty:
            raise ValueError("Input dataset contains zero records.")

        # Force a single, canonical CRS before any spatial predicate runs.
        gdf = gdf.set_crs("EPSG:4326", allow_override=True).to_crs("EPSG:4326")

        valid_mask = gdf.geometry.apply(
            lambda g: isinstance(g, BaseGeometry) and g.is_valid
        )
        gdf_valid = gdf[valid_mask].copy()
        gdf_invalid = gdf[~valid_mask].copy()

        # One deterministic auto-repair pass; anything still broken is quarantined.
        if not gdf_invalid.empty:
            gdf_invalid["geometry"] = gdf_invalid["geometry"].apply(make_valid)
            repaired = gdf_invalid.geometry.apply(
                lambda g: isinstance(g, BaseGeometry) and g.is_valid and not g.is_empty
            )
            gdf_valid = pd.concat([gdf_valid, gdf_invalid[repaired]])
            final_invalid = gdf_invalid[~repaired]
        else:
            final_invalid = gdf_invalid

        # Precedence: keep the highest-hierarchy definition per zone_id.
        gdf_valid = gdf_valid.sort_values("zone_hierarchy", ascending=False)
        gdf_valid = gdf_valid.drop_duplicates(subset=["zone_id"], keep="first")

        with psycopg2.connect(conn_string) as conn:
            conn.autocommit = False
            with conn.cursor() as cur:
                records = [
                    (
                        str(row.get("zone_id", uuid.uuid4())),
                        row["fare_tier"],
                        row["valid_from"],
                        row.get("valid_to"),
                        row["modal_scope"],
                        int(row.get("zone_hierarchy", 0)),
                        str(row["base_fare"]),  # str() keeps NUMERIC exact
                        row["geometry"].wkt,
                    )
                    for _, row in gdf_valid.iterrows()
                ]
                execute_values(
                    cur,
                    """
                    INSERT INTO fare_zones
                        (zone_id, fare_tier, valid_from, valid_to,
                         modal_scope, zone_hierarchy, base_fare, geometry)
                    VALUES %s
                    ON CONFLICT (zone_id) DO UPDATE SET
                        fare_tier      = EXCLUDED.fare_tier,
                        valid_from     = EXCLUDED.valid_from,
                        valid_to       = EXCLUDED.valid_to,
                        modal_scope    = EXCLUDED.modal_scope,
                        zone_hierarchy = EXCLUDED.zone_hierarchy,
                        base_fare      = EXCLUDED.base_fare,
                        geometry       = EXCLUDED.geometry;
                    """,
                    records,
                )

                if not final_invalid.empty:
                    cur.execute(
                        f"""
                        CREATE TABLE IF NOT EXISTS {quarantine_table} (
                            id             SERIAL PRIMARY KEY,
                            original_id    TEXT,
                            error_reason   TEXT,
                            geometry_wkt   TEXT,
                            quarantined_at TIMESTAMPTZ DEFAULT NOW()
                        );
                        """
                    )
                    execute_values(
                        cur,
                        f"INSERT INTO {quarantine_table} "
                        "(original_id, error_reason, geometry_wkt) VALUES %s",
                        [
                            (str(r.get("zone_id", "UNKNOWN")),
                             "Invalid topology after repair", r["geometry"].wkt)
                            for _, r in final_invalid.iterrows()
                        ],
                    )

                conn.commit()
        logger.info(
            "Ingestion complete. Upserted=%d quarantined=%d",
            len(records), len(final_invalid),
        )
        return len(records), len(final_invalid)
    except (psycopg2.Error, ValueError) as exc:
        logger.error("Zone ingestion failed: %s", exc)
        raise

Resolving a Tap to Its Fare Zone

With clean polygons in place, the per-tap resolution is a single indexed query that encodes the rule exactly: filter to the tap’s mode, intersect the point, apply the validity window, and order by precedence to take the one winner. The function returns a typed ResolvedZone carrying a Decimal fare, or raises when no zone matches so the caller can route the tap to an auditable unassigned bucket instead of guessing.

import logging
from dataclasses import dataclass
from datetime import datetime, timezone
from decimal import Decimal

import psycopg2
from psycopg2.extras import RealDictCursor

logger = logging.getLogger("fare_zone.resolver")


class ZoneResolutionError(Exception):
    """Raised when a tap cannot be resolved to a deterministic fare zone."""


@dataclass(frozen=True)
class ResolvedZone:
    zone_id: str
    fare_tier: str
    zone_hierarchy: int
    base_fare: Decimal


# Precedence lives entirely in the ORDER BY: highest hierarchy first, then the
# smallest (most specific) polygon. LIMIT 1 guarantees a single deterministic row.
_RESOLVE_SQL = """
    SELECT zone_id, fare_tier, zone_hierarchy, base_fare
    FROM fare_zones
    WHERE modal_scope = %(mode)s
      AND %(ts)s >= valid_from
      AND (valid_to IS NULL OR %(ts)s < valid_to)
      AND ST_Contains(geometry, ST_SetSRID(ST_MakePoint(%(lon)s, %(lat)s), 4326))
    ORDER BY zone_hierarchy DESC, ST_Area(geometry::geography) ASC
    LIMIT 1;
"""


def resolve_tap_zone(
    conn: "psycopg2.extensions.connection",
    lat: float,
    lon: float,
    tap_ts: datetime,
    mode: str,
) -> ResolvedZone:
    """Resolve one AFC tap to exactly one fare zone, or raise."""
    if tap_ts.tzinfo is None:
        raise ZoneResolutionError("tap_ts must be timezone-aware (UTC)")
    tap_ts = tap_ts.astimezone(timezone.utc)

    try:
        with conn.cursor(cursor_factory=RealDictCursor) as cur:
            cur.execute(
                _RESOLVE_SQL,
                {"mode": mode, "ts": tap_ts, "lon": lon, "lat": lat},
            )
            row = cur.fetchone()
    except psycopg2.Error as exc:
        logger.error("Spatial resolution query failed: %s", exc)
        raise ZoneResolutionError("PostGIS query failed") from exc

    if row is None:
        logger.warning(
            "No zone matched lat=%s lon=%s mode=%s ts=%s", lat, lon, mode, tap_ts
        )
        raise ZoneResolutionError(f"No fare zone contains ({lat}, {lon}) for {mode}")

    return ResolvedZone(
        zone_id=str(row["zone_id"]),
        fare_tier=row["fare_tier"],
        zone_hierarchy=row["zone_hierarchy"],
        base_fare=Decimal(str(row["base_fare"])),  # str() preserves NUMERIC precision
    )

Validation and Test Cases

Exercise the resolver against three fixtures that cover the normal path, the overlap that makes precedence matter, and the miss that must fail loudly. Assume three seeded polygons for mode='bus': a base district (Z1, hierarchy 0, fare 2.90), a concession overlay covering the same downtown block (ZC, hierarchy 10, fare 1.45), and no polygon at all over the river.

import pytest

def test_single_zone(conn):
    zone = resolve_tap_zone(conn, 40.760, -73.980, tap_ts_utc, "bus")
    assert zone.zone_id == "Z1"
    assert zone.base_fare == Decimal("2.90")

def test_overlap_precedence(conn):
    # Point sits inside BOTH Z1 (h=0) and ZC (h=10); the overlay must win.
    zone = resolve_tap_zone(conn, 40.748, -73.985, tap_ts_utc, "bus")
    assert zone.zone_id == "ZC"
    assert zone.zone_hierarchy == 10

def test_unmatched_tap_raises(conn):
    with pytest.raises(ZoneResolutionError):
        resolve_tap_zone(conn, 40.700, -74.020, tap_ts_utc, "bus")

The overlap case is the one that actually protects revenue: without the ORDER BY, that tap would non-deterministically match either polygon and charge 2.90 or 1.45 at random. A naive-timezone tap_ts is also an error case — the resolver rejects it up front rather than letting an implicit local time drift a tap across a validity boundary at midnight.

Spatial Debugging and Edge-Case Resolution

Multi-modal networks routinely produce coordinate drift, sliver polygons, and boundary misalignments that break resolution before precedence ever runs. Handle these before deploying:

1. Sliver detection and removal. Microtransit catchment areas often generate sub-metre artefacts that trigger false containment. Because geometry is stored in EPSG:4326 (degrees), cast to geography for a metric area and reject anything below a threshold — filter on ST_Area(geometry::geography) > 100 (square metres) during ingestion validation.
2. Boundary-tap ambiguity. A rider tapping exactly on a shared edge can be contained by two adjacent polygons at once. The zone_hierarchy-then-area ordering resolves this deterministically; where you need to exclude the boundary entirely, swap ST_Contains for ST_ContainsProperly.
3. Coordinate-system drift. Every AFC GPS log must be transformed to EPSG:4326 before it reaches the resolver. Mismatched projections cause systematic fare undercharging, which is why the parent Core Architecture & Fare Taxonomy mandates strict CRS alignment across ingestion, validation, and reconciliation layers.

Run this diagnostic weekly to flag overlapping polygons whose fare tiers conflict — the pairs most likely to produce disputed charges:

-- Overlapping zones with conflicting fare tiers, ranked by overlap area.
SELECT
    a.zone_id AS zone_a,
    b.zone_id AS zone_b,
    a.fare_tier AS tier_a,
    b.fare_tier AS tier_b,
    ST_Area(ST_Intersection(a.geometry, b.geometry)::geography) AS overlap_sqm
FROM fare_zones a
JOIN fare_zones b ON a.zone_id < b.zone_id
WHERE ST_Intersects(a.geometry, b.geometry)
  AND a.fare_tier <> b.fare_tier
  AND a.modal_scope = b.modal_scope
  AND ST_Area(ST_Intersection(a.geometry, b.geometry)::geography) > 50
ORDER BY overlap_sqm DESC;

Reconciliation Join

To attribute a whole day of taps at once, push the same precedence rule into a LATERAL join so every tap resolves to its one winning zone in bulk — the set-based twin of the per-tap resolver. Applying the temporal filter inside the join keeps historical taps priced against the zone definition that was live when the rider actually tapped.

CREATE OR REPLACE VIEW v_fare_reconciliation AS
SELECT
    e.tap_id,
    e.device_id,
    e.tap_timestamp,
    e.latitude,
    e.longitude,
    z.zone_id,
    z.fare_tier,
    z.base_fare
FROM afc_events e
LEFT JOIN LATERAL (
    SELECT fz.zone_id, fz.fare_tier, fz.base_fare
    FROM fare_zones fz
    WHERE fz.modal_scope = e.modal_scope
      AND e.tap_timestamp >= fz.valid_from
      AND (fz.valid_to IS NULL OR e.tap_timestamp < fz.valid_to)
      AND ST_Contains(
              fz.geometry,
              ST_SetSRID(ST_MakePoint(e.longitude, e.latitude), 4326))
    ORDER BY fz.zone_hierarchy DESC, ST_Area(fz.geometry::geography) ASC
    LIMIT 1
) z ON TRUE;

The entity relationship below shows how AFC tap events bind to versioned fare zones through the spatial-plus-temporal join that drives reconciliation:

The GIST index keeps this join sub-second across millions of daily taps; reference the official PostGIS ST_MakeValid documentation when debugging pathological multi-modal intersections.

Integration

This resolution step is the geometric core of Fare Zone Taxonomy Design: it consumes the versioned GeoJSON that stage defines and emits the single zone tag every downstream pricing decision keys off. Its output feeds directly into the pricing rules in Fare Rule Validation & Calculation Engines, where a zone-tagged tap is combined with time-of-day and rider profile — including the transfer-eligibility windows worked out in Calculating Cross-Operator Transfer Windows with Python. A tap that this stage sends to the unassigned bucket must never be silently priced; it is held for spatial audit and re-resolved once the zone geometry or GPS fix is corrected.

FAQ

Related

• Fare Zone Taxonomy Design→
• Smart Card Schema Mapping→
• GTFS-RT Realtime Sync→
• Calculating Cross-Operator Transfer Windows with Python→
• Fare Rule Validation & Calculation Engines→

↑ Part of Fare Zone Taxonomy Design