# Presentation: 451778

## 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

## 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.

## 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.

## 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

## 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.

## WQ-WET:  Process Overview

## WQ-WET:  File Preparation

- Download template from WQ-WET and format data accordingly
- Format is flexible, to a point

## 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

## 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

## WQ-WET:  File Preparation

- Example:  Analytical procedure search results for “mass”

## WQ-WET:  File Preparation

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

## WQ-WET:  Process Overview

## 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

## 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

## WQ-WET:  Field Checker

- Example:  Set up alias for imported field “Comment”

## 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

## WQ-WET:  Process Overview

## 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

## 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

## 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

## 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)

## WQ-WET:  Process Overview

## WQ-WET:  Extract

- Success!  WQ-WET exports a SIM-ready file.

## 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 MXTM, JavaTM, and MySQLTM.  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.

## 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.

## 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