CalCOFI Query

Net-tow ichthyoplankton matched to CTD-bottle measurements by scientific name. Replaces the retired /ichthyodata Plumber endpoint.

Mirrors calcofi4r::cc_match_ichthyo_by_name() — the SQL emitted here is character-identical to its attr(d, "sql"). See the Matching Helpers chapter for the windows, join methods, and version pinning.

Default form returns 13 rows — the same Q1 2018 sardine-larva worked example used across the docs and the vignette.

Net-tow ichthyoplankton matched to CTD-bottle measurements by WoRMS taxon ID and every descendant. Replaces the retired /itis_ichthyodata Plumber endpoint, swapping the dead ITIS path regex for a recursive walk of taxon.parentNameUsageID over the WoRMS authority.

Look up a worms_id in Browse → species, or paste one from WoRMS. Default 125724 is the genus Engraulis — its subtree currently resolves to E. mordax (northern anchovy) in the CalCOFI taxonomy.

Mirrors calcofi4r::cc_match_ichthyo_by_taxon().

Power-user mode. The engine (cc_match_bio_env) just needs two SELECT sub-queries with the right column contract — drop in your own and the temporal interval + spatial ST_Distance_Sphere join runs on top.

Contract. bio must yield bio_id (unique per observation), bio_datetime (TIMESTAMP), bio_lon, bio_lat (decimal degrees) and bio_value (DOUBLE). Any extras are carried through to the output as grouping keys. env must yield exactly env_id, env_datetime, env_lon, env_lat, env_value, env_depth_m, measurement_type.

Default form is the worked example pre-filled — click Run and you get the same 13 sardine-larva rows. __TBL:table__ in either box resolves at Run to the pinned release’s read_parquet(...) for that table.

Net-tow displacement-volume biomass matched to environmental measurements. Biomass comes from the total_plankton_biomass / small_plankton_biomass measurement types in sample_measurement (mL per net haul, joined to sample for position + time); the env side is the same consolidated obs table (realm = 'env') as the other bio↔env queries.

Replaces the retired /zooplankton_biomass Plumber endpoint. Mirrors calcofi4r::cc_match_zooplankton_biomass().

One row per CalCOFI cruise, filtered by the cast datetime_start_utc falling in your date range. Columns: cruise_key (YYYY-MM-NODC natural key — see Database), date span, ship name, number of casts.

cruise itself has one row per cruise; the count(*) over bottle casts in sample makes the date-window filter meaningful and adds n_casts.

The measurement_type registry — every variable that can appear as a measurement_type value in bottle_measurement, ctd_thin, dic_*, ichthyo etc. Each row has units and description. Use this list to pick the right env_var for the bio↔env queries.

taxon.parquet is the project’s unified taxonomic registry — one row per taxon across every dataset, keyed by taxon_key (worms:<id> / itis:<id>) — with scientific_name, common_name, rank, and the authority IDs (worms_id, itis_id, gbif_id). Substring filter on either name. Take the worms_id of a hit and feed it to Bio ↔ Env Matching → by taxon to get the matching subtree.

The bottle dataset’s obs rows (realm = 'env': one value per depth of a cast) for one measurement_type over a depth + date window. Most CalCOFI oceanographic variables live here: temperature, salinity, oxygen, nutrients, chlorophyll-a, sigma-theta, dynamic height, pH, …

The env_var dropdown is populated on first focus from the release’s measurement_type table, so it always reflects the release you’re querying. A value taken once per cast (not at each depth) is a row of sample_measurement, not of obs.

grid_key changed for about one bottle row in ten in v2026.10.04 (a cell per official station); to carry a key you stored earlier forward, use Spatial → old grid keys → new cells.

calcofi_bottle dataset page ↗

Single-table ichthyoplankton — net-tow counts joined to species, net, tow, and site, no environmental match. std_tally is the standardized catch-per-effort: standard_haul_factor × tally / prop_sorted.

For the same observations matched to CTD-bottle measurements (one env_value per row), see Bio ↔ Env Matching → by name.

swfsc_ichthyo dataset page ↗

Headline counts and date ranges from the pinned release. A 9-row scorecard:

• cruises · casts · species · ichthyo_rows · bottle_measurements — table-level row counts.
• bio_date_start / bio_date_end — net-tow temporal coverage (tow.datetime_start_utc).
• env_date_start / env_date_end — CTD-bottle env coverage (casts.datetime_start_utc). The gap between this and the bio coverage is why the bio↔env matching examples use 2018 dates.

Each __TBL:table__ resolves through that release’s catalog.json to the parquet it reads — change version and the whole scorecard refreshes against that release. See available releases on GCS (one folder per release).

Bottle casts whose decimal lon/lat fall inside the bounding box and whose datetime_start_utc falls in the date range. Pasted straight from the CalCOFI sampling grid: defaults (-125 to -117 lon, 30 to 38 lat) cover the full historical pattern from Pt. Conception south to Baja and out to ~Sta. 60.

grid_key is the cell of the station grid the cast lies in. From v2026.10.04 the grid is one cell per official station (decimal stations such as st26.7-ln93.3 included) and a key kept from an earlier release may name a different polygon: map old keys with Spatial → old grid keys → new cells, and take line and station from grid.line / grid.station (doubles), not by parsing the key.

For richer spatial filtering (polygon, transect, distance to shore) use the SQL shell with ST_Within / ST_Distance_Sphere.

Where the water of a previous grid_key is now. From v2026.10.04 the grid is one cell per official station (plus the historical cells it keeps), and a key that survives as a name may name a different polygon: st30-ln90 was the cell of four stations and the cell that now carries its name holds about half of that water. A grid_key stored from an earlier release — in a URL, a cache, a per-cell table of your own — must be mapped through grid_crosswalk, never matched by name.

One row per overlapping (previous cell, current cell) pair:

• prev_frac — the share of the previous cell’s area that lies in this cell; grid_frac — the share of this cell that came from the previous one.
• same_name — the key is unchanged. It says nothing about the water: read prev_frac.
• value_share — your value split across the new cells in proportion to prev_frac, so the pieces add back to value. prev_frac itself is of the whole old cell and does not always sum to one (the finer coastline turns up to ~2% of an old cell into land), so the shares are rescaled by prev_frac_kept. To carry a table of per-cell quantities forward, join it to this crosswalk on prev_grid_key and sum(value * prev_frac / prev_frac_kept) by grid_key. This is right for counts and totals; for an intensive quantity (a mean, a concentration) use the cell’s prev_frac-weighted average instead of the sum.
• line and station are doubles since this release (st26.7-ln93.3, st27.7-ln90): do not parse them out of the key with a whole-number pattern.

A key that returns no rows is not one of the previous release’s keys.

grid_crosswalk exists from release v2026.10.04 on; pin an earlier version and the query stops with a “table is not in the catalog” message.

Rows in the sample tables are already keyed to the current grid in v2026.10.04; the crosswalk is for keys you hold from before. To go the other way — which previous cell a current one came from — filter on grid_key in the SQL shell.

Free-form DuckDB SQL against the release Parquet — same engine as the other queries, no form-driven template. The httpfs and spatial extensions are already loaded.

Name a release table as __TBL:table__ (any table in the release’s catalog.json — __TBL:obs__, __TBL:sample__, __TBL:cruise__, …). It is resolved at Run into the read_parquet(...) expression for the pinned release, so the SQL tab shows exactly what ran and copies anywhere.

Useful for:

• Ad-hoc queries the other forms don’t cover
• Trying out a SQL pattern before turning it into a permanent query (drop a .md file in the right _queries/ subfolder)
• Joining tables that aren’t paired in the named queries — e.g. taxa_rank, cast_condition, spatial, dic_*

For arbitrary SQL with no UI at all, shell.duckdb.org is DuckDB’s official WASM shell. Same engine, no CalCOFI context.

CTD casts whose datetime_start_utc falls in your date window — globally, no spatial filter. Defaults to all of 2018. Pair this with the Browse → cruises query to see one row per cruise instead of one per cast.