Merge rule for NMFS DPS-listed species: the model stays the distribution, ER rides beside it

For species whose ESA listing is scoped below the species — Distinct Population Segments, ESUs, subspecies (dps_nmfs: humpback whale, Southern Resident killer whale, the salmonid ESUs, …) — extinction risk varies across the range, but unlike the sea turtles (turtle_sql()) the risk is NOT multiplied into the merged value. The merged model is the distribution: the suitability surface (suit_ds, max over the survivors of supersede_sql()) masked to the ER footprint (the species' IUCN range ∪ its listed entities' habitat, i.e. the same range mask every other taxon gets), and where no suitability model covers a footprint cell the cell is valued at its ER — the plain rule's "fitting point" (greatest(er, coalesce(suit, 0)) reduces to er there). The per-cell ER is returned beside the value (er) for score_cell_metrics to multiply in, exactly as the taxon-level er_score is for every other species: extrisk = er × val / 100.

Usage

dps_sql(dps_ds, suit_ds, src = "dps_src")

Arguments

dps_ds

character vector; ds_key(s) of the per-cell extinction-risk dataset(s) ("dps_nmfs").

suit_ds

character vector; suitability ds_key(s) (c("am", "ax") from v9).

src

character; name of the source relation (ms_merge_key, ds_key, cell_id, val).

Value

SQL string selecting (mdl_key, cell_id, val, er) — the whole-range surface with its per-cell extinction risk.

Details

Why not the turtle rule: round(er × suit / 100) with the humpback's ER of 1 across 99.8 % of its range collapsed the merged surface to 1 everywhere except critical habitat — the merged model showed the weight, not the whale. Critical-habitat datasets are not an input here (the caller excludes ch_* from src): dps_nmfs already carries every designation with its own entity's status, while ch_* carries the species-level status.

Examples

cat(dps_sql("dps_nmfs", c("am", "ax")))
#> WITH er   AS (SELECT ms_merge_key, cell_id, max(val) er_value   FROM dps_src WHERE ds_key IN ('dps_nmfs')   GROUP BY 1, 2),
#>      suit AS (SELECT ms_merge_key, cell_id, max(val) suit_value FROM dps_src WHERE ds_key IN ('am', 'ax') GROUP BY 1, 2)
#> SELECT er.ms_merge_key AS mdl_key, er.cell_id,
#>        CAST(coalesce(suit.suit_value, er.er_value) AS DOUBLE) AS val,
#>        CAST(er.er_value AS DOUBLE) AS er
#> FROM er LEFT JOIN suit USING (ms_merge_key, cell_id)