Value added table/view reference

fwa_approx_borders

Lines of latitude / longitude for 49N, 60N, -120W. These are used by fwapg for finding cross-border streams

Column	Type	Description
`approx_border_id`	`integer`	Unique identifer
`border`	`text`	A code identifying the border (USA49, YTNWT_60, AB_120)
`geom`	`geometry(LineString,3005)`	Geometry of the border line

fwa_assessment_watersheds_lut

Some workflows require relating fwa_assessment_watersheds_poly to fundamental watersheds. There are no existing keys in the data that maintain this link - the query requires a resource intensive spatial function. Rather than running a spatial query every time, this lookup is provided.

Column	Type	Description
watershed_feature_id	integer
assmnt_watershed_id	integer

fwa_assessment_watersheds_streams_lut

Some workflows require relating fwa_assessment_watersheds_poly to stream segments. There are no existing keys in the data that maintain this link - the query requires a resource intensive spatial function. Rather than running a spatial query every time, this lookup is provided.

Column	Type	Description
linear_feature_id	integer
assmnt_watershed_id	integer

fwa_basins_poly

Large BC watersheds consisting of at least 2-3 watershed groups, used by fwapg for watershed pre-aggregation

Column	Type	Description
`basin_id`	`integer`	Basin unique identifier
`basin_name`	`text`	Basin name, eg Thompson River
`wscode_ltree`	`ltree`	The watershed code associated with the stream at the outlet of the basin
`localcode_ltree`	`ltree`	The local watershed code associated with the stream at the outlet of the basin
`geom`	`geometry(Polygon,3005)`	Geometry of the basin

fwa_bcboundary

Boundary of BC as defined by FWA - used by FWA_WatershedAtMeasure()

Column	Type	Description
`bcboundary_id`	`integer`	Boundary polygon id
`geom`	`geometry(Polygon,3005)`	Boundary geometry

fwa_named_streams

Named streams of BC, aggregated per watershed group and simplified using a 25m tolerance (primarily for mapping use)

Column	Type	Description
`named_streams_id`	`integer`	Named stream unique identifier
`gnis_name`	`text`	The BCGNIS (BC Geographical Names Information System) name associated with the stream
`blue_line_key`	`bigint`	The blue line key of the named stream, see FWA documentation for blue_line_key description
`stream_order`	`integer`	The maximum stream order associated with the stream name
`watershed_group_code`	`text`	The watershed group code associated with the named stream
`geom`	`geometry(MultiLineString,3005)`	The geometry of the named stream, an aggregation of the source features and simpified by 25m

whse_basemapping.fwa_streams_vw

View of FWA stream networks and value-added attributes

Column	Type	Description
`linear_feature_id`	`bigint`	See FWA documentation
`edge_type`	`integer`	See FWA documentation
`blue_line_key`	`integer`	See FWA documentation
`watershed_key`	`integer`	See FWA documentation
`wscode`	`ltree`	FWA watershed code as postgres ltree type, with trailing -000000 strings removed
`localcode`	`ltree`	FWA local watershed code as postgres ltree type, with trailing -000000 strings removed
`watershed_group_code`	`character varying(4)`	See FWA documentation
`downstream_route_measure`	`double precision`	See FWA documentation
`upstream_route_measure`	`double precision`	See FWA documentation
`length_metre`	`double precision`	See FWA documentation
`waterbody_key`	`integer`	See FWA documentation
`gnis_name`	`character varying(80)`	See FWA documentation
`stream_order`	`integer`	See FWA documentation
`stream_magnitude`	`integer`	See FWA documentation
`feature_code`	`character varying(10)`	See FWA documentation
`gradient`	`double precision`	See FWA documentation
`left_right_tributary`	`character varying(7)`	See FWA documentation
`stream_order_parent`	`integer`	Stream order of parent stream at confluence with stream having `blue_line_key` of the stream segment
`stream_order_max`	`integer`	Maxiumum order of the stream with equivalent `blue_line_key` as given segment)
`upstream_area_ha`	`double precision`	Area (ha) upstream of the stream segment (including all fundamental watersheds with equivalent watershed code)
`map_upstream`	`integer`	Area weighted average mean annual precipitation upstream of the stream segment, source ClimateBC
`channel_width`	`double precision`	Channel width of the stream segment in metres, with source as per channel_width_source
`channel_width_source`	`text`	Data source for channel_width at given segment, with values (FIELD_MEASURMENT, FWA_RIVERS_POLY, MODELLED). FIELD_MEASUREMENT is derived from PSCIS and FISS data, MODELLED is taken from Thorley et al, 2021
`mad_m3s`	`double precision`	Modelled mean annual discharge at the stream segment (Pacific Climate Impacts Consortium, University of Victoria, (January 2020) VIC-GL BCCAQ CMIP5: Gridded Hydrologic Model Output)
`geom`	`geometry(LineStringZM,3005)`

fwa_streams_watersheds_lut

A convenience lookup for quickly relating streams and fundamental watersheds

Column	Type	Description
`linear_feature_id`	`bigint`	FWA stream segment unique identifier
`watershed_feature_id`	`integer`	FWA fundamental watershed unique identifer

fwa_waterbodies

All FWA waterbodies in one table for convenience (lakes, wetlands, rivers, manmade waterbodies, glaciers). Note that this table is meant for connectivity purposes only, only features connected to the network are included (waterbodies with watershed code 999.999999 are excluded). For column descriptions see FWA docs for the various waterbody tables.

Column	Type	Description
`waterbody_key`	`integer`
`waterbody_type`	`character varying(1)`
`blue_line_key`	`integer`
`downstream_route_measure`	`double precision`
`wscode_ltree`	`ltree`
`localcode_ltree`	`ltree`

fwa_waterbodies_upstream_area

A lookup storing area of lake/reservoir/wetland upstream of individual stream segments. Note that this table differs from fwa_watersheds_upstream_area noted above - we use streams as the lookup base rather than watersheds because waterbodies can be nested within fundamental watersheds.

NOTE - output currently includes area upstream WITHIN BC ONLY, this will not be accurate in watersheds that have contributing drainage outside of BC!

Column	Type	Description
linear_feature_id	bigint
upstream_lake_ha	double precision
upstream_reservoir_ha	double precision
upstream_wetland_ha	double precision

fwa_watersheds_upstream_area

Area upstream (ha) for all fundamental watersheds as a lookup table. Area includes the area of the fundamental watershed indicated by the id.

NOTE - output currently includes area upstream WITHIN BC ONLY, this will not be accurate in watersheds that have contributing drainage outside of BC!

When working with watersheds, join to this lookup directly via watershed_feature_id. When working with streams, relate the streams to watersheds via the lookup fwa_streams_watersheds_lut:

SELECT
  s.linear_feature_id,
  ua.upstream_area_ha
FROM whse_basemapping.fwa_stream_networks_sp s
LEFT OUTER JOIN whse_basemapping.fwa_streams_watersheds_lut l
ON s.linear_feature_id = l.linear_feature_id
INNER JOIN whse_basemapping.fwa_watersheds_upstream_area ua
ON l.watershed_feature_id = ua.watershed_feature_id

Column	Type	Description
watershed_feature_id	integer
upstream_area_ha	double precision