Appendix G.7

G.7 Update of Bedrock Elevation (Automated)

• Tables
  • D_BOREHOLE
  • D_GEOLOGY_LAYER
• Views
  • V_SYS_BH_BEDROCK_ELEV
• 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.