search

smnorris / bcfishobs

Reference BC Known Fish Observations to the Freshwater Atlas stream network
Apache License 2.0
6 stars 2 forks source link

consistent fish_obsrvtn_pnt_distinct_id #23

Closed smnorris closed 2 years ago

smnorris commented 2 years ago

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
smnorris commented 2 years ago

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