Skip to contents

SQL selecting the model cells a taxon x zone exclusion removes

Source: R/exclude.R
zone_exclude_sql.Rd

v7c drops a taxon from a zone where the evidence says it does not occur, as rows of one evidence table: sea turtles from the ecoregions where they have never been recorded in any observational channel, and the North Atlantic right whale from the Gulf of America. A cell straddling two zones goes with the zone covering most of it (cell_zone_major_sql()), and critical habitat is never excluded: a cell the taxon holds under a designated critical habitat (ch_src) survives inside an excluded zone.

Usage

zone_exclude_sql(
  exclusions,
  src = "model_cell",
  cell_zone = "cell_zone",
  ch_src = NULL,
  id_col = "mdl_seq"
)

Arguments

exclusions

data frame with the id column named by id_col (mdl_seq, integer-like, or mdl_key, character) and zone_key (character, a zone.value); one row per excluded model x zone. Duplicate rows are collapsed.

src

character; name of the model-cell relation holding (id_col, cell_id, ...), default "model_cell".

cell_zone

character; name of the relation (cell_id, zone_key) giving each cell's majority zone, typically a temporary table built from cell_zone_major_sql().

ch_src

character or NULL; name of a relation (id_col, cell_id) of critical-habitat cells per model. Rows listed there are never selected. NULL (default) = no guard.

id_col

character; the model id column, "mdl_seq" (v1-v7, default) or "mdl_key" (v8+).

Value

SQL string selecting distinct (id_col, cell_id), ordered by id_col, cell_id.

Details

The result is the set of (id_col, cell_id) rows of src to DELETE, so the caller applies it as

DELETE FROM model_cell WHERE (mdl_seq, cell_id) IN (<zone_exclude_sql(...)>)

A model with no row in exclusions is never touched, and an exclusion for one model never touches another model's rows in the same cell. The exclusions are inlined as a VALUES list, so the SQL needs no temporary table for them.

Examples

zone_exclude_sql(
  data.frame(mdl_seq = c(54238L, 54252L), zone_key = c("EBS", "EGOA")),
  ch_src = "ch_cells")
#> WITH ex(mdl_seq, zone_key) AS (
#>   VALUES
#>   (54238, 'EBS'),
#>   (54252, 'EGOA')
#> )
#> SELECT DISTINCT s.mdl_seq, s.cell_id
#> FROM model_cell s
#> JOIN cell_zone cz ON cz.cell_id = s.cell_id
#> JOIN ex ON ex.mdl_seq = s.mdl_seq AND ex.zone_key = cz.zone_key
#> WHERE NOT EXISTS (
#>   SELECT 1 FROM ch_cells ch
#>   WHERE ch.mdl_seq = s.mdl_seq AND ch.cell_id = s.cell_id)
#> ORDER BY s.mdl_seq, s.cell_id