Database Manual

Oak Ridges Moraine Groundwater Program

Appendix G.29

G.29 Update MOE BORE_HOLE_ID (D_LOCATION_ALIAS)

Upon import of the latest version of the MOE WWDB (refer to Section G.10), a check should be made of any new mapping between a BORE_HOLE_ID (in the MOE database; MOEDB) and a LOC_ID (in the ORMGP database; ORMGPDB). Note that this will only apply in the case of a 1:1 relationship a single WELL_ID matching a single BORE_HOLE_ID in the MOEDB; in the ORMGPDB, a single WELL_ID maps to a single LOC_ID.

This is first tested using the following script

select
v.loc_id
,t3.well_id
,t3.bore_hole_id
,v.moe_bore_hole_id
--count(*) as rcount
from 
(
select
cast(t2.well_id as int) as well_id
,b2.bore_hole_id
from 
(
select
t.well_id
from 
(
select
b.well_id
,count(*) as bhi_num
from 
moe_20190509.dbo.tblbore_hole as b
group by
well_id
) as t
where 
t.bhi_num= 1
) as t2
inner join moe_20190509.dbo.tblbore_hole as b2
on t2.well_id=b2.well_id
) as t3
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on t3.well_id=v.moe_well_id
where 
v.moe_bore_hole_id is null

Any NULL values occurring in the MOE_BORE_HOLE_ID field (or any rows returned) indicate that the BORE_HOLE_ID needs to be incorporated in the D_LOCATION_ALIAS table, as follows

insert into d_location_alias
(
loc_id, loc_name_alias, loc_alias_type_code, data_id, sys_temp1, sys_temp2
)
select
v.loc_id
,cast(t3.bore_hole_id as varchar(255)) as loc_name_alias
,3 as loc_alias_type_code
,521 as data_id
,'20190530a' as sys_temp1
,20190530 as sys_temp2
from 
(
select
cast(t2.well_id as int) as well_id
,b2.bore_hole_id
from 
(
select
t.well_id
from 
(
select
b.well_id
,count(*) as bhi_num
from 
moe_20190509.dbo.tblbore_hole as b
group by
well_id
) as t
where 
t.bhi_num= 1
) as t2
inner join moe_20190509.dbo.tblbore_hole as b2
on t2.well_id=b2.well_id
) as t3
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on t3.well_id=v.moe_well_id
where 
v.moe_bore_hole_id is null

This process should be undertaken before examining locations for new data from the latest MOEDB. Note the inclusion of the DATA_ID (which should correspond to the MOEDB under examination) and SYS_TEMP1/SYS_TEMP2 (making them available for any subsequent checks).