smnorris/bcfishobs

consistent fish_obsrvtn_pnt_distinct_id

smnorris opened this issue · 1 comments

instead of using a serial PK, ensure the PK is consistent between runs by using a reference to the stream network like this:

id bigint
     GENERATED ALWAYS AS ((((blue_line_key::bigint + 1) - 354087611) * 10000000) + round(downstream_route_measure::bigint)) STORED PRIMARY KEY

This does not work in the initial creation of distinct points - they are grouped before matching to streams. This can perhaps be applied to bcfishobs.fiss_fish_obsrvtn_events