# 06 - RouteTrack Pi — Route Processing & Summary Generation

#### **Date:** December 25, 2025  
**Category:** Raspberry Pi / GPS / Data Processing  
**Backlink:** [RouteTrack Pi — GPS Logging & Data Ingestion](https://docs.natenetworks.com/books/06-raspberry-pi-python-linux-tips/page/04-routetrack-pi-gps-data-logging-service)

---

## Project Goal

This phase transforms RouteTrack from a **raw GPS logger** into a **route intelligence system**.

Instead of calculating metrics at ingestion time, RouteTrack uses a **post-processing model**:

- Raw GPS points are logged continuously
- Route intelligence is calculated later
- Derived data can be safely regenerated if logic changes

This mirrors how professional telemetry and fleet-tracking systems are built.

---

## High-Level Architecture

<table id="bkmrk-layer-responsibility"><thead><tr><th>Layer</th><th>Responsibility</th></tr></thead><tbody><tr><td>GPS Logger</td><td>Writes raw telemetry (`gps_points`)</td></tr><tr><td>Route Processor</td><td>Computes stops, mileage, summaries</td></tr><tr><td>SQLite</td><td>Stores raw + derived data</td></tr><tr><td>Dashboard (next phase)</td><td>Reads only processed tables</td></tr></tbody></table>

Only **one component writes raw data**.  
All intelligence is derived afterward.

---

## Unified Database Schema

All RouteTrack data structures are defined in a **single schema file**:

```bash
/opt/routetrack/config/schema.sql
```

This file defines:

- Raw GPS telemetry
- Derived stop events
- Aggregated daily summaries

---

### SQLite Schema (with WAL enabled)

```sql
-- ============================================================
-- RouteTrack SQLite Schema
-- ============================================================

PRAGMA journal_mode=WAL;

-- ============================================================
-- TABLE: gps_points (RAW TELEMETRY)
-- ============================================================
CREATE TABLE IF NOT EXISTS gps_points (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  ts TEXT NOT NULL,
  lat REAL,
  lon REAL,
  speed REAL,
  track REAL,
  alt REAL,
  mode INTEGER,
  epx REAL,
  epy REAL,
  eps REAL
);

CREATE INDEX IF NOT EXISTS idx_gps_points_ts
  ON gps_points(ts);

-- ============================================================
-- TABLE: stop_events (DERIVED)
-- ============================================================
CREATE TABLE IF NOT EXISTS stop_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  start_ts TEXT NOT NULL,
  end_ts TEXT NOT NULL,
  duration_seconds INTEGER NOT NULL,
  lat REAL,
  lon REAL
);

CREATE INDEX IF NOT EXISTS idx_stop_events_start_ts
  ON stop_events(start_ts);

-- ============================================================
-- TABLE: daily_summary (AGGREGATED)
-- ============================================================
CREATE TABLE IF NOT EXISTS daily_summary (
  date TEXT PRIMARY KEY,
  start_ts TEXT,
  end_ts TEXT,
  total_distance_miles REAL,
  moving_time_seconds INTEGER,
  stopped_time_seconds INTEGER,
  stop_count INTEGER
);

```

---

## Schema Design Breakdown

### `gps_points` — Source of Truth

- Stores raw TPV messages from `gpsd`
- Append-only
- Never modified or recalculated
- All other tables derive from this data

---

### `stop_events` — Route Intelligence

- Represents **continuous stationary periods**
- Derived using:
    
    
    - Speed threshold
    - Minimum dwell time
- Used for:
    
    
    - Time-on-site tracking
    - Map stop markers
    - Shift analysis

---

### `daily_summary` — Fast Reporting

- One row per calendar day
- Stores:
    
    
    - Start / end timestamps
    - Total mileage
    - Moving vs stopped time
    - Stop count
- Prevents rescanning raw GPS points for dashboards

---

## Applying the Schema (Important!)

SQLite requires an **exclusive lock** for schema changes.

### Safe Workflow

```bash
sudo systemctl stop routetrack-logger.service
sqlite3 /opt/routetrack/data/routetrack.sqlite < /opt/routetrack/config/schema.sql
sqlite3 /opt/routetrack/data/routetrack.sqlite ".tables"
sudo systemctl start routetrack-logger.service
```

Expected tables:

```
gps_points
stop_events
daily_summary
```

---

## Route Processing Script

The route processor converts raw GPS points into usable metrics.

### Responsibilities

- Filter invalid fixes (`mode != 3`)
- Ignore GPS drift
- Calculate distance (Haversine)
- Track moving vs stopped time
- Detect stop events
- Populate `stop_events` and `daily_summary`

---

## Route Processor Script (Final Version Used)

**File:**

```bash
/opt/routetrack/bin/routetrack-process.py
```

```python
#!/usr/bin/env python3
"""
RouteTrack Route Processor (Daily)
"""

import math
import sqlite3
import sys
from datetime import datetime, date, timezone

DB_PATH = "/opt/routetrack/data/routetrack.sqlite"

# 5 mph ≈ 2.235 m/s
MOVEMENT_THRESHOLD_MPS = 2.235
STOP_DWELL_SECONDS = 120
EARTH_RADIUS_KM = 6371.0

def haversine_meters(lat1, lon1, lat2, lon2):
    phi1, phi2 = math.radians(lat1), math.radians(lat2)
    dphi = math.radians(lat2 - lat1)
    dlambda = math.radians(lon2 - lon1)

    a = (
        math.sin(dphi / 2) ** 2 +
        math.cos(phi1) * math.cos(phi2) *
        math.sin(dlambda / 2) ** 2
    )
    return 2 * EARTH_RADIUS_KM * 1000 * math.atan2(
        math.sqrt(a), math.sqrt(1 - a)
    )

def parse_ts(ts):
    return datetime.fromisoformat(ts.replace("Z", "+00:00"))

def main():
    day = sys.argv[1] if len(sys.argv) == 2 else date.today().isoformat()

    start = f"{day}T00:00:00Z"
    end = f"{day}T23:59:59Z"

    conn = sqlite3.connect(DB_PATH)
    cur = conn.cursor()

    cur.execute("""
        SELECT ts, lat, lon, speed, mode
        FROM gps_points
        WHERE ts >= ? AND ts <= ?
        ORDER BY ts
    """, (start, end))

    rows = cur.fetchall()
    if not rows:
        print("No GPS data for this date.")
        return

    cur.execute("DELETE FROM stop_events WHERE start_ts >= ? AND start_ts <= ?", (start, end))
    cur.execute("DELETE FROM daily_summary WHERE date = ?", (day,))

    total_dist = 0
    moving = 0
    stopped = 0
    stops = []

    last = None
    stop_start = None

    for ts, lat, lon, speed, mode in rows:
        if mode != 3 or lat is None or lon is None:
            continue

        now = parse_ts(ts)

        if last:
            prev_t, prev_lat, prev_lon = last
            dt = (now - prev_t).total_seconds()

            if speed and speed >= MOVEMENT_THRESHOLD_MPS:
                total_dist += haversine_meters(prev_lat, prev_lon, lat, lon)
                moving += int(dt)

                if stop_start:
                    dur = int((now - stop_start[0]).total_seconds())
                    if dur >= STOP_DWELL_SECONDS:
                        stops.append((stop_start[0], now, dur, stop_start[1], stop_start[2]))
                        stopped += dur
                    stop_start = None
            else:
                if not stop_start:
                    stop_start = (now, lat, lon)

        last = (now, lat, lon)

    for s in stops:
        cur.execute("""
            INSERT INTO stop_events
            (start_ts, end_ts, duration_seconds, lat, lon)
            VALUES (?, ?, ?, ?, ?)
        """, (s[0].isoformat(), s[1].isoformat(), s[2], s[3], s[4]))

    miles = total_dist * 0.000621371

    cur.execute("""
        INSERT INTO daily_summary
        (date, start_ts, end_ts, total_distance_miles,
         moving_time_seconds, stopped_time_seconds, stop_count)
        VALUES (?, ?, ?, ?, ?, ?, ?)
    """, (day, rows[0][0], rows[-1][0], round(miles, 2), moving, stopped, len(stops)))

    conn.commit()
    conn.close()

    print(f"Processed {day}: miles={round(miles,2)} stops={len(stops)}")

if __name__ == "__main__":
    main()

```

Make executable:

```bash
sudo chmod +x /opt/routetrack/bin/routetrack-process.py
```

---

## Running the Processor (Safe Method)

Because SQLite needs exclusive access for deletes/inserts:

```bash
sudo systemctl stop routetrack-logger.service
/opt/routetrack/venv/bin/python /opt/routetrack/bin/routetrack-process.py
sudo systemctl start routetrack-logger.service
```

---

## Verification Queries

### Daily Summary

```bash
sqlite3 /opt/routetrack/data/routetrack.sqlite \
"SELECT * FROM daily_summary ORDER BY date DESC LIMIT 1;"
```

### Stop Events

```bash
sqlite3 /opt/routetrack/data/routetrack.sqlite \
"SELECT start_ts,end_ts,duration_seconds FROM stop_events ORDER BY id DESC LIMIT 5;"
```

---

## Real-World Validation (Stationary Test)

With the GPS unit **not moving at all**:

<table id="bkmrk-metric-result-distan"><thead><tr><th>Metric</th><th>Result</th></tr></thead><tbody><tr><td>Distance</td><td>0.0 miles</td></tr><tr><td>Moving time</td><td>0 seconds</td></tr><tr><td>Stops</td><td>1</td></tr><tr><td>Stopped time</td><td>Entire duration</td></tr></tbody></table>

This confirmed:

- GPS drift was eliminated
- Movement detection is stable
- Stop logic behaves correctly

---

## Why This Matters

This phase turns RouteTrack into a **true telemetry system**:

- Accurate mileage
- Reliable stop detection
- Regenerable summaries
- Dashboard-ready data model

The UI is now just a **viewer**, not a calculator.

---

## Next Steps

The next phase will focus on:

1. Automating route processing (systemd timer)
2. Local Flask API for data access
3. Leaflet map dashboard for:
    
    
    - Routes
    - Stops
    - Daily summaries