Skip to contents

Build the release's climatology table

Source: R/climatology.R
build_climatology.Rd

A plain mean per dataset, station (site_key), the cruise's month, 10 m depth bin and measurement type over the env realm of obs across a fixed window of years — the baseline every CalCOFI anomaly (ctd-transects, the CalCOFI Explorer's Sections lens, calcofi4r::cc_climatology()) is a departure from. Written once at release time so the products cannot disagree.

Usage

build_climatology(
  con,
  qual_ok_sql,
  yr_min = 1993L,
  yr_max = 2013L,
  min_cruises = 3L,
  depth_bin_m = 10L,
  depth_max_m = NULL,
  round_digits = 6L,
  cast_types = NULL,
  season = FALSE,
  tbl = "climatology",
  sample_tbl = "sample",
  sample_measurement_tbl = "sample_measurement"
)

Arguments

con

DuckDB connection holding obs (with realm, sample_key, grid_key, datetime, depth_min_m, measurement_value, measurement_qual, cruise_key, dataset_key, measurement_type) and sample_tbl (with sample_key, site_key).

qual_ok_sql

the quality predicate over alias o — calcofi4r::cc_qual_ok_sql("o"), passed in so this package never carries a second copy of it.

yr_min, yr_max

the baseline window, inclusive years.

min_cruises

a cell needs this many distinct cruises to be a baseline at all.

depth_bin_m

bin width in metres (floor bins, labelled by the shallow edge).

depth_max_m

deepest bin kept (its shallow edge), or NULL — the default — for no depth ceiling at all: the baseline then reaches the deepest bin the min_cruises rule itself passes, which is the only rule that ever decided whether a cell is a baseline (plan 2026-09-11 "Measurement faces" § D6/F7). Until calcofi4db 4.15.0 this defaulted to 500L, the depth the Explorer's Sections lens draws to, and the cap silently travelled beyond that lens: 117,302 temperature, 105,689 salinity, 81,423 oxygen and 26,076 nitrate values below 500 m in v2026.09.10 had no normal to depart from, so a measurement page could not show a deep band's anomaly at all. Pass a number to cap it again (the release did so while a consumer needed it).

round_digits

decimal places clim_mean/clim_sd are rounded to — chosen to be far below sensor resolution but well above the parallel-aggregation floating-point noise floor (see above), so the table is byte-identical across re-exports of unchanged data.

cast_types

per-cast measurement types (in sample_measurement_tbl) to give a baseline too, with depth_bin NULL; NULL (the default) for the per-bin arm alone.

season

also compute the seasonal baseline (quarter, season_* columns); FALSE leaves those columns NULL and keeps only cells that pass the monthly floor.

tbl

output table (default climatology).

sample_tbl

the sample table joined for site_key (default "sample").

sample_measurement_tbl

the per-cast table read for cast_types.

Value

Invisibly, the row count. Columns: dataset_key, site_key, grid_key, month, depth_bin, measurement_type, clim_mean, clim_sd, clim_n, n_cruises, clim_yr_min, clim_yr_max, quarter, season_mean, season_sd, season_n, season_n_cruises.

Details

Why each part of the grain:

  • Month. Quarterly-ish cruises over decades give many years per calendar month at a station but only a handful of days, so month is the finest season CalCOFI supports — and the coarsest that works: a baseline pooled over all months is a map of the seasonal cycle, not an anomaly (line 90 surface: January 15.2, July 18.3, annual mean 16.8 degC; at 50–100 m the sign flips). A plain mean rather than harmonics: Rudnick et al. (2017) fit annual and semiannual harmonics for the CUGN glider climatology, which suits continuous glider sampling; CalCOFI's is episodic, and a monthly mean is something a reader can state exactly.

  • 10 m floor bins — floor(depth_min_m / 10) * 10, the obs_env.depth_bin convention, labelled by the shallow edge. obs carries the thinned CTD series (a 10 m grid plus RDP inflection points plus bottle depths), so at 5 m the off-grid bins hold about a third of the casts, sampled exactly where the profile bends, and their means sit visibly off their neighbours'. Every 10 m bin holds every cast.

  • The cruise's month, not the cast's. A cast is filed under the month and year its cruise_key designates (YYYY-MM-NODC, the month SWFSC assigns the cruise), never the calendar date it was occupied. A cruise sails for two to three weeks and often starts in the last days of the month before: CalCOFI 2607 (2026-07-3322) worked line 93.3 inshore of station 50 on 30 June, so its anomaly looked up a June baseline that does not exist and ctd-transects drew nothing inshore (Rasmus Swalethorp, 2026-10-06). Within 1993-2013, 9.9 % of CTD casts (35 cruises) and 9.3 % of bottle samples fall in another calendar month than their cruise's; by event month they were both missing from their season's baseline and stranded in a month no cruise samples, where they mostly failed the cruise floor. The cruise month is the season the cruise samples, so it is the season both sides of an anomaly are matched on; a consumer reads it as substr(cruise_key, 6, 2). A row whose cruise_key does not parse falls back to its calendar date.

  • The window (yr_min–yr_max, default 1993–2013: Rasmus Swalethorp's CCIEA request; the Wilkinson archive fills 1993–2002 so it does not quietly mean "1998 plus 2003–2013"). 21 years with both phases of the 1997–99 ENSO inside it, ending before the 2014–16 marine heatwave so the heatwave and everything after read as departures. Not a WMO normal. The bounds are stamped on every row (clim_yr_min, clim_yr_max), so a consumer reading the parquet alone knows what the mean is a mean of.

  • The station is sample.site_key, not the grid cell. grid_key is a point-in-polygon into ~2,350 km² nearshore cells that each hold 2–4 real stations, all occupied every cruise since 2004 (st30-ln90 holds 90.30, 90.28, 90.27.7 and 88.5/30.1; 3.7 CTD occupations per cruise). Until calcofi4db 4.8.0 the baseline pooled them into one cell — ~1 °C off at the surface for the inshore station — while ctd-transects drew one of them and the Explorer averaged them. The grain is now the station, read from sample through sample_key (obs does not carry site_key); grid_key stays on the row as the station's modal cell (709 of 9,705 stations straddle a cell edge across their occupations) so a map or hex consumer can still aggregate by cell. Rows whose sample has no station (underway, transect, region-pooled) contribute nothing.

  • A floor in cruises, not observations (min_cruises, default 3). A grid cell can hold several stations' casts from one cruise (st30-ln90 holds 90.30, 90.28, 90.27.7 and 88.5/30.1), so an observation floor is met by one lucky cruise. clim_n and n_cruises both ship, so a thin cell is visible rather than silently trusted.

  • One row per dataset. A CTD-only consumer filters dataset_key; a cross-dataset one (the explorer's "temperature" = bottle temperature + CTD temperature_ave) pools rows weighted by clim_n — sum(clim_mean * clim_n) / sum(clim_n) is exactly the mean over the pooled observations, so the two ways of reading the table cannot disagree either.

  • clim_mean/clim_sd are rounded to round_digits (default 6 decimal places). DuckDB's avg()/stddev_samp() are computed by combining per-thread partial sums in parallel, and floating-point addition is not associative — the combine order (and so the last 1-2 bits of the double) varies run to run with no change to the input rows. Measured on a 200-cell/1.6M-row synthetic fixture: clim_n and n_cruises (integer) were identical across repeated builds, but clim_mean/clim_sd differed on distinct runs with |relative diff| up to ~1.8e-16 (machine epsilon) — this is what turned 60 of 71 real climatology partitions non-reproducible. Six decimal places is a fixed point ~1e9 times coarser than that noise floor (safe up to values on the order of 1e9, far beyond any CalCOFI variable's range) while being far finer than any instrument's resolution (CTD temperature/salinity ship to 3-4 decimal places, bottle nutrients to 2-3, oxygen to 2) — so rounding this way discards only reproducibility-breaking noise, never scientifically meaningful precision. Applied to the finished aggregate, so it is a pure function of the (order-independent) value, not of how it was summed.

  • Per-cast values too (cast_types, calcofi4db 4.24.0). A cast's mixed-layer depth, the depth and value of its chlorophyll maximum and its integrated chlorophyll have no depth: they are one value per cast in sample_measurement, so obs never sees them and until 4.24.0 they had no baseline (Rasmus Swalethorp, 2026-10-07: "anomalies for the derived products"). The types named in cast_types are pooled by the same rules (station, cruise month, window, quality, cruise floor) with depth_bin NULL — "a per-cast value", never a depth. A NULL never matches =, so no per-bin join can pick one up by accident; a per-cast consumer matches depth_bin IS NULL. The caller names the types (the release lists them in release_database.qmd's clim_cast_types), so this package carries no list of datasets.

  • A seasonal baseline beside the monthly one (season = TRUE, the release's setting; calcofi4db 4.24.0; Rasmus Swalethorp, 2026-10-06: let the user toggle between a monthly and a seasonal baseline). The quarter is that of the cruise's month — Q1 Jan–Mar, Q2 Apr–Jun, Q3 Jul–Sep, Q4 Oct–Dec, the Explorer's season filter — so 2607 (2026-07-3322) is Q3 even where it worked 93.3 on 30 June. It ships as columns, not rows: quarter and season_mean, season_sd, season_n, season_n_cruises sit on every month of a quarter that passes the cruise floor, so a consumer still looks a baseline up by (station, month, bin, type) and nothing that sums the table counts a cell twice. A station whose months are each below the floor but whose quarter is not (the northern lines 60–73.3, where winter and spring are split across Jan–Apr) gets rows whose monthly clim_mean is NULL: a NULL clim_mean is "no monthly baseline" exactly as an absent row is.

Rows without a station (site_key), a time or a depth, non-finite values, and values the quality predicate rejects are left out; nothing is interpolated. A cell that is absent, or whose mean is NULL, has no baseline — a consumer must leave its anomaly blank, never 0.

Examples

if (FALSE) { # \dontrun{
n <- build_climatology(con, qual_ok_sql = calcofi4r::cc_qual_ok_sql("o"))
DBI::dbGetQuery(con, "SELECT * FROM climatology WHERE grid_key = 'st60-ln90' AND month = 7")
} # }