Database Manual

Oak Ridges Moraine Groundwater Program

Appendix G.34

G.34 Incorporation of Bedrock Elevations as Bedrock Picks

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, geol_unit_code, gnd_elev, top_elev, sessionnum, data_id, sys_temp1, 
  sys_temp2, sys_record_id )
select
t1.loc_id
,t1.formation
,t1.geol_unit_code
,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
,7 as geol_unit_code
,d.ground_elev as gnd_elev
,d.bedrock_elev as top_elev
,573 as sessionnum
,240500573 as data_id
,'20240401a' as sys_temp1
,20240401 as sys_temp2
,row_number() over (order by d.loc_id) as rkey
from 
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: 2024-04-08