WQWET

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

• MPCAs 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