Supersession predicate: one suitability dataset replaces another inside a mask

From v9, AquaX (ax) is a newer model of the same thing as AquaMaps (am) — but it was delivered only over the US study area (its own mask: the union over models of their modeled pixels — one model's NA area is its range crop, i.e. the model saying absent — so the mask is the union, measured against cell.in_usa by the ingest). The rule is therefore per taxon, inside the mask: for a taxon that has a superseding model, the superseded dataset's cells that fall inside the mask are dropped from the merge input; outside the mask (the rest of the world, and any US cell no model reaches) the old dataset carries on. Applied to BOTH output surfaces, since it filters the input they share.

Usage

supersede_sql(
  superseded = "am",
  taxa = "supersede",
  mask = "ax_mask",
  src = "mc"
)

Arguments

superseded

ds_key whose cells are dropped inside the mask (default "am")

taxa

relation holding ms_merge_key of the taxa to supersede (default "supersede")

mask

relation holding the cell_ids of the superseding dataset's extent (default "ax_mask")

src

alias of the relation being filtered, with ds_key, ms_merge_key, cell_id

Value

a SQL predicate (no leading WHERE) that is TRUE for rows to KEEP

Details

Why a filter on the input rather than a coalesce() inside merge_sql(): a per-cell coalesce would keep an AquaMaps cell wherever AquaX has no value — i.e. exactly where the newer model says the species is absent — which defeats the point of superseding it. Dropping the old cells over the whole mask lets AquaX's absences be absences.

Which taxa are superseded is a TABLE the caller materializes (taxa, one ms_merge_key per row), not derived here from "has a superseding model", because the policy question — does an AquaX run that predicted no US presence also supersede? (v9: no, AX_ABSENT_SUPERSEDES) — belongs in the notebook, where it is a flag and a table a reviewer can read.

Examples

supersede_sql()
#> [1] "NOT (mc.ds_key = 'am' AND mc.ms_merge_key IN (SELECT ms_merge_key FROM supersede) AND mc.cell_id IN (SELECT cell_id FROM ax_mask))"
if (FALSE)  DBI::dbGetQuery(con, paste("SELECT * FROM mc WHERE", supersede_sql(src = "mc")))  # \dontrun{}