WQ-WET

1. WQ-WET STORET Water Quality Data Web Entry Tool James M. Porter / Hafiz M. Munir / Louise E. Hotka / Jennifer Oknich, MPCA Akira Matoba, URS Corporation Minnesota Pollution Control Agency

2. WQ-WET: Need For Flexibility • The Minnesota Pollution Control Agency (MPCA) consolidates lake and stream data from dozens of local projects into a single statewide STORET database using SIM software. • Projects receiving certain funding must submit data in a STORET-ready format. • Past attempts to standardize across sources have not been successful.

3. WQ-WET: Need For Flexibility Barriers to standardization: • SIM result-row format. Projects overwhelmingly prefer sample-row format spreadsheets. • STORET allowable values • Periodic updates to allowable values and STORET / SIM features create moving targets.

4. WQ-WET: Need For Flexibility MPCA’s solution is WQ-WET. • Uploads a formatted spreadsheet and facilitates transformation to a SIM import file • Upload, not entry as the name implies. WQ-WUT? • Web-based application allows centralized updates to allowable values, fields, and features • Places more responsibility for STORET-readiness on the data provider

5. WQ-WET: Process Overview • The user looks up allowable STORET values or requests new values while preparing result files. • Upon upload, WQ-WET validates the file and facilitates aliasing of unrecognized field names and result values. This iterative process may require multiple uploads. • When the user has correctly configured the result file and aliases, WQ-WET loads the data and can export a SIM-ready file.

6. WQ-WET: Process Overview

7. WQ-WET: File Preparation • Download template from WQ-WET and format data accordingly • Format is flexible, to a point

8. WQ-WET: File Preparation • Look up allowable fields and values • Provide these wherever possible in upload file • Aliasing step will allow translation of unclear field names and values

9. WQ-WET: File Preparation Example: Station search • Search by name, description, or coordinates • Coordinate search returns lists of stations by proximity • Map links to MapQuest and TopoZone

10. WQ-WET: File Preparation Example: Analytical procedure search results for “mass”

11. WQ-WET: File Preparation When the spreadsheet is ready, the user logs into WQ-WET and accesses the relevant project.

12. WQ-WET: Process Overview

13. WQ-WET: Field Checker Upload spreadsheet to WQ-WET Field Checker • Attempts to identify provided field names • Checks for presence of required fields • Returns errors and their locations in the upload file

14. WQ-WET: Field Checker • Translates field names which match pre-set or previously assigned aliases • Invites user to set up an alias for each unrecognized field • Aliases may be deleted and reassigned

15. WQ-WET: Field Checker Example: Set up alias for imported field “Comment”

16. WQ-WET: Field Checker • When aliasing is complete, reload the file • Make additional file changes or set up aliases as necessary • Repeat until successful • WQ-WET will export a new file with correct field names

17. WQ-WET: Process Overview

18. WQ-WET: Validator Upload Field Checked file to WQ-WET Validator • Checks validity of values for each field • Checks for activity duplication within file and versus previously loaded results • Returns errors and their locations in the Field Checked file

19. WQ-WET: Validator Stations • Translates station IDs which match pre-set or previously assigned aliases • Invites user to set up an alias for each unrecognized station • Search and mapping tools are available when aliasing • Aliases may be deleted and reassigned

20. WQ-WET: Validator Metadata/Result Values • Translates other values which match pre-set or previously assigned aliases • Invites user to set up an alias for each unrecognized value • Allowable-value search tools are available when aliasing • Aliases may be deleted and reassigned

21. WQ-WET: Validator • When aliasing is complete, reload the Field Checked file • Make additional file changes or set up aliases as necessary • Repeat until successful • WQ-WET will load the results into its internal database (not STORET)

22. WQ-WET: Process Overview

23. WQ-WET: Extract Success! WQ-WET exports a SIM-ready file.

24. WQ-WET: More Information • Established values, aliases, and results remain until the next import unless deleted by the user. • User accounts are password-protected and restricted to assigned projects only. Multiple users may access one project if desired. • WQ-WET does not directly access STORET for security reasons. The allowable value tables are rewritten nightly in a separate database. MPCA staff will establish new values and load SIM import files independently from WQ-WET. • WQ-WET uses ColdFusion MX™, Java™, and MySQL™. TopoZone is a trademark of Maps a la carte, Inc. MapQuest is a trademark of MapQuest.com, Inc. ColdFusion MX is a trademark of Macromedia, Inc. Java is a trademark of Sun Microsystems, Inc. MySQL is a trademark of MySQL AB.

25. WQ-WET: Status • URS developed the prototype for a now-completed contract. MPCA manages development now. • Basically ready for internal agency use. The application will save substantial staff time even in its current, somewhat unrefined form. • Output is currently STORET 1.x compatible and needs to be brought up to STORET 2.x standards. • MPCA web developers want it to meet agency web standards before releasing to local projects. • MPCA data managers want it to be user-friendly so we do not overwhelm, discourage, and lose data providers.

26. WQ-WET STORET Water Quality Data Web Entry Tool James M. Porter / Hafiz M. Munir / Louise E. Hotka / Jennifer Oknich, MPCA Akira Matoba, URS Corporation Minnesota Pollution Control Agency