G.15 Synchronize elevations between D_BOREHOLE and D_LOCATION_ELEVATION
- Estimated Recurrence Time: 1 month
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).