Database Manual

Oak Ridges Moraine Groundwater Program

Appendix G.4

G.4 Ground Elevation Assignment

The ground elevation for locations within the database are stored in D_BOREHOLE (BH_GND_ELEV) and D_LOCATION_SPATIAL_HIST (LOC_ELEV). These values should (optimally) match. However, for historical reasons, the BH_GND_ELEV can be updated without capturing the change in D_LOCATION_SPATIAL_HIST. The latter allows a tight connection to be made between the coordinates of the location and its ground elevation and should be updated for any change of information related to coordinates or elevation data (e.g. QA_COORD_CONFIDENCE_CODE or QA_ELEV_CONFIDENCE_CODE from D_LOCATION_QA). A new record is created in this table for each change (or changes) - D_LOCATION_SPATIAL records reflect the ‘current’ coordinates and elevation for any particular location. These values can be accessed (easily) using V_SYS_LOC_COORDS. Further details can be found in Section 2.4.2.

All locations with valid coordinates (as found in D_LOCATION) should have a record in D_LOCATION_GEOM. (Note that a QA_COORD_CONFIDENCE_CODE of ‘117’ indicates invalid coordinates in D_LOCATION_QA.) If a location has any coordinates or elevation associated with it, this will be recorded in DLSH (and DLS).

An elevation can be entered directly as part of an import process for any particular location. This would be considered an ‘Original’ elevation (i.e. LOC_ELEV_CODE ‘2’ in R_LOC_ELEV_CODE) and is generally not kept as the ground surface elevation unless a QA_ELEV_CONFIDENCE_CODE (or QA_ELEV_CODE in DLSH) of ‘1’ has been applied (i.e. a ‘Surveyed’ location). Note that another exception, this time using a code of ‘11’ (i.e. an ‘External change has been applied - do not modify’), can also be used to ‘lock’ an elevation value. This, though, is usually applied against locations that have already been imported (and not to new locations).

Each location should have a Digital Elevation Model (DEM) value associated with it (whether it is being used or not, as in the case of a surveyed location). Within the ORMGP study area this will be drawn from the Ministry of Natural Resources (2005) DEM (DEM_MNR_v2; a 10-metre resolution dataset; a LOC_ELEV_CODE of ‘3’ in R_LOC_ELEV_CODE). Outside the ORMGP study area this will be drawng from the Jarvis et al (2008) Shuttle Radar Topography Mission DEM (DEM_SRTM_v41; a 90-metre resolution dataset; a LOC_ELEV_CODE of ‘5’ in R_LOC_ELEV_CODE). In some cases, both DEM values will be stored (though in more recent data imports, this practice has been abandoned).

When populating the elevation fields, the location coordinates and LOC_ID are exported to an external GIS (the coordinates are found in either of D_LOCATION or DLS/DLSH - these should match). Alternatively, the LOC_ID and geometry from D_LOCATION_GEOM can be accessed by an external GIS (the geometry must match the coordinates found in the other tables). These locations are compared against the DEM_MNR_v2 and/or DEM_SRTM_v41 grids and the associated elevation recorded (by LOC_ID) as new attribute in a temporary table accessible to the master database.

For example, the command

gnat grid_examine -grass -grid=DEM_MNR_v2_10m_BATHY_25km_buffer
          -hdrs=x,y,spat_id -method=locnrwf
          -i=odbc,cloca_temphold,<user>,<pass>,elev_upd_20201013
          -o=odbc,cloca_temphold,<user>,<pass>,elev_mnr_20201013

could be used in a pre-configured Grass GIS environment to determine and store the appropriate DEM values linked to a specific SPAT_ID.

This table can then be used to update the associated LOC_ELEV field (as found in DLSH) using, for example

update d_location_spatial_hist
set
loc_elev_code= 3
,loc_elev_date= '2020-10-13'
,loc_elev= m.value
,loc_elev_unit_code= 6
,loc_elev_ouom= m.value
,loc_elev_unit_ouom= 'masl'
,qa_elev_code= 10
from d_location_spatial_hist as dlsh
inner join d_location_spatial as dls
on dlsh.spat_id=dls.spat_id
inner join temphold.dbo.elev_mnr_20201013 as m
on dls.loc_id=m.loc_id
where
m.value <> -9999

Note that this presupposes that a new record containing the current coordinates has been added to DLSH and referenced in DLS (refer to Appendix G.2 for details). Here we are incorporating DEM_MNR_v2 values (as indicated by a LOC_ELEV_CODE of ‘3’).

In some cases, a NULL value could be determined as part of this process (this is indicated with a value of ‘-9999’). This is checked as part of the update query and usually occurs for those locations that lie outside the ORMGP study area. When this occurs, a DEM_SRTM_v41 value would be determined and incorporated.

This would directly be available as the ‘current’ coordinates and elevation as found through V_SYS_LOC_COORDS. The D_BOREHOLE values, however, would still need to be updated. This is accomplished by

update d_borehole
set
bh_gnd_elev= delev.assigned_elev
,bh_gnd_elev_ouom= delev.assigned_elev
,bh_gnd_elev_unit_ouom= 'masl'
,bh_dem_gnd_elev= delev.assigned_elev
,sys_temp1= delev.sys_temp1
,sys_temp2= delev.sys_temp2
from d_borehole as dbore
inner join v_sys_loc_coords as delev
on dbore.loc_id=delev.loc_id
cross join (
select valf as _elevcorr from s_constant where const_name='SYS_ELEV_CORR_RANGE'
) as sc
where
delev.sys_temp1= '20201013z '
and not( dbore.bh_gnd_elev between (delev.assigned_elev - sc._elevcorr) and (delev.assigned_elev + sc._elevcorr) )

Here, each of BH_GND_ELEV, BH_GND_ELEV_OUOM and BH_DEM_GND_ELEV are updated with the same value. The use of the SYS_TEMP1 and SYS_TEMP2 fields in the query aid in updating affected elevations in other tables (refer to Appendix G.28).