smnorris / bcfishpass Goto Github PK

Some documentation is needed 😬

To support:

• defining areas outside of model consideration
• misc barriers that aren't covered by existing types

Perhaps an additional misc table is required - add 01_prep/04_other/data/misc.csv and a corresponding misc.sql.

Hopefully it is safe to presume that there won't be very many of these, no automated matching of points to streams is necessary - misc points can simply be coded with an id and notes, plus blue_line_key (or linear_feature_id) and downstream_route_measure for location.

Currently the layer file points to streams as vector tiles at hillcrestgeo.ca.
All other data sources are on localhost, move these to localhost as well so that changes to the model can be QAed locally without pushing everything to production server.

Extract PSCIS remediations into a separate temp table and run bcfishpass.py add-downstream-ids so we can easily view success stories. See also #16

PSCIS assessed crossings are not getting matched to nearby modelled crossings because of empty modelled_crossing_id values in the xref.

Fix may require some manual QA but a query on nearby modelled crossings (instream within 100m or so) should identify most and be fairly easy to fix.

Alternatively changing the logic in the matching is possible... but I think this xref should explicitly match pscis crossings to modelled crossings where possible, not just streams. When modelled_crossing_id is null in the xref it should be fairly safe to conclude that there is no mapped road at this location.

1. Rearing habitat downstream of spawning habitat does not need to actually be connected to the spawning habitat
2. Consider a maximum distance upstream from spawning habitat for connected rearing habitat to be valid
3. double line rivers meeting slope criteria should be coded as spawning habitat (due to general under-estimation of channel width by the channel width model on larger streams)
4. Investigate measuring channel width across river polygons if time allows
5. Include ALL potential rearing habitat DOWNSTREAM of spawning habitats (for that spp)
6. Include potential rearing habitat UPSTREAM of spawning habitat that is NOT connected IF slope between the rearing/spawning does not exceed 5% (with caveat that there may be an instream distance threshold applied in future)
7. Sockeye are different - model rearing habitat first and then add potential spawning habitat DOWNSTREAM of rearing lake where slope of segments between spawn and rear is <2% (again subject to a distance threshold TBD)

When adding sums, each area needs to be wrapped in a COALESCE():

COALESCE(ROUND(((SUM(uwb.area_lake) + SUM(uwb.area_manmade)) / 10000)::numeric, 2), 0) AS total_lakereservoir_ha,

becomes

ROUND(((SUM(COALESCE(uwb.area_lake, 0)) + SUM(COALESCE(uwb.area_manmade, 0))) / 10000)::numeric, 2) AS total_lakereservoir_ha,

First, add provided key barriers for January 2021 workshops discussion.
After workshops, add all crossings.

To qualify as rearing habitat, a stream segment must be directly connected to spawning habitat. Currently this restriction is not applied.

Summarize length of spawning / rearing habitat upstream.
Do we want to report by spp? That is a lot of columns...

All the subfolders in /sql are a bit scattered, this could still use some tidying

These should be included in the qgis layer file
A left join of pscis_points_all to pscis_events_sp should extract the points necessary but some checks needed to make sure any required datafixes make it through.

Existing values are ad-hoc, some improvement could be made so user can quickly understand more about the crossing:

select distinct crossing_source, crossing_type from bcfishpass.crossings;
   crossing_source    | crossing_type
----------------------+---------------
 ASSESSED             | BRIDGE
 ASSESSED             | PIPEARCH
 MODELLED_CROSSINGS   | CBS
 ASSESSED             | OVAL
 ASSESSED             | WOODBOX
 HABITAT CONFIRMATION | ROUND
 HABITAT CONFIRMATION | OVAL
 ASSESSED             | FORD
 ASSESSED             | ROUND
 REMEDIATED           | BRIDGE
 HABITAT CONFIRMATION | FORD
 MODELLED_CROSSINGS   | OBS
 ASSESSED             | CRTBOX
 BCDAMS               | DAM

For crossings linked to modelled crossings we can note what road/rail source the modelled crossing came from.
For crossings that are PSCIS only I'm less sure what we can pull through.

MORR - for New Graph Bulkley reporting
ADMS/LNTH - for Braumandl 2020 confirmation mapping

Of course, this is already covered by #36 - Processs all salmon watersheds - but adding just these three is a good short term step towards that.

Based on review by Nick M, exclude these points from channel width measures:

stream_sample_site_id IN 
(44813,44815,10997,8518,37509,37510,53526,15603,98,8644,117,8627,142,8486,8609,15609,10356)

stream_crossing_id IN  (57592,123894,57408,124137)

populate remaining fields in 02_model/sql/00_report.sql, set NULLs to zero (for serving as MVT) where appropriate

Upgrade old python 2.7 code to python 3, or migrate completely to sql

See blue_line_key=356518984 at Frozen Lake in Flathead watershed.

Segment linear_feature_id=706880333 is almost 40% over 528m - a gradient barrier should be created at the beginning of this line. Problem likely because this stream segment is not connected with the rest of the stream, it dips down into Montana.

For WCRP:

a) the total habitat blocked by each barrier type (Barrier Extent)
b) the % of each barrier type that are barriers/potential barriers (out of those that have been assessed) (Barrier Severity)

likely a spritezero issue:
404 - https://www.hillcrestgeo.ca/projects/cwf_wcrp/[email protected]

Channel width predictor model is provided by Nick Mazany-Wright at CWF based on an R analysis that is run per watershed group.

• add Lower Nicola when model available by dropping a query in to https://github.com/smnorris/bcfishpass/blob/main/01_prep/04_channel_width/sql/modelled_channel_width.sql#L40
• consider how to encode the model/model param in the database (rather than transcribing to SQL)
• consider adding the R analysis to the repository so that additional watersheds can be run

Many QA updates to various features are required.
Currently several data files are included, mostly in separate folders:

• 01_prep/01_modelled_stream_crossings/data/modelled_stream_crossings_fixes.csv
• 01_prep/02_pscis/data/pscis_barrier_result_fixes.csv
• 01_prep/02_pscis/data/pscis_modelledcrossings_streams_xref.csv
• 01_prep/05_other/data/falls.geojson

There may be other lookups required as well, and usage needs to be documented.
It might be easier to consolidate these data files into a single folder so the user applying QA can see them all / and the documentation in the same spot.

review

• PSCIS - stream - modelled crossing matches
• modelled crossings with most upstream stream
• dams with most upstream stream (Owen Lake outlet in particular)

for bcfishpass.barriers_anthropogenic, create view/report Including the usual passable/potentially accessible:

• area upstream
• length upstream total
• length upstream, by slope class

etc- for prioritization.

Also include key PSCIS and road attributes where available.

Seems worth differentiating between the various barrier status codes, especially if we start adding partial barriers.

Summarize the status per watershed (but perhaps make the reporting area flexible):

• number of crossings/type etc
• length, pct stream accessible / potentially accessible
• length, pct habitat accessible / potentially accessible
  etc

Join crossings to whse_basemapping.dbm_mof_50k_grid and add one of map_tile / map_tile_display_name

These features are out of scope for remediation and are already included as 'definite barriers'.

Currently only CWF target watersheds are processed (BULK,'HORS,'LNIC,ELKR)
For FPTWG, process and display all watershed groups containing salmon (as identified in CWF scoping exercise from spring 2020, https://github.com/smnorris/cwf_fishpassage/blob/master/outputs/01_wsg_spp.csv)

review

• PSCIS - stream - modelled crossing matches
• modelled crossings with most upstream stream
• dams with most upstream stream

Crossings come from 3 sources:

1. modelled crossings
2. PSCIS
3. dams

Because each source has its own set of ids, we create a new aggregated_crossings_id column for the crossings table. This is generally fine because the source modelled_crossing_id, pscis crossing_id and dam_id are kept. But for the dnstr_crossings and dnstr_barriers_anthropogenic columns it would be much nicer to use the source ids for clarity. Plus yet another crossing id column gets even more confusing.

There shouldn't be much/any overlap in the id values - perhaps there is a way to combine them into the aggregated_crossings_id without changing them?

Or simply create a primary key based on all three columns? This might work in the db but will not work for QGIS or other tools which need a single unique id

Not sure why ali.crossings modelled_crossing_id 4606812 is listed as PASSABLE . Was this reviewed by hand or are there bridge details from a railway layer? If so, should there be a flag to indicate this?

I had a look at https://github.com/smnorris/bcfishpass/blob/main/01_prep/01_modelled_stream_crossings/sql/08_identify_open_bottom_structures.sql for clues but couldn't figure things out.

We want to be able to update model output with new PSCIS crossings without having to re-run the entire job.

• make sure the stream segmenter retains all columns from source in output
• add a script/method for updating anthropogenic barriers with latest PSCIS crossings
• consider adding a function (trigger?) for deleting barriers

Helpful for self documentation

Add table that attempts to summarize road tenure info associated with each crossing.
Keep this as a separate table from the main reports, let the user join them together.

https://github.com/smnorris/bcfishpass/blob/main/02_model/sql/model.sql burns output values to the streams table in the accessiblility_model_<species> columns.

CURRENT CODES

ACCESSIBLE - downstream of definite barriers and any modelled or assessed anthropogenic barriers
POTENTIALLY ACCESSIBLE - downstream of definite barriers, upstream of modelled anthropogenic barrier(s)
POTENTIALLY ACCESSIBLE - PSCIS BARRIER DOWNSTREAM - downstream of definite barriers, upstream of assessed PSCIS barrier / potential barrier

PROPOSED ADDITIONS

POTEENTIALLY ACCESSIBLE - PSCIS POTENTIAL DOWNSTREAM - upstream of crossing assessed as POTENTIAL
ACCESSIBLE - REMEDIATED - upstream of a remediated crossing, downstream of any other barriers

Remediated class would require extracting remediations into a separate temp table and running bcfishpass.py add-downstram-ids ...

The summary report should be synced with code commits - call summary report from model.sh rather than requiring user to remember to run the report as well

Some streams do not get assigned a channel width, eg linear_feature_id = 17055882.

This is because they don't get a precipitation value... because when we relate the precip data provided based on watershed_feature_id of the fundamental watersheds to streams based on watershed codes... there is actually no fundamental watershed with watershed codes matching this stream.

This seems to generally be an issue for segments in rivers.

Fix is likely to do a spatial join for streams with watershed code / local code combination that is not present in fwa_watersheds_poly.

psql2csv is listed as a requirement and is available on Homebrew .

I am currently on a windows machine.

Homebrew 2.0.0 documentation indicates that homebrew officially supports Linux and Windows 10 with Windows Subsystem for Linux (WSL).

Do you think this is the way to go and if so do you think that the other requirements - bcfishobs, bcdata and pgdata should be loaded into a conda env that is loaded inside of a WSL as per https://gist.github.com/kauffmanes/5e74916617f9993bc3479f401dfec7da ?

When modelled crossings meet modelled habitat-based criteria that flags them as potentially important and dnstr_crossings is NULL it allows the reviewer to have a look at only the most downstream crossing within the watershed of interest. If that crossing looks like a good crossing to follow up on in the field it can be assumed that the rest of the crossings upstream will be looked at in turn if/when it makes sense.

Currently, all modelled_crossings that meet my habitat-based query in the Upper Elk River have dnstr_crossings . A review of a subset of these indicates that often these crossings are actually on the Elk River and have a barrier_status of PASSABLE (ex aggregated_crossings_id = 22095 and aggregated_crossings_id = 9510) .

Is there a way to pull the aggregated_crossings_id with the barrier_status = PASSABLE out of the dnstr_crossings column of the crossings layer or add another queriable column for review purposes?

Append CWF selected falls from New Graph file to cwf.waterfalls_addtional

Create a conda environment that includes all dependencies - this should take some of the pain out of cross-platform installation (but windows users would still have to edit the shell scripts or use bash via windows subsystem linux or similar)

We want to multiply CO rearing habitat by 1.5x in wetlands to better account for it, but currently wetlands are not included at all in the spawning/rearing models - only rivers and edge_type IN (1000,1100,2000,2300) (single line bluelines, primary and secondary flow).

Are wetlands potential rearing habitat for all species, or just Coho?

This is a lookup simply for remapping barrier_result_code based on manual review.
There is no need to retain PSCIS info other than the stream_crossing_id - remove columns:

• crossing_subtype_code
• barrier_result_code
• assessment_comment

Add reviewer, notes columns, a structure similar to the other lookup/xref tables.

For various upstream summary indicators in point_report.sql, report in m rather than km.

For goal setting and prioritization, identify potential spawning (and perhaps rearing) habitat along streams.

One simple habitat potential model based on attributes already in the streams table:

This just requires adding 4-5 new columns (one for a combination of the four species) and applying an UPDATE based on the gradient and stream order.

A better/more refined option is to model potential spawning habitat based on gradient, channel width and potentially channel confinement. Channel width would be modelled based on: upstream watershed area, mean annual precip, stream magnitude, area wetlands upstream etc. Channel confinement modelling TBD.

Potentially even better is to model potential habitat based on gradient and modelled annual discharge for the stream.

Some gradient barrier points are in waterbodies because the valley slopes up quickly from river/lake edge.
When running the slope calculation, remove streams of waterbody edge types.

Document how to tweak the barriers/observations/streams sql files