Reading Aftershock Sequences With A 30-Day Rolling Rate

A magnitude 6 event near a populated coast. The first hour is noise; the question I care about is the second week. I keep a small local archive of USGS event feeds for offline replay, and the thing that has paid off most is a dead-simple 30-day rolling rate per source region, not magnitude thresholds.

The setup: a Postgres table of events (time, lat, lon, depth, mag, place), one row per event from the public feed. I bucket events into 1° grid cells and count events per cell per day. A shell one-liner populates the table, and this query gives the rolling rate for a cell I care about:

SELECT date_trunc('day', time) AS day,
       count(*) FILTER (WHERE mag >= 3.0) AS m3plus,
       count(*) FILTER (WHERE mag >= 4.5) AS m45
FROM quakes
WHERE round(lat) = 38 AND round(lon) = 142
  AND time > now() - interval '30 days'
GROUP BY 1 ORDER BY 1;

To make the rate comparable I divide by 30 and plot it against the same 30-day window from the prior year. A sequence that decays back to background within ~2 weeks is what I expect; one that stays 3–5x above background at day 20 is worth looking at more closely, because it means the aftershock productivity is still elevated, not that a bigger event is coming. I do not treat that as a forecast. It is a bookkeeping signal.

Why this and not magnitude-based alerts: magnitude thresholds get crowded by the biggest events and hide the long tail. Counts are noisy but debuggable. I can open the underlying rows, see whether a spike is one event counted twice across feed revisions, or an actual step change. The cost is that I have to deduplicate: the feed republishes events with updated magnitudes, and naive inserts double-count. I key on a composite of time rounded to the second plus rounded coordinates, and keep the latest magnitude.

Where it fails: near a swarm, the 1° cell is too coarse and adjacent cells split the counts; a magnitude 5 in a quiet cell looks like a 20x jump that means nothing. Offshore events cluster at cell boundaries and I have seen a single sequence counted in four cells. Also, my archive is only as good as the feed’s completeness — in the first hours after a large event the feed lags, so the day-1 rate can undercount and the decay looks faster than it is.

A specific question for anyone keeping a similar archive: how do you handle cell assignment for events that sit right on a boundary, and do you recompute the historical baseline after a feed revision or leave the original counts?