Spatial-ER (turtle) multiplicative merge rule

Taxa whose extinction risk is SPATIAL — a per-cell ER surface built from Distinct Population Segment polygons (sea turtles from SWOT + NMFS DPS since v4b; from v9.1 any species with NMFS DPS boundaries — but those now use dps_sql(), which keeps the distribution as the model) merge differently: the per-cell ER surface (turtle_ds, one or more datasets) is multiplied by the suitability (suit_ds), floored at 1 over the ER footprint, then critical-habitat datasets (ch_keys) override with a max. val = greatest(1, round(er * suit / 100)) then greatest(that, ch). Reads a source relation src with (ms_merge_key, ds_key, cell_id, val).

Usage

turtle_sql(
  turtle_ds,
  suit_ds,
  ch_keys,
  src = "turtle_src",
  suit_min = 0,
  fill = 1,
  half_even = FALSE,
  ch_outer = FALSE
)

Arguments

turtle_ds: character vector; ds_key(s) of the per-cell extinction-risk dataset(s).
suit_ds: character vector; ds_key(s) of the suitability dataset(s) ("am", or c("am", "ax") from v9 — after supersede_sql() at most one survives per cell, and max() over the survivors is the value).
ch_keys: character vector of critical-habitat ds_keys that override with a max (may be empty).
src: character; name of the source relation to read (default "turtle_src").
suit_min: numeric; suitability strictly below this is ABSENT, before the multiply (default 0 = keep everything; v7.1 uses 50).
fill: numeric or NA; suitability assumed for an ER cell with none retained (default 1, the v1–v9 floor-of-1 range fill). NA DROPS such a cell unless critical habitat covers it.
half_even: logical; TRUE rounds half-to-even (round_even(), what R does and what built v1–v7; DuckDB round() differs on 28,598 v7 cells where ER = 50). Default FALSE.
ch_outer: logical; TRUE keeps critical-habitat cells OUTSIDE the ER footprint (v1–v7's union). Default FALSE.

Value

SQL string selecting (mdl_key, cell_id, val) — the whole-range turtle surface.

Details

This is what keeps an Endangered species from painting flat: the plain rule values every range cell at the taxon's governing ER (max(er, suit) = 100 everywhere for NMFS:EN), while here the ER varies by DPS and the suitability still shows through.

Core habitat (`suit_min`, `fill`) — v7.1

The defaults (suit_min = 0, fill = 1, half_even = FALSE, ch_outer = FALSE) reproduce the v8/v9 rule exactly. v7.1 thresholds the suitability term before the multiply (suit_min = 50, AquaMaps' own core-habitat default) and drops the floor-of-1 range fill (fill = NA), because a threshold alone removes none of it: for five of the six sea turtles half to nine tenths of the merged US area is fill, with no suitability value at all.

suit_min > 0 with fill = 1 kept does not shrink a footprint, it RE-CREATES the fringe as a field of 1s: every cell whose suitability fell below the cut comes back at the floor. Under an ecoregional min–max rescale that field is then stretched back over 0–100, which is why the Aleutian Arc goes UP (70.9 → 74.5) under that combination. fill = NA is the pairing that means "no core habitat here".