bcfishobs

Known BC Fish Observations is documented as the most current and comprehensive information source on fish presence for the province. These scripts locate these observation points as linear referencing events on the most current and comprehensive stream network currently available for BC, the Freshwater Atlas.

The scripts:

• download whse_fish.fiss_fish_obsrvtn_pnt_sp, the latest observation data from DataBC
• download a lookup table whse_fish.wdic_waterbodies used to match the 50k waterbody codes in the observations table to FWA waterbodies
• download a lookup table species_cd, linking the fish species code found in the observation table to species name and scientific name
• load above tables to a PostgreSQL database
• discard any observations not coded as point_type_code = 'Observation' (Summary records are all duplicates of Observation records)
• references the observation points to their position on the FWA stream network (as outlined below)
• creates output table bcfishobs.observations, as documented below

Matching logic, observations

1. For observation points associated with a lake or wetland (according to wbody_id):

- match observations to the closest FWA stream in a waterbody that matches the observation's `wbody_id`, within 1500m
- if no FWA stream in a lake/wetland within 1500m matches the observation's `wbody_id`, match to the closest stream in any lake/wetland within 1500m

1. For observation points associated with a stream:

- match to the closest FWA stream within 100m that has a matching watershed code (via `fwa_streams_20k_50k_xref`)
- for remaining unmatched records within 100m of an FWA stream, match to the closest stream regardless of a match via watershed code
- for remaining unmatched records between 100m to 500m of an FWA stream, match to the closest FWA stream that has a matching watershed code

This logic is based on the assumptions:

• for observations noted as within a lake/wetland, we can use a relatively high distance threshold for matching to a stream because
  • an observation may be on a bank far from a waterbody flow line
  • as long as an observation is associated with the correct waterbody, it is not important to exactly locate it on the stream network within the waterbody
• for observations on streams, the location of an observation should generally take priority over a match via the xref lookup because many points have been manually snapped to the 20k stream lines - the lookup is best used to prioritize instances of multiple matches within 100m and allow for confidence in making matches between 100 and 500m

General requirements

• PostgreSQL/PostGIS
• a FWA database created by fwapg
• GDAL >= 3.4
• Python (>=3.6)
• bcdata

Run the scripts

Scripts presume that:

• environment variable DATABASE_URL points to the appropriate db
• FWA data are loaded to the db via fwapg

To set up the database/create schema:

$ git clone https://github.com/smnorris/bcfishobs.git
$ cd bcfishobs
$ psql $DATABASE_URL -f db/v0.2.0.sql
$ psql $DATABASE_URL -f db/v0.3.0.sql

To run the job:

$ ./process.sh

Output table

bcfishobs.observations

Source observations that have been successfully matched to a FWA stream. Geometries are snapped to the closest point on the the stream network to which the observation is matched.

For a list of columns and descriptions, see db/v0.3.0.sql or the bcfishpass feature service.

Use the data

With the observations now linked to the Freswater Atlas, we can write queries to find fish observations relative to their location on the stream network.

Example 1

List all species observed on the Cowichan River (blue_line_key = 354155148), downstream of Skutz Falls (downstream_route_meaure = 34180).

``` SELECT DISTINCT speciescode FROM bcfishobs.observations WHERE bluelinekey = 354155148 AND downstreamroutemeasure < 34180 ORDER BY speciescode;

species_code

ACT AS BNH BT C CAL CAS CH CM CO CT DV EB GB KO L MARFAL RB SA SB ST TR TSB ```

Example 2

What is the slope (percent) of the stream at all distinct locations of Steelhead observations in COWN watershed group (on single line streams)?

``` SELECT e.fishobsrvtneventid, s.gnisname, s.gradient FROM bcfishobs.observations e INNER JOIN whsebasemapping.fwastreamnetworkssp s ON e.linearfeatureid = s.linearfeatureid WHERE e.speciescodes && ARRAY['ST'] AND e.watershedgroupcode = 'COWN' AND s.edgetype = 1000 ORDER BY e.wscodeltree, e.localcodeltree, e.downstreamroutemeasure

fishobsrvtneventid | gnisname | gradient -----------------------+----------------------+---------- 675380033961 | Cowichan River | 0.0071 675380034170 | Cowichan River | 0.0614 641720026729 | Koksilah River | 0.0058 641720036829 | Koksilah River | 0.0369 641720037394 | Koksilah River | 0.0761 581370001848 | Kelvin Creek | 0.0061 581370006510 | Kelvin Creek | 0.0167 564060000660 | Glenora Creek | 0.0137 564060008058 | Glenora Creek | 0.0843 ... ```

Example 3

What are the order, elevation and gradient of all Arctic Grayling observations in the Parsnip watershed group?

``` SELECT fishobservationpointid, s.gradient, s.streamorder, round((STZ((STDump(STLocateAlong(s.geom, e.downstreamroutemeasure))).geom))::numeric) as elevation FROM bcfishobs.observations e INNER JOIN whsebasemapping.fwastreamnetworkssp s ON e.linearfeatureid = s.linearfeatureid WHERE e.speciescode = 'GR' AND e.watershedgroupcode = 'PARS' ORDER BY e.wscodeltree, e.localcodeltree, e.downstreamroutemeasure;

fishobservationpointid | gradient | streamorder | elevation ---------------------------+----------+--------------+----------- 233425 | 0 | 7 | 674 233402 | 0.0007 | 7 | 675 318578 | 0.0007 | 7 | 675 233432 | 0.0004 | 7 | 685 96418 | 0 | 6 | 694 233458 | 0.0003 | 7 | 696 ... ```

Warnings

Primary key and duplicates

Column fish_observation_point_id is present in the ouput table but should generally be disregarded, it is unique when downloaded but unstable over time.

Column observation_key is generated by this script as a persistent unique identifier. The value is created by hashing input columns source, species_code, observation_date, utm_zone, utm_easting, utm_northing, life_stage_code, activity_code. This combinaition of data is mostly unique in the source - any duplicates are dropped.

Scheduled job

The bcfishpass scheduled workflow runs this script on a weekly basis, dumping the results to a parquet file on NRS object storage.