search

kcigeospatial / MDOT-SHA-NPDES-Next-Gen

Code and issues related to the MDOT SHA NPDES Project. Project codes: Config = 31, Management = 32.
0 stars 0 forks source link

Treatment Pipe Survey, Feature and BMP Inspection "Merge" #365

Open KCI-Ablowers opened 5 years ago

KCI-Ablowers commented 5 years ago

Background

During the Treatment Pipe Schema discussion on 7/23/2019, the team determined that the UG Treatment Pipe Inspection survey form should be rolled up into the UG BMP Inspection survey form, eliminating the separate UG Treatment Pipe Inspection form. The team also determined items that would be removed from the treatment pipe inspection and rolled up to the treatment pipe geometry. Those items are listed below.

Solution

UG Treatment Pipe Inspection Item Treatment Pipe Geometry (inventory) UG BMP Inspection Remove Entirely Comment Complete
Start/End Time   :white_check_mark:   Start and End time built into Survey123 metadata. This information write into the database fields via ETL process.
Underground Facility Matches Plan     :white_check_mark: Handled by the UG BMP Inspection.  
Upstream/Downstream Structure Rating and Comment   :white_check_mark:      
Pipe Material :white_check_mark:        
Surveyed Length :white_check_mark:     
Pipe Diameter 1 & 2 :white_check_mark:        
Cross Section Area :white_check_mark:        
Cross Section Shape :white_check_mark:        
Pressure :white_check_mark:        
Precleaned     :white_check_mark: Delise stated that a preclean was rare.  
Estimated Upstream/Downstream Invert Depths  :white_check_mark:      
Survey Direction   :white_check_mark:      
Pipe Issue Tracker (Group)   :white_check_mark:      
Pipe Condition Rating Form (Group)   :white_check_mark:      

@talllguy The question marks represent where I was unsure what happens to those fields. If you can recall, please update the table.

talllguy commented 5 years ago

@KCI-Ablowers Nice work!

KCI-Ablowers commented 5 years ago

@KCI-Ablowers Nice work!

  • I think we inadvertently skipped surveyed length but it seems like it should go into the UG BMP column
  • We said US/DS depths would be a part of the inventory.

Updated table.

KCI-Ablowers commented 5 years ago

@talllguy -

It looks like the size, material, etc already exist in the Treatment Pipe -

image

talllguy commented 4 years ago

Add to BMP Inspection (INSPECTION table)

2/5/2020 Use same BMP inspection table but have special form for UG UG geometry is strictly for reference

talllguy commented 4 years ago

Ask at next meeting: impacts of inspection table schema change and (later) geometry schema changes.

Inspection Updates (higher priority)

Geometry Updates Order (lower priority)

talllguy commented 3 years ago

Update for 2021

We touched on this at a WAB implementation today.

During the COVID-19 workflow handoff, Andrew set up a form in AGOL-hosted that supports the schema changes outlined in this plan. Inspections recorded by that form are not ETL'd back to SHA production. The team discussed the potential future implementation of such and ETL but no definitive timeline was set. Thus, the UG inspection data will remain in AGOL-hosted only and any ETL would be manual. @KCI-Ablowers sent this database dump of the combined form used in 2020. UG_BMP_Inspection_Combined_20200909.zip

Schema outline for implementation

  1. SWMFAC has one or many BMP inspections. UG inspection is a type of BMP inspections.
  2. BMP inspection has one or many UG Issue Tracker to track issues inside the underground portion

Meeting per this comment

@ladejobi @johnshiu @KCI-Ablowers image

talllguy commented 1 year ago

Found some of these layers and am reopening this for reference.