Building BuzzOff: An Offline-First Geolocation Alert System with Python and SQLite R-trees

How I Built a Python-Powered, Offline-First Geolocation Alert System

Itay Shmool developed BuzzOff, an offline-first geolocation alert system designed to function within the Faraday cage of a vehicle. The system utilizes a Python data pipeline to ingest OpenStreetMap data via the Overpass API, enabling nationwide camera detection without active network dependencies.

Why This Matters

Mobile data is inherently unreliable in moving vehicles, making network-dependent geolocation apps prone to failure and high latency. By shifting heavy geospatial processing to a backend pipeline and distributing optimized SQLite Country Packs with R-tree spatial indexes, developers can ensure sub-second query performance and battery efficiency on the edge, bypassing the limitations of unreliable cellular infrastructure.

Key Insights

• OpenStreetMap data ingestion via Overpass API allows fetching nationwide camera nodes with a single query (Shmool, 2026).
• Geospatial deduplication using PostGIS merges camera coordinates within a 50-meter threshold to clean raw ingestion data before distribution.
• SQLite R-tree virtual tables enable high-speed 2D bounding box queries for ‘what’s near me’ logic directly on mobile hardware.
• Multi-stage filtering combines broad R-tree spatial queries with precise Haversine distance calculations to optimize mobile battery life.
• FastAPI with Pydantic provides automated validation and serialization for geospatial data schemas including latitude, longitude, and camera type.

Working Examples

Fetching camera data for an entire country using the Overpass API.

import httpx
OVERPASS_QUERY = """
[out:json];
area["ISO3166-1"="IL"][admin_level=2];
(node["highway"="speed_camera"](area););
out body;
"""
def fetch_osm_cameras():
    response = httpx.post("https://overpass-api.de/api/interpreter", data={"data": OVERPASS_QUERY})
    response.raise_for_status()
    return response.json()["elements"]

Generating an optimized SQLite ‘Country Pack’ with an R-tree spatial index.

import sqlite3
def generate_pack(country_code, cameras):
    conn = sqlite3.connect(f"{country_code}.db")
    cursor = conn.cursor()
    cursor.execute("CREATE TABLE cameras (id TEXT PRIMARY KEY, lat REAL, lon REAL, type TEXT)")
    cursor.execute("CREATE VIRTUAL TABLE cameras_rtree USING rtree(id, min_lat, max_lat, min_lon, max_lon)")
    for cam in cameras:
        cursor.execute("INSERT INTO cameras VALUES (?, ?, ?, ?)", (cam['id'], cam['lat'], cam['lon'], cam['type']))
        cursor.execute("INSERT INTO cameras_rtree VALUES (?, ?, ?, ?, ?)", (cam['id'], cam['lat'], cam['lat'], cam['lon'], cam['lon']))
    conn.commit()

High-performance R-tree bounding box query for proximity detection.

SELECT id FROM cameras_rtree WHERE min_lat > ? AND max_lat < ? AND min_lon > ? AND max_lon < ?

Practical Applications

• Use case: BuzzOff uses SQLite R-trees to alert drivers of nearby cameras within a 500-meter radius based on GPS heading. Pitfall: Relying on real-time network requests for geospatial lookups leads to alert failures in areas with poor cellular coverage.
• Use case: The system generates portable .db files for offline ‘Country Packs’ to be downloaded once by the Flutter client. Pitfall: Processing gigabytes of raw, messy geospatial data directly on a mobile device causes excessive resource consumption and poor user experience.

References:

• https://dev.to/itayshmool/how-i-built-a-python-powered-offline-first-geolocation-alert-system-15d
• https://buzzoff.me