calcofi4r

Introduction

The calcofi4r package provides access to the CalCOFI (California Cooperative Oceanic Fisheries Investigations) database, which contains over 75 years of oceanographic and biological data from the California Current ecosystem.

The database is organized into a tidy relational structure stored as Parquet files on Google Cloud Storage. This allows fast queries without downloading the entire database.

Connect to the Database

library(calcofi4r)
library(dplyr)
library(DBI)
library(sf)
library(mapview)

# connect to the latest CalCOFI database release
con <- cc_get_db()
q <- dbExecute(con, "INSTALL spatial; LOAD spatial;")

# list available tables
dbListTables(con)
#>  [1] "climatology"        "cruise"             "dataset"           
#>  [4] "dataset_taxon"      "fish"               "grid"              
#>  [7] "grid_crosswalk"     "lookup"             "measurement_type"  
#> [10] "obs"                "obs_attribute"      "obs_bio"           
#> [13] "obs_env"            "region"             "sample"            
#> [16] "sample_measurement" "sample_spatial"     "ship"              
#> [19] "spatial"            "spatial_attribute"  "taxon"             
#> [22] "taxon_group"

Convenience Functions

The package provides convenience functions for common operations:

# list available versions
cc_list_versions()
#> # A tibble: 38 × 9
#>    version     release_date tables total_rows size_mb doi           consolidated
#>    <chr>       <chr>         <int>      <int>   <dbl> <chr>         <lgl>       
#>  1 v2026.10.14 2026-10-14       25  375348707   2620. 10.5281/zeno… FALSE       
#>  2 v2026.10.13 2026-10-13       25  374740011   2614  10.5281/zeno… FALSE       
#>  3 v2026.10.08 2026-10-08       25  373376204   2605. 10.5281/zeno… FALSE       
#>  4 v2026.10.06 2026-10-06       25  373467857   2606. 10.5281/zeno… FALSE       
#>  5 v2026.10.05 2026-10-05       25  373474855   2606. 10.5281/zeno… FALSE       
#>  6 v2026.10.01 2026-10-01       23  375505833   2603. 10.5281/zeno… FALSE       
#>  7 v2026.09.11 2026-09-11       23  362124968   2497. 10.5281/zeno… FALSE       
#>  8 v2026.09.10 2026-09-11       23  356635055   2462. 10.5281/zeno… FALSE       
#>  9 v2026.09.06 2026-09-06       23  348657010   2350. 10.5281/zeno… FALSE       
#> 10 v2026.09.04 2026-09-04       23  348657010   2351. 10.5281/zeno… FALSE       
#> # ℹ 28 more rows
#> # ℹ 2 more variables: retired <df[,3]>, is_latest <lgl>

# list tables (con = reuses the connection opened above)
cc_list_tables(con = con)
#>  [1] "climatology"        "cruise"             "dataset"           
#>  [4] "dataset_taxon"      "fish"               "grid"              
#>  [7] "grid_crosswalk"     "lookup"             "measurement_type"  
#> [10] "obs"                "obs_attribute"      "obs_bio"           
#> [13] "obs_env"            "region"             "sample"            
#> [16] "sample_measurement" "sample_spatial"     "ship"              
#> [19] "spatial"            "spatial_attribute"  "taxon"             
#> [22] "taxon_group"

# describe a table
cc_describe_table("obs", con = con)
#> # A tibble: 18 × 6
#>    column_name       data_type is_nullable name_long        units description_md
#>    <chr>             <chr>     <chr>       <chr>            <chr> <chr>         
#>  1 obs_id            BIGINT    YES         Observation ID   NA    Surrogate key…
#>  2 realm             VARCHAR   YES         Realm            NA    `env` for phy…
#>  3 dataset_key       VARCHAR   YES         Dataset Key      NA    Provenance st…
#>  4 sample_key        VARCHAR   YES         Sample Key       NA    The sampling …
#>  5 grid_key          VARCHAR   YES         Grid Key         NA    CalCOFI stati…
#>  6 cruise_key        VARCHAR   YES         Cruise Key       NA    Cruise, denor…
#>  7 latitude          DOUBLE    YES         Latitude         deci… Observation l…
#>  8 longitude         DOUBLE    YES         Longitude        deci… Observation l…
#>  9 datetime          TIMESTAMP YES         Datetime         NA    Observation t…
#> 10 depth_min_m       DOUBLE    YES         Depth Min        m     Shallowest de…
#> 11 depth_max_m       DOUBLE    YES         Depth Max        m     Deepest depth…
#> 12 taxon_key         VARCHAR   YES         Taxon Key        NA    Global taxon …
#> 13 life_stage        VARCHAR   YES         Life Stage       NA    Life stage wh…
#> 14 measurement_type  VARCHAR   YES         Measurement Type NA    Measured quan…
#> 15 measurement_value DOUBLE    YES         Measurement Val… NA    The measured …
#> 16 measurement_qual  VARCHAR   YES         Measurement Qua… NA    Source qualit…
#> 17 measurement_prec  DOUBLE    YES         Measurement Pre… NA    Source-report…
#> 18 hex_id            UBIGINT   YES         Hex ID           NA    H3 cell at re…

# list measurement types
cc_list_measurement_types(con = con) |> head(10)
#> # A tibble: 10 × 3
#>    measurement_type    description                                         units
#>    <chr>               <chr>                                               <chr>
#>  1 abundance           Specimen count per net tow (headline occurrence; s… count
#>  2 acoustic_presence   Presence (1) or absence (0) of the taxon's calls i… dime…
#>  3 air_temp_c          Air temperature                                     deg_C
#>  4 alkalinity          Total alkalinity                                    umol…
#>  5 alkalinity_rep1     Total alkalinity replicate 1                        umol…
#>  6 alkalinity_rep2     Total alkalinity replicate 2                        umol…
#>  7 ammonia             Ammonium concentration (QC'd; the source column is… umol…
#>  8 anchovy_eggs        Northern anchovy egg count                          count
#>  9 atm_pressure_mb     Atmospheric pressure (ship level)                   mb   
#> 10 atm_pressure_slc_mb Atmospheric pressure (sea-level corrected)          mb

Read Data Directly

Convenience functions return tibbles with optional filtering:

# taxonomy: one row per taxon, keyed worms:<id> or itis:<id>
taxa <- cc_read_taxon()
head(taxa)
#> # A tibble: 6 × 19
#>   taxon_key worms_id itis_id gbif_id ncbi_id inat_id scientific_name common_name
#>   <chr>        <int>   <int>   <int>   <int>   <int> <chr>           <chr>      
#> 1 calcofi_…       NA      NA      NA      NA      NA NA              NA         
#> 2 calcofi_…       NA      NA      NA      NA      NA NA              NA         
#> 3 calcofi_…       NA      NA      NA      NA      NA NA              NA         
#> 4 calcofi_…       NA      NA      NA      NA      NA NA              NA         
#> 5 calcofi_…       NA      NA      NA      NA      NA NA              NA         
#> 6 calcofi_…       NA      NA      NA      NA      NA NA              NA         
#> # ℹ 11 more variables: rank <chr>, rank_order <int>, taxonomic_status <chr>,
#> #   status_checked <chr>, parent_taxon_key <chr>, kingdom <chr>, phylum <chr>,
#> #   class <chr>, order_taxon <chr>, family <chr>, notes <chr>

# ichthyoplankton occurrences
ichthyo_sample <- cc_read_ichthyo() |> head(100)
head(ichthyo_sample)
#> # A tibble: 6 × 18
#>     obs_id realm dataset_key   sample_key grid_key cruise_key latitude longitude
#>      <dbl> <chr> <chr>         <chr>      <chr>    <chr>         <dbl>     <dbl>
#> 1 33305725 bio   swfsc_ichthyo swfsc_ich… NA       2003-10-3…     9.41     -99.2
#> 2 33321960 bio   swfsc_ichthyo swfsc_ich… NA       2000-10-3…     7.74     -82.1
#> 3 33311416 bio   swfsc_ichthyo swfsc_ich… NA       2002-10-3…    28.0     -178. 
#> 4 32892421 bio   swfsc_ichthyo swfsc_ich… st20-ln… 1965-07-3…    26.6     -113. 
#> 5 33015488 bio   swfsc_ichthyo swfsc_ich… st40-ln… 1966-08-3…    26.9     -114. 
#> 6 32923125 bio   swfsc_ichthyo swfsc_ich… st30-ln… 1969-07-3…    32.8     -118. 
#> # ℹ 10 more variables: datetime <dttm>, depth_min_m <dbl>, depth_max_m <dbl>,
#> #   taxon_key <chr>, life_stage <chr>, measurement_type <chr>,
#> #   measurement_value <dbl>, measurement_qual <chr>, measurement_prec <dbl>,
#> #   hex_id <dbl>

Database Schema

The database used to expose one table triple per dataset — ichthyo, bottle, bottle_measurement, casts, species and roughly thirty more. Those are gone. Every dataset now projects into one small core family, so a query written against ichthyoplankton works unchanged against CTD, zooplankton or seabirds:

• obs — the occurrence table, one scalar per row. realm is bio or env; biology carries taxon_key, environment carries a measurement type.
• sample — one row per physical sampling event (cast, tow, net, bottle, transect), with an adjacency list via parent_sample_key / root_sample_key.
• obs_attribute — sub-occurrence detail: length and stage frequency, plus categorical behaviour.
• sample_measurement — event-level effort (volume sampled, haul factor, cast conditions).

Shared references: taxon (one row per taxon, keyed worms:<id> or itis:<id>), dataset_taxon (each dataset’s vocabulary crosswalked to it), taxon_group, cruise, ship, grid, measurement_type, spatial / spatial_attribute, region, dataset, lookup.

Full column-level reference: calcofi.io/schema.

# show row counts for each table
tables <- dbListTables(con)
tibble(
  table = tables,
  rows  = sapply(tables, function(t) {
    dbGetQuery(con, sprintf("SELECT COUNT(*) as n FROM %s", t))$n
  })) |>
  arrange(desc(rows))
#> # A tibble: 22 × 2
#>    table                  rows
#>    <chr>                 <dbl>
#>  1 obs                33326406
#>  2 obs_env            31739611
#>  3 sample_spatial      1994263
#>  4 climatology         1828215
#>  5 obs_bio             1586795
#>  6 sample              1467016
#>  7 sample_measurement   658009
#>  8 obs_attribute        455300
#>  9 spatial_attribute     37943
#> 10 fish                  10796
#> # ℹ 12 more rows

Query Environmental Data

Environmental measurements live in obs alongside biology, distinguished by realm, in a long format: one row per (sample, depth, measurement type). Position and time come from sample.

# surface temperature with location and depth
d_temp <- dbGetQuery(con, "
  SELECT
    s.longitude AS lon,
    s.latitude  AS lat,
    s.datetime,
    o.depth_min_m AS depth_m,
    o.measurement_value AS temperature
  FROM obs o
  JOIN sample s USING (sample_key)
  WHERE o.measurement_type = 'temperature'
    AND o.measurement_value IS NOT NULL
    AND o.depth_min_m <= 10
  LIMIT 100000")

head(d_temp)
#>         lon      lat            datetime depth_m temperature
#> 1 -107.9750 23.30000 1956-02-06 17:54:00      10       20.24
#> 2 -122.8167 32.93333 1949-09-17 21:30:00       0       18.90
#> 3 -122.8167 32.93333 1949-09-17 21:30:00       8       18.48
#> 4 -107.5167 23.55000 1956-02-06 22:36:00       0       22.00
#> 5 -107.5167 23.55000 1956-02-06 22:36:00       9       21.37
#> 6 -107.5167 23.55000 1956-02-06 22:36:00      10       21.34
nrow(d_temp)
#> [1] 93985

Summarize by Location

# summarize surface temperature by location
d_t <- d_temp |>
  group_by(lon, lat) |>
  summarize(
    n     = n(),
    t_avg = mean(temperature, na.rm = TRUE),
    .groups = "drop") |>
  filter(!is.na(lon), !is.na(lat)) |>
  st_as_sf(coords = c("lon", "lat"), crs = 4326, remove = FALSE)

head(d_t)
#> Simple feature collection with 6 features and 4 fields
#> Geometry type: POINT
#> Dimension:     XY
#> Bounding box:  xmin: -164.0833 ymin: 20.05 xmax: -150.025 ymax: 42.38333
#> Geodetic CRS:  WGS 84
#> # A tibble: 6 × 5
#>     lon   lat     n t_avg             geometry
#>   <dbl> <dbl> <int> <dbl>          <POINT [°]>
#> 1 -164.  42       2  10.1       (-164.0833 42)
#> 2 -158.  42.4    16  16.7 (-157.9833 42.36667)
#> 3 -158.  42.4     4  11.8 (-157.9833 42.38333)
#> 4 -153.  20.1     4  24.2    (-153.1 20.10833)
#> 5 -150.  31.2     3  23.1     (-150.1167 31.2)
#> 6 -150.  20.0     4  24.3     (-150.025 20.05)

CalCOFI Grid

The CalCOFI grid has one cell per official station: the Voronoi tessellation of the 113 official station positions, ending at the coastline, beside the historical cells beyond the official pattern, kept as they were. The package includes pre-loaded grid data:

• cc_grid - station cells (one per official station) and the kept historical cells
• cc_grid_ctrs - the station of each cell
• cc_grid_zones - aggregated zones by station pattern
• cc_station_positions - the official station table
• cc_grid_v1 - the previous grid (database releases through v2026.10.01)

cc_grid_key(lon, lat) returns the cell a position falls in.

The maps below pipe each mapview() through cc_vector_basemap(): mapview’s default basemaps start with CARTO’s Positron and Dark Matter, whose raster tiles draw “API KEY REQUIRED” since Sep 2026, and cc_vector_basemap() draws them from CARTO’s vector tiles instead.

# show the CalCOFI grid colored by zone
(mapview(cc_grid, zcol = "zone_key", layer.name = "Zone") +
  mapview(cc_grid_ctrs, cex = 1, col.regions = "black", legend = FALSE)) |>
  cc_vector_basemap()

Grid from Database

The grid is also available in the database with additional attributes:

# query grid from database (includes geometry)
grid_db <- dbGetQuery(con, "SELECT * EXCLUDE(geom, geom_ctr) FROM grid")
head(grid_db)
#>           grid_key station line     shore    pattern spacing
#> 1 st-20-ln130_hist     -20  130 nearshore historical      20
#> 2 st-20-ln140_hist     -20  140 nearshore historical      20
#> 3 st-20-ln150_hist     -20  150 nearshore historical      20
#> 4 st-20-ln160_hist     -20  160 nearshore historical      20
#> 5 st-40-ln160_hist     -40  160 nearshore historical      20
#> 6    st0-ln10_hist       0   10 nearshore historical      20
#>                   zone  area_km2
#> 1 nearshore-historical  2067.983
#> 2 nearshore-historical 10696.875
#> 3 nearshore-historical 21747.087
#> 4 nearshore-historical 31989.836
#> 5 nearshore-historical  2970.610
#> 6 nearshore-historical 23158.491

Show Effort by Grid Cell

Join temperature observations to the CalCOFI grid to show sampling effort:

# count observations per grid cell
n_grid <- cc_grid |>
  st_join(d_t) |>
  group_by(sta_key) |>
  summarize(n = sum(n, na.rm = TRUE))

mapview(n_grid, zcol = "n", layer.name = "Observations") |>
  cc_vector_basemap()

Show Effort by Station Point

# join counts to centroids
n_pts <- cc_grid_ctrs |>
  left_join(
    n_grid |> st_drop_geometry() |> select(sta_key, n),
    by = "sta_key")

mapview(n_pts, cex = "n", layer.name = "Observations") |>
  cc_vector_basemap()

Map Contours

Interpolate temperature data to create contour maps using Inverse Distance Weighting (IDW).

All Zones

# interpolate points to raster using IDW
r_all <- pts_to_rast_idw(d_t, "t_avg", cc_grid_zones)

# generate contour polygons
p_all <- rast_to_contours(r_all, cc_grid_zones)
mapview(p_all, zcol = "z_avg", layer.name = "Temp (C)") |>
  cc_vector_basemap()

Standard and Extended Pattern

# filter to standard + extended zones
aoi_ext <- cc_grid_zones |>
  filter(sta_pattern %in% c("standard", "extended"))

# interpolate and contour
r_ext <- pts_to_rast_idw(d_t, "t_avg", aoi_ext)
p_ext <- rast_to_contours(r_ext, aoi_ext)
mapview(p_ext, zcol = "z_avg", layer.name = "Temp (C)") |>
  cc_vector_basemap()

Query Ichthyoplankton Data

The ichthyoplankton survey counts fish larvae by species across sampling sites.

# top 10 taxa by total count. `taxon_key` is global, so the same join works for
# any dataset — swap the dataset_key and this counts euphausiids or seabirds.
top_species <- dbGetQuery(con, "
  SELECT
    t.scientific_name,
    t.common_name,
    SUM(o.measurement_value)      AS total_count,
    COUNT(DISTINCT o.sample_key)  AS n_samples
  FROM obs o
  JOIN taxon t USING (taxon_key)
  WHERE o.realm = 'bio'
    AND o.dataset_key = 'swfsc_ichthyo'
  GROUP BY 1, 2
  ORDER BY total_count DESC
  LIMIT 10")

top_species
#>              scientific_name                common_name total_count n_samples
#> 1                  Teleostei       Unidentified Teliost     9016352     61933
#> 2           Engraulis mordax           Northern anchovy     6415353     29497
#> 3            Sardinops sagax Pacific sardine (pilchard)      902351      9883
#> 4       Merluccius productus    Pacific hake or whiting      881780     12872
#> 5       Vinciguerria lucetia           Panama lightfish      422288     14858
#> 6                   Sebastes                 Rockfishes      240613     18574
#> 7      Trachurus symmetricus              Jack mackerel      193469      9564
#> 8      Leuroglossus stilbius    California smoothtongue      163247     12669
#> 9  Stenobrachius leucopsarus          Northern lampfish      142382     12919
#> 10     Triphoturus mexicanus           Mexican lampfish      138132     14610

Species Distribution

Map the distribution of a common species:

# get Northern Anchovy observations with locations
# `sample` replaces the old site -> tow -> net chain: obs joins it directly.
anchovy <- dbGetQuery(con, "
  SELECT
    s.latitude  AS lat,
    s.longitude AS lon,
    SUM(o.measurement_value) AS count
  FROM obs o
  JOIN taxon  t USING (taxon_key)
  JOIN sample s USING (sample_key)
  WHERE t.scientific_name = 'Engraulis mordax'
    AND o.realm = 'bio'
  GROUP BY 1, 2") |>
  filter(!is.na(lon), !is.na(lat)) |>
  st_as_sf(coords = c("lon", "lat"), crs = 4326)

mapview(anchovy, cex = "count", layer.name = "Anchovy count") |>
  cc_vector_basemap()

Cruise Timeline

View the temporal coverage of CalCOFI cruises:

# get cruise timeline (date_ym is YYYYMM format)
cruises <- dbGetQuery(con, "
  SELECT
    cruise_key,
    CAST(SUBSTRING(CAST(date_ym AS VARCHAR), 1, 4) AS INTEGER) as year,
    CAST(SUBSTRING(CAST(date_ym AS VARCHAR), 5, 2) AS INTEGER) as month,
    ship_key
  FROM cruise
  WHERE date_ym IS NOT NULL
  ORDER BY date_ym")

# count cruises by year
cruises |>
  count(year) |>
  filter(!is.na(year)) |>
  ggplot2::ggplot(ggplot2::aes(year, n)) +
  ggplot2::geom_col(fill = "steelblue") +
  ggplot2::labs(
    title = "CalCOFI Cruises by Year",
    x = "Year", y = "Number of Cruises") +
  ggplot2::theme_minimal()

Available Measurement Types

measurement_type is the shared vocabulary across every dataset, with units and the physical bounds each value is validated against:

# list all measurement types
dbGetQuery(con, "
  SELECT measurement_type, description, units
  FROM measurement_type
  ORDER BY measurement_type") |>
  head(20)
#>       measurement_type
#> 1            abundance
#> 2    acoustic_presence
#> 3           air_temp_c
#> 4           alkalinity
#> 5      alkalinity_rep1
#> 6      alkalinity_rep2
#> 7              ammonia
#> 8         anchovy_eggs
#> 9      atm_pressure_mb
#> 10 atm_pressure_slc_mb
#> 11 barometric_pressure
#> 12    beam_attenuation
#> 13            behavior
#> 14         body_length
#> 15        bottom_depth
#> 16      bottom_depth_m
#> 17   bottom_depth_mb_m
#> 18        btl_ammonium
#> 19   btl_chlorophyll_a
#> 20           btl_depth
#>                                                                                                                                                                                        description
#> 1                                                                                                                Specimen count per net tow (headline occurrence; standardize via std_haul_factor)
#> 2  Presence (1) or absence (0) of the taxon's calls in one hour of sonobuoy recording, scanned as 60-s spectrogram windows (headline occurrence; the call types heard are obs_attribute call_type)
#> 3                                                                                                                                                                                  Air temperature
#> 4                                                                                                                                                                                 Total alkalinity
#> 5                                                                                                                                                                     Total alkalinity replicate 1
#> 6                                                                                                                                                                     Total alkalinity replicate 2
#> 7                                                                                                                                    Ammonium concentration (QC'd; the source column is named NH3)
#> 8                                                                                                                                                                       Northern anchovy egg count
#> 9                                                                                                                                                                Atmospheric pressure (ship level)
#> 10                                                                                                                                                      Atmospheric pressure (sea-level corrected)
#> 11                                                                                                                                                                             Barometric pressure
#> 12                                                                                                                                                                    Beam attenuation coefficient
#> 13                                                                                                                   Seabird/marine-mammal behavior category (obs_attribute; e.g. Flying, Feeding)
#> 14                                                                                                                                                   Larva body length (obs_freq binned attribute)
#> 15                                                                                                                            Water depth at the sampling event (sea floor depth beneath the cast)
#> 16                                                                                                                                                                      Bottom depth (single-beam)
#> 17                                                                                                                                                                        Bottom depth (multibeam)
#> 18                                                                                                                                                                                 Bottle ammonium
#> 19                                                                                                                                                                            Bottle chlorophyll-a
#> 20                                                                                                                                                                               Bottle trip depth
#>            units
#> 1          count
#> 2  dimensionless
#> 3          deg_C
#> 4        umol/kg
#> 5        umol/kg
#> 6        umol/kg
#> 7         umol/L
#> 8          count
#> 9             mb
#> 10            mb
#> 11     millibars
#> 12           1/m
#> 13          <NA>
#> 14            mm
#> 15             m
#> 16             m
#> 17             m
#> 18        umol/L
#> 19          ug/L
#> 20             m

Disconnect

Always close the database connection when finished:

dbDisconnect(con)

Package Data Objects

The package also includes pre-loaded spatial data objects for convenience:

• cc_grid - CalCOFI station grid polygons (sf)
• cc_grid_ctrs - Station centroids (sf)
• cc_grid_zones - Aggregated zone polygons (sf)
• cc_bottle - Sample bottle data for examples (tibble)

See also vignette("bio-env-matching") for joining biology to environment, and vignette("ctd-temperature-anomalies") for transects, climatologies and anomalies.

These can be used without connecting to the database for quick spatial operations.