Edition 5: Turn a USGS earthquake feed into a source-linked report with DuckDB

A public API gives you a wall of JSON. You need a short report someone can read, with a source link beside every row. An assistant can draft a query, but it should not invent the numbers or the story around them.

This tutorial turns a real USGS earthquake-feed snapshot into two useful files: an editable CSV containing 145 events and a five-row HTML report you can open in a browser. DuckDB 2.0.0 reads the GeoJSON as event rows directly. You do not need to build a database server or call an AI model to reproduce the result.

See the finished report first

The saved USGS feed was generated at 23:38:03 UTC on October 31, 2026. These are the five largest reported magnitudes in that particular snapshot, with event ID used to break ties:

The completed HTML report includes UTC event times. The CSV contains all 145 rows, and the complete source snapshot is readable JSON. These are ordinary files, not a ZIP or a private-repository prerequisite.

This is a historical data demonstration, not a live alert, hazard forecast or assessment of damage. Magnitude alone does not tell you which event caused the most harm. Source estimates and records may be revised.

1. Understand the input before asking for a query

USGS publishes a GeoJSON summary feed. The top-level object contains metadata and a features list. Each feature has an event ID, a properties object and geometry. For this report, the useful fields are magnitude, place, event time, event type and the source-page URL.

Two timestamps serve different purposes. The feed's generated time says when this copy was produced. An event's time says when that event occurred. The API uses milliseconds since the Unix epoch, so treating those values as seconds produces nonsense dates.

The live magnitude-4.5-plus weekly feed changes. To reproduce this article, use the saved snapshot linked above. Its SHA-256 is 78ace9717a390ed4934b67021c6e5d85bc3f31c6f065ab3e45fa7f7dde4dee8a.

2. Let AI help with the query, then run the query yourself

Here is a complete task you can copy into an assistant you already use. No assistant is required because the finished SQL is provided below.

I have a public USGS GeoJSON FeatureCollection. Its features list contains event.id and event.properties.mag, place, time, url and type. I am using DuckDB 2.0.0 and read_json with geojson=true. Inspect the resulting columns before drafting SQL. Select earthquake records with non-null magnitudes and order by magnitude descending with event ID as the tie-breaker. Preserve the source URL and event time. Do not infer damage, casualties, risk or a trend. Do not assume the imported table still has a top-level features column.

The key transformation is small:

CREATE TABLE events AS
SELECT * FROM read_json('source.geojson', geojson = true);

SELECT id, mag AS magnitude, place, time AS time_ms, url AS source_url
FROM events
WHERE type = 'earthquake' AND mag IS NOT NULL
ORDER BY magnitude DESC, id;

read_json with GeoJSON handling enabled produces one row per feature for this file. The field selections keep the original identifiers and source URLs attached to each result. DuckDB 2.0 added GeoJSON support; the documentation describes the geometry parsing flag. Inspect the actual imported columns rather than assuming a generic JSON recipe has the right shape.

AI assisted this article's query drafting. The observed results come from executing the supplied SQL against the saved public file. They do not establish that a particular chat product writes reliable SQL or that natural-language querying is always easier.

3. Build the CSV and browser-readable report

The recorded execution used Python 3.12 and duckdb==2.0.0. In an empty folder, create a Python virtual environment and install the pinned package:

python -m venv .venv
# macOS or Linux:
source .venv/bin/activate
# Windows PowerShell instead: .venv\Scripts\Activate.ps1
python -m pip install duckdb==2.0.0

Save this complete program as usgs_brief.py, then run python usgs_brief.py. It downloads the saved article snapshot, checks its bytes, runs the query and writes a new earthquake-results folder. It refuses to overwrite an existing results folder.

"""Build an inspectable brief from the frozen public USGS snapshot."""
import csv
from datetime import datetime, timezone
import hashlib
import html
import json
from pathlib import Path
import sys
from urllib.request import urlopen
import duckdb

EXPECTED = "78ace9717a390ed4934b67021c6e5d85bc3f31c6f065ab3e45fa7f7dde4dee8a"
SNAPSHOT = "https://publishhaven.com/static/publishing/demos/usgs-20261031.geojson"
output = Path(sys.argv[1] if len(sys.argv) > 1 else "earthquake-results")
source = sys.argv[2] if len(sys.argv) > 2 else SNAPSHOT
if output.exists():
    raise SystemExit("STOP: output folder exists; choose a new folder")
if source.startswith("https://"):
    with urlopen(source, timeout=30) as response:
        data = response.read()
else:
    data = Path(source).read_bytes()
if hashlib.sha256(data).hexdigest() != EXPECTED:
    raise SystemExit("STOP: snapshot checksum mismatch")
document = json.loads(data)
output.mkdir(parents=True)
saved = output / "source.geojson"
saved.write_bytes(data)
db = duckdb.connect()
db.execute("CREATE TABLE events AS SELECT * FROM read_json(?, geojson = true)", [str(saved)])
rows = db.execute("""
SELECT id, mag AS magnitude, place, time AS time_ms, url AS source_url
FROM events
WHERE type = 'earthquake' AND mag IS NOT NULL
ORDER BY magnitude DESC, id
""").fetchall()
assert len(rows) == document["metadata"]["count"] == 145
assert len({row[0] for row in rows}) == len(rows)
assert rows[0][0] == "us6000u4r9" and rows[0][1] == 6.5
generated = datetime.fromtimestamp(document["metadata"]["generated"] / 1000, timezone.utc).isoformat()
def utc(milliseconds):
    return datetime.fromtimestamp(milliseconds / 1000, timezone.utc).isoformat()
with (output / "events.csv").open("w", newline="", encoding="utf-8") as stream:
    writer = csv.writer(stream)
    writer.writerow(["id", "magnitude", "place", "time_utc", "source_url"])
    writer.writerows((identity, magnitude, place, utc(time_ms), url)
                     for identity, magnitude, place, time_ms, url in rows)
parts = ["<!doctype html><meta charset='utf-8'><title>USGS earthquake snapshot</title>",
         "<style>body{font:18px/1.6 system-ui;max-width:1000px;margin:40px auto;padding:20px}td,th{padding:10px;text-align:left;vertical-align:top}table{border-collapse:collapse}tr{border-bottom:1px solid #ddd}</style>",
         "<h1>Five largest magnitudes in this USGS snapshot</h1>",
         "<p>Feed generated: " + html.escape(generated) + ". Historical snapshot, not a live alert.</p>",
         "<table><tr><th>Magnitude</th><th>Place</th><th>Time UTC</th><th>Source</th></tr>"]
for identity, magnitude, place, time_ms, url in rows[:5]:
    assert url.startswith("https://earthquake.usgs.gov/earthquakes/eventpage/")
    parts.append("<tr><td>" + str(magnitude) + "</td><td>" + html.escape(place)
                 + "</td><td>" + html.escape(utc(time_ms)) + "</td><td><a href='"
                 + html.escape(url, quote=True) + "'>" + html.escape(identity) + "</a></td></tr>")
parts.append("</table><p>Magnitude ranking does not measure damage, risk or a trend. Source estimates can be revised.</p>")
(output / "brief.html").write_text("\n".join(parts), encoding="utf-8")
db.close()
print("SNAPSHOT sha256=MATCH generated=2026-10-31T23:38:03+00:00")
print("ROWS total=145 unique_ids=145")
print("TOP5 ids=" + ",".join(row[0] for row in rows[:5]))
print("EXPORT events.csv rows=145; brief.html rows=5; source.geojson preserved")
print("CHECK PASS: source-linked data brief; no model call")

Complete observed stdout:

SNAPSHOT sha256=MATCH generated=2026-10-31T23:38:03+00:00
ROWS total=145 unique_ids=145
TOP5 ids=us6000u4r9,us7000ttxz,us7000tu4f,us6000u4rf,us7000ttlm
EXPORT events.csv rows=145; brief.html rows=5; source.geojson preserved
CHECK PASS: source-linked data brief; no model call

Open earthquake-results/brief.html in your browser. Open events.csv in a spreadsheet or text editor. Follow a row's USGS link and compare its ID, reported magnitude and location. A current event page may contain a later revision, which should be recorded as a difference from the historical snapshot rather than silently substituted into it.

For an offline rerun after you have downloaded the source, use python usgs_brief.py another-results path/to/usgs-20261031.geojson. No network or model is then needed.

4. Repair the version-specific mistake we actually encountered

Our first query used unnest(features) after reading the file. That worked in a preliminary DuckDB 1.4.1 run, but the 2.0.0 run failed with Referenced column "features" not found. Inspecting the imported schema showed event fields such as id, mag and place already exposed as columns. The repair was to query those event rows directly, as the complete program now does. This was an observed version-specific failure, not a fabricated model response.

You can inspect your own imported schema with DESCRIBE SELECT * FROM read_json('source.geojson', geojson = true);. Pinning a version is useful, but understanding the shape it returns is what repairs the query.

For a second check, change ORDER BY magnitude DESC, id to ORDER BY magnitude ASC, id and run into a new results folder. The check expecting the known largest event fails. The resulting order is wrong for the question even though the SQL is valid. Restore DESC and rerun into another new folder.

That is a better check than asking a model whether its own query looks correct. It uses an independently inspectable record in the actual input. It still does not prove every possible transformation is correct. Inspect the CSV, IDs and source links as well.

If the program stops with a checksum mismatch, inspect the input version before changing anything. If the output folder already exists, choose a new name. Do not delete an earlier run merely to make a command succeed. If DuckDB is missing, activate the environment where you installed the pinned package.

Where this becomes useful beyond earthquakes

The same pattern applies to a public product catalog, a city-service feed or an approved operational export: inspect the nested structure, select only the fields needed, keep the record ID and source, execute the query, then publish a small readable view alongside the complete editable data.

An assistant can help draft or explain a transformation. A person decides the question, verifies the meaning of fields and checks whether the report says more than the data supports. An automated count does not approve a narrative. This example supplies no automatic model-generated interpretation, alerting service or publication pipeline.

For a fresh dataset, create a new dated snapshot, inspect its schema and establish new expected counts and example records. The fixed 145-row and top-event checks here belong to this saved file. Copying them unchanged onto a rolling feed would confuse input changes with a broken query.

Sources and method

USGS feed documentation defines the source structure and fields. DuckDB JSON loading explains the implemented GeoJSON reader and its geometry option. The article adds an executed end-to-end conversion, an inspectable report, complete code and a wrong-sort repair.

AI assisted the writing and local execution on October 31, 2026. No paid model API, private data, cloud database or independent beginner trial was involved. Execution and file checks were performed in the authoring session. We have not measured time savings, reader demand or acquisition, and this example is not a seismology or model-performance benchmark.