G.34 Incorporation of Bedrock Elevations as Bedrock Picks
- Estimated Recurrence Time: As needed
For locations where a bedrock elevation has not been incorporated in the D_PICK table through direct user interpretation, the value found in D_BOREHOLE (BH_BEDROCK_ELEV) can be used instead. Refer to Appendix G.7 for details concerning how this field is populated.
For inclusion, these lcoations should be checked for being within the ORMGP study area and having a QA_COORD_CODE that is less than ‘6’ (which relates to position accuracy). In addition, V_SYS_BH_BEDROCK_ELEV is called to examine the GEOL_SUBCLASS_CODE; this is used to tag those geologic layers that should not be used for populating the D_PICK table. Usually this relates to likely invalid values that cannot be (or have not been) checked against their original data sources.
This is accomplished through
insert into d_pick ( loc_id, formation, gnd_elev, top_elev, sessionnum, data_id, sys_temp1, sys_temp2, sys_record_id ) select t1.loc_id ,t1.formation ,t1.gnd_elev ,t1.top_elev ,t1.sessionnum ,t1.data_id ,t1.sys_temp1 ,t1.sys_temp2 ,t2.sri from ( select d.loc_id ,'Top of Bedrock' as formation ,d.ground_elev as gnd_elev ,d.bedrock_elev as top_elev ,570 as sessionnum ,240500570 as data_id ,'20231106a' as sys_temp1 ,20231106 as sys_temp2 ,row_number() over (order by d.loc_id) as rkey from --d_borehole as d v_gen_borehole as d inner join v_sys_agency_ormgp as vorm on d.loc_id=vorm.loc_id inner join v_sys_loc_coords as vcoord on d.loc_id=vcoord.loc_id inner join v_sys_bh_bedrock_elev as vbed on d.loc_id=vbed.loc_id where vcoord.qa_coord_code< 6 and d.bedrock_elev is not null and d.ground_elev is not null and d.loc_id not in ( select loc_id from d_pick where geol_unit_code= 7 ) and ( vbed.geol_subclass_code is null or vbed.geol_subclass_code<>9 ) and d.loc_id <> -2147483443 ) as t1 inner join ( select t.sri ,row_number() over (order by t.sri) as rkey from ( select top 5000 v.new_id as sri from v_sys_random_id_bulk_001 as v where v.new_id not in ( select sys_record_id from d_pick ) ) as t ) as t2 on t1.rkey=t2.rkey
Note that the SYS_TEMP1, SYS_TEMP2, DATA_ID and SESSIONNUM field values should be modified to reflect the current date and a new import (escpecially with regard for the latter). In addition, the ‘select …’ portion of the SQL statement should be run to determine the number of records to be added. This number should be reflected in the ‘top …’ part of the statement for generating random identifiers.
Last Modified: 2023-11-06