From USGS GeoJSON to a Public Earthquake Report with Python and SQLite

What happens when the same earthquake appears twice in a data feed? If a pipeline counts both rows, the daily report can look perfectly reasonable while showing the wrong total. Seismic Snapshot treats the event ID as a primary key and stops the release when the source violates that rule.

The project turns a public USGS earthquake feed into a downloadable dataset and a static report. Python handles ingestion, SQLite runs the transformations, and GitHub Actions deploys the result to GitHub Pages. The workflow uses GitHub-hosted infrastructure and requires no AWS services or API credentials.

• Public source repository
• Live Seismic Snapshot report
• First successful build and deployment

1. Define the input before writing the pipeline

The USGS magnitude 2.5+ weekly feed returns a GeoJSON collection for a rolling seven-day window. In the feed format, the event ID belongs to the feature, the magnitude and time belong to its properties, and the coordinates are ordered longitude, latitude, and depth in kilometers. Swapping latitude and longitude at ingestion can put an event in the wrong part of the world.

The pipeline checks the response status and feature count before filtering to events classified as earthquakes. It converts timestamps from milliseconds to UTC and checks that retained events have an ID, a point geometry, valid coordinates, and finite measurements. A missing location label gets a readable placeholder. A missing magnitude fails the build because it would affect both the ranking and the daily maximum.

2. Load a reproducible snapshot

The fetch has a 30-second timeout per attempt and retries network errors up to two times. After validation, the pipeline saves the incoming GeoJSON bytes and loads one row per event into SQLite. The table replacement and inserts share a transaction, so a duplicate primary key aborts the replacement. Replaying a saved input rebuilds the same set of event rows without appending duplicates from a previous execution.

SQLite fits this workload because a weekly batch can be transformed in a single file. The runner can archive that file alongside the source response so a reader can investigate what went into a particular report.

3. Aggregate in SQL and check the totals

The transformation groups normalized event timestamps by UTC date and computes the event count, peak magnitude, and average depth for each date.

SELECT substr(occurred_utc, 1, 10) AS day_utc, count(*) AS event_count, round(max(magnitude), 2) AS max_magnitude, round(avg(depth_km), 1) AS average_depth_km FROM events GROUP BY day_utc ORDER BY day_utc DESC 

Before export, the script checks that the sum of the daily counts matches the total number of loaded events. It writes the event-level and daily CSVs and renders a report with the 12 largest events. The source generation time appears in the report so readers can check freshness. The first and last UTC dates may cover only part of a day, and the catalog can revise event estimates between snapshots.

4. Put validation before deployment

The GitHub Actions workflow runs on pushes to main, on manual dispatch, and every six hours. The build runs the Python pipeline, archives the raw response and SQLite database for seven days, and uploads the HTML and CSVs as the Pages artifact.

The deploy job depends on the build with needs: build. A failed fetch, load, or export stops the release. Only the deployment job receives Pages write and OIDC permissions. In repository settings, the Pages publishing source is set to GitHub Actions. The GitHub Pages workflow documentation describes how the artifact moves between the two jobs.

The first successful run processed 327 earthquake events. The report showed a maximum magnitude of 6.40, and the counts for its seven UTC dates added up to 327.

5. Run the same pipeline locally

With Python 3.9 or newer, clone the repository and run:

git clone https://github.com/Cristhian465/seismic-snapshot-pipeline.git cd seismic-snapshot-pipeline python3 pipeline.py python3 -m http.server 8000 --directory site 

Open http://localhost:8000. To debug a specific snapshot, pass its file with python3 pipeline.py --input evidence/usgs.geojson. The same transform and validation steps run in both modes.

Each deployment represents one rolling snapshot. A permanent history of catalog revisions would require retaining snapshots beyond the workflow artifact window and storing event versions separately.

Data source: U.S. Geological Survey. The project and article were prepared with AI assistance.