PSCIS data flow

[smnorris]

If we want a stable database and reports, new PSCIS crossings should not be included in the bcfishpass db without first being reviewed by a human - crossings on unmapped tribs are incorrectly snapped to nearby rivers.

For example, stream_crossing_id 198347 (assessed March 22, 2023) was automatically loaded and matched to the Eve River, incorrectly blocking 82km of potentially accessible Steelhead "habitat" upstream. More can probably be done to improve the accuracy of matching crossings to streams - but it is unlikely to ever be perfect.

Review of the handful of crossings that make it in to the system should be quick and easy but:

• who might do this review?
• on what schedule?

@CaptainMarmot @NewGraphEnvironment

[smnorris]

An alternative to manually checking every PSCIS crossing to be loaded to bcfishpass (and thus delay db updates and updates to various products) might be to continue with existing automated loads and add an automated check - generating some kind of alert if a new PSCIS crossing is assessed as a barrier and blocks more than x km of stream. Doing that analysis before load of PSCIS data to bcfishpass.crossings table and holding back any suspect records might be possible as well.

[CaptainMarmot]

Thanks for mentioning this Simon - good catch.
I think anything we can do to automate this check would be a good thing. The original PSCIS load routine has a spatial check which confirms the co-ordinates are inside BC but this is more involved for sure.
The hope is to eventually move away from ESF submissions and have the Survey 123 generated submission go straight into the Oracle data tables. I can discuss this idea with the GeoBC folks who are working on that and see if there is a point in the validation process where we could try to do this.

[CaptainMarmot]

How do I add another person to this issue? I was going to loop Eric Milan from GeoBC in. From: Simon Norris ***@***.***> Sent: Thursday, July 27, 2023 10:53 AM To: smnorris/bcfishpass ***@***.***> Cc: Mount, Craig A WLRS:EX ***@***.***>; Mention ***@***.***> Subject: Re: [smnorris/bcfishpass] PSCIS data flow (Issue #373) [EXTERNAL] This email came from an external source. Only open attachments or links that you are expecting from a known sender. An alternative to checking every PSCIS crossing that gets submitted (and thus delaying updates) might be to continue with automated loads and add an automated check - generating some kind of alert if a new PSCIS crossing is assessed as a barrier and blocks more than x km of stream. — Reply to this email directly, view it on GitHub<#373 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ALKECN4VWFEIX6TJR3UXGYDXSKTGFANCNFSM6AAAAAA2XYWKGQ>. You are receiving this because you were mentioned.Message ID: ***@***.******@***.***>>

[smnorris]

If Eric has a GH account, tag them like I tagged you with the @username

[smnorris]

All my thoughts above were related only to bcfishpass though - adding checks to PSCIS itself might be a better fix but that is a much different question.

[CaptainMarmot]

Understood - thanks for clarifying that. So I guess that would be a check / correction that would have to happen on your end?

[smnorris]

Yes, once the points are matched to streams, there could be a filter on the max length upstream for barriers.

https://github.com/smnorris/bcfishpass/blob/main/model/01_access/pscis/sql/04_pscis.sql

We would have to determine

• what would be the threshold
• would a crossing be filtered out or just flagged for review
• who/when would review the filtered crossings and match them to correct stream (where possible)

[smnorris]

Related, from @nickw-CWF : creation of bcfishpass.crossings assumes that PSCIS crossing coordinates are to be preferred over mapped crossing coordinates - and this is not always a good assumption.

For example, PSCIS crossing 195944 is matched to the correct modelled crossing (1800255) but the location of the point in bcfishpass.crossings is from the PSCIS GPS coordinates, which are way off. The best fix is directly in PSCIS but we need an interm method for adjusting errors like this in bcfishpass (while awaiting PSCIS data fixes).

[smnorris]

Another PSCIS data fix that I'm not sure how to apply in bcfishpass - office review indicates a bridge at pscis crossing id 196200 (Bittner Cr at Foreman Rd), google confirms the fix https://news.gov.bc.ca/releases/2020TRAN0099-001219, but latest in PSCIS is a culvert assessed as a barrier in 2014.

Fixable via https://github.com/smnorris/bcfishpass/blob/main/data/user_pscis_barrier_status.csv but only the barrier status is updated, not the crossing type / PSCIS status etc.

[NewGraphEnvironment]

Well, that is incredibly serendipitous that you would comment on this just right now. About 20 minutes ago I was in a plane about to land in Prince George and I flew over Bittner Creek and thought. Hey I know that site - I assessed it.

https://www.newgraphenvironment.com/fish_passage_moti_2022_reporting/results-and-discussion.html#bittner-creek---196200

[smnorris]

So I have to ask... have you submitted the latest assessments to PSCIS?

[smnorris]

closing in favour of #521.

• fixing matching of PSCIS crossings to FWA streams will continue to be case by case as required
• fixes to PSCIS itself to be noted in separate repository