Skip to contents

SQL for the coverage of each zone by each component metric

Source: R/coverage.R
coverage_sql.Rd

Builds the SQL that answers, per (zone, metric): what share of the zone is covered by cells that actually carry that metric? Numerator = the weight of the zone's cells holding a non-NULL value for the metric; denominator = the weight of ALL of that zone's cells. Reads the v1-v7 scoring schema (zone, zone_cell, cell_metric, metric, and cell for weight = "area").

Usage

coverage_sql(
  zone_tbl,
  metric_keys,
  weight = c("pct_covered", "area"),
  val_col = "value"
)

Arguments

zone_tbl

character; the value of zone.tbl selecting the zone set, e.g. "ply_programareas_2026_v7". Only zones of that table are considered.

metric_keys

character vector of metric.metric_key values, e.g. the component keys extrisk_{sp_cat}_ecoregion_rescaled plus primprod_ecoregion_rescaled.

weight

one of "pct_covered" (v7-faithful) or "area" (in-zone km2).

val_col

name of the measurement column of cell_metric. Defaults to "value", which is every release v1-v7 (and every served release); a caller holding an open connection should pass sdm_val_col(con, "cell_metric") rather than hardcoding either spelling – this is a SQL-string builder and has no connection of its own to introspect.

Value

SQL string selecting (zone_seq, metric_key, coverage), coverage in [0, 1], ordered by zone_seq, metric_key.

Details

weight = "pct_covered" reproduces v7's pct_area exactly (SUM(zone_cell.pct_covered) over each side, as calc_scores_v7.qmd:2056-2091 computes it), so the same function serves both the reporting floor and the existing weighting. weight = "area" (what v10 wants) weights each cell by its in-zone area, cell.area_km2 * pct_covered / 100: an edge cell half inside the zone contributes half its area, and a 30.8 km2 equatorial cell outweighs a 4.0 km2 Arctic one. On the equal-area-per-cell fixtures of the tests the two agree; on the real grid they do not, which is the point.

A zone with no scored cell yields NO ROW – not a zero. That distinction is the whole contract downstream: a component absent from a Program Area is excluded from the composite mean (which is SUM(value)/COUNT(value) over present rows), while a zero would drag it down.

Examples

if (FALSE) { # \dontrun{
con <- duckdb::dbConnect(duckdb::duckdb(), sdm_db_path("v7"), read_only = TRUE)
d   <- DBI::dbGetQuery(con, coverage_sql(
  "ply_programareas_2026_v7",
  c(paste0("extrisk_", sp_cats, "_ecoregion_rescaled"), "primprod_ecoregion_rescaled"),
  val_col = sdm_val_col(con, "cell_metric")))
coverage_floor(d, kappa = 0.05)
} # }