Database Manual

Oak Ridges Moraine Groundwater Program

Appendix G.26

G.26 Correction or update of borehole coordinates (in comparison with an updated MOE Water Well Database)

Corrections to coordinates within the database are made under certain conditions or assumptions. As an aid (or check) to the process, the centroid of all Township polygons were determined. All MOE borehole locations with an invalid coordinate pair (identified with a QA code of ‘117’) but with an identifiable Township code were given a new QA code (‘118’) and assigned the coordinates of the centroid (i.e. to LOC_COORD_EASTING and LOC_COORD_NORTHING). Note that this check was initially performed using the ‘2016-05-31’ release of the MOE Water Well Database.

Any change in the coordinates of a location has a tremendous impact upon a number of tables (listed above). A new elevation will need to be determined (Appendix G.4) and incorporated into D_LOCATION_SPATIAL_HIST (matched to the updated coordinates). This will directly impact elevation values in multiple tables that will need to be updated (refer to Appendix G.XXX to do so).

The DATA_ID of the particular MOE WWDB import should be included as part of the record added to D_LOCATION_SPATIAL_HIST iin order to track update occurrences. These records should be tagged with a LOC_COORD_HIST_CODE of ‘5’ (‘MOE WWIS Update’).

Coordinate Check 00 - MOE Zone 17 Coordinates

Using V_SYS_MOE_LOCATIONS as a base, convert those locations found in the MOE WWDB with UTM Zone 18 coordinates to UTM Zone 17 and copy both these and the original UTM Zone 17 coordinates into each of the new ‘east_17’ and ‘north_17’ fields (remove the records that are present in another zone; this will be accomplished in an external GIS). An updated copy of this table should be called YC_20220328_BORE_HOLE_ID_COORDS_UPD. We’ll use this as a base of further comparison. Note that we’re not including in this comparison locations that have previously been corrected using this process (i.e. having a LOC_COORD_HIST_CODE of ‘6’).

select
v.loc_id
,v.moe_well_id
,v.moe_bore_hole_id
,vc.spat_id
,vc.x
,c.east83
,cast( null as float) as east83_17
,vc.y
,c.north83
,cast(null as float) as north83_17
,c.zone
,c.utmrc
,vc.qa_coord_code
,c.location_method
,c.improvement_location_source
,c.improvement_location_method
,c.elevation
,c.elevrc
,row_number() over (order by v.loc_id) as rkey
into moe_20220328.dbo.YC_20220328_BORE_HOLE_ID_COORDS_CMP
from 
oak_20160831_master.dbo.v_sys_moe_locations as v
inner join oak_20160831_master.dbo.d_location as dloc
on v.loc_id=dloc.loc_id
inner join moe_20220328.dbo.tblbore_hole as c
on v.moe_bore_hole_id=c.bore_hole_id
inner join oak_20160831_master.dbo.v_sys_loc_coords as vc
on v.loc_id=vc.loc_id
where 
dloc.data_id<>524
and c.zone in (17,18)
and v.loc_id not in 
(
select
distinct( loc_id ) as loc_id
from
oak_20160831_master.dbo.d_location_spatial_hist
where
loc_coord_hist_code= 6
)

Note that there may be duplicate LOC_IDs (based upon grouping of BORE_HOLE_IDs to one location). As such, this should be checked and corrected (for the MOE 20200721 import, there were five duplicate LOC_ID rows; the coordinates were within one metre of each other; one of each duplicate was removed manually).

Coordinate Check 01 - MOE Coordinates - Township (QA 118)

In the case that the MOE have updated the coordinates for a borehole, compare all QA ‘118’ locations against the MOE coordinates. We’re checking to see if the centroid coordinates (i.e. the assigned MOE Township code) lies within 25km (chosen as a default) of the MOE assigned coordinates. Note that this was applied against both Zone 17 and 18 coordinates (the latter converted to Zone 17) as found within the master database.

select
dloc.LOC_ID
,dloc.LOC_COORD_EASTING
,dloc.LOC_COORD_NORTHING
,cast( 5 as int ) as LOC_COORD_HIST_CODE 
,cast( '2022-03-28' as datetime ) as LOC_COORD_DATE
,m.east83_17 as X
,m.north83_17 as Y
,cast( 26917 as int ) as EPSG_CODE
,m.east83 as X_OUOM
,m.north83 as Y_OUOM
,case
when m.zone='18' then 26918
else 26917
end as EPSG_CODE_OUOM
,case
when m.utmrc is not null then cast(m.utmrc as int) 
else null
end as QA_COORD_CODE
,cast(
case
when m.location_method is not null then m.location_method + '; '
else ''
end
+
case
when m.improvement_location_method is not null then m.improvement_location_method + '; '
else ''
end
+
case
when m.improvement_location_source is not null then m.improvement_location_source + '; '
else ''
end
as varchar(255)) as LOC_COORD_METHOD
,cast( 'Updated from QA_COORD_CODE 118' as varchar(255) ) as LOC_COORD_COMMENT
,cast( 524 as int ) as LOC_COORD_DATA_ID
,case
when m.elevation is not null then cast( 2 as int ) 
else null
end as LOC_ELEV_CODE
,case 
when m.elevation is not null then cast( '2022-03-28' as datetime ) 
else null
end as LOC_ELEV_DATE
,m.elevation as LOC_ELEV
,case
when m.elevation is not null then cast( 6 as int ) 
else null
end as LOC_ELEV_UNIT_CODE
,case
when len(m.elevrc)>0 and isnumeric( m.elevrc )=1 then cast(m.elevrc as int) 
else null
end as QA_ELEV_CODE
,cast( '20220510a' as varchar(255) ) as SYS_TEMP1
,cast( 20220510 as int ) as SYS_TEMP2
from 
moe_20220328.dbo.yc_20220328_bore_hole_id_coords_upd as m
inner join oak_20160831_master.dbo.d_location as dloc
on m.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dloc.loc_id=dlqa.loc_id
where
-- make sure to not include the current DATA_ID
( dloc.data_id is null or dloc.data_id<>524 )
and dlqa.qa_coord_confidence_code= 118
and m.east83_17 is not null and m.north83_17 is not null
and m.utmrc<>9
and 
(
not( m.east83_17 between ( m.x - 5 ) and (m.x + 5) )
or not( m.north83_17 between (m.y - 5 ) and ( m.y + 5 ) )
)

For MOE 20220328, this returned 270 locations - these were inserted as new rows in the coordinate tracking table D_LOCATION_SPATIAL_HIST.

Coordinate Check 02 - MOE Coordinates - Invalid (QA 117)

In the case that the MOE has updated its coordinates, compare the QA ‘117’ location coordinates against the MOE coordinates. Here we’re looking to see if the original (invalid) database coordinates differ from those in the recent MOE database. We’ll need to check, firstly, that these have not already been captured in the database.

select
m.*
from 
moe_20200721.dbo.yc_20200721_bore_hole_id_coords_upd_z17 as m
inner join d_location_qa as dlqa
on m.loc_id=dlqa.loc_id
inner join 
(
select
loc_id
,x
,y
from 
d_location_spatial_hist
where
x is not null and y is not null
group by
loc_id,x,y
) as t
on m.loc_id=t.loc_id and m.east83_final=t.x and m.north83_final=t.y
where
dlqa.qa_coord_confidence_code= 117

Any records returned should be marked as ‘present’ (i.e. they’ve already been added).

For new records, an allowance of a +/- 1m error in the coordinate values (for the comparison) is implemented. Note that this was applied against both Zone 17 and 18 coordinates (the latter was converted to Zone 17 in a previous step).

select
dloc.LOC_ID
,dloc.LOC_COORD_EASTING
,dloc.LOC_COORD_NORTHING
,cast( 5 as int ) as LOC_COORD_HIST_CODE 
,cast( '2021-01-19' as datetime ) as LOC_COORD_DATE
,m.east83 as X
,m.north83 as Y
,cast( 26917 as int ) as EPSG_CODE
,m.east83_orig as X_OUOM
,m.north83_orig as Y_OUOM
,case
when m.zone_orig=18 then 26918
else 26917
end as EPSG_CODE_OUOM
--,m.utmrc as QA_COORD_CODE
,case
when m.utmrc is not null then cast(m.utmrc as int) 
else null
end as QA_COORD_CODE
,cast(
case
when m.location_method is not null then m.location_method + '; '
else ''
end
+
case
when m.improvement_location_method is not null then m.improvement_location_method + '; '
else ''
end
+
case
when m.improvement_location_source is not null then m.improvement_location_source + '; '
else ''
end
as varchar(255)) as LOC_COORD_METHOD
,cast( 'Updated from QA_COORD_CODE [117]' as varchar(255) ) as LOC_COORD_COMMENT
,cast( 523 as int ) as LOC_COORD_DATA_ID
,case
when m.elevation is not null then cast( 2 as int ) 
else null
end as LOC_ELEV_CODE
,case 
when m.elevation is not null then cast( '2021-01-19' as datetime ) 
else null
end as LOC_ELEV_DATE
,m.elevation as LOC_ELEV
,case
when m.elevation is not null then cast( 6 as int ) 
else null
end as LOC_ELEV_UNIT_CODE
--,m.elevrc as QA_ELEV_CODE
,case
when len(m.elevrc)>0 then cast(m.elevrc as int) 
else null
end as QA_ELEV_CODE
,cast( '20210202b' as varchar(255) ) as SYS_TEMP1
,cast( 20210202 as int ) as SYS_TEMP2
from 
moe_20210119.dbo.yc_20210119_bore_hole_id_coords_upd as m
inner join oak_20160831_master.dbo.d_location as dloc
on m.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dloc.loc_id=dlqa.loc_id
where
dlqa.qa_coord_confidence_code= 117
and m.present is null
and 
(
not( dloc.loc_coord_easting between (m.east83 - 1) and (m.east83 + 1) )
or not( dloc.loc_coord_northing between (m.north83 - 1) and (m.north83 + 1) )
)

This returned 1 location for the MOE 20200721 import - these were added D_LOCATION_SPATIAL_HIST.

Coordinate Check 03 - MOE Coordinates - Valid coordinates (non-QA 117 and 118)

In the case that the MOE has updated it’s coordinates, a comparison of the MOE coordinates is made with non-QA ‘117’ and ‘118’ location coordinates. Here we’re looking to see if the current location coordinates differ from those in the recent MOE database. An allowance of a +/- 1m error in the coordinate values (for the comparison) is implemented. As of the v20190509 MOE database only original UTM Zones 17 and 18 (as found in the MOE database) are examined.

select
dloc.LOC_ID
,dloc.LOC_COORD_EASTING
,dloc.LOC_COORD_NORTHING
,cast( 5 as int ) as LOC_COORD_HIST_CODE 
,cast( '2022-03-28' as datetime ) as LOC_COORD_DATE
,m.east83_17 as X
,m.north83_17 as Y
,cast( 26917 as int ) as EPSG_CODE
,m.east83 as X_OUOM
,m.north83 as Y_OUOM
,case
when m.zone=18 then 26918
else 26917
end as EPSG_CODE_OUOM
,case
when m.utmrc is not null and isnumeric( m.utmrc )=1 then cast(m.utmrc as int) 
else null
end as QA_COORD_CODE
,cast(
case
when m.location_method is not null then m.location_method + '; '
else ''
end
+
case
when m.improvement_location_method is not null then m.improvement_location_method + '; '
else ''
end
+
case
when m.improvement_location_source is not null then m.improvement_location_source + '; '
else ''
end
as varchar(255)) as LOC_COORD_METHOD
,cast( 'Updated from various QA_COORD_CODEs (CHECK03)' as varchar(255) ) as LOC_COORD_COMMENT
,cast( 524 as int ) as LOC_COORD_DATA_ID
,case
when m.elevation is not null then cast( 2 as int ) 
else null
end as LOC_ELEV_CODE
,case 
when m.elevation is not null then cast( '2022-03-28' as datetime ) 
else null
end as LOC_ELEV_DATE
,m.elevation as LOC_ELEV
,case
when m.elevation is not null then cast( 6 as int ) 
else null
end as LOC_ELEV_UNIT_CODE
--,m.elevrc as QA_ELEV_CODE
,case
when len(m.elevrc)>0 then cast(m.elevrc as int) 
else null
end as QA_ELEV_CODE
,cast( '20220510c' as varchar(255) ) as SYS_TEMP1
,cast( 20220510 as int ) as SYS_TEMP2
from 
moe_20220328.dbo.yc_20220328_bore_hole_id_coords_upd as m
inner join oak_20160831_master.dbo.d_location as dloc
on m.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dloc.loc_id=dlqa.loc_id
where
(dloc.data_id is null or dloc.data_id<>524 )
and dlqa.qa_coord_confidence_code not in ( 117, 118 )
and m.present is null
and 
(
not( dloc.loc_coord_easting between (m.east83_17 - 1) and (m.east83_17 + 1) )
or not( dloc.loc_coord_northing between (m.north83_17 - 1) and (m.north83_17 + 1) )
)
and m.east83_17 is not null and m.north83_17 is not null
and m.east83_17 between ( 100000 ) and ( 1000000 )
and m.north83_17 between ( 1000000 ) and ( 6000000 )
and dloc.loc_id not in
(
select
distinct(loc_id)
from 
oak_20160831_master.dbo.d_location_spatial_hist
where
loc_coord_hist_code=5
)

In addition, cut-off checks are used to exclude obviously bad coordinates (note the ranges specified for EAST83_FINAL and NORTH83_FINAL). The LOC_COORD_HIST_CODE value of ‘5’ (‘MOE WWIS Update’) and the DATA_ID can be used to determine what coordinates were incorporated from what version of the MOE database (refer to D_DATA_SOURCE through V_SYS_MOE_DATA_ID to determine the version dates).

Coordinate Check 04 - MOE Coordinates - Geocoded Locations

The previous instructions for this section no longer apply - the ‘gcode’ locational method (from the MOE database) will be caught in ‘Coordinate Check 03’, above.

Coordinate Check 05 - MOE Boreholes - Manual Check

For those locations with a QA of ‘117’ or ‘118’, examine the original MOE report and determine for each borehole the location coordinates using a GIS or Google Maps (for example). As the latter returns latitude/longitude values (WGS84), convert these to UTM Zone 17 (NAD83) and store both sets of values.

Coordinate Check 06 - MOE Coordinates - Invalid (QA 117 or 118) Update

For those locations with a QA of ‘117’ or ‘118’, examine those MOE coordinates where they differ from the database *_OUOM coordinates and where the UTMRC code is not ‘9’ (generally, these locations will have coordinates that have been tagged as invalid; for example, having values of ‘99999’ or similar). Note the use of the LOC_COORD_HIST_CODE and DATA_ID to limit the examination to a particular MOE database import.

select
dloc.loc_id
,dlsh.loc_coord_hist_code
,dlsh.loc_coord_date
,dlsh.x
,dlsh.y
,dlsh.epsg_code
,dlsh.x_ouom
,dlsh.y_ouom
,dlsh.epsg_code_ouom
,dlsh.qa_coord_code
,dlsh.loc_coord_data_id
,dlsh.loc_coord_method 
,dlsh.loc_coord_comment 
,3 as loc_elev_code
,cast( '2021-01-19' as datetime ) as loc_elev_date
,6 as loc_elev_unit_code
,10 as qa_elev_code
,'20210202d' as sys_temp1
,20210202 as sys_temp2
from 
oak_20160831_master.dbo.d_location as dloc
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dloc.loc_id=dlqa.loc_id
inner join oak_20160831_master.dbo.d_location_spatial_hist as dlsh
on dloc.loc_id=dlsh.loc_id
where 
dlqa.qa_coord_confidence_code in ( 117, 118 )
and dlsh.loc_coord_data_id= 523
and ( dlsh.qa_elev_code is null or dlsh.qa_elev_code<>1 )

Insert these into D_LOCATION_SPATIAL_HIST and populate the elevations (note that the fields, above, are set to reflect the DEM that is presently being used - MNR version 2, 2005, at 10m resolution). Once the elevations have been populated, D_LOCATION_SPATIAL should be updated to point to these records.

Coordinate Check 07 - MOE Coordinates - QA improvement Update

For those locations whose QA codes, as found in the MOE database, are better than that currently specified, the new MOE coordinates are substituted. We’ll first check against a current QA_COORD_CODE of 9 (this value should be indicative of those locations that have not been manually reviewed by the ORMGP group).

select
dlsh.loc_id
,dlsh.loc_coord_hist_code
,dlsh.loc_coord_date
,dlsh.x
,dlsh.y
,dlsh.epsg_code
,dlsh.x_ouom
,dlsh.y_ouom
,dlsh.epsg_code_ouom
,dlsh.qa_coord_code
,dlsh.loc_coord_method
,dlsh.loc_coord_comment + '; Updated from QA_COORD_CODE 9 (CHECK07)' as loc_coord_comment
,dlsh.loc_coord_data_id
,cast( 3 as int ) as loc_elev_code
,cast( '2021-01-19' as datetime ) as loc_elev_date
,cast( 6 as int ) as loc_elev_unit_code
,cast( 10 as int ) as qa_elev_code
,'20210202e' as sys_temp1
,20210202 as sys_temp2
from 
oak_20160831_master.dbo.d_location_spatial_hist as dlsh
inner join oak_20160831_master.dbo.v_sys_loc_coords as v
on dlsh.loc_id=v.loc_id
where 
dlsh.sys_temp1= '20210119b'
and dlsh.qa_coord_code < v.qa_coord_code
and v.qa_coord_code= 9

These should be inserted into D_LOCATION_SPATIAL_HIST and updated with an elevation (from the current DEM). Make these the current coordinates in D_LOCATION_SPATIAL. Make a note of those with invalid elevations (i.e. outside of the DEM area) and assign a QA_COORD_CODE of ‘117’.

For the remainder of the QA_COORD_CODEs, check if they’ve been manually modified based upon the LOC_COORD_HIST_CODE, the SYS_USER_STAMP and any of the comment fields. If indicated in the latter fields, update the LOC_COORD_HIST_CODE of the record to ‘8’ (i.e. ‘Manual - Other’) and provide a comment in LOC_COORD_COMMENT.

select
dlsh2.*
--distinct( dlsh2.loc_coord_comment )
from 
d_location_spatial_hist as dlsh
inner join v_sys_loc_coords as v
on dlsh.loc_id=v.loc_id
inner join d_location_spatial_hist as dlsh2
on v.spat_id=dlsh2.spat_id
where
dlsh.sys_temp1= '20200806b'
and dlsh.qa_coord_code < v.qa_coord_code
and v.qa_coord_code <> 9 
and dlsh2.loc_coord_hist_code < 6

Find those that appear to be updates and not otherwise modified in the database.

select
dlsh.loc_id
,dlsh.loc_coord_hist_code
,dlsh.loc_coord_date
,dlsh.x
,dlsh.y
,dlsh.epsg_code
,dlsh.x_ouom
,dlsh.y_ouom
,dlsh.epsg_code_ouom
,dlsh.qa_coord_code
,dlsh.loc_coord_method
,dlsh.loc_coord_comment + '; Updated from QA_COORD_CODE ' + cast( v.qa_coord_code as varchar(255) ) + ' (CHECK07)' as loc_coord_comment
,dlsh.loc_coord_data_id
,cast( 3 as int ) as loc_elev_code
,cast( '2021-01-19' as datetime ) as loc_elev_date
,cast( 6 as int ) as loc_elev_unit_code
,cast( 10 as int ) as qa_elev_code
,'20210202f' as sys_temp1
,20210202 as sys_temp2
from 
oak_20160831_master.dbo.d_location_spatial_hist as dlsh
inner join oak_20160831_master.dbo.v_sys_loc_coords as v
on dlsh.loc_id=v.loc_id
inner join oak_20160831_master.dbo.d_location_spatial_hist as dlsh2
on v.spat_id=dlsh2.spat_id
where 
dlsh.sys_temp1= '20210202b'
and dlsh.qa_coord_code < v.qa_coord_code
and v.qa_coord_code <> 9 
and dlsh2.loc_coord_hist_code < 6

Insert these into D_LOCATION_SPATIAL_HIST, update their elevations and update D_LOCATION_SPATIAL to reflect these changes.

For all of these checks and updates, be sure to update the appropriate fields in D_LOCATION, D_LOCATION_QA and D_LOCATION_GEOM (for the latter, specify a COORD_CHECK of ‘10001’ to indicate the coordinates and elevation have been modified; this is a key used as a reminder to update all elevation-affected fields).

Last Modified: 2022-05-10