DEQ BMP Warehouse Overview Bill Keeling NPS Modeling

DEQ BMP Warehouse Overview Bill Keeling NPS Modeling and Data Coordinator Virginia Department of Environmental Quality 6/17/2019

Agenda • CBP Reporting – National Environmental Information Exchange Network - NEIEN • DEQ reporting application – WH development • Application status and updates • Website and link • Registration and Operation • Templates • Search/Export Options • Issues/solutions • Q&A 3

Reporting to the Bay Program • Nonpoint Source BMP data reporting - condition of EPA Chesapeake Bay Implementation and 319 Grants • 2009 last year partnership jurisdictions allowed to report using spreadsheet input decks to CBP • 2010 progress first EPA mandated use of the National Environmental Information Exchange Network (NEIEN) • NEIEN uses XML instance file database to database transfer using a schema and governed by most current Data Element Table, Codes Lists, and BMP Appendix A http: //webservices. chesapeakebay. net/schemas/ • Process best described as a hungry but very finicky consumer of BMP data 4

Development of the Warehouse (2014 Concept) 5

Development of the Warehouse (2014 Concept) • 2014 to early 2015 • Conceptualization and requirements development • Contract development and award • Summer 2015 development begins • Data flow • Begins with successful upload – import database • BMPs located outside CBWS or not currently accepted by CBP not transformed to NEIEN XML schema • BMPs located inside CBWS and accepted by CBP are made ready to submit to DEQ NEIEN node – NEIEN export database • From 1 to on average 3, 000 records are selected per NEIEN submission – unique tracking IDs associated with unique submission ID 6

Application Use and Upgrades • 12/2015 first instance of WH system operational • Supplied 2015 annual BMP data to phase 5 model • 2016 DEQ updated system for reporting 2017 progress data • phase 5’s final year and begin phase 6 reporting includes historical record – ability to report to both phases of CBP model • Ability for MS 4 BMP reporting • Ability to collect Section 319 NPS Grant funded BMPs • Replaces Attachment D 1 NPS BMP Reporting Form • 2018 annual reporting and onward will be to phase 6 7

Bay Program Data Flow Reporting Entity VADEQ node CBP node Warehouse NEIEN Scenario Builder VADEQ CBP node CBP Watershed Model • Data may pass NEIEN validation but fail processing in CBP Scenario Builder – Iterative correction process • Report by 12/1 of each year for CBP progress year i. e. 7/1/2017 to 6/30/2018 = 2018 progress year 8

Phase 6 Historical Record • Submitted several times with different EPA mandated changes to NEIEN (4/2016, 5/2016, 9/2016, 12/2016, and 8/2017) • Developed by hand submitted outside WH • 2/2018 all records through 6/30/2016 (2017 Progress) resubmitted via WH application • Simplifies updating information – i. e. Inspection/Maintenance dates provided CBP 9

Current Status • Utilized for 2015 thru 2018 “Annual Progress” reporting • As of June 3, 2019 • • • 10 216, 896 records imported (earliest record 1955) 186, 057 records submitted for CBP phase 6 model 45, 212 records submitted for CBP phase 5 model 175 users (141 external or non-DEQ) 527 listed organizations

Planned Use • For Virginia’s future annual progress reporting to CBP • Data collection for grants reporting • Collect more than just CBP (non-Bay) BMP implementation • Target Deadline for submitting data to DEQ BMP Warehouse for CBP reporting • October 1, 2019 11

Website and Link to Application • https: //apps. deq. virginia. gov/BMP/ 12

Registering 13

Requesting Credentials 14

Selecting an Organization 15

Agree to Terms and Conditions 16

Email with link to set a password 17

Link to set password 18

Set Your Password 19

Landing Page 20

Uploading Data 21

Uploading Data 22

Uploading Data 23

Downloading a Template 24

Templates – General BMP Upload 25

Templates – Grants BMP Reporting 26

Uploading Data 27

Uploading Data 28

Uploading Data 29

Uploading Data 30

Uploading Data 31

Uploading Data 32

Uploading Data 33

Data Validation 34

Data Validation 35

Data Validation • Validation is iterative across a record • Records with multiple validation issues will require multiple upload attempts (date out of range, missing extent, no location…. ) • Until all records validate template will not process 36

Data Submission 37

Successful Submission of Data 38

Searching using Filters 39

Searching using Filters 40

Customizing Columns 41

Customizing Columns 42

Search Results - Export 43

Exporting Filter Results to Excel 44

Exporting Filter Results to Excel 45

Questions • Who can view/upload data? • Registered persons (email associated with a listed organization) can upload, search, and edit that organizations data • Contractors with confirmed organization approval • DEQ can add new organizations at anytime 46

Questions • Who gets “credit”? • Location information uploaded is retained and reported as provided • BMP distribution at finest CBP model segment scale (land river segment) • “Submitted” verses “Credited” and Verification 47

Questions • What can be uploaded? • BMP template as downloaded – any alteration of format/structure = rejection • BMPs listed in the General and Grants Upload Templates • What currently cannot be uploaded? • BMPs not listed in the General or Grants Upload Templates 48

Questions • Are programmatic BMPs reported in the warehouse? • Not currently but could explore adding this functionality • Need BMP names and measures 49

Questions • What about BMPs for industrial facilities, outside of the MS 4 service area but within the locality, who reports those and gets the credit? • Any listed organization willing to provide verification can report via the WH. Credit is based on reason selected and location data provided. 50

Questions • How do BMPs on unregulated urban lands get reported? • Select Other Reporting (Non-319(h)/WQIF NPS) instead of MS 4 Required Reporting on upload screen’s reason for reporting dropdown list. Conditionally required fields based on that selection. 51

Questions • Has DEQ considered adding an option from the home screen to view all data, sortable by county (or other geographic boundaries such as University MS 4 s or Federal facilities) and/or reporting organization? • Application is a data collection and reporting tool. DEQ is exploring the options for a publically available searchable database. Likely a different application that accesses the import and export databases. 52

Questions • In the BMP name dropdown in the reporting template, there is no way to choose different levels of clearinghouse BMPs. These each have different treatment efficiencies. • SWCGP BMP Codes are available for selection in the template. These BMPs may have different efficiencies in the VA SWCGP system but not necessarily so in CBP modeling. 53

Questions • Nor is there a way to differentiate a current approved BMP vs historically approved BMPs from the Blue Book which may have still been implemented off approved plans in recent years. • The WH only accepts implemented BMPs with an installation date. Planned BMPs not yet implemented are not eligible for reporting to the WH. 54

Issues and Solutions • Historical data is missing coordinate pairs and or unique ID provided as part of DEQ records request DEQ has endeavored to attribute records from organizations (> 300) with information provided to DEQ particularly unique IDs Non-decimal degree coordinate pairs converted • Ease of getting data – searches, exports, other organizations data • Additional documentation – definitions and sources • Functional phone number for questions 55

Some Planned Upgrades • Have system generate an email to reporting contact indicating which records need inspection to continue lifespan in CBP model • Remove BMP names unique to phase 5 model • Expand list of multi measure BMPs • Transform applicable Grants data for NEIEN • Improved geographic validation • Inspection upload template • Separate Urban/Suburban and Ag General upload templates 56

Link to Webinar • https: //www. deq. virginia. gov/Programs/Water/Chesapeake. Bay/ Chesapeake. Bay. TMDL/BMPVerification. aspx 57

Contact Information: Bill Keeling NPS Modeling and Data Coordinator DEQ Office of Watershed Programs william. keeling@deq. virginia. gov (804) 698 -4342 Nicole Sandberg Nonpoint Source Program Coordinator DEQ Office of Watershed Programs nicole. sandberg@deq. virginia. gov (804) 698 -4043 Susan Hale Chesapeake Bay Grant Administrator DEQ Office of Ecology susan. hale@deq. virginia. gov (804) 698 -4154 58