MS4, NPDES, & TMDL Program Data Development Lesson’s Learned

1. MS4, NPDES, & TMDL Program Data Development Lesson’s Learned Brent Reeves Rhonda Hughes Sr. GIS Analyst Sr. GIS Analyst CWEA Stormwater Commitee 2015 Spring Seminar Linthicum Heights, Maryland April 16, 2015

2. Agenda • Permitting Program • MS4, NPDES, TMDL • Annual Compliance/Restoration Reporting • Discussion Topics • Requirements • Database Design • Data Collection • Viewing, Editing, Reporting • Tracking and Planning • Lesson’s Learned

3. Permitting Programs • Why collect storm water data? • MS4 / NPDES Requirements • Storm Water Implementation Plan • Inventory/Inspection • Maintenance Plan • IDDE, Allowable Discharge Limits • Annual Reporting • TMDL and Impervious Reduction Requirements • Nitrogen/Phosphorus/SS Treatment • TMDL Regulations • Baseline/Milestones • TMDL Goals • Impervious Accounting • Goal – prove pollutant removal program

4. Requirements • Understand the Permit Requirements • % Reductions/Impervious Equivalencies • Reporting Requirements • Identify the Multiple Stakeholders • Multiple Data Repositories • Data Editing/Updating Process • System Architecture • Oracle, Web, Android, Windows • Define the Internal Workflow • Existing vs. Proposed Workflows • Define the entire workflow from planning and construction to inspections and maintenance • Ever-changing Workflow

5. Synchronization Synchronization Synchronization Synchronization Database Design • Tracking and Reporting of Storm Water Assets • Centralized Database • Enhance or Remodel? • NPDES vs TMDL • Relationships, Domains • Standardization • Data Migration • Multiple Sources • Varying Formats • Flexible / Scalable • MDE Requirements Change • Operational Needs – Future Growth • Data Dictionary and Documentation ArcGIS Server Data Service

6. Database Design • Querying and Viewing Considerations • What Needs to be Viewed and Queried? • Link to Data Housed in External Locations • Customization • For Editors/Users • Consider Existing Database Designs • 100 BMPs or 1,000s • Business Rules • Data Standardization • Quality Control • Data Maintenance Advantage • Application Development

7. Data Collection • Identify Existing Workflows • Office vs. Field Workflows • Identify Data Sources (office vs. field efforts) • Developing Editing Application • Select the Platform • Mobile vs. Desktop vs. Android vs. Collector • What Does the Future Entail? • Weigh Current vs. Future Pros/Cons • Identify the “Must Haves” vs. “Nice to Have” • Create & Update SOPs • Versioning, Data Management SOPs

8. Data Collection • Integrate Criteria • Ratings, Status • Documentation/Photo Management • Level of Effort • Most Solutions are Scalable • Cost for Everything (code, software, hardware) • Fieldwork? • Inspection, Feature Verification

9. Viewing, Editing, Reporting • Customized Views & Editing Templates • Stakeholder Dependent • Simple vs. Complex Solution • Mockups / Development • Identify Required Queries for Reporting & Tracking • Latest Inspection Data • List of BMPs vs. Milestone Year • On-the-Fly Reporting, Single Button Solution

10. Tracking and Planning • Database Requirements • Holistic way to Manage Entire Program • Business and Regulatory Requirements • Inventory, Inspections, Attachments • Lifecycle of Projects • Assets • Site Selection • Watershed Implementation Plans • Ranking Projects/Review Info • Design Documentation/Proof • Inspection and Maintenance • Scenario Development • Restoration and Progress

11. Lessons Learned

12. Lessons Learned • Understand Permit and Bay Requirements • Reporting Requirements Drive the Workflow/Database Design • Milestone/Reporting Due Dates • Impervious Treatment / Bay TMDL Pollutant Removal / Local TMDLs / Annual Reporting / and More • Establish Baseline • Documentation and Proven Functionality • Centralized Database **Scalable and Flexible** • Consistent and Accurate Data • Extract, Transform, Load (ETL) for Delivering Data

13. Lessons Learned • Database Development For Scenario Planning • On-the-Fly Reports to Show Progress Towards Goals • Extract Data for Input into Modeling Tools, Track Results • Key Data for Baseline, Allowable Credit • Engineering Parameters (DA, Impervious, Type, Era, WQv, PE Treated) • Include Subject Matter Experts • Tracking Planned Projects • Budget and Funding • Projected Restoration Amount • Understand Data and the Future • Updating Programs and Code • Changing Software & Hardware • Compatible for New Views; Expandable

14. Lessons Learned • Identify Stakeholders • Internal Requirements Not Related to Permit • Also Identify External Data Users • Cross Jurisdictional Coordination • Ownership Discrepancies/ROW • What Can Actually Be Claimed? • MDE as a Stakeholder • Requirements Meetings • Important for Open Communication Within an Organization • Have First Meeting Early w/ Stakeholders • Requirements Continuously Updating

15. Lessons Learned • Develop and Train SOPs • Program Workflow Diagrams • Organization Chart/Identify Subject Matter Experts • Data Management Procedures • Versioning • Check-in/-out • Editing Procedures • Migration Steps • Update Often as Procedure/Workflow Changes • Quality Assurance/Control Processes • Consistent Data Entry • Make use of domains • More Organized Up Front, Less Hassle At End

16. Questions?