Appendix G.2

G.2 Update of D_LOCATION_GEOM (Automated)

• Tables
  • D_LOCATION
  • D_LOCATION_GEOM
  • D_LOCATION_QA
  • D_LOCATION_SPATIAL
  • D_LOCATION_SPATIAL_HIST
• Views
  • V_SYS_CHK_LOC_GEOM_ADD
  • V_SYS_CHK_LOC_GEOM_CHANGE
  • V_SYS_CHK_LOC_GEOM_REMOVE
  • V_SYS_CHK_LOC_GEOM_WKB_UPDATE
  • V_SYS_LOC_GEOMETRY
  • V_SYS_LOC_GEOMETRY_WKB
• Estimated Recurrence Time: Weekly

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

• Removal of those LOC_IDs not found in D_LOCATION (V_SYS_CHK_LOC_GEOM_REMOVE)
• Determination if the coordinates in D_LOCATION have changed from those stored in D_LOCATION_GEOM (as a geometry; V_SYS_CHK_LOC_GEOM_CHANGE); if there has been a change, the COORD_CHECK field is updated (incremented by a value of ‘1’ if non-NULL)
• Addition of those LOC_IDs present in D_LOCATION but absent from D_LOCATION_GEOM (V_SYS_CHK_LOC_GEOM_ADD); these are calculated using V_SYS_LOC_GEOMETRY
• Update of NULL GEOM fields (V_SYS_LOC_GEOMETRY)
• Update of GEOM_WKB (V_SYS_CHK_LOC_GEOM_WKB_UPDATE); these are calculated using V_SYS_LOC_GEOMETRY_WKB

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