G.2 Update of D_LOCATION_GEOM (Automated)
- 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
(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