View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

G.15 Synchronize elevations between D_BOREHOLE and D_LOCATION_ELEVATION

The values found in BH_GND_ELEV, BH_GND_ELEV_OUOM and BH_DEM_GND_ELEV (in D_BOREHOLE) should all match that of ASSIGNED_ELEV (in D_LOCATION_ELEV). If the QA code has a value of ‘1’ (note that there are two QA confidence codes - one for coordinates, QA_COORD_CONFIDENCE_CODE, and a second for elevations, QA_ELEV_CONFIDENCE _CODE; a ‘1’ in either implies either a survey-grade coordinate in the horizontal or a survey-grade elevation in the vertical dimension), the location has been surveyed. Refer to Section 2.4 for details regarding the assignment of elevations (i.e. what values end up in ASSIGNED_ELEV and the appropriate columns in D_BOREHOLE).

Though SiteFX uses the elevations contained within D_BOREHOLE for calculation, all elevations should be stored initially in D_LOCATION_ELEV and only the ASSIGNED_ELEV should be copied across to the D_BOREHOLE table.

The view CHK_D_LOC_ELEV_D_BOREHOLE returns those LOC_IDs which have differing values between the D_LOCATION_ELEV (ASSIGNED_ELEV) and the D_BOREHOLE (BH_GND_ELEV) table. These need to be evaluated by the user to determine the appropriate values to use/apply for each of the particular elevation fields available. Example code (also checking against the QA_COORD_CONFIDENCE_CODE) would be

SELECT 
chk.[LOC_ID]
,[ASSIGNED_ELEV]
,[SURVEYED_ELEV]
,[DEM_MNR_10m_v2]
,[ELEV_ORIGINAL]
,[BH_GND_ELEV]
,[BH_GND_ELEV_OUOM]
,[BH_GND_ELEV_UNIT_OUOM]
,[BH_BOTTOM_ELEV]
,BH_BOTTOM_DEPTH
,dlqa.QA_COORD_CONFIDENCE_CODE
,dlqa.QA_ELEV_CONFIDENCE_CODE
FROM [OAK_CHECKS].[dbo].[CHK_D_LOC_ELEV_D_BOREHOLE] as chk
inner join 
[OAK_20120615_MASTER].dbo.D_LOCATION_QA as dlqa
on
chk.LOC_ID=dlqa.LOC_ID
where
dlqa.QA_COORD_CONFIDENCE_CODE != 1
order by
chk.loc_id

If a location has been surveyed and no SURVEYED_ELEV is present in D_LOCATION_ELEV, the BH_GND_ELEV_OUOM (converted to ‘masl’ as necessary) value will be assigned to SURVEYED_ELEV (and, subsequently, to the ASSIGNED_ELEV and all the borehole elevation fields). In almost all other cases, the BH_GND_ELEV_OUOM (converted) will be stored in ELEV_ORIGINAL and the DEM_MNR_10m_v2 value will be assigned to ASSIGNED_ELEV (and to all the appropriate D_BOREHOLE fields). Do not forget, the BH_GND_ELEV_UNIT_OUOM field needs to be corrected (or changed) to read ‘masl’ and that the BH_BOTTOM_ELEV field needs to be updated. So, for non-surveyed locations, D_BOREHOLE can be updated directly.

select
dbore.LOC_ID
,dbore.BH_GND_ELEV
,chk.ASSIGNED_ELEV
,chk.ELEV_ORIGINAL
,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
inner join 
[OAK_20120615_MASTER].dbo.D_LOCATION_QA as dlqa
on
dbore.LOC_ID=dlqa.LOC_ID
where 
dlqa.QA_COORD_CONFIDENCE_CODE != 1

update [OAK_20120615_MASTER].dbo.D_BOREHOLE
set
BH_GND_ELEV=chk.ASSIGNED_ELEV
,BH_GND_ELEV_OUOM=chk.ASSIGNED_ELEV
,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
inner join 
[OAK_20120615_MASTER].dbo.D_LOCATION_QA as dlqa
on
dbore.LOC_ID=dlqa.LOC_ID
where 
dlqa.QA_COORD_CONFIDENCE_CODE != 1

If a SURVEYED_ELEV is present in D_LOCATION_ELEV and a different elevation is present in the D_BOREHOLE field BH_GND_ELEV_ORIG (translated values don’t signify), it is considered that the surveyed elevation has been updated (unless there is, seemingly, a loss in accuracy; an example of this may be a change in the value recorded from 6 significant digits down to 5 significant digits). As such, SURVEYED_ELEV is assigned this value and all other fields adjusted accordingly.

The BH_GND_ELEV, if a change has been made, can be set to NULL to enable a re-conversion of all appropriate elevations for this location. Remember, all ground elevations in D_BOREHOLE should be set to the same value (the ASSIGNED_ELEV). Also, NULLs do not show up in comparisons (i.e. these rows will not be pulled with a CHK_D_LOC_ELEV_D_BOREHOLE run).