flightlog

A portfolio / learning project: a cloud-native geospatial data service that ingests drone telemetry, stores 3D flight paths in PostGIS, and serves them as GeoJSON over a containerised FastAPI. The CI/CD pipeline — running on a self-hosted GitLab CE instance — validates, tests, processes, and renders a flight report on every commit, tying each artifact to the exact code that produced it.

Architecture

Layer	Technology	Detail
Storage	PostGIS 16	geometry(PointZ, 4326) — 3D WGS-84 points with a GIST spatial index
Ingest	Python CLI	Idempotent: INSERT … ON CONFLICT (source_file) DO NOTHING
API	FastAPI + uvicorn	Returns GeoJSON LineStringZ aggregated in SQL via ST_MakeLine
Container	Docker Compose	PostGIS + API services with a DB health-check dependency
CI/CD	Self-hosted GitLab CE	5-stage pipeline: lint → validate → test → process → render
Mirror	GitHub Actions	Mirrors the same checks on the public repo

Quickstart — geospatial service

# 1. Copy credentials (never commit .env — it is gitignored)
cp .env.example .env

# 2. Start PostGIS + API
docker compose -f docker-compose.geo.yml up --build -d

# 3. Ingest the sample flight
DATABASE_URL=postgresql://geo:geopassword@localhost:5432/flightlog \
  python -m geo.ingest data/sample_flight.csv

# 4. Health check
curl http://localhost:8001/health
# {"status":"ok"}

# 5. Query the flight track
curl http://localhost:8001/flights/1/track

Sample response (coordinates truncated):

{
  "type": "Feature",
  "properties": { "flight_id": 1 },
  "geometry": {
    "type": "LineString",
    "coordinates": [
      [172.6362, -43.5321, 0.0],
      [172.636206, -43.5321, 4.0],
      ["..."]
    ]
  }
}

Sample flight track rendered from the GeoJSON API response — lawnmower survey pattern over Christchurch, NZ.

Quickstart — pipeline (non-geo)

python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev,viz]"

ruff check .
pytest
flightlog validate data/sample_flight.csv
flightlog report data/sample_flight.csv --out-dir out/   # writes out/flight-report.png + out/metrics.json

Pipeline

Stage	What it does	Output
lint	Static analysis (ruff)	—
validate	Data-quality gate: schema, value ranges, monotonic time	pass/fail
test	Unit tests + haversine golden check	JUnit XML + coverage
process	Compute flight metrics	metrics.json (artifact)
render	Draw route map + charts	flight-report.png (artifact)

Telemetry format

CSV with columns: time_s, lat, lon, alt_m, battery_v, speed_ms

data/sample_flight.csv is a synthetic but realistic log (a lawnmower survey over Christchurch, NZ). Regenerate it with:

python scripts/make_sample_flight.py > data/sample_flight.csv

GitHub ↔ GitLab

The pipeline runs on a self-hosted GitLab CE instance — that is the skill on display. GitHub is the public showcase. The same code lives in two places via two git remotes:

• origin → GitHub (public portfolio)
• gitlab → local GitLab CE (runs the real .gitlab-ci.yml)

GitHub Actions (.github/workflows/ci.yml) mirrors the checks so the GitHub page also shows passing CI.

Built vs Roadmap

Built

• PostGIS schema with geometry(PointZ, 4326) and a GIST spatial index
• Idempotent ingest CLI (src/geo/ingest.py)
• FastAPI serving GeoJSON LineStringZ — /health and /flights/{id}/track
• Docker Compose stack with health-checked service startup order
• Integration test suite (auto-skipped when PostGIS is unavailable; runs in CI)
• 5-stage GitLab CI/CD pipeline on self-hosted GitLab CE
• GitHub Actions mirror pipeline

Roadmap (planned — not built)

• Kubernetes: deploy the geo service on a local k3s cluster with a Helm chart
• Terraform: provision the k3s node and persistent volumes declaratively
• Prometheus + Grafana: scrape ingest duration, point count, and API latency
• Tile server endpoint (/flights/{id}/tiles/{z}/{x}/{y}) for map rendering
• Streaming ingest for large files via PostgreSQL COPY
• API authentication

Design notes

Why PostGIS? PostGIS gives us first-class spatial types and functions inside an ACID-compliant database. Storing raw lat/lon floats in a plain Postgres table would work for simple distance queries, but the moment you need bounding-box lookups, spatial joins, or format conversions the SQL becomes hand-rolled and fragile. With PostGIS those operations are a single function call (ST_Within, ST_Intersects, ST_AsGeoJSON) and they run against a spatial index rather than a full scan.

Why idempotent ingest? CI pipelines retry failed jobs. A data pipeline that creates duplicate rows on retry is worse than one that fails loudly — you end up with silent data corruption. The ingest script uses INSERT … ON CONFLICT (source_file) DO NOTHING: if the file was already processed, the entire transaction is a no-op. This makes re-running safe without needing a separate deduplication job.

Why build the LineString in SQL? ST_MakeLine(geom ORDER BY time_s) is an aggregate function — it folds all track_points for a flight into a single geometry in one pass, with ordering guaranteed by the database. The alternative (fetching all points into Python, sorting, then constructing a GeoJSON object) costs an extra round-trip, moves sorting responsibility to application code, and requires a geometry library dependency. The SQL approach is faster, shorter, and keeps the spatial logic where the spatial data lives.

Why 3D geometry (PointZ)? Drone telemetry naturally carries altitude. Storing a flat 2D point would either discard it or force it into a separate column outside the spatial type system. PointZ keeps all three dimensions in the geometry, so altitude is available for spatial predicates (e.g. ST_3DDWithin) and the full 3D path is preserved in the LineStringZ returned by the API.

---

License

MIT — see LICENSE.