View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

G.17 Correction of elevations (D_BOREHOLE and D_LOCATION_ELEV)

At times, elevation corrections are made (generally through SiteFX) that update elevations in D_BOREHOLE that do not get passed onto D_LOCATION_ELEV. In particular, the latter table should catch any locations whose status has been designated as ‘surveyed’ (i.e. QA_ELEV_CONFIDENCE_CODE of ‘1’).

CHK_D_LOC_ELEV_SURV_NULL checks whether a surveyed elevation exists for any location with a QA_ELEV_CONFIDENCE_CODE of ‘1’. This view returns elevations from D_BOREHOLE and D_LOCATION_ELEV to be used as a check if no SURVEYED_ELEV exists. In most cases, the BH_GND_ELEV_OUOM should be used to update SURVEYED_ELEV as BH_GND_ELEV should match that of ASSIGNED_ELEV (if the latter do not match, the BH_GND_ELEV* should match; if these do not, the original documents from which the elevations are drawn should be referenced). Correct these elevations as necessary (i.e. in D_LOCATION_ELEV).

CHK_D_LOC_ELEV_D_BOREHOLE_NOT_ASSIGNED should be run to compare the D_LOCATION_ELEV and D_BOREHOLE tables. Elevations should be updated as appropriate (with regard to the QA_* codes). Note that, in the absence of surveyed elevations, the current value in BH_GND_ELEV should be copied into ELEV_ORIGINAL if the latter is NULL. Update all depths in D_BOREHOLE before changing the elevations.

CHK_D_LOC_ELEV_D_BOREHOLE should be run subsequently (this compares the values/differences between the D_BOREHOLE and D_LOCATION_ELEV tables). BH_GND_ELEV_OUOM (and *_UNIT) should be updated appropriately with both BH_GND_ELEV and BH_BOTTOM_ELEV values deleted. The SiteFX update routine should then be run.

A complete example follows. Firstly, include/add the missing LOC_IDs into D_LOCATION_ELEV:

insert into [OAK_20120615_MASTER].dbo.D_LOCATION_ELEV 
(LOC_ID)
SELECT 
 [LOC_ID]
FROM 
[OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_Missing]

Then update the ‘DEM_MNR_10m_v2’ and ‘DEM_SRTM_90m_v41’ fields based upon the externally derived elevations for the missing locations:

update [OAK_20120615_MASTER].dbo.D_LOCATION_ELEV
set
DEM_MNR_10m_v2=leu.MNR
,DEM_SRTM_90m_v41=leu.SRTM
FROM 
[OAK_20120615_MASTER].dbo.D_LOCATION_ELEV as delev
inner join [temphold].[dbo].[LOC_ELEV_UPDATE_20150320] as leu
on delev.loc_id=leu.loc_id

Check for surveyed values and modify the SURVEYED_ELEV field as appropriate:

SELECT 
 [LOC_ID]
,[ASSIGNED_ELEV]
,[SURVEYED_ELEV]
,[DEM_MNR_10m_v2]
,[BH_GND_ELEV]
,[BH_GND_ELEV_OUOM]
,[BH_GND_ELEV_UNIT_OUOM]
FROM [OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_SURV_NULL]

And then:

update [OAK_20120615_MASTER].dbo.D_LOCATION_ELEV
set
SURVEYED_ELEV=t1.BH_GND_ELEV
from 
[OAK_20120615_MASTER].dbo.D_LOCATION_ELEV as delev
inner join
(
select
chk.LOC_ID 
,chk.BH_GND_ELEV
from 
[OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_D_BOREHOLE_NOT_ASSIGNED] as chk
where
QA_COORD_CONFIDENCE_CODE=1 or QA_ELEV_CONFIDENCE_CODE=1
) as t1
on delev.LOC_ID=t1.LOC_ID

Assign those elevations, as appropriate (i.e. using the MNR or SRTM DEM) for those locations that are not boreholes (and, thus, do not exist in the D_BOREHOLE table):

update [OAK_20120615_MASTER].dbo.D_LOCATION_ELEV
set
assigned_elev=
 case 
 when delev.DEM_MNR_10m_v2 is not null then delev.DEM_MNR_10m_v2
 else delev.DEM_SRTM_90m_v41
 end 
from 
[OAK_20120615_MASTER].dbo.D_LOCATION_ELEV as delev
inner join [OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_D_BOREHOLE_NOT_ASSIGNED] as chk
on delev.loc_id=chk.loc_id
where 
chk.bh_gnd_elev is null

Update the ELEV_ORIGINAL field based upon the existing ground elevation in D_BOREHOLE:

update [OAK_20120615_MASTER].dbo.D_location_elev 
set
elev_original=dbore.BH_GND_ELEV
from 
[OAK_20120615_MASTER].dbo.D_LOCATION_ELEV as delev
inner join [OAK_20120615_MASTER].dbo.D_BOREHOLE as dbore
on delev.loc_id=dbore.loc_id 
inner join [OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_D_BOREHOLE_NOT_ASSIGNED] as chk
on dbore.loc_id=chk.loc_id

Update the elevations and depth in D_BOREHOLE (note that the elevation to use is dependent upon whether there is a SURVEYED, MNR or SRTM value):

update [OAK_20120615_MASTER].dbo.D_BOREHOLE
set
bh_bottom_ouom=bh_bottom_depth 
,BH_BOTTOM_UNIT_OUOM='mbgs'
,bh_bottom_elev=null
,BH_GND_ELEV=null 
,bh_gnd_elev_ouom=chk.DEM_MNR_10m_v2
,BH_DEM_GND_ELEV=chk.DEM_MNR_10m_v2
from 
[OAK_20120615_MASTER].dbo.D_BOREHOLE as dbore
inner join [OAK_CHECKS].dbo.CHK_D_LOC_ELEV_D_BOREHOLE_NOT_ASSIGNED as chk
on dbore.loc_id=chk.loc_id

Assign the appropriate value to ASSIGNED_ELEV:

update [OAK_20120615_MASTER].dbo.D_LOCATION_ELEV
set
ASSIGNED_ELEV=
case
when delev.SURVEYED_ELEV is not null then delev.SURVEYED_ELEV
when delev.DEM_MNR_10m_v2 is not null then delev.DEM_MNR_10m_v2
else delev.DEM_SRTM_90m_v41
end
from
[OAK_20120615_MASTER].dbo.D_LOCATION_ELEV as delev
inner join [OAK_CHECKS].dbo.CHK_D_LOC_ELEV_D_BOREHOLE_NOT_ASSIGNED as chk
on delev.loc_id=chk.loc_id

The D_BOREHOLE table can (now) be updated based upon the differences found between the two tables (note the correction of the BH_BOTTOM_ELEV):

update [OAK_20120615_MASTER].dbo.D_BOREHOLE
set
BH_GND_ELEV=chk.ASSIGNED_ELEV
,BH_GND_ELEV_OUOM=chk.ASSIGNED_ELEV
,BH_GND_ELEV_UNIT_OUOM='masl'
,BH_DEM_GND_ELEV=chk.ASSIGNED_ELEV
,BH_BOTTOM_ELEV=chk.ASSIGNED_ELEV-dbore.BH_BOTTOM_DEPTH
from 
[OAK_20120615_MASTER].dbo.D_BOREHOLE as dbore
inner join [OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_D_BOREHOLE] as chk
on dbore.loc_id=chk.loc_id

Periodically, a check should be made that ‘new’ boreholes (or other location types) have been included in the D_LOCATION_ELEV table. In this case, the D_LOC_ELEV_MISSING view will return those LOC_IDs that do not appear in latter table. These can be incorporated directly using the query:

insert into [OAK_20120615_MASTER].dbo.D_LOCATION_ELEV
(LOC_ID)
SELECT
[LOC_ID]
FROM [OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_Missing]

The CHK_D_LOC_ELEV_NOT_ASSIGNED view returns the coordinates of those LOC_IDs that do not have an ASSIGNED_ELEV (or a DEM value assigned) in the D_LOCATION_ELEV table - this is used to directly import those locations into a GIS so as to determine the elevations (from the MNR and SRTM DEMs).

Inserting and populating new LOC_IDs is described further in Section G.4.