View on GitHub

Oak Ridges Moraine Groundwater Program - Database Manual

ORMGP Database Manual

G.2 Update of D_LOCATION_GEOM (Automated)

This process has been automated, to a certain extent, such that a weekly check of the D_LOCATION_GEOM in comparison to D_LOCATION is made. This is controlled by

d_loc_geom.bat

(as described in Section G.32) and includes

If a non-NULL COORD_CHECK ???

The view specified (i.e. V_D_LOCATION_Geometry) uses the coordinates found in D_LOCATION to create spatial geometries (i.e. points) for each location with valid coordinates (i.e. not having a QA_COORD_CONFIDENCE_CODE of ‘117’). Using a ‘SELECT ? INTO ?’ statement, these spatial objects are stored in the D_LOCATION_GEOM table (when re-creating the table; otherwise an ‘INSERT ?’ is used). Note that this (latter) table will be replaced every time this procedure is applied.

A ‘shortcut’ is provided by ‘CHK_D_LOC_GEOM_Missing_LOC_ID’ (found in OAK_CHECKS). This view access each of the D_LOCATION, D_LOCATION_QA and V_D_LOCATION_Geometry tables/views and returns those LOC_IDs (and their geometries) which are not found in D_LOCATION_GEOM. An ‘INSERT ?’ can then be used. For example

insert into [OAK_SPATIAL].dbo.D_LOCATION_GEOM
(LOC_ID,GEOM)
SELECT [LOC_ID],[GEOM]
FROM [OAK_CHECKS].[dbo].[CHK_D_LOC_GEOM_Missing_LOC_ID]

A check for non-populated LOC_COORD_EASTING and LOC_COORD_NORTHING values (based upon the last time SiteFX was correctly run) can be accomplished through the following query.

SELECT dloc.[LOC_ID]
,[LOC_NAME]
,[LOC_NAME_ALT1]
,[LOC_TYPE_CODE]
,[OWN_ID]
,[LOC_ORIGINAL_NAME]
,[LOC_MASTER_LOC_ID]
,[LOC_AREA]
,[LOC_STUDY]
,[LOC_COORD_EASTING]
,[LOC_COORD_NORTHING]
,[LOC_COORD_EASTING_OUOM]
,[LOC_COORD_NORTHING_OUOM]
,[LOC_COORD_OUOM_CODE]
FROM [OAK_20120615_MASTER].[dbo].[D_LOCATION] as dloc
inner join [OAK_20120615_MASTER].dbo.D_LOCATION_QA as dlqa
on dloc.LOC_ID=dlqa.LOC_ID
where
dlqa.QA_COORD_CONFIDENCE_CODE<>117
and LOC_COORD_EASTING_OUOM is not null 
and LOC_COORD_NORTHING_OUOM is not null
and LOC_COORD_EASTING is null 
and LOC_COORD_NORTHING is null

D_LOCATION can then be updated by

update [OAK_20120615_MASTER].[dbo].[D_LOCATION]
set
LOC_COORD_EASTING=LOC_COORD_EASTING_OUOM
,LOC_COORD_NORTHING=LOC_COORD_NORTHING_OUOM
FROM [OAK_20120615_MASTER].[dbo].[D_LOCATION] as dloc
inner join [OAK_20120615_MASTER].dbo.D_LOCATION_QA as dlqa
on dloc.LOC_ID=dlqa.LOC_ID
where
dlqa.QA_COORD_CONFIDENCE_CODE<>117
and LOC_COORD_EASTING_OUOM is not null 
and LOC_COORD_NORTHING_OUOM is not null
and LOC_COORD_EASTING is null 
and LOC_COORD_NORTHING is null