6  Data Access

CalCOFI database releases are published as Parquet files on a public Google Cloud Storage (GCS) bucket. You can query them directly with DuckDB — from R, Python, the DuckDB CLI, or any DuckDB client — with no credentials, no API server, and no full download. DuckDB reads only the columns and row groups a query actually touches, straight over HTTPS.

This page covers querying the release Parquet directly. For convenience-wrapped biological ↔︎ environmental matching, see Matching Helpers. Looking for a particular dataset rather than a query? Every dataset has its own page at calcofi.io/datasets/{dataset_key}/ — coverage, every place it can be reached (this page’s Parquet included), its citation and its provider — see Portals.

The releases are read-only snapshots. The CTD team’s working database — multi-user PostgreSQL on the CalCOFI server, private, reached over SSH — is described in Server Access, including how to join it with the releases from DuckDB.

6.1 Where the data lives

A release is a versioned folder of sidecars — the record of what the release is — and its tables are Parquet objects that the release’s catalog.json points at:

gs://calcofi-db/ducklake/
├── releases/
│   ├── latest.txt              # the promoted version, e.g. v2026.08.25
│   ├── versions.json           # every version: date, tables, rows; consolidated / retired
│   ├── RELEASES.md             # what changed between versions and why, newest first
│   └── {version}/
│       ├── catalog.json        # table list, rows, partitioned/supplemental flags,
│       │                       #   and objects[] — where each table's bytes live
│       ├── relationships.json  # primary keys + foreign keys
│       ├── metadata.json       # table/column descriptions, units, datasets, measurement types
│       ├── RELEASE_NOTES.md    # this version's RELEASES.md section + a generated appendix
│       └── parquet/            # legacy per-release copy — promoted + consolidated versions only
│           ├── {table}.parquet
│           └── {table}/dataset_key=.../*.parquet
└── tables/                     # content-addressed objects, from the v2026.09 releases
    ├── {table}/{content_hash}/{table}.parquet
    └── {table}/{col}={value}/{content_hash}/data_0.parquet

• Public HTTPS root of the bucket: https://storage.googleapis.com/calcofi-db/ — every path in a catalog is relative to it. https://storage.calcofi.io/calcofi-db/ serves the same bucket and redirects a legacy releases/{version}/parquet/… URL to the canonical object where one still exists.
• The promoted version is at releases/latest.txt; the table list — and where each table’s bytes live — at releases/{version}/catalog.json.
• Resolve a table through the catalog; never build a parquet/ path by hand. From the v2026.09 releases each table entry in catalog.json carries objects[] — one object for a single-file table, one per partition for a partitioned one — with a bucket-relative path, bytes, sha256, content_hash, since (the first version that shipped that exact object) and its own compat_path (the legacy per-release path of that one file). Objects are immutable and shared between versions: a release that changed three tables shares every other object with the release before it, so since is the per-table “what changed”. The legacy releases/{version}/parquet/{table}.parquet copy remains only for the promoted version and the consolidated ones (see Versions). The packages’ resolvers — calcofi4r::cc_release_sources(), calcofi4py.release_sources() — turn a catalog entry into URLs for any version, including those before v2026.09.
• Most tables are single-file. obs and the supplemental obs_ctd_full / obs_mets_full are hive-partitioned by dataset_key (catalog.json flags these with "partitioned": true); a partition object’s path carries its dataset_key=… segment, so hive_partitioning = true recovers the column. Supplemental tables ("supplemental": true) are hosted and cataloged but excluded from cc_get_db() by default — obs_ctd_full alone is ~270 M rows.
• To see what’s in each table before writing a query, open the CalCOFI Schema explorer — ERD, sortable tables/columns with units and descriptions, dataset provenance, and the canonical measurement-type registry, all reading the same metadata.json sidecar.

6.2 Versions

• latest vs pinned. latest.txt names the promoted version — a release is promoted only after its consumer-contract query suite passes, so latest is safe to follow interactively and is what cc_get_db() defaults to. Anything that must be reproducible — a paper, an app build, a download bundle — pins a vYYYY.MM.DD. Releases are immutable: the same version always returns the same rows.
• Consolidated vs retired. Every version keeps its sidecars and notes, but only some keep their bytes. The consolidated versions ("consolidated": true in versions.json) mark a schema or data milestone and keep their parquet/ copy indefinitely — v2026.04.08 (last of the per-dataset schema), v2026.05.14 (first ctd_thin), v2026.06.26 (zoodb and zooscan enter), v2026.07.17 (unified taxon), v2026.08.14 (last before content-addressing) and v2026.08.25 (cruise_key by date span, depth ceiling, quality flags mapped) — plus, always, the promoted version and its predecessor. Every other version is retired by archive thinning once it is neither: its versions.json entry gains retired: {retired_utc, to, reason}, where to is the consolidated version to use instead, and cc_get_db("v2026.08.10") errors naming it rather than failing table by table on 404s. When you pin, pin a consolidated version.
• What changed. RELEASES.md is the running changelog across versions — one section per release, newest first, saying what was wrong, what is true now, and what it costs a consumer (**Consumers:** lines). Each version’s RELEASE_NOTES.md is its section plus a generated appendix (tables and rows, datasets, the consumer-contract result, package versions): calcofi4r::cc_release_notes(version) prints it, and cc_list_versions() / calcofi4py.cc_list_versions() list versions.json. Per table, objects[].since in the catalog says which version last changed its bytes.

6.3 Setup: the httpfs extension

DuckDB reads remote Parquet through its httpfs extension. Spatial queries (e.g. distances between casts and tows) also need spatial. Both are one-time installs, then loaded per session:

INSTALL httpfs; LOAD httpfs;
INSTALL spatial; LOAD spatial;   -- only if you use ST_* functions

6.4 Single-file tables

A single-file table is one HTTPS URL handed to read_parquet(). Take it from the catalog (the packages do this for you, below); typed by hand it must be a version whose parquet/ copy is kept — v2026.08.25 is consolidated, so this URL answers indefinitely:

SELECT taxon_key, scientific_name, common_name, worms_id, rank
FROM read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/taxon.parquet')
WHERE scientific_name = 'Sardinops sagax';

Joins work the same way — name each table’s URL. The core model is small: obs holds one measured value per row (realm = bio | env) and points at the sample it came from (sample_key; parent_sample_key walks net → tow → site and bottle → cast) and, for biology, at taxon (taxon_key); event-level effort such as std_haul_factor is in sample_measurement:

SELECT s.sample_key, s.sample_type, s.datetime, s.latitude, s.longitude,
       sm.measurement_type, sm.measurement_value
FROM read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/sample.parquet') s
JOIN read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/sample_measurement.parquet') sm ON sm.sample_key = s.sample_key
WHERE s.dataset_key = 'swfsc_ichthyo'
  AND sm.measurement_type = 'std_haul_factor'
LIMIT 10;

6.5 Hive-partitioned tables

obs (and the supplemental obs_ctd_full / obs_mets_full) is partitioned into one folder per dataset_key. From the v2026.09 releases the catalog lists each partition as its own object, so a partitioned table is read as an explicit list of HTTPS URLs — objects[].path prefixed with the bucket root — with hive_partitioning = true to recover dataset_key from the dataset_key=… path segment. That runs in every DuckDB, the browser included:

-- the list is catalog.json ▸ tables[name = 'obs'] ▸ objects[].path, one per partition
SELECT dataset_key, count(*) AS n
FROM read_parquet([
    'https://storage.googleapis.com/calcofi-db/ducklake/tables/obs/dataset_key=calcofi_bottle/{hash}/data_0.parquet',
    'https://storage.googleapis.com/calcofi-db/ducklake/tables/obs/dataset_key=calcofi_ctd-cast/{hash}/data_0.parquet',
    -- … one URL per partition, in catalog order
    'https://storage.googleapis.com/calcofi-db/ducklake/tables/obs/dataset_key=swfsc_ichthyo/{hash}/data_0.parquet'
  ], hive_partitioning = true)
GROUP BY dataset_key
ORDER BY dataset_key;

Because dataset_key is the partition column, filtering on it lets DuckDB prune whole partitions — a single-dataset query never opens the other files. Nobody types that list: calcofi4r::cc_read_parquet_sql() / calcofi4py.read_parquet_sql() emit it from the catalog (next sections).

A partitioned table may also publish one whole-table file — a single-file twin — and obs does. In the catalog it is the objects[] entry without partition_by (ducklake/tables/obs/{content_hash}/obs.parquet); the resolvers leave it out of urls and return it separately as single_file (NA / None for a table that has none). It exists for https-only readers that cannot take a list — browser DuckDB-WASM, a read_parquet() that wants one URL — which read single_file instead of the partition list. Read one or the other, never both: the twin holds the same rows as the partitions, so a query over both counts every row twice. The price of the twin is partition pruning — a dataset_key filter still works, but scans the one file. On releases before v2026.09 it is the plain per-release copy, …/releases/{version}/parquet/obs.parquet:

-- the single-file twin of obs, for readers that cannot take a list or glob
SELECT dataset_key, count(*) AS n
FROM read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/obs.parquet')
GROUP BY dataset_key
ORDER BY dataset_key;

Releases before v2026.09 (v2026.08.25 and the consolidated versions before it) have no objects[]: their partitioned table is a directory under parquet/, and plain HTTPS URLs cannot glob a directory. Reading one needs an s3-style glob, so configure DuckDB’s anonymous s3 access — GCS is s3-compatible:

INSTALL httpfs; LOAD httpfs;
SET s3_region            = 'auto';
SET s3_endpoint          = 'storage.googleapis.com';
SET s3_url_style         = 'path';
SET s3_access_key_id     = '';
SET s3_secret_access_key = '';

SELECT dataset_key, count(*) AS n
FROM read_parquet(
  's3://calcofi-db/ducklake/releases/v2026.08.25/parquet/obs/**/*.parquet',
  hive_partitioning = true)
GROUP BY dataset_key
ORDER BY dataset_key;

The resolvers return this form for those versions automatically.

6.6 From R

Let the calcofi4r package register every release table as a view — it resolves each table through the catalog and handles httpfs, the partitioned tables, the supplemental opt-in and local caching (content-addressed, so a table unchanged between releases is cached once):

# remotes::install_github("calcofi/calcofi4r")
library(calcofi4r)

con <- cc_get_db()                       # promoted release, tables as views
con <- cc_get_db("v2026.08.25")          # pinned; a retired version errors, naming the replacement
DBI::dbListTables(con)

# lazy dbplyr against the remote parquet
library(dplyr)
tbl(con, "taxon") |> filter(scientific_name == "Sardinops sagax")

# or a one-off SQL query
cc_query("SELECT dataset_key, count(*) AS n FROM obs GROUP BY 1 ORDER BY 2 DESC")

To hand a table’s URLs to DuckDB (or anything else) yourself, resolve them through the catalog rather than building a path — cc_release_sources() is the one place a catalog entry becomes URLs, for any version:

library(DBI)
cat_ <- cc_catalog("latest")              # or a pinned "vYYYY.MM.DD"
src  <- cc_release_sources(cat_, "obs")   # list(urls, hive, canonical, hashes, local_paths, single_file)
src$urls                                  # one https URL per partition (the legacy path or s3 glob before v2026.09)
src$single_file                           # obs's whole-table twin — read it OR src$urls, never both

con <- dbConnect(duckdb::duckdb())
dbExecute(con, "INSTALL httpfs; LOAD httpfs;")
d <- dbGetQuery(con, sprintf(
  "SELECT scientific_name, common_name, worms_id
   FROM %s
   WHERE common_name ILIKE '%%sardine%%'",
  cc_read_parquet_sql(cc_release_sources(cat_, "taxon"))))   # read_parquet('…') or read_parquet([...], hive_partitioning = true)

6.7 From Python

calcofi4py registers every release table as a DuckDB view for you (partitioned + supplemental handling included), mirroring calcofi4r:

# pip install "calcofi4py @ git+https://github.com/CalCOFI/calcofi4py"
import calcofi4py as cc
con = cc.cc_get_db()                    # promoted release; cc_get_db("v2026.08.25") to pin
df  = con.sql("SELECT * FROM taxon WHERE common_name ILIKE '%sardine%'").df()
cc.cc_query("SELECT count(*) FROM obs").fetchone()

ImportantApply the quality flags

obs.measurement_qual carries each dataset’s own flag vocabulary, uninterpreted — bottle (6 = OK but from CTD, 8 = suspect, 9 = missing), CTD cast files (1/2 = use the primary/secondary sensor, 8 = questionable, 9 = bad/missing), DIC WOCE (2 = good, 3 = questionable, 4 = bad, 9 = missing); see metadata/measurement_qual.csv. A flagged value is still a row. Filter it yourself, or use the one predicate the apps use:

calcofi4r::cc_qual_ok_sql("o")     # append to any WHERE over obs / obs_ctd_full / sample_measurement

calcofi4py.qual_ok_sql("o")

-- what both expand to (NULL-safe: an unflagged row is kept)
AND COALESCE(NOT ((o.dataset_key IN ('calcofi_bottle', 'calcofi_ctd-cast')
                     AND regexp_replace(o.measurement_qual, '\.0+$', '') IN ('8', '9'))
               OR (o.dataset_key = 'calcofi_dic' AND o.measurement_qual IN ('3', '4', '9'))), TRUE)

Or plain duckdb, resolving the URLs through the catalog yourself (release_sources() mirrors calcofi4r::cc_release_sources(); a retired version raises RetiredVersionError, whose .to is the version to use instead):

import duckdb
import calcofi4py as cc

catalog = cc.cc_catalog("latest")                 # or "v2026.08.25"
src     = cc.release_sources(catalog, "taxon")    # {"urls", "hive", "canonical", "hashes", "local_paths", "single_file"}

con = duckdb.connect()
con.sql("INSTALL httpfs; LOAD httpfs;")
df = con.sql(f"""
  SELECT scientific_name, common_name, worms_id
  FROM {cc.read_parquet_sql(src)}
  WHERE common_name ILIKE '%sardine%'
""").df()

Without the package the same resolution is a few lines of json: fetch releases/{version}/catalog.json and prefix each table’s objects[].path with https://storage.googleapis.com/calcofi-db/, as the browser example below does.

6.8 From your browser

DuckDB compiles to WebAssembly, so the same engine runs client-side in your browser — no server, no install, no R or Python. CalCOFI ships a ready-made form at → Open CalCOFI Query that wraps the three retired bio↔︎env Plumber endpoints (and a free-form SQL shell): pick a function, fill the form, click Run, and a Chromium / Firefox / Safari worker thread fetches Parquet range-by-range over HTTPS and joins them in-browser.

To embed DuckDB-WASM in your own page, the boilerplate is about 15 lines — load the bundle from a CDN, instantiate, INSTALL httpfs and you’re ready:

<script type="module">
  import * as duckdb from "https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.29.0/+esm";

  const bundles = duckdb.getJsDelivrBundles();
  const bundle  = await duckdb.selectBundle(bundles);
  const worker  = await duckdb.createWorker(bundle.mainWorker);
  const db      = new duckdb.AsyncDuckDB(new duckdb.ConsoleLogger(), worker);
  await db.instantiate(bundle.mainModule, bundle.pthreadWorker);
  const conn = await db.connect();
  await conn.query("INSTALL httpfs; LOAD httpfs;");
  await conn.query("INSTALL spatial; LOAD spatial;");

  const root    = "https://storage.googleapis.com/calcofi-db";
  const version = (await (await fetch(`${root}/ducklake/releases/latest.txt`)).text()).trim();
  const catalog = await (await fetch(`${root}/ducklake/releases/${version}/catalog.json`)).json();

  // resolve a table through the catalog: objects[].path → https URL. A partitioned
  // table lists one object per partition (needs hive_partitioning) and may also
  // publish a whole-table twin — the object WITHOUT partition_by (obs does); a
  // browser reads the twin instead of the list, never both (that doubles every row).
  // catalogs before v2026.09 have no objects[] — fall back to the per-release path
  const readParquet = (name) => {
    const t    = catalog.tables.find(t => t.name === name);
    const objs = t.objects ?? [{ path: `ducklake/releases/${version}/parquet/${name}.parquet` }];
    const twin = t.partitioned ? objs.find(o => !o.partition_by) : null;
    if (twin) return `read_parquet('${root}/${twin.path}')`;
    const urls = objs.map(o => `'${root}/${o.path}'`).join(", ");
    return `read_parquet([${urls}]${t.partitioned ? ", hive_partitioning = true" : ""})`;
  };

  const result = await conn.query(`
    SELECT scientific_name, common_name, worms_id
    FROM ${readParquet("taxon")}
    WHERE common_name ILIKE '%sardine%'
  `);
  console.log(result.toArray());
</script>

For arbitrary one-off SQL with no setup at all, paste a query into shell.duckdb.org — DuckDB’s official DuckDB-WASM shell. Same WebAssembly engine, same public Parquet, identical rows.

6.9 Run it from anywhere

The same portable SQL — what calcofi4r::cc_match_*() emits as attr(d, "sql"), what the Integrated App download bundle ships in its query/ folder, what is shown in the worked example below — runs in any DuckDB client:

Where	How
R, on your laptop	calcofi4r::cc_match_ichthyo_by_name(...) — emits and runs the SQL; attr(d, "sql") hands it back
Python, on a notebook server	duckdb.connect().sql(open("query.sql").read()).df() — see From Python
shell, on the command line	duckdb < query.sql
your web browser, no install	CalCOFI Query — point-and-click form, runs DuckDB-WASM client-side

6.10 Reproducibility

Because every query is plain SQL against immutable, versioned, public Parquet, a CalCOFI result is reproducible by anyone — pin the {version} and re-run the SQL. Pin a consolidated version: its bytes are kept indefinitely, and a later release that shares an object with it shares the same bytes.

The Integrated App builds on this: its data download bundle ships a query/ folder alongside the data:

data/original/{bio,env}.csv          ← query/{bio,env}.sql
data/integrated/integrated_*.csv     ← query/integrated_*.sql
query/manifest.json                  release version, filters, GCS source URLs,
                                     per-file row counts + md5 checksums
query/REPRODUCE.md                   DuckDB-CLI / Python / R re-run snippets

Each *.sql file is fully interpolated, GCS-URL-based, and copy-paste runnable (prefixed with the INSTALL/LOAD it needs). Re-run query/integrated_*.sql in DuckDB and you get back exactly the rows in the matching .csv — the manifest.json md5 checksums let you confirm it. The same SQL is what calcofi4r::cc_match_bio_env() executes and attaches as attr(x, "sql"), what the browser-based CalCOFI Query generates as its SQL panel, and what a hand-written query produces — a single source of truth across R, Python, the CLI and JavaScript.

6.11 Worked example: sardine larvae + temperature

The recurring example through these pages is Pacific sardine (Sardinops sagax) larvae matched to CTD-bottle temperature, Q1 2018, with relaxed (5 km / 72 hr) matching.

Note

Q1 2018 — not a more recent year — because CTD-bottle environmental data in the current release ends 2021-05, while net-tow biological data runs later. Q1 2018 has ample overlap of both.

Done as direct SQL, the query is a temporal interval join plus a spatial ST_Distance_Sphere filter. After the INSTALL/LOAD setup above, run the query below. This block is character-for-character what calcofi4r::cc_match_ichthyo_by_name() returns as attr(d, "sql") with version = "v2026.08.25" — the two produce the identical 13 rows. Every table in it was resolved through that release’s catalog, and because v2026.08.25 is consolidated the URLs stay valid. On this pre-v2026.09 release the partitioned obs resolves to an s3:// glob, so the query needs the anonymous-s3 settings; from v2026.09 the same call emits an explicit HTTPS list that runs anywhere, the browser included. The Integrated App download bundle builds query/integrated_*.sql with the same cc_match_bio_env() engine (its filter set is taxa + quarters + dates rather than life_stage, so a sardine / Q1 2018 bundle returns the egg + larva superset — same matching mechanics, same reproducibility):

WITH bio AS (
SELECT
  o.obs_id::VARCHAR AS bio_id,
  o.datetime AS bio_datetime,
  o.longitude AS bio_lon,
  o.latitude AS bio_lat,
  o.measurement_value * shf.measurement_value / nullif(ps.measurement_value, 0) AS bio_value,
  o.measurement_value AS tally,
  o.taxon_key,
  t.scientific_name,
  t.worms_id,
  o.life_stage
FROM read_parquet('s3://calcofi-db/ducklake/releases/v2026.08.25/parquet/obs/**/*.parquet', hive_partitioning = true) o
JOIN read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/taxon.parquet') t ON t.taxon_key = o.taxon_key
LEFT JOIN read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/sample_measurement.parquet') shf ON shf.sample_key = o.sample_key AND shf.measurement_type = 'std_haul_factor'
LEFT JOIN read_parquet('https://storage.googleapis.com/calcofi-db/ducklake/releases/v2026.08.25/parquet/sample_measurement.parquet') ps ON ps.sample_key = o.sample_key AND ps.measurement_type = 'prop_sorted'
WHERE o.realm = 'bio'
    AND o.dataset_key = 'swfsc_ichthyo'
    AND o.measurement_type = 'abundance'
    AND o.measurement_value IS NOT NULL
    AND COALESCE(NOT ((o.dataset_key = 'calcofi_bottle' AND regexp_replace(o.measurement_qual, '\.0+$', '') IN ('8', '9')) OR (o.dataset_key = 'calcofi_ctd-cast' AND regexp_replace(o.measurement_qual, '\.0+$', '') IN ('8', '9')) OR (o.dataset_key = 'calcofi_dic' AND regexp_replace(o.measurement_qual, '\.0+$', '') IN ('3', '4', '9'))), TRUE)
    AND o.datetime IS NOT NULL
    AND o.longitude IS NOT NULL
    AND o.latitude IS NOT NULL
    AND t.scientific_name IN ('Sardinops sagax')
    AND o.life_stage IN ('larva')
    AND o.datetime >= TIMESTAMP '2018-01-01'
    AND o.datetime <= TIMESTAMP '2018-03-31'
),
env AS (
SELECT
  obs_id AS env_id,
  datetime AS env_datetime,
  longitude AS env_lon,
  latitude AS env_lat,
  measurement_value AS env_value,
  depth_min_m AS env_depth_m,
  measurement_type AS measurement_type
FROM read_parquet('s3://calcofi-db/ducklake/releases/v2026.08.25/parquet/obs/**/*.parquet', hive_partitioning = true)
WHERE realm = 'env'
    AND measurement_type = 'temperature'
    AND measurement_value IS NOT NULL
    AND COALESCE(NOT ((dataset_key = 'calcofi_bottle' AND regexp_replace(measurement_qual, '\.0+$', '') IN ('8', '9')) OR (dataset_key = 'calcofi_ctd-cast' AND regexp_replace(measurement_qual, '\.0+$', '') IN ('8', '9')) OR (dataset_key = 'calcofi_dic' AND regexp_replace(measurement_qual, '\.0+$', '') IN ('3', '4', '9'))), TRUE)
    AND datetime IS NOT NULL
    AND longitude IS NOT NULL
    AND latitude IS NOT NULL
    AND datetime >= TIMESTAMP '2018-01-01' - INTERVAL '72 hours'
    AND datetime <= TIMESTAMP '2018-03-31' + INTERVAL '72 hours'
),
matched AS (
  -- temporal interval join: every env observation within ± max_time_hr
  SELECT
    bio.*,
    env.* EXCLUDE (env_lon, env_lat),
    abs(epoch(bio.bio_datetime) - epoch(env.env_datetime)) / 3600.0 AS time_diff_hr,
    ST_Distance_Sphere(
      ST_Point(bio.bio_lon, bio.bio_lat),
      ST_Point(env.env_lon, env.env_lat)) / 1000.0                  AS dist_km
  FROM bio
  JOIN env
    ON env.env_datetime BETWEEN bio.bio_datetime - INTERVAL '72 hours'
                            AND bio.bio_datetime + INTERVAL '72 hours'
),
within AS (
  -- spatial filter: keep pairs within max_dist_km
  SELECT * FROM matched
  WHERE dist_km <= 5
),
ranked AS (
  SELECT
    *,
    min(time_diff_hr) OVER (PARTITION BY bio_id) AS mn_time_diff_hr,
    min(dist_km)      OVER (PARTITION BY bio_id) AS mn_dist_km
  FROM within
)
-- one row per bio observation (× measurement_type): env values aggregated
SELECT
  * EXCLUDE (
    env_id, env_value, env_datetime, env_depth_m,
    time_diff_hr, dist_km, mn_time_diff_hr, mn_dist_km),
  count(*)                                            AS n_env,
  avg(env_value)                                      AS env_value,
  CASE WHEN count(*) = 1 THEN 0
       ELSE coalesce(stddev_samp(env_value), 0) END   AS env_value_sd,
  avg(env_depth_m)                                    AS env_depth_m,
  min(env_datetime)                                   AS env_datetime_min,
  max(env_datetime)                                   AS env_datetime_max,
  avg(dist_km)                                        AS dist_km,
  avg(time_diff_hr)                                   AS time_diff_hr
FROM ranked
WHERE time_diff_hr = mn_time_diff_hr
GROUP BY ALL
ORDER BY bio_id

The next page, Matching Helpers, shows this same query as a one-liner.