Earthquake Pipeline

A production-style batch ETL pipeline that ingests live global earthquake data from the USGS API every hour, stores it in a partitioned PostgreSQL database, and serves an interactive heatmap — all running locally with a single command.

---

Live map preview

Once running, open http://localhost:8050

The map shows all events from the past 30 days, magnitude-weighted, with a toggle between heatmap and individual point layers. The stats panel updates automatically every 5 minutes.

---

Quick start

Prerequisites: Docker Desktop with WSL2 backend (Windows) or Docker Engine (Mac/Linux)

# 1. Clone the repo
git clone https://github.com/your-username/earthquake-pipeline.git
cd earthquake-pipeline

# 2. Create your .env file
cp .env.example .env          # credentials are already set for local dev

# 3. Initialise Airflow (run once — creates DB schema + admin user, then exits)
docker compose up airflow-init

# 4. Start everything
docker compose up -d postgres airflow-scheduler airflow-webserver vizserver

Service	URL	Credentials
Live map	http://localhost:8050	—
Airflow UI	http://localhost:8080	admin / admin
PostgreSQL	localhost:5432	postgres / postgres

5. Trigger the first pipeline run

Open the Airflow UI → find earthquake_pipeline → toggle it on → click ▶ Trigger DAG.

Refresh the map at http://localhost:8050 to see your first data.

---

Architecture

USGS Earthquake API (GeoJSON, updated every minute)
        │
        ▼  every hour
 ┌─────────────────┐
 │  Airflow DAG    │  earthquake_pipeline
 │                 │
 │  task 1         │  fetch_earthquakes.py
 │  ingest_raw     │  → HTTP GET /all_hour.geojson
 │                 │  → bulk INSERT into earthquakes_raw
 │                 │
 │  task 2         │  CALL upsert_analytical(today)
 │  aggregate      │  → aggregates raw rows into
 │                 │    magnitude bins per day
 └─────────────────┘
        │
        ▼
 ┌─────────────────────────────────┐
 │         PostgreSQL              │
 │                                 │
 │  earthquakes_raw                │  partitioned by event_time (monthly)
 │  earthquakes_analytical         │  daily magnitude-bin aggregates
 │  earthquakes_binned (view)      │  used by the upsert procedure
 └─────────────────────────────────┘
        │
        ▼  live queries
 ┌─────────────────┐
 │   vizserver     │  Flask + Leaflet.js
 │   :8050         │  → /api/earthquakes  (GeoJSON, last 30 days)
 │                 │  → /api/stats        (bin counts)
 │                 │  → /                 (heatmap UI)
 └─────────────────┘

---

Tech stack

Layer	Technology	Why
Ingestion	Python + requests	Minimal, no framework overhead
Orchestration	Apache Airflow 2.9 (LocalExecutor)	Industry-standard, full retry/log/UI
Storage	PostgreSQL 15	Range partitioning, JSONB, stored procedures
Visualisation	Flask + Leaflet.js + leaflet.heat	Self-hosted, no API keys required
Infrastructure	Docker Compose	Single command, reproducible on any machine

---

Data source

USGS Earthquake Hazards Program — https://earthquake.usgs.gov

Feed used: all_hour.geojson — every seismic event globally in the past 60 minutes, refreshed every minute. Fields captured: magnitude, depth, coordinates, place description, alert level, tsunami flag, felt reports, significance score.

Feed documentation: https://earthquake.usgs.gov/earthquakes/feed/v1.0/geojson_detail.php

---

Project layout

earthquake-pipeline/
├── docker-compose.yml          ← all 5 services defined here
├── .env                        ← your local credentials (gitignored)
├── .env.example                ← template to copy from
├── requirements.txt            ← local dev dependencies
│
├── sql/
│   ├── init.sh                 ← creates 'earthquakes' DB on first Postgres start
│   ├── create_raw.sql          ← earthquakes_raw partitioned table + indexes
│   └── create_analytical.sql  ← analytical table, binned view, upsert procedure
│
├── scripts/
│   ├── db.py                   ← DB connection helper (reads from env vars)
│   └── fetch_earthquakes.py   ← USGS fetch + parse + bulk insert
│
├── dags/
│   └── earthquake_pipeline.py ← Airflow DAG: ingest_raw → aggregate_analytical
│
└── viz/
    └── app.py                  ← Flask server: live map + JSON API

---

Stopping and resetting

# Stop all containers, keep database data
docker compose down

# Stop and wipe all data (fresh start)
docker compose down -v

# View live logs from all services
docker compose logs -f

# View logs for one service
docker compose logs -f airflow-scheduler

---

Loading more historical data

The default feed covers the past hour. To backfill more data, trigger a one-off run against the monthly feed:

# Windows PowerShell
docker run --rm `
  --network earthquake-pipeline_default `
  -v "${PWD}/scripts:/scripts" `
  -e EARTHQUAKE_DB_HOST=postgres `
  -e EARTHQUAKE_DB_NAME=earthquakes `
  -e EARTHQUAKE_DB_USER=postgres `
  -e EARTHQUAKE_DB_PASSWORD=postgres `
  python:3.11-slim bash -c "pip install psycopg2-binary requests -q && python /scripts/fetch_earthquakes.py https://earthquake.usgs.gov/earthquakes/feed/v1.0/summary/all_month.geojson"

This inserts ~8,000–12,000 events (all global earthquakes in the past 30 days) and makes the heatmap substantially more interesting.