{"id":166141,"name":"bcfishobs","description":"Known BC Fish Observations is documented as the most current and comprehensive information source on fish presence for the province.","url":"https://github.com/smnorris/bcfishobs","last_synced_at":"2026-04-14T16:30:17.984Z","repository":{"id":71794537,"uuid":"133072177","full_name":"smnorris/bcfishobs","owner":"smnorris","description":"Reference BC Known Fish Observations to the Freshwater Atlas stream network","archived":false,"fork":false,"pushed_at":"2025-11-07T22:36:32.000Z","size":366,"stargazers_count":6,"open_issues_count":4,"forks_count":1,"subscribers_count":1,"default_branch":"main","last_synced_at":"2026-03-10T02:34:00.299Z","etag":null,"topics":["british-columbia","fish-observations","fiss","fwa","stream-network"],"latest_commit_sha":null,"homepage":"","language":"PLpgSQL","has_issues":true,"has_wiki":null,"has_pages":null,"mirror_url":null,"source_name":null,"license":"apache-2.0","status":null,"scm":"git","pull_requests_enabled":true,"icon_url":"https://github.com/smnorris.png","metadata":{"files":{"readme":"README.md","changelog":null,"contributing":null,"funding":null,"license":"LICENSE","code_of_conduct":null,"threat_model":null,"audit":null,"citation":null,"codeowners":null,"security":null,"support":null,"governance":null,"roadmap":null,"authors":null,"dei":null,"publiccode":null,"codemeta":null,"zenodo":null,"notice":null,"maintainers":null,"copyright":null,"agents":null,"dco":null,"cla":null}},"created_at":"2018-05-11T18:00:01.000Z","updated_at":"2025-11-07T22:36:35.000Z","dependencies_parsed_at":"2025-11-21T18:01:16.534Z","dependency_job_id":null,"html_url":"https://github.com/smnorris/bcfishobs","commit_stats":{"total_commits":152,"total_committers":1,"mean_commits":152.0,"dds":0.0,"last_synced_commit":"64e667bef218e27ffb72bf5bebac20c450d4b375"},"previous_names":[],"tags_count":8,"template":false,"template_full_name":null,"purl":"pkg:github/smnorris/bcfishobs","repository_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs","tags_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs/tags","releases_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs/releases","manifests_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs/manifests","owner_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/owners/smnorris","download_url":"https://codeload.github.com/smnorris/bcfishobs/tar.gz/refs/heads/main","sbom_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs/sbom","scorecard":null,"host":{"name":"GitHub","url":"https://github.com","kind":"github","repositories_count":286080680,"owners_count":30481424,"icon_url":"https://github.com/github.png","version":null,"created_at":"2022-05-30T11:31:42.601Z","updated_at":"2026-03-13T23:57:05.347Z","status":"ssl_error","status_checked_at":"2026-03-13T23:56:58.046Z","response_time":60,"last_error":"SSL_read: unexpected eof while reading","robots_txt_status":"success","robots_txt_updated_at":"2025-07-24T06:49:26.215Z","robots_txt_url":"https://github.com/robots.txt","online":false,"can_crawl_api":true,"host_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub","repositories_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/repositories","repository_names_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/repository_names","owners_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/owners"}},"owner":{"login":"smnorris","name":"Simon Norris","uuid":"1323293","kind":"user","description":"","email":"","website":"www.hillcrestgeo.ca","location":"British Columbia","twitter":null,"company":"Hillcrest Geographics","icon_url":"https://avatars.githubusercontent.com/u/1323293?v=4","repositories_count":52,"last_synced_at":"2025-10-10T16:35:26.744Z","metadata":{"has_sponsors_listing":false},"html_url":"https://github.com/smnorris","funding_links":[],"total_stars":51,"followers":55,"following":38,"created_at":"2022-11-05T19:12:36.181Z","updated_at":"2025-10-10T16:35:26.744Z","owner_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/owners/smnorris","repositories_url":"https://repos.ecosyste.ms/api/v1/hosts/GitHub/owners/smnorris/repositories"},"packages":[],"commits":{"id":1462543,"full_name":"smnorris/bcfishobs","default_branch":"main","total_commits":178,"total_committers":1,"total_bot_commits":0,"total_bot_committers":0,"mean_commits":178.0,"dds":0.0,"past_year_total_commits":17,"past_year_total_committers":1,"past_year_total_bot_commits":0,"past_year_total_bot_committers":0,"past_year_mean_commits":17.0,"past_year_dds":0.0,"last_synced_at":"2026-04-11T15:00:39.167Z","last_synced_commit":"217b650630d8f62c66a0490b2edb6f012bf5f94e","created_at":"2024-01-04T00:19:37.643Z","updated_at":"2026-04-11T15:00:39.127Z","committers":[{"name":"Simon Norris","email":"snorris@hillcrestgeo.ca","login":"smnorris","count":178}],"past_year_committers":[{"name":"Simon Norris","email":"snorris@hillcrestgeo.ca","login":"smnorris","count":17}],"commits_url":"https://commits.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs/commits","host":{"name":"GitHub","url":"https://github.com","kind":"github","last_synced_at":"2026-04-13T00:00:06.408Z","repositories_count":6212899,"commits_count":903834069,"contributors_count":34933035,"owners_count":1144061,"icon_url":"https://github.com/github.png","host_url":"https://commits.ecosyste.ms/api/v1/hosts/GitHub","repositories_url":"https://commits.ecosyste.ms/api/v1/hosts/GitHub/repositories"}},"issues_stats":{"full_name":"smnorris/bcfishobs","html_url":"https://github.com/smnorris/bcfishobs","last_synced_at":"2026-04-09T14:01:47.680Z","status":"error","issues_count":46,"pull_requests_count":13,"avg_time_to_close_issue":12732470.846153846,"avg_time_to_close_pull_request":1281081.8333333333,"issues_closed_count":39,"pull_requests_closed_count":12,"pull_request_authors_count":1,"issue_authors_count":2,"avg_comments_per_issue":1.2826086956521738,"avg_comments_per_pull_request":0.0,"merged_pull_requests_count":12,"bot_issues_count":0,"bot_pull_requests_count":0,"past_year_issues_count":2,"past_year_pull_requests_count":2,"past_year_avg_time_to_close_issue":null,"past_year_avg_time_to_close_pull_request":19.0,"past_year_issues_closed_count":0,"past_year_pull_requests_closed_count":2,"past_year_pull_request_authors_count":1,"past_year_issue_authors_count":1,"past_year_avg_comments_per_issue":0.5,"past_year_avg_comments_per_pull_request":0.0,"past_year_bot_issues_count":0,"past_year_bot_pull_requests_count":0,"past_year_merged_pull_requests_count":2,"created_at":"2024-01-04T00:19:51.819Z","updated_at":"2026-04-09T14:01:47.680Z","repository_url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs","issues_url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub/repositories/smnorris%2Fbcfishobs/issues","issue_labels_count":{"enhancement":3,"bug":3},"pull_request_labels_count":{},"issue_author_associations_count":{"OWNER":43,"NONE":3},"pull_request_author_associations_count":{"OWNER":13},"issue_authors":{"smnorris":43,"NewGraphEnvironment":3},"pull_request_authors":{"smnorris":13},"host":{"name":"GitHub","url":"https://github.com","kind":"github","last_synced_at":"2026-04-09T00:00:10.509Z","repositories_count":14207162,"issues_count":34660469,"pull_requests_count":113258760,"authors_count":11232439,"icon_url":"https://github.com/github.png","host_url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub","repositories_url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub/repositories","owners_url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub/owners","authors_url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub/authors"},"past_year_issue_labels_count":{},"past_year_pull_request_labels_count":{},"past_year_issue_author_associations_count":{"OWNER":2},"past_year_pull_request_author_associations_count":{"OWNER":2},"past_year_issue_authors":{"smnorris":2},"past_year_pull_request_authors":{"smnorris":2},"maintainers":[{"login":"smnorris","count":56,"url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub/authors/smnorris"}],"active_maintainers":[{"login":"smnorris","count":4,"url":"https://issues.ecosyste.ms/api/v1/hosts/GitHub/authors/smnorris"}]},"events":{"total":{"IssuesEvent":4,"DeleteEvent":1,"IssueCommentEvent":3,"PushEvent":22,"PullRequestEvent":3,"CreateEvent":3},"last_year":{"IssuesEvent":4,"DeleteEvent":1,"IssueCommentEvent":3,"PushEvent":16,"PullRequestEvent":2,"CreateEvent":2}},"keywords":["british-columbia","fish-observations","fiss","fwa","stream-network"],"dependencies":[],"score":2.302585092994046,"created_at":"2024-01-04T00:07:59.021Z","updated_at":"2026-04-14T16:30:17.998Z","avatar_url":"https://github.com/smnorris.png","language":"PLpgSQL","category":"Biosphere","sub_category":"Marine Life and Fishery","monthly_downloads":0,"total_dependent_repos":0,"total_dependent_packages":0,"readme":"# bcfishobs\n\n[Known BC Fish Observations](https://catalogue.data.gov.bc.ca/dataset/known-bc-fish-observations-and-bc-fish-distributions) 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](https://www2.gov.bc.ca/gov/content/data/geographic-data-services/topographic-data/freshwater).\n\nThe scripts:\n\n- download `whse_fish.fiss_fish_obsrvtn_pnt_sp`, the latest observation data from DataBC\n- download a lookup table `whse_fish.wdic_waterbodies` used to match the 50k waterbody codes in the observations table to FWA waterbodies\n- download a lookup table `species_cd`, linking the fish species code found in the observation table to species name and scientific name\n- load above tables to a PostgreSQL database\n- discard any observations not coded as `point_type_code = 'Observation'` (`Summary` records are all duplicates of `Observation` records)\n- references the observation points to their position on the FWA stream network (as outlined below)\n- creates output table `bcfishobs.observations`, as documented below\n\n### Matching logic, observations\n\n1. For observation points associated with a lake or wetland (according to `wbody_id`):\n\n    - match observations to the closest FWA stream in a waterbody that matches the observation's `wbody_id`, within 1500m\n    - 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\n\n2. For observation points associated with a stream:\n\n    - match to the closest FWA stream within 100m that has a matching watershed code (via `fwa_streams_20k_50k_xref`)\n    - for remaining unmatched records within 100m of an FWA stream, match to the closest stream regardless of a match via watershed code\n    - for remaining unmatched records between 100m to 500m of an FWA stream, match to the closest FWA stream that has a matching watershed code\n\nThis logic is based on the assumptions:\n\n- for observations noted as within a lake/wetland, we can use a relatively high distance threshold for matching to a stream because\n    -  an observation may be on a bank far from a waterbody flow line\n    -  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\n- 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\n\n## General requirements\n\n- PostgreSQL/PostGIS \n- a FWA database created by [fwapg](https://github.com/smnorris/fwapg)\n- GDAL \u003e= 3.4\n- Python (\u003e=3.6)\n- [bcdata](https://github.com/smnorris/bcdata)\n\n\n## Run the scripts\n\nScripts presume that:\n\n- environment variable `DATABASE_URL` points to the appropriate db\n- FWA data are loaded to the db via `fwapg`\n\nTo set up the database/create schema/load required supporting datasets:\n\n    $ bcdata bc2pg -e -c 1 whse_fish.fiss_fish_obsrvtn_pnt_sp\n    $ git clone https://github.com/smnorris/bcfishobs.git\n    $ cd bcfishobs\n    $ psql $DATABASE_URL -f db/v0.2.0.sql\n    $ psql $DATABASE_URL -f db/v0.3.0.sql\n    $ psql $DATABASE_URL -f db/v0.3.1.sql\n    $ psql $DATABASE_URL -f db/v0.3.2.sql\n    $ ./load_supporting_data.sh\n\nTo run the job:\n\n    $ ./process.sh\n\n## Output table\n\n#### `bcfishobs.observations`\n\nSource observations that have been successfully matched to a FWA stream.\nGeometries are snapped to the closest point on the the stream network to which the observation is matched.\n\nFor a list of columns and descriptions, see [db/v0.3.0.sql](db/v0.3.0.sql) or the [bcfishpass feature service](https://features.hillcrestgeo.ca/bcfishpass/collections/bcfishobs.observations.html).\n\n\n## Use the data\n\nWith the observations now linked to the Freswater Atlas, we can write queries to find fish observations relative to their location on the stream network.\n\n### Example 1\n\nList all species observed on the Cowichan River (`blue_line_key = 354155148`), downstream of Skutz Falls (`downstream_route_meaure = 34180`).\n\n```\nSELECT DISTINCT species_code\nFROM bcfishobs.observations\nWHERE blue_line_key = 354155148 AND\ndownstream_route_measure \u003c 34180\nORDER BY species_code;\n\n species_code\n--------------\n ACT\n AS\n BNH\n BT\n C\n CAL\n CAS\n CH\n CM\n CO\n CT\n DV\n EB\n GB\n KO\n L\n MARFAL\n RB\n SA\n SB\n ST\n TR\n TSB\n```\n\n### Example 2\n\nWhat is the slope (percent) of the stream at observations of Steelhead in `COWN` watershed group (on single line streams)?\n\n```\nSELECT \n  e.observation_key,\n  s.gnis_name,\n  s.gradient\nFROM bcfishobs.observations e\nINNER JOIN whse_basemapping.fwa_stream_networks_sp s\nON e.linear_feature_id = s.linear_feature_id\nWHERE e.species_code = 'ST'\nAND e.watershed_group_code = 'COWN'\nAND s.edge_type = 1000\nORDER BY e.wscode, e.localcode, e.downstream_route_measure;\n\n observation_key |      gnis_name       | gradient\n-----------------+----------------------+----------\n 7367b743b4      | Cowichan River       |   0.0071\n 57ac3d846b      | Cowichan River       |   0.0614\n ed8506273b      | Cowichan River       |   0.0614\n 78e91acb40      | Koksilah River       |   0.0058\n 6904a74c2d      | Koksilah River       |   0.0369\n 8fe2263fa4      | Koksilah River       |   0.0761\n...\n```\n\n### Example 3\n\nWhat are the order, elevation and gradient of all Arctic Grayling observations in the Parsnip watershed group?\n\n```\nSELECT\n  observation_key,\n  s.gradient,\n  s.stream_order,\n  round((ST_Z((ST_Dump(ST_LocateAlong(s.geom, e.downstream_route_measure))).geom))::numeric) as elevation\nFROM bcfishobs.observations e\nINNER JOIN whse_basemapping.fwa_stream_networks_sp s\nON e.linear_feature_id = s.linear_feature_id\nWHERE e.species_code = 'GR'\nAND e.watershed_group_code = 'PARS'\nORDER BY e.wscode, e.localcode, e.downstream_route_measure;\n\n observation_key | gradient | stream_order | elevation\n-----------------+----------+--------------+-----------\n c91700c9d1      |        0 |            7 |       674\n f60bc8c392      |   0.0007 |            7 |       675\n e8209ab836      |   0.0007 |            7 |       675\n bd3162b43f      |   0.0004 |            7 |       685\n 0ba700a120      |        0 |            6 |       694\n 49d94cdb5c      |   0.0003 |            7 |       696\n fee71785e4      |   0.0003 |            7 |       698\n...\n```\n\n## Warnings\n\n### Primary key and duplicates\n\nColumn `fish_observation_point_id` is present in the ouput table but should generally be disregarded, it is unique when downloaded but unstable over time.\n\nColumn `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.\n\n## Scheduled job\n\nThis script is run weekly by workflows in the [`bcfishpass` repository](https://github.com/smnorris/bcfishpass). The resulting parquet file is available at [https://nrs.objectstore.gov.bc.ca/bchamp/bcfishobs/observations.parquet](https://nrs.objectstore.gov.bc.ca/bchamp/bcfishobs/observations.parquet).","funding_links":[],"readme_doi_urls":[],"works":{},"citation_counts":{},"total_citations":0,"keywords_from_contributors":[],"project_url":"https://ost.ecosyste.ms/api/v1/projects/166141","html_url":"https://ost.ecosyste.ms/projects/166141"}