Skip to contents

Convert Columns to PostgreSQL Generated Columns from Geometry

Source: R/frs_col_generate.R
frs_col_generate.Rd

Replace static columns with PostgreSQL GENERATED ALWAYS columns derived from the table's LineStringZM geometry. After this, any operation that modifies geometry (e.g. frs_break_apply()) will auto-recompute gradient, route measures, and length — no manual recalculation needed.

Usage

frs_col_generate(conn, table, geom_col = "geom", exclude = NULL)

Arguments

conn

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

table

Character. Schema-qualified working table name (from frs_extract()).

geom_col

Character. Name of the geometry column. Default "geom".

exclude

Character vector or NULL. Column names to skip (e.g. "gradient" to preserve parent-segment gradient after splitting). Default NULL (regenerate all columns).

Value

conn invisibly, for pipe chaining.

Details

This mirrors the bcfishpass.streams table design where gradient, downstream_route_measure, upstream_route_measure, and length_metre are all GENERATED ALWAYS AS (...) from the geometry.

Converts these columns (drops if they exist, re-adds as generated):

gradient

round(((ST_Z(end) - ST_Z(start)) / ST_Length(geom))::numeric, 4)

downstream_route_measure

ST_M(ST_PointN(geom, 1))

upstream_route_measure

ST_M(ST_PointN(geom, -1))

length_metre

ST_Length(geom)

Requires LineStringZM geometry (Z for elevation, M for route measures). FWA stream networks have this by default.

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_join(), 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

# --- Why generated columns matter (bundled data) ---
# When you split a 500m segment at 10% average gradient, the two
# pieces have different actual gradients. Generated columns auto-compute
# the correct value from each piece's geometry.

d <- readRDS(system.file("extdata", "byman_ailport.rds", package = "fresh"))
streams <- d$streams

# FWA streams carry Z (elevation) and M (route measure) on every vertex
head(sf::st_coordinates(streams[1, ]))
#>             X       Y    Z         M L1
#> [1,] 990484.4 1062687 1270  58.03441  1
#> [2,] 990509.5 1062667 1270  89.87584  1
#> [3,] 990524.9 1062656 1270 108.99646  1

if (FALSE) { # \dontrun{
# --- Live DB: extract, generate, break ---
conn <- frs_db_conn()
aoi <- d$aoi

# 1. Extract FWA streams (static columns)
conn |> frs_extract(
  from = "whse_basemapping.fwa_stream_networks_sp",
  to = "working.demo_gen",
  aoi = aoi, overwrite = TRUE)

# 2. Convert to generated columns
conn |> frs_col_generate("working.demo_gen")

# 3. Break — gradient auto-recomputes on new segments
conn |> frs_break("working.demo_gen",
  attribute = "gradient", threshold = 0.08)

# Verify: all segments have gradient (no NULLs, all accurate)
result <- frs_db_query(conn,
  "SELECT gradient, geom FROM working.demo_gen")
summary(result$gradient)
plot(result["gradient"], main = "Gradient (auto-computed after break)")

# Clean up
DBI::dbExecute(conn, "DROP TABLE IF EXISTS working.demo_gen")
DBI::dbExecute(conn, "DROP TABLE IF EXISTS working.breaks")
DBI::dbDisconnect(conn)
} # }