gerrykelly
commented
8 years ago The product of the hydrant ETL routines is ready for review in City SQL Server dev (tables HYDRANT_STATUS_DEV & WA_HYDRANT)
• The ETL for HYDRANT_STATUS_DEV is performed as described in my email from yesterday and the table is what would be our HYDRANT_STATUS starting point if I deleted all records and first ran the ETL routine “for real”.
• All ETL is performed by the single stored procedure: [GIS_ADMIN].[SP_HY_UPDATE_HYSTAT_FROM_ALL_SOURCES](but needs to be run by dashboard)
• The stored procedure is scheduled to run as a one-step SQL Server Agent job owned and executed by dashboard
o Scheduled nightly at 11
• The job for the ETL routines will only be logged from SQL Server Agent as having failed if the action where Inspection IDs are populated fails. For all the other actions if something fails it will be logged into the new HYDRANT_STATUS_ERROR_LOG table and processing will continue
• Besides your review of HYDRANT_STATUS_DEV for initial content, you intend to evaluate the ETL so far as inserting/not-inserting new records based on changes in the contributing datasources. While for the FD and Wach’s data you could in theory make a copy of those views and a copy of HYDRANT_STATUS_DEV, then look for changes the next day I can’t see you ever getting through a good test – and for the BACO data that won’t work unless you manually replace the workbook (which will be a good idea at some point bc it needs to get into workflow, but not now for testing. What I would suggest instead is to go to the HYDRANT_STATUS_DEV and delete some records and change inspection dates (forward and backward) for some records for each org – record what you did; look at the comparable records from the contributing datasources then run the stored procedure and examine the results.
o There are a number of views that I created for QA and development that will not move on to production. So far as QA on the source data & source data vs. WA_HYDRANT I just reached a point where I decided that we can’t fix it and just need to make sure that the ETL does the best it can. But if you have a specific question there is probably view that addresses it.
The one view that might be useful in planning QA is [GIS_ADMIN].[V_HY_HYDRANTS_in_InspectionSource] which for each hydrant from WA_HYDRANT shows you yes or no if there is a matching record in one of the three status sources.
• For the update of WA_HYDRANT, I write to the following fields based on data from V_HY_BCHYDPWHydrantAccess & V_HY_BACO_HYDRANTS_OUT_OF_SERVICE as possible (no location data in the Wachs table). The logic is that if V_HY_BCHYDPWHydrantAccess has the info for the hydrant V_HY_BACO_HYDRANTS_OUT_OF_SERVICE data will not overwrite it. V_HY_BACO_HYDRANTS_OUT_OF_SERVICE only updates the featureclass when LOCATION_DESCRIPTION is null after updating from V_HY_BCHYDPWHydrantAccess. (you can tell what records are updated by the BACO worksheet with a clause when querying WA_HYDRANT: where not [LOCATION_DESCRIPTION] is null and [CAD_ADDR] is null)
There is a lot of complication in the SQL for populating these fields that is not worth the eyes-glazing-over documentation, but basically I try to get the best results possible, so review different cases and see if you agree.
o [LOCATION_TYPE]
o [STREET_NUM]
o [STREET_NAME]
o [CAD_ADDR]
o [CROSS_STREET]
o [CORNER_POSITION]
o [LOCATION_DESCRIPTION]
o [DISTRICT]
o [HYDR_ZONE]
I am not updating [BFD_FIELDCHECK_DATE] & [BFD_FIELDCHECK_STATUS] bc I don’t think those fields are what I originally suspected.
Create/update data migration scripts to populate HYDRANT_STATUS with data from data sources: WA_HYDRANT Baltimore County out of service hydrants Wachs - maint2water_hyd Baltimore City Fire Department journal
Populate WA_HYDRANT with location information into the new fields added: LOCATION_TYPE] ,[STREET_NUM] ,[STREET_NAME] ,[CAD_ADDR] ,[CROSS_STREET] ,[CORNER_POSITION] ,[LOCATION_DESCRIPTION] ,[DISTRICT] ,[HYDR_ZONE] (and [BFD_FIELDCHECK_DATE] & [BFD_FIELDCHECK_STATUS]