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 only has 691 rows; 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.

bottle_measurement ⋈ bottle ⋈ casts 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 measurement_type.parquet so it always reflects the release you’re querying.

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).

CTD 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.

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

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.