G.7 Update of Bedrock Elevation (Automated)
- Estimated Recurrence Time: Weekly
V_SYS_BH_BEDROCK_ELEV checks for locations/boreholes that intersect bedrock and returns the BH_BEDROCK_ELEV. This determination is made by examining D_GEOLOGY_LAYER for those locations with a GEOL_MAT1_CODE that corresponds to a ‘ROCK’ designation (i.e. having a value of ‘1’) in R_GEOL_MAT1_CODE.
The bedrock elevation to be assigned to BH_BEDROCK_ELEV in D_BOREHOLE is the value contained in ‘new_BH_BEDROCK_ELEV. This can be compared against BH_BEDROCK_ELEV.
The value of BH_BEDROCK_ELEV in D_BOREHOLE can be updated by
update d_borehole set bh_bedrock_elev=v.new_bh_bedrock_elev from d_borehole as dbore left outer join v_sys_bh_bedrock_elev as v on dbore.loc_id=v.loc_id where (dbore.bh_bedrock_elev is null and v.new_bh_bedrock_elev is not null) or (dbore.bh_bedrock_elev is not null and v.new_bh_bedrock_elev is null) or dbore.bh_bedrock_elev<>v.new_bh_bedrock_elev
Note that this process has been automated - refer to Appendex G.32 for details.