Ask a question

How the Moon joins the lake

• Each hour the Worker computes the Moon’s phase, distance and position from a published low-precision lunar series (accurate to about half a degree). No outside service is called.
• The numbers are saved to the lake as a CSV file, then parsed into a moon table with one row per hour.
• “Position” is the Moon’s angle across the sky from the middle of New Zealand: overhead, setting, underfoot on the far side of the Earth, then rising again.
• Because the Moon spends the same time in each position, the table compares quakes per day spent there, not raw counts.

Read with care

Thirty days is about one lunar cycle, and earthquakes cluster: one large quake brings hundreds of aftershocks in the same few days. A swarm landing in one phase can make that phase look busy without the Moon having anything to do with it.

Any real tidal link is expected to be weak. Treat this as a worked example of joining two datasets, not as evidence. The longer the lake runs, the more meaningful the comparison becomes.

What the numbers are

• Crime occurrences and family harm investigations recorded by NZ Police each day, nationally, 1 January 2018 to 31 December 2020. They are not 111 calls: Police don’t publish call volumes by day.
• Each day is compared with the average for the same weekday in the same year, so weekday habits and the three-year trend drop out.
• The COVID level 3 and 4 lockdown (25 March to 13 May 2020) is left out. Public holidays and New Year are not, so a handful of days sit well above normal.
• The Moon is taken at noon New Zealand time on each day.

Read with care

The correlation figures run from −1 to +1, where 0 is no link. Values this close to zero mean the Moon’s phase tells you nothing useful about how busy Police will be. Three years is roughly 37 lunar cycles, so a real effect of any size would show up here.

Data: New Zealand Police, policedata.nz, Daily Occurrences of Crime and Family Violence. Crown copyright, reused under CC BY 4.0. Provisional counts as at release.

Two ways to fill a lake

Pipe it in. The usual way: a schedule pulls from a source and files every pull as it arrives. The earthquakes (every 15 minutes) and the Moon (every hour) work like this.

Restock it. Sometimes you stock the lake with a one-off dump. A CSV that someone emailed, an export from a system that has been switched off, a report that stopped publishing. That is just as valid. The police files went in once, untouched, and from then on they behave like every other raw file in the lake.

What stays the same either way

• The raw file is kept exactly as received, whatever its format. These ones are UTF-16 text exported from a dashboard.
• Parsing and typing happen afterwards, in the warehouse, and can be redone from the file.
• The load is repeatable: a second run notices the data is already there and does nothing.
• Where the file came from is recorded with it.

How the warehouse is modelled

• One row per GeoNet event ID, so a re-read never creates duplicates.
• Each pull re-reads six hours of history. If GeoNet has revised an event’s magnitude, depth or location, the row is updated and its revision count goes up.
• Only events GeoNet classes as earthquakes are charted. Quarry blasts and other event types stay in the warehouse but are filtered out.
• “Place” is the town in GeoNet’s description, so “12 km north of Taupō” counts towards Taupō.

SELECT substr(time, 1, 10) AS day,
       SUM(magnitude < 2)  AS small,
       SUM(magnitude >= 4) AS m4_plus
FROM quakes
WHERE event_type = 'earthquake'
  AND time >= :thirty_days_ago
GROUP BY day ORDER BY day;

Good to know

Times are stored in UTC and shown in New Zealand time. Very small quakes are not detected everywhere, so counts under magnitude 2 depend on how close the nearest seismometer is.

This is a demonstration of a data pipeline, not an official hazard feed. For anything that matters, go to geonet.org.nz.

Data: GeoNet, GNS Science / Toka Tū Ake EQC (CC BY 3.0 NZ). Coastline: Natural Earth. Pipeline and analysis by Tom Rowley.