search

smnorris / bcfishpass

Model and monitor aquatic habitat connectivity in BC. Tools to plan and prioritize the assessment and remediation of barriers.
https://smnorris.github.io/bcfishpass
Apache License 2.0
8 stars 13 forks source link

add WCRP tracking tables #482

Open smnorris opened 7 months ago

smnorris commented 7 months ago

Add table schemas to db, and include tracking info in the WCRP views. Load will have to be done via a workflow in the CWF jobs repo (in order to access that db)

smnorris commented 4 months ago

BULK https://github.com/Canadian-Wildlife-Federation/bulkley-wcrp-online/tree/main/content/tracking-tables ELKR https://github.com/Canadian-Wildlife-Federation/elk-wcrp/tree/main/content/tracking-tables HORS https://github.com/Canadian-Wildlife-Federation/horsefly-wcrp/tree/main/content/tracking-tables LNIC https://github.com/Canadian-Wildlife-Federation/lower-nicola-wcrp/tree/main/content/tracking-tables

smnorris commented 4 months ago

@nickw-CWF @andrewp-CWF - tracking tables questions/suggestions:

Draft tables here, based on structure posted in Bulkley WCRP repo: https://github.com/smnorris/bcfishpass/blob/wcrp_tracking/db/bcfishpass/tables/wcrp.sql This branch has been deleted, I can try and find/restore if required.

  1. Does proposed schema look generally ok?
  2. Am I correct in presuming the primary key should be existing aggregated_crossings_id?
  3. Can we presume all crossings/barriers will be in the database already, or can additional barriers be tracked in these tables? (ie, barriers that are not on mapped streams)
  4. Related to 2 above, presumably easting/northing/zone/barrier_type could all come from existing values in the database and should not need to be entered in the tracking table - or is a place to store better location information, and if so, should it be used to update the crossings table?
  5. If barrier_type is not coming from existing data, what would be valid values?
  6. I've standardized on comments and removed notes column from confirmed barriers table
  7. UPPER_CASE codes for values, with descriptions of codes in xls dropdowns probably more reliable than variable case checks
  8. For assessment_step_completed, we can extract the latest PSCIS status from the database. Maybe only include non-PSCIS values in user editable the spreadsheets, overriding the PSCIS status if present?
  9. Does reason in the confirmed barriers table refer to the others involved column?
  10. A very minor nit to pick, but I wonder if the table names could be suffixed with _crossings / _sites / _locations or similar rather than _structures? A structure may not exist at a given site/crossing.
smnorris commented 3 months ago

I'm open to options but it seems like WCRP specific data might best fit in a CWF code repository rather than in bcfishpass itself.