generate primary key for falls

[smnorris]

fish_obstacle_point_id is only guaranteed to be a unique identifier when the data is downloaded, it is not stable between downloads (when new records are loaded).

Generate a stable primary key based on geom and some other combination of columns

[smnorris]

geom || source is not unique, even if just considering falls:

select count (*) FROM whse_fish.fiss_obstacles_pnt_sp ;
 count 
-------
 24832
(1 row)

select count (*) FROM (SELECT distinct geom, source from whse_fish.fiss_obstacles_pnt_sp) f;
 count 
-------
 24070
(1 row)

select count (*) FROM whse_fish.fiss_obstacles_pnt_sp  where obstacle_name = 'Falls';
 count 
-------
  6165
(1 row)

select count (*) FROM (SELECT distinct geom, source from whse_fish.fiss_obstacles_pnt_sp where obstacle_name = 'Falls') f;
 count 
-------
  5981

[smnorris]

As in current script I'll pull distinct locations with max height at that location - and create new primary key that is combination of blue_line_key and downstream_route_measure.

[smnorris]

Adding pk to the fiss obstacles table isn't worth the effort given that source and geom aren't unique. We don't care about the rest of the data in the source table (Dams can come from https://github.com/smnorris/bcdams where FISS data is already added).

Weirs are the only thing lost from v2.3.1 of model but:

• there are only 22 provincially
• they may or may not be barriers

They can be added manually through misc/other channel on case by case basis. If lots of weirs start getting added to FISS at some later date maybe there will be a primary key in the db by then...

[smnorris]

Actually, I'm going to move the falls code (and data) over into bcfishpass. This can go back to just being an observations repo for now.