Build the model + dataset registry (`dataset` + `model` tables)

Published

2026-07-15 11:10:43

Assemble the dataset and model registry tables that the v8 ingests define implicitly — dataset properties in each ingest_*.qmd msens: frontmatter block, model properties in each {dist}/model_{ds}.csv. v7 wrote these tables inline in every ingest (INSERT INTO dataset/model); the v8 Parquet rewrite dropped those inserts, so the registry was never consolidated. Building it once here (rather than per-ingest) makes it a first-class target that fails loudly if an ingested dataset is missing its metadata — the single source of truth for STAC, the marine-atlas release, apps and the API.

Written to sdm.duckdb (dataset, model) and staged as Parquet for the release.

1 Design

Figure 1: Registry consolidation: dataset props from front-matter, model props from per-ingest CSVs + merged taxa

2 Setup

Code

librarian::shelf(DBI, dplyr, duckdb, fs, glue, here, jsonlite, logger, purrr, readr,
                 stringr, tibble, yaml, quiet = T)
source(here("libs/paths.R"))
options(readr.show_col_types = F)
if (!exists("%||%")) `%||%` <- function(a, b) if (is.null(a)) b else a
dir_atlas <- glue("{dir_big_v}/marine-atlas")
dir_dist  <- glue("{dir_atlas}/dist")
merge_db  <- glue("{dir_atlas}/merge.duckdb")
manifest  <- here("data/manifests/build_registry.json"); dir_create(path_dir(manifest))
stopifnot("run ingests first" = dir_exists(dir_dist), "run build_cell_grid first" = file_exists(sdm_db))

# canonical dataset order (merged first, then contributing datasets)
ds_order <- c("ms_merge","am","ca_nmfs","ch_nmfs","ch_fws","rng_fws","bl","rng_iucn",
              "rng_turtle_swot_dps","gm","nc")

3 `dataset` — from the `msens:` frontmatter (+ v7 metadata where keys match)

Code

# one row per ingest that declares a `msens: dataset:` block (the authoritative v8 props)
fm_dataset <- function(f) {
  txt <- readLines(f, warn = FALSE)
  d   <- which(txt == "---")
  if (length(d) < 2) return(NULL)
  m <- tryCatch(yaml.load(paste(txt[(d[1] + 1):(d[2] - 1)], collapse = "\n")), error = \(e) NULL)
  ds <- m$msens$dataset
  if (is.null(ds$ds_key)) return(NULL)
  tibble(ds_key = ds$ds_key, response_type = ds$response_type %||% NA,
         source_broad = ds$source_authority %||% NA, temporal_res = ds$temporal_interval %||% "static",
         native_format = ds$native_format %||% NA, qmd = path_file(f))
}
ds_fm <- map_dfr(dir_ls(".", glob = "ingest_*.qmd"), fm_dataset)

# the merged product is its own "dataset"
ds_fm <- bind_rows(ds_fm, tibble(
  ds_key = "ms_merge", response_type = "mixed", source_broad = "MarineSensitivity",
  temporal_res = "static", native_format = "parquet", qmd = "merge_models.qmd"))

# inherit rich metadata (name_short/description/citation/links/env dates) from v7 for
# matching keys; v8 frontmatter props override. am was `am_0.05` in v7.
v7 <- dbConnect(duckdb(glue("{dir_big}/{ver_prev}/sdm.duckdb"), read_only = TRUE))
d_v7 <- dbGetQuery(v7, "SELECT * FROM dataset") |>
  mutate(ds_key = if_else(ds_key == "am_0.05", "am", ds_key))
dbDisconnect(v7, shutdown = TRUE)

dataset <- ds_fm |>
  left_join(d_v7 |> select(-response_type, -source_broad, -temporal_res, -sort_order), by = "ds_key") |>
  mutate(
    name_short  = coalesce(name_short, glue("{source_broad} ({response_type})")),
    description = coalesce(description, name_short),
    spatial_res_deg = 0.05,
    sort_order  = match(ds_key, ds_order)) |>
  arrange(sort_order) |>
  relocate(ds_key, name_short, response_type, source_broad, temporal_res, native_format, sort_order)

# datasets that were actually ingested (have a dist partition) MUST have a dataset row
ingested <- dir_ls(dir_dist, type = "directory", glob = "*dataset=*") |> path_file() |> str_remove("dataset=")
missing_meta <- setdiff(ingested, dataset$ds_key)
if (length(missing_meta))
  stop("ingested datasets missing a `msens: dataset:` block: ", paste(missing_meta, collapse = ", "))
not_ingested <- setdiff(setdiff(dataset$ds_key, "ms_merge"), ingested)
if (length(not_ingested))
  log_warn("dataset(s) declared but NOT ingested (no dist partition): {paste(not_ingested, collapse=', ')}")

knitr::kable(dataset |> select(ds_key, name_short, response_type, source_broad, temporal_res, native_format, sort_order))

ds_key	name_short	response_type	source_broad	temporal_res	native_format	sort_order
ms_merge	Marine Sensitivity Merged Model, 2026-03-25	mixed	MarineSensitivity	static	parquet	1
am	AquaMaps suitability for all marine taxa (except birds) globally, 2019	suitability	AquaMaps	static	raster	2
ca_nmfs	NMFS Core Areas, 2019	range	NOAA NMFS	static	vector	3
ch_nmfs	NMFS Critical Habitat for USA, 2025-05-12	range	NOAA NMFS	static	vector	4
ch_fws	FWS Critical Habitat for USA, 2025-05-05	range	USFWS	static	vector	5
rng_fws	FWS Current Range Maps, 2025-07-23	range	USFWS	static	vector	6
bl	BirdLife Birds of the World, 2024	range	BirdLife	static	vector	7
rng_iucn	IUCN Red List Range Maps, 2025-2	range	IUCN	static	vector	8
rng_turtle_swot_dps	SWOT Global + NMFS DPS turtle ranges	range	SWOT/NOAA NMFS	static	vector	9
gm	NOAA SEFSC (density)	density	NOAA SEFSC	monthly	vector	10
nc	NOAA NCCOS (density)	density	NOAA NCCOS	seasonal	raster	11

4 `model` — union of every `model_{ds}.csv` (+ merged `ms_merge` models)

Code

# each ingest writes model_{ds}.csv with dataset-native columns; map to a common schema
# keyed by the stable mdl_key.
read_model_csv <- function(f) {
  ds <- str_match(path_file(f), "^model_(.+)\\.csv$")[, 2]
  d  <- read_csv(f) |> rename_with(tolower)
  pick <- function(cands) { hit <- intersect(tolower(cands), names(d)); if (length(hit)) d[[hit[1]]] else NA }
  tibble(
    mdl_key     = d$mdl_key,
    ds_key      = ds,
    sp_id       = as.character(pick(c("sp_key","sisid","sp_id","spcode","id_no","code"))),
    sci_name    = as.character(pick(c("taxa","sci_name","sciname","sciename","scientific_name"))),
    common_name = as.character(pick(c("common","comname","common_name"))),
    er_score    = suppressWarnings(as.numeric(pick(c("er_score")))))
}
model_raw <- map_dfr(dir_ls(dir_dist, glob = "*/model_*.csv") |> c(dir_ls(dir_dist, glob = "model_*.csv")),
                     read_model_csv) |> distinct(mdl_key, .keep_all = TRUE)

# merged per-taxon models (mdl_key = ms_merge_key) from the merge taxon
con <- dbConnect(duckdb(merge_db, read_only = TRUE))
model_merged <- dbGetQuery(con, "SELECT ms_merge_key AS mdl_key, scientific_name AS sci_name,
  er_score, sp_cat FROM taxon") |>
  transmute(mdl_key, ds_key = "ms_merge", sp_id = mdl_key, sci_name, common_name = NA_character_, er_score, sp_cat)
dbDisconnect(con, shutdown = TRUE)

model <- bind_rows(model_raw |> mutate(sp_cat = NA_character_), model_merged) |>
  mutate(ds_key = factor(ds_key, levels = ds_order)) |> arrange(ds_key, mdl_key) |>
  mutate(ds_key = as.character(ds_key))
# stable integer id per mdl_key: the compact partition key for the serving surface
# (serve/model_cell/mdl_id=*, so a titiler tile is a partition-pruned point read) and
# the mdl_key->mdl_id map the app/tile-URL builder joins on. dense_rank over the unique
# sorted mdl_key makes it a deterministic function of the model set (1..N).
model <- model |> mutate(mdl_id = dense_rank(mdl_key)) |> relocate(mdl_id, .after = mdl_key)
log_info("model registry: {nrow(model)} models ({n_distinct(model_raw$ds_key)} raw datasets + ms_merge)")
knitr::kable(model |> count(ds_key, name = "n_models"))

ds_key	n_models
am	23699
bl	10995
ca_nmfs	1
ch_fws	29
ch_nmfs	38
ms_merge	37051
rng_fws	2196
rng_iucn	6246
rng_turtle_swot_dps	6

5 Write registry → `sdm.duckdb` + release Parquet

Code

con <- dbConnect(duckdb(sdm_db))
dbWriteTable(con, "dataset", dataset, overwrite = TRUE)
dbWriteTable(con, "model",   model,   overwrite = TRUE)
# stage as Parquet (V2/zstd/80MB row groups) alongside the other release tables
reg_dir <- glue("{dir_atlas}/registry"); dir_create(reg_dir)
msens::copy_atlas_parquet(con, "dataset", glue("{reg_dir}/dataset.parquet"))
msens::copy_atlas_parquet(con, "model",   glue("{reg_dir}/model.parquet"))
smry <- dbGetQuery(con, "SELECT (SELECT count(*) FROM dataset) n_datasets, (SELECT count(*) FROM model) n_models")
# content fingerprints of both registry tables (fold into one manifest hash)
reg_hash <- paste0(msens::hash_query(con, "dataset"), msens::hash_query(con, "model"))
dbDisconnect(con, shutdown = TRUE)
msens::report_table(smry, caption = "registry counts")

registry counts
n_datasets	n_models
11	80261

6 Manifest

Code

# content-addressed: fingerprint of dataset+model tables (sorted lists for determinism)
msens::write_manifest(
  manifest, target = "build_registry", content_hash = reg_hash,
  stats = list(ver = ver, n_datasets = smry$n_datasets, n_models = smry$n_models,
               ingested = paste(sort(ingested), collapse = ","),
               not_ingested = if (length(not_ingested)) paste(sort(not_ingested), collapse = ",") else ""),
  force = msens::force_target("build_registry"))

1 Design

2 Setup

3 dataset — from the msens: frontmatter (+ v7 metadata where keys match)

4 model — union of every model_{ds}.csv (+ merged ms_merge models)

5 Write registry → sdm.duckdb + release Parquet

6 Manifest

3 `dataset` — from the `msens:` frontmatter (+ v7 metadata where keys match)

4 `model` — union of every `model_{ds}.csv` (+ merged `ms_merge` models)

5 Write registry → `sdm.duckdb` + release Parquet