Skip to contents

SQL for each species' global denominator (its value-area total over its whole modelled range)

Source: R/share.R
denom_sql.Rd

The share needs, per species, D_s = sum over range(s) of v * A on the same val scale as the US scoring surface. Three sources, in order of precedence (page §8 and §9b; the review page's scripts/v10_review/02-05 recipe):

  1. a curated fallback fraction f (fallback): D_s = total_usa / f, source = 'fallback:<method>' – it takes precedence because a national-only taxon's "global" merged surface IS its US range (FWS-range-only birds, fishes, shrimps, sea-otter subspecies);

  2. the merged global surface (mc_global): the whole-range max(er, suitability-at-range) the merge wrote for every range-constrained taxon, source = 'merged_global';

  3. AquaMaps-only taxa (no merged global surface, since they reuse the AquaMaps COGs): AquaMaps outside the US (mc_am where NOT cell.in_usa, every model of the taxon, no dedup – as the US am-only branch keeps duplicates) plus the merged US surface inside, source = 'aquamaps'; a taxon with AquaMaps cells only inside the US gets D_s = total_usa (f_us = 1, an EEZ endemic as modelled).

A taxon whose datasets are ALL national (national_ds) and that has no fallback row gets source = 'national_only_unresolved' with its US-range total as a placeholder, so the caller can refuse or curate it (data/us_share_fallback.csv) rather than score a species at share 1. An er_mode = 'cell' taxon is flagged cell_er_unsupported until its treatment is settled (see the header of R/share.R; no v10 taxon is in that mode).

Usage

denom_sql(
  val_col = "val",
  nc = 7200L,
  national_ds = c("rng_fws", "ca_nmfs", "ch_fws", "ch_nmfs", "dps_nmfs", "ax"),
  suit_ds = "am"
)

Arguments

val_col

character; the value column of the three surfaces.

nc

integer; the grid's number of columns (7200 for global05).

national_ds

character; dataset keys that never carry a global range.

suit_ds

character; the suitability dataset key(s) feeding the am-only branch.

Value

SQL (SELECT) yielding taxon_denom (ms_merge_key, total_usa, total_usa_strip, total_global, total_global_strip, n_cells_global, n_cells_no_area, source) for every taxon with a US scoring surface.

Details

The whole range includes land. The merged global surfaces carry a bird's entire range (the ingests apply no land mask, by design), while cell holds ocean cells only (17.1 M of the 25.9 M global05 cells); on v8 one bird has 1.66 M global cells of which 1.5 % are in cell. Joining the surfaces to cell would silently measure a bird's "range" as its marine fraction and inflate f_us up to ~60x. So an ocean cell takes its own cell.area_km2 (terra's ellipsoidal area, exactly what the US numerator uses) and a cell absent from cell takes the area of its latitude row from row_area (one value per row from that row's ocean cells; every cell of a row has the same area on a regular lat-lon grid) – no sphere-vs-ellipsoid mismatch. A land cell in a row with no ocean cell at all contributes nothing (counted in n_cells_no_area). nc is the grid's column count (grid_spec_for(grid_id)$nc); cell_id is 1-based row-major.

Both totals come in a plain and a _strip variant: the strip omits the floored cells (val <= 1) of er_mode = 'premultiplied' taxa, for share_sql()'s floor_strip.

Relations the caller materializes: model_cell (mdl_key, cell_id, <val_col>) the US scoring surface; mc_global (mdl_key, cell_id, <val_col>) the merged global surface (a view over dist_merged_global); mc_am (mdl_key, cell_id, <val_col>) the AquaMaps surfaces (a view over dist/dataset=am); taxon_model (mdl_key, ms_merge_key, ds_key); taxon (ms_merge_key, er_mode, ...); cell (cell_id, area_km2, in_usa); fallback (ms_merge_key, fraction, method) with one row per species.