Skip to contents

SQL for the v10 distribution-share score family

Source: R/share.R
share_sql.Rd

Returns the SQL strings that compute the share score from the scoring schema. The caller (normally share_run()) materializes these relations in the connection first:

model_cell

(mdl_key, cell_id, <val_col>) – the US scoring surface, one row per (taxon, cell, model): values 0-100 as the merge wrote them (suitability inside the range, the governing er_score on range-only cells, ER x suitability for the premultiplied sea turtles). A multi-model AquaMaps-only taxon keeps duplicate (taxon, cell) rows, as the merge does.

model_cell_er

(mdl_key, cell_id, er) – the per-cell extinction risk of the er_mode = 'cell' taxa. May be empty, must exist, must cover every scored cell of those taxa.

taxon

(ms_merge_key, sp_cat, er_score, er_mode, ...) plus whatever taxa_where references (is_valid_usa, is_marine). er_mode is one of taxon, premultiplied, cell.

taxon_denom

(ms_merge_key, total_global, total_global_strip, source) – each species' total sum(val / 100 * area_km2) over its WHOLE modelled range (from denom_sql()), on the same val scale as model_cell; the _strip variant omits the floored cells (val = 1) of premultiplied taxa. Must exist; only the global denominator divides by it.

cell

(cell_id, area_km2, in_usa).

zone, zone_cell

(zone_seq, fld, val) and (zone_seq, cell_id, pct_covered) with pct_covered in 0-100.

the nation

nation_sql, a query yielding (cell_id, pct_covered): the cells the national reference (share_USA, A_USA) sums over. Default: every cell.in_usa cell in full. It is deliberately NOT a zone row: v8's USA pseudo-zone is the union of the subregion polygons and misses 10,996 coastal and estuarine study-area cells (1.7 % of the cells, 3.4 % of the scored rows – the Emperor Goose's whole US range), so a species' national share would not be its US total. Every scored cell must lie in the nation; share_run() asserts it.

primprod_cell

(cell_id, npp) – optional, for $primprod.

Usage

share_sql(
  cats = c("bird", "coral", "fish", "invertebrate", "mammal", "primary_producer",
    "turtle"),
  denom = c("global", "usa", "pooled"),
  floor_strip = FALSE,
  er_sql =
    "(CASE t.er_mode WHEN 'premultiplied' THEN 100 WHEN 'cell' THEN e.er ELSE t.er_score END)",
  val_col = "val",
  taxa_where = "t.is_valid_usa AND t.is_marine",
  nation_sql = "SELECT cell_id, 100.0 AS pct_covered FROM cell WHERE in_usa"
)

Arguments

cats

character; the scored categories (taxon.sp_cat values), default the seven of v8+.

denom

one of "global", "usa", "pooled".

floor_strip

logical; TRUE drops the floored cells (val <= 1) of premultiplied taxa from every sum (numerator, US total, coverage, species counts) and takes total_global_strip as their denominator (page §9b, the sensitivity column; the default keeps the surface continuous, plan D4).

er_sql

character; the SQL expression of the per-(taxon, cell) risk weight over aliases t (taxon) and e (model_cell_er): the CASE score_cell_metrics.qmd uses. Its species-level counterpart for the percent of the world, w_s, is er_score for the taxon and cell modes and 100 for premultiplied.

val_col

character; the value column of model_cell (sdm_val_col(con, "model_cell")).

taxa_where

character; SQL over alias t selecting the scored species.

nation_sql

character; SQL yielding (cell_id, pct_covered) for the national reference.

Value

a named list of SQL strings (see above).

Details

The strings, in the order share_run() executes them:

taxon_w

CREATE OR REPLACE TEMP TABLE share_taxon: per scored species of cats, the species weight w_s, its US total (total_usa, the risk-free value-area over the scoring surface), its denominator denom_total for this denom, f_us = total_usa / total_global and the denominator source.

base

CREATE OR REPLACE TEMP TABLE share_base: per (zone, species) the two numerators every denominator divides – num = sum(v * A * p / 100) and num_w = sum(w * v * A * p / 100) (the risk weight applied at cell level so a per-cell ER is honoured) – and n_rows. This is the one pass over model_cell x zone_cell; share_run() builds it once and reuses it across denominators (share_base_sig records the arguments it was built with).

zone_taxon

CREATE OR REPLACE TEMP TABLE share_zone_taxon: per (zone, species), the species' share of its range inside the zone under BOTH per-species denominators (share_global, share_usa) and its risk-weighted contribution to the zone total under the chosen one (contrib = num_w / D_s / 100).

cat_cell

CREATE OR REPLACE TEMP TABLE share_cat_cell: per (category, cell), the risk-weighted sum of the species' cell shares (share) and n_species (distinct).

zone_area

CREATE OR REPLACE TEMP TABLE share_zone_area: each zone's covered area area_km2 = sum(area_km2 * pct_covered / 100) and, per category, the covered area of the cells carrying that category (area_cat).

zone_cat

SELECT: per (zone, category) the zone total share, n_species, mean_share, area_km2, coverage, pct_nation, pct_world, v10; the caller applies the floor.

cell_score

SELECT: per (category, cell) share, n_species and the cell-level v10 (100 * (share / A_c) / (share_USA / A_USA)).

primprod

SELECT: per zone the area-weighted mean of npp over ALL the zone's cells (a cell without a value counts 0, as the published pct_area down-weight does), its coverage, and v10 = 100 * mean_z / mean_USA (mean_usa over the nation, same estimator).

nation

SELECT: per category the national reference share_usa, area_usa, n_cells.

outside_nation

SELECT: scored rows / cells outside the nation (must be 0).

er_gap

SELECT: scored cells of cell-mode taxa without a per-cell ER (must be 0).

er_dup

SELECT: duplicate (mdl_key, cell_id) rows of model_cell_er (must be 0: the LEFT JOIN would double a species' share).

Examples

sq <- share_sql(denom = "global")
names(sq)
#>  [1] "taxon_w"        "base"           "zone_taxon"     "cat_cell"      
#>  [5] "zone_area"      "zone_cat"       "cell_score"     "primprod"      
#>  [9] "nation"         "outside_nation" "er_gap"         "er_dup"