Appending Features to MBTiles Without a Full Rebuild

Part of the Incremental Data Processing guide.

Operative rule: an MBTiles file is just a SQLite table of tile blobs keyed on (zoom_level, tile_column, tile_row) — so when a handful of features change, re-tile only their bounding box and UPSERT the affected rows instead of regenerating the entire store.

How Surgical MBTiles Patching Works

A full tileset rebuild reprocesses every feature at every zoom level, which is wasteful when a nightly refresh changed a few dozen records. The insight that unlocks incremental patching is that the MBTiles format is not an opaque archive — it is an ordinary SQLite database. It contains a tiles table with the columns zoom_level, tile_column, tile_row, and tile_data (a BLOB), plus a metadata table of key/value pairs. Reading and writing individual tiles is a plain SQL operation you can do with Python’s standard sqlite3 module. Nothing about the format requires you to regenerate the whole thing to change one tile.

So the patch strategy is: take the change set from your delta step, compute the union bounding box of the changed features, and for each zoom level translate that box into the range of z/x/y tiles it overlaps. Those are the only tiles whose content could have changed. You then re-tile just that small region — clipping the current features in the box and running them through tippecanoe (or building the tiles directly) — and write each resulting blob back into the store with an INSERT ... ON CONFLICT ... DO UPDATE. Two details make or break correctness. First, MBTiles stores tile_row in TMS order, whose y axis is flipped relative to the XYZ scheme web maps request, so you must convert. Second, this new tileset is a fresh body under the same tile URLs, so the patch is only complete once you invalidate caches — a topic covered by cache invalidation strategies. The change set itself comes from computing GeoJSON feature deltas with geopandas, and the initial full tileset from generating vector tiles from PostGIS with tippecanoe.

Production-Ready Implementation

The routine below opens an existing .mbtiles store, computes the affected tiles for a changed bounding box across a zoom range, re-tiles that region with tippecanoe, and upserts each blob. It applies the TMS y-flip on write and wraps the writes in a single transaction so a failure leaves the store untouched.

# mbtiles_patch.py — patch only the tiles a changed bbox touches.
# Requires: tippecanoe on PATH, Python 3.10+. MBTiles is opened via sqlite3 (stdlib).

from __future__ import annotations

import math
import sqlite3
import subprocess
import tempfile
from pathlib import Path


def deg2tile(lon: float, lat: float, z: int) -> tuple[int, int]:
    """Convert WGS84 lon/lat to XYZ tile coordinates at zoom z."""
    lat_rad = math.radians(lat)
    n = 2 ** z
    x = int((lon + 180.0) / 360.0 * n)
    y = int((1.0 - math.asinh(math.tan(lat_rad)) / math.pi) / 2.0 * n)
    return x, y


def affected_tiles(
    bbox: tuple[float, float, float, float], zooms: range
) -> list[tuple[int, int, int]]:
    """All XYZ tiles overlapping bbox=(min_lon, min_lat, max_lon, max_lat)."""
    min_lon, min_lat, max_lon, max_lat = bbox
    tiles: list[tuple[int, int, int]] = []
    for z in zooms:
        x0, y0 = deg2tile(min_lon, max_lat, z)   # top-left (max lat is smaller y)
        x1, y1 = deg2tile(max_lon, min_lat, z)   # bottom-right
        for x in range(min(x0, x1), max(x0, x1) + 1):
            for y in range(min(y0, y1), max(y0, y1) + 1):
                tiles.append((z, x, y))
    return tiles


def retile_region(region_geojson: Path, min_z: int, max_z: int) -> Path:
    """Build a small MBTiles for just the changed features with tippecanoe."""
    out = Path(tempfile.mkstemp(suffix=".mbtiles")[1])
    out.unlink()  # tippecanoe needs the path free
    subprocess.run(
        [
            "tippecanoe",
            "-o", str(out),
            "-Z", str(min_z), "-z", str(max_z),
            "--layer", "features",
            "--force", "--quiet",
            str(region_geojson),
        ],
        check=True,
    )
    return out


def upsert_tiles(
    target: Path, patch: Path, wanted: set[tuple[int, int, int]]
) -> int:
    """Copy the wanted XYZ tiles from `patch` into `target`, applying the TMS y-flip."""
    written = 0
    with sqlite3.connect(target) as dst, sqlite3.connect(patch) as src:
        dst.execute("PRAGMA journal_mode=WAL")
        for (z, x, y) in wanted:
            tms_y = (2 ** z) - 1 - y                       # XYZ → TMS row flip
            row = src.execute(
                "SELECT tile_data FROM tiles "
                "WHERE zoom_level=? AND tile_column=? AND tile_row=?",
                (z, x, tms_y),
            ).fetchone()
            if row is None:
                continue                                   # no data here anymore
            dst.execute(
                "INSERT INTO tiles (zoom_level, tile_column, tile_row, tile_data) "
                "VALUES (?, ?, ?, ?) "
                "ON CONFLICT(zoom_level, tile_column, tile_row) "
                "DO UPDATE SET tile_data=excluded.tile_data",
                (z, x, tms_y, row[0]),
            )
            written += 1
        dst.commit()
    return written


def patch_mbtiles(
    target: Path,
    region_geojson: Path,
    bbox: tuple[float, float, float, float],
    min_z: int = 6,
    max_z: int = 14,
) -> int:
    """End-to-end: re-tile a bbox region and upsert only its tiles into `target`."""
    wanted = set(affected_tiles(bbox, range(min_z, max_z + 1)))
    patch = retile_region(region_geojson, min_z, max_z)
    try:
        return upsert_tiles(target, patch, wanted)
    finally:
        patch.unlink(missing_ok=True)


if __name__ == "__main__":
    n = patch_mbtiles(
        target=Path("public/incidents.mbtiles"),
        region_geojson=Path("build/changed_region.geojson"),
        bbox=(-74.05, 40.70, -73.95, 40.78),   # lower Manhattan
    )
    print(f"Upserted {n} tiles")

Note the MBTiles schema uses a unique index on (zoom_level, tile_column, tile_row) — the ON CONFLICT clause depends on it. If your store predates that index (some older tilers omit it), add it once with CREATE UNIQUE INDEX tile_index ON tiles (zoom_level, tile_column, tile_row); before patching.

Alternative Variants

Deduplicated stores that use a map + images schema

Some MBTiles files split storage into a map table (coordinates → tile_id) and an images table (tile_id → blob) with a tiles view joining them, to deduplicate identical tiles. Patch both tables:

def upsert_dedup(dst: sqlite3.Connection, z: int, x: int, tms_y: int, blob: bytes, tid: str) -> None:
    """Write into the map/images deduplicated schema instead of a flat tiles table."""
    dst.execute("INSERT OR REPLACE INTO images (tile_id, tile_data) VALUES (?, ?)", (tid, blob))
    dst.execute(
        "INSERT OR REPLACE INTO map (zoom_level, tile_column, tile_row, tile_id) "
        "VALUES (?, ?, ?, ?)",
        (z, x, tms_y, tid),
    )

Full rebuild versus incremental patch

For routine refreshes the patch wins decisively; keep the full rebuild for schema changes or as a periodic reconciliation to catch any drift the incremental path missed.

Verification Steps

• Extract a patched tile with sqlite3 store.mbtiles "SELECT length(tile_data) FROM tiles WHERE zoom_level=14 AND tile_column=? AND tile_row=?" and confirm the blob length changed after the patch.
• Load the store in a MapLibre GL viewer and pan to the changed area at several zooms — the new features must appear, and neighbouring untouched tiles must be unchanged.
• Assert the y-flip is correct: pick one changed feature, compute its XYZ tile, and confirm the affected row in the store is at tile_row = 2**z - 1 - y.
• Diff tile counts before and after; only tiles inside the bbox range should differ.
• After patching, purge or version the affected tile URLs so caches serve the new blobs.

Common Errors & Fixes

Patched features appear in the wrong place vertically

The TMS y-flip was skipped or applied twice. MBTiles rows are TMS (origin bottom-left), while web maps request XYZ (origin top-left). Convert exactly once with tms_y = 2**z - 1 - y on every read and write between the two schemes. If features are mirrored top-to-bottom across the map, this conversion is the cause.

sqlite3.IntegrityError or the upsert silently inserts duplicates

The tiles table has no unique index on (zoom_level, tile_column, tile_row), so ON CONFLICT has no constraint to act on and either errors or appends a second row the renderer ignores. Create the unique index once before patching; from then on upserts replace in place.

Some changed features are missing from patched tiles

The bounding box did not cover the full extent of the change, or the zoom range was too narrow. A feature that grew or moved spans more tiles than its old footprint — compute the bbox from the union of the old and new geometry, and patch across the same zoom range the full tileset uses.

database is locked during the patch

Another process — often the tile server — holds a lock on the store. Enable WAL mode with PRAGMA journal_mode=WAL so readers and the writer coexist, or patch a copy and atomically swap it into place. Never write to the live file while a renderer streams from it without WAL.

Related

• Incremental Data Processing — parent guide covering delta detection and partial rebuilds for geo-dashboards
• Computing GeoJSON Feature Deltas with geopandas — produces the change set and bounding box this patch consumes
• Generating Vector Tiles from PostGIS with Tippecanoe — builds the initial full tileset you later patch in place
• Cache Invalidation Strategies — invalidate the patched tile URLs so viewers fetch the new blobs