Skip to contents

SQL assigning each cell to its single majority zone

Source: R/exclude.R
cell_zone_major_sql.Rd

A cell on the boundary between two zones (1,125 of v7's 630,806 cells straddle two ecoregions) belongs to the zone covering most of it, by zone_cell.pct_covered. A tie is broken deterministically: the zone whose key (zone.value) sorts first wins, so the same inputs always give the same assignment regardless of zone_seq order or scan order.

Usage

cell_zone_major_sql(
  zone_fld = "ecoregion_key",
  zone_cell = "zone_cell",
  zone = "zone"
)

Arguments

zone_fld

character; the zone.fld of the zone set, default "ecoregion_key".

zone_cell

character; name of the zone-cell relation, default "zone_cell".

zone

character; name of the zone relation, default "zone".

Value

SQL string selecting (cell_id, zone_key), one row per cell, ordered by cell_id.

Details

Reads the v1-v7 scoring schema: zone (zone_seq, tbl, fld, value, ...) and zone_cell (zone_seq, cell_id, pct_covered); the zone key is zone.value. Only zones whose fld equals zone_fld are considered. Should one key appear under several zone_seq (the same field in two zone tables), its cover of a cell is collapsed by max() rather than summed, so a duplicated vintage cannot double a zone's claim on a cell.

A cell in no zone of zone_fld yields no row, so it can never be excluded by zone_exclude_sql().

Examples

if (FALSE) { # \dontrun{
con <- duckdb::dbConnect(duckdb::duckdb(), sdm_db_path("v7c"))
DBI::dbExecute(con, paste("CREATE TEMP TABLE cell_zone AS", cell_zone_major_sql()))
} # }