Join Columns from a Lookup Table onto a Working Table

Add columns from any lookup table to a working table via SQL UPDATE ... SET ... FROM. This is the generic enrichment step in the habitat pipeline — join channel width for intrinsic potential, upstream area and precipitation for flooded's bankfull regression, or any custom model output.

Usage

frs_col_join(conn, table, from, cols, by = "linear_feature_id")

Arguments

conn

A DBI::DBIConnection object (from frs_db_conn()).

table

Character. Schema-qualified working table to enrich.

from

Character. Source table (or subquery wrapped in parentheses) containing the columns to join.

cols

Character vector. Column names to add from the source table.

by

Character vector. Join key(s). Unnamed elements match the same column in both tables. Named elements map working table column (name) to source column (value): c(linear_feature_id = "lid"). Default "linear_feature_id".

Value

conn invisibly, for pipe chaining.

Details

Pipeable between frs_extract() and frs_col_generate():

conn |>
  frs_extract(...) |>
  frs_col_join("working.streams",
    from = "fwa_stream_networks_channel_width",
    cols = c("channel_width", "channel_width_source"),
    by = "linear_feature_id") |>
  frs_col_generate("working.streams")

See also

Other habitat: frs_aggregate(), frs_break(), frs_break_apply(), frs_break_find(), frs_break_validate(), frs_categorize(), frs_classify(), frs_cluster(), frs_col_generate(), frs_extract(), frs_feature_find(), frs_feature_index(), frs_habitat(), frs_habitat_access(), frs_habitat_classify(), frs_habitat_overlay(), frs_habitat_partition(), frs_habitat_predicates(), frs_habitat_species(), frs_network_segment()

Examples

if (FALSE) { # \dontrun{
conn <- frs_db_conn()

# Channel width — direct join by linear_feature_id
conn |>
  frs_col_join("working.streams",
    from = "fwa_stream_networks_channel_width",
    cols = c("channel_width", "channel_width_source"),
    by = "linear_feature_id")

# MAD (mean annual discharge) — same pattern
conn |>
  frs_col_join("working.streams",
    from = "fwa_stream_networks_discharge",
    cols = "mad_m3s",
    by = "linear_feature_id")

# Upstream area — two-hop join via subquery
conn |>
  frs_col_join("working.streams",
    from = "(SELECT l.linear_feature_id, ua.upstream_area_ha
             FROM fwa_streams_watersheds_lut l
             JOIN fwa_watersheds_upstream_area ua
               ON l.watershed_feature_id = ua.watershed_feature_id) sub",
    cols = "upstream_area_ha",
    by = "linear_feature_id")

# MAP (mean annual precipitation) — composite key
conn |>
  frs_col_join("working.streams",
    from = "fwa_stream_networks_mean_annual_precip",
    cols = "map_upstream",
    by = c("wscode_ltree", "localcode_ltree"))

DBI::dbDisconnect(conn)
} # }