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")

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".

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_break(), frs_break_apply(), frs_break_find(), frs_break_validate(), frs_extract()

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,] 992630.9 1057269 1282.000 162.2097  1
#> [2,] 992618.9 1057269 1282.438 174.1640  1
#> [3,] 992592.1 1057271 1283.517 201.0709  1
#> [4,] 992571.0 1057271 1284.355 222.1792  1
#> [5,] 992563.9 1057267 1284.773 230.1611  1
#> [6,] 992547.4 1057252 1285.328 252.7469  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)
} # }