Water Quality Exchange WQX

1
Water Quality Exchange (WQX)

• Kristen Gunthardt US EPA Office of Water
• Deb Soule New Hampshire Department of
  Environmental Services
• 2009 Exchange Network National Meeting
• April 28, 2009

2
Overview

• WQX Flow and Content
• Mechanics of Participating
• The STORET Warehouse
• Web Services
• Future Directions
• WQX Implementation from New Hampshires
  Perspective

3
What is WQX?

• WQX defines the framework by which EPA compiles
  water quality monitoring data (physical, chemical
  and biological) that are collected by a number of
  entities via a shared schema.
• The schema allows for anybody to share data
  regardless of what the original source of the
  data was.
• Organizations
• Projects
• Monitoring Locations
• Activities/Results

4
What does WQX mean for users?

• Distributed STORET users
• WQX requires new mechanisms and infrastructure
  for sharing data (adoption of Exchange Network
  and WQX standards)
• Distributed STORET database submissions will no
  longer be supported as of September this year
• New systems such as AWQMS and WQX Web can assist
  in transition
• Non distributed STORET users
• These folks dont need a replacement database,
  and no longer are required to do data entry in
  two databases
• Must map their data to the common schema and
  set up infrastructure for sharing data
• Small users
• Some will need to shift away from WebSIM
• WQX Web is the replacement tool for WebSIM

5
(No Transcript)
6
(No Transcript)
7
Options for Submitting

• There are various ways of sharing data with WQX
• Full database integration with Exchange Network
  node for XML creation and submission
• Use of a node client for submitting data once XML
  is created
• Use of WQX Web for XML creation from flat file
  and submission
• Other customized solutions

8
WQX Interaction via Node

• Nodes can provide more automated and less manual
• XML creation from source water monitoring
  database
• Data submission
• Data access via WQX or other data partners
• Can customize interaction with WQX and reuse
  processes over and over again
• User management is more integrated with your node
  and is handled by the node administrator
• Data submission and error handling with WQX must
  be coordinated between node administrator and
  water program staff
• Node maintenance is a continuing cost

9
WQX Interaction via Node Client

• Node clients may be used for all WQX submission
  processes
• Can submit, query, solicit, download, etc via
  node client
• All processes are manual
• WQX Exchange Network services must be accessible
  within the node client (e.g. NodeClientLite
  requires a manual addition of WQX services)
• User management is handled on an individual basis
  directly with the CDX Node Helpdesk
• A node client is free in many cases, unless you
  want to customize
• Node clients can not respond to requests for data
• Use of a node client assumes you have a local
  data management system from which you can create
  XML

10
WQX Data Submission Process
Step 4 Map your source data to WQX schema and
valid values
Step 1 Review the resources that are available
(FCD, DET)
Step 3 Set up NAAS account and an Organization
ID
Step 2 Get involved with the Exchange Network
(Node or Node Client?)
Step 7 Correct any errors encountered (CDX
validation and WQX rules)
Step 6 Submit a test file to WQX test
Step 5 Create XML in WQX schema format
Step 8 Confirm successful test submission and
submit to production
Step 10 Determine best method for interacting
with WQX for your Organization
Step 9 Confirm submissions via WQX query and
solicit services
Step 11 Access submitted data via the STORET
Warehouse
11
Setting up an Organization ID and a NAAS Account

• An Organization Identifier (Org ID) is a unique
  identifier to identify you within the WQX system
  and the STORET Warehouse (e.g. YUROK for the
  Yurok Tribe or 1VTDECWQ for Vermont Dept of
  Environmental Conservation).
• You must have an Org ID to be able to submit to
  WQX
• Organization IDs are assigned by the EPA STORET
  Team upon the request of the user. To request an
  Org ID, send an email to storet_at_epa.gov
• Provide the NAAS accounts that you want to have
  access to your Organization ID
• NAAS accounts are assigned by Node Administrators
  or by the CDX Help Desk
• For users of node clients (who dont have a Node
  Administrator), NAAS accounts are assigned by the
  CDX Help Desk on an individual basis

12
Mapping your source data to WQX schema and valid
values

• The largest challenge for users of WQX is mapping
  their water monitoring data to the WQX schema
  business rules and domain values
• Key Domain Value Lists
• Characteristics
• WQX standardizes on the EPA Substance Registry
  System (SRS) for chemical names
• WQX will maintain candidate names if a chemical
  name has not been vetted through SRS
• Taxa Names
• WQX standardizes on the Integrated Taxonomic
  Information System (ITIS) for taxa names and rank
• May need to roll up to lowest ITIS rank name
• Monitoring program working with ITIS system for
  updates
• Please contact us for new requests

13
Dealing with WQX errors

• The CDX node performs validation that the
  submitted XML is well formed
• If the file does not pass CDX validation, it does
  not continue on to WQX for processing
• The WQX processing software checks that files
  conform to WQX data element formats, business
  rules and domain values
• Once a file is submitted and has either failed
  or completed, users can download the following
• Original XML file submitted
• CDX validation report
• CDX notification file reporting status as either
  completed or failed
• WQX processing report summarizing the
  inserted/updated/deleted data and any encountered
  errors

14
WQX Exchange Network Data Services

• WQX data services allow users to confirm data
  presence and accuracy in the WQX database
• All Organization specific services return data in
  WQX v2.0 schema format (same format as input)
• Via Query or Solicit, users may request data from
  the WQX database using the following services
• GetProjectByParameters_v2.0
• GetMonitoringLocationByParameters_v2.0
• GetActivityByParameters_v2.0
• GetActivityGroupByParameters_v2.0
• GetBiologicalHabitatIndexByParameters_v2.0
• GetResultByParameters_v2.0
• GetDomainValueByElementName_v1.0 and _v2.0
• GetTransactionHistoryByParameters_v1.0 and _v2.0

15
WQX Web

• A new web-based tool that is easy to use for
  submitting data via WQX
• A WQX XML generation and submission tool
• Is built on the WQX v2.0 schema (all WQX data
  elements are available in WQX Web)
• WQX Web accepts basic input formats (i.e. Excel
  Spreadsheets) to generate WQX XML
• Allows user to set up translations for mapping
  source data to WQX domain values or defaults
• Integrated into the WQX framework for submission
  and error checking
• Accessible in the CDX Web environment
• User authentication is through CDX Web
• CDX Web passes username to WQX Web for use and
  access to Organization specific settings and
  configurations

16
WQX Web Data Submission Process
Step 4 Set up CDX Web Account and an
Organization ID
Step 1 Use Excel, Access, or other software to
track and analyze data
Step 3 Save data as a .csv or .txt file
Step 2 Use the WQX Web Excel template to match
your data to the WQX data rules
Step 6 Use an existing import configuration or
create a new one for your data in WQX Web
Step 5 Set up Organization specific look up
values
Optional Step Use WQX Web tutorials and sample
data to familiarize yourself with WQX Web

• Step 7 Import data into WQX Web

Step 9 Export data into XML and submit to CDX
and the STORET Warehouse
Step 8 Correct any errors encountered
Step 10 Correct any errors encountered
17
Setting up a CDX Web Account and an Organization
ID

• Contact the STORET Team to request both a
  Organization ID and a CDX Web Account
• Send an email with your contact information to
  storet_at_epa.gov
• The STORET Team will pass this information on to
  the CDX Help Desk in order for CDX to set you up
  with a CDX Web Account
• The CDX Help Desk sends users a Customer
  Retrieval Key (CRK) for setting up a new CDX Web
  Account that has access to WQX Web.
• Users with existing CDX Accounts can be set up to
  use WQX Web please let the STORET team know if
  you have an existing CDX Account

18
WQX and WQX Web Summary

• WQX Web
• Requires an internet connection to submit data
  over the Exchange Network
• XML is created from a flat file
• Uses a CDX Web Account ID for identifying a
  submitter

• WQX
• Requires a node or a node client to submit data
  over the Exchange Network
• XML must be already created
• Uses the Exchange Network NAAS ID for identifying
  a submitter

All data is placed in the STORET Warehouse under
your Organization ID on a weekly basis
19
The STORET Warehouse

• All data submitted via WQX and WQX Web are placed
  in the STORET Warehouse
• The purpose of the compilation of data in the
  STORET Data Warehouse is to provide a seamless
  collection of monitoring data that is not
  restricted by jurisdictional boundaries.

20
Accessing Data
21
STORET Warehouse Tools for Data Access

• Publicly available web services (dont require
  Exchange Network authentication or protocols for
  access)
• Watershed Summary Tool uses the STORET
  Warehouse web services to provide data summary
  information by 8-digit HUC
• www.epa.gov/storet/web_services

22
STORET Warehouse Public Web Services

• Four core services
• Stations service provides specific station
  information by geographic location
• Results service provides results for modeling,
  analysis, and decision making
• Watershed/Station Catalog service provides
  summary information on what data are available
  within an 8-digit HUC
• Project Catalog service provides summary
  information by projects by geographic location

23
Watershed Summary Example
24
EPA STORET and USGS NWIS

• STORET and NWIS diverged in data model when
  STORET modernized to the distributed database
  (e.g. use of the parameter code)
• Working together to develop a common suite of web
  services for discrete water quality data
• Focus sharing of water monitoring data via a
  common format and common terminology
• USEPA initial web services are now available
• USGS web services are now available
• http//qwwebservices.usgs.gov/

25
Future Directions

• Short Term
• Shepherd the transition from distributed STORET
  database to WQX framework
• Continue to enhance WQX framework (WQX processing
  improvements, WQX Web tool improvements)
• Implement web services that are fully compatible
  with USGS NWIS water quality data services
• Implement common data portal for STORET and
  NWIS data access
• Begin to engage on common catalogue services with
  others
• Begin to integrate more geospatial functionality
• E.g. Linkage of stations to NHD and NHDPlus
  framework, provide better Web Mapping Services

26
Future Directions, continued

• The WQX Schema
• No plans to change current version at this time,
  but must consider
• Future adoption of the Toxicity and Population
  Data Standard by the Exchange Network governance
  (as put forth by the National Water Quality
  Monitoring Council)
• Demand for continuous monitoring data sharing
  framework
• Expand work with other Federal Agencies (USGS and
  NOAA) for data sharing

27
NH Perspective Database Background

• Have a multi-media Oracle database
• built in-house 6 years ago to meet STORET
  reporting requirements and department needs for
  monitoring data
• supports
• lake, river, estuary, and ocean data
• site remediation, Superfund and air data
• data loggers
• includes
• 30 organizations
• 170 projects
• 19,000 monitoring locations
• 2.7 million results
• Besides hand entry, data also incorporated via
• automated uploads from state lab
• web uploads (Excel spreadsheets) from registered
  users
• Supported primarily by one developer, business
  analyst, and data specialist (all part-time
  have other databases to support)

28
Getting data into STORET

• Currently query out data, import it into local
  copy of STORET using the STORET Import Module
  (SIM), and then send dump files to the EPA.
• Issues with this approach
• Have to keep track of changes to data that went
  to STORET
• History logs
• Lock down records staff ask data management
  staff to update
• Then hand edit records in STORET
• Basic problem of data in 2 places
• Limited amount of data put into STORET
• Delay in getting records uploaded/updated in
  STORET
• Storage space for STORET and own database

29
Why WQX is a good thing

• Will eliminate some data entry and maintenance
  into 2 systems
• Will eliminate storage space and DBA maintenance
  for STORET
• Allows us to return editing ability to staff for
  records that have been sent to the EPA data
  editing freedom
• Enables easier data sharing with other groups
  (such as drinking water) since dealing with the
  same national data standards
• Reduces data manipulations and will be more
  consistent since one program will do all
  translations
• Data will be refreshed more quickly and
  eventually automatically
• Forced us to get our data into better shape since
  transmissions would be more automated

30
Challenges to using the WQX

• Had to develop several crosswalks between our
  domain lists and WQX
• WQX had slightly different values than STORET
• Users were accustomed to their values
• WQX requires items that are difficult to get from
  labs and others such as result value type
  (actual, estimated, or calculated), time zone,
  and fraction type (total, dissolved, suspended
  etc.)
• Needed to determine how to characterize
  non-detects, values above quantification limits,
  and non-reported data.
• Our data has gt and lt symbols, ND, BDL,
  TNTC, or no value
• Created program to translate data to text values
  of Not Detected, Present Above Quantification
  Limit, or Not Reported
• If have field and lab data in the same sample,
  will require analytical methods for all results
  if indicate it is a sample and not a field
  measurement. Analytical methods for Weather
  Comments, General Observations etc. had to be set
  to None.

31
Challenges to using the WQX (cont)

• Needed another programmer to query our data,
  transform it according to the schema and the
  uniqueness of our data, and create the XML
  file. Didnt need a programmer for STORET uploads
  except DBA to create dump file.
• Developed new tables and processes to capture
  records deleted by the users so can send a delete
  WQX file
• Developed a program that would guide selection of
  data to send to WQX since not all data in
  database will go to WQX
• Had to set up infrastructure in database to
  track
• What samples were sent to WQX and when,
• If WQX columns in records were updated and the
  record needed to be resent to WQX,
• Whether records were complete enough to meet WQX
  requirements
• Needed to enhance data, especially old data to
  meet requirements analytical methods, sample
  collection methods, value types, etc.

32
How will we transition to WQX?

• Use custom built program to extract data and
  create XML file
• After review of XML file, can have program log
  into CDX for the submitter and upload file and
  download any errors - no interaction with our
  node
• Then log into CDX to see transaction log
• Plan to submit to CDX via our node after upgrade
  it to OpenNode2
• Custom built program
• Looks for WQX complete records for project
  dates of interest
• Distinguishes if record needs to be
  sent/resent/deleted
• Translates data to WQX domain values
• Incorporates our unique parameter data (e.g.
  Algal Growth, Wildlife Observed) into
  activity comments since no match for these
  parameters in WQX/SRS
• Creates time zone based on date and formula
• Translates ND, BDL, TNTC, lt, gt etc.
• Inserts date sent into our database records after
  WQX submission is completed
• Doesnt transmit documents (objects) or data
  logger info at this time
• Investigating use of WQX Web to submit data

33
Where are we on transitioning to WQX?

• Database updated and tracking overhead in place
• Testing custom built program
• Reviewing data translation for accuracy
• Still making program tweaks
• Havent submitted anything to WQX using this
  process yet
• Will send to WQX Test site first and then check
  data
• Submitted some project data via WQX Web
• Still need to work out dual project submission
  for beaches
• Submit each beach as a project with its own data
• Submit under EPABeach project

34
Using the WQX for Gulf of Maine Exchange

• Gulf of Maine Ocean Data Partnership is using the
  WQX 2.0 as a base for developing a data exchange
  template
• Some of the partners NHDES, GoMOOS, UNH, Tufts,
  USC-NOAA, GoMC, Canadian entities, USGS, EPA,
  WHOI
• Incorporate GeoRSS Geography Markup Language
  (GML) profile for locational data
• Need to handle cruise and trawl data, traveling
  buoys etc.
• Allow for lines and polygons as monitoring
  locations not just points
• Incorporating a catalog record concept
• Include Global Change Master Directory (GCMD)
  data elements
• Partners previously developed or incorporated
  their metadata into GCMD
• Build on this work and give partners opportunity
  to clean up metadata
• Include elements from the New Jersey Water
  Monitoring Inventory Project
• Thanks to Paul Morton and company for guidance
• Allow partners to participate at many levels
• Project/Catalog record (use GCMD info)
• Monitoring Locations
• Activities

35
Web Resources, Support and Contacts

• www.epa.gov/storet
• www.exchangenetwork.net/exchanges/water/wqx.htm
• www.epa.gov/storet/wqx.html
• storet_at_epa.gov or 1-800-424-9067
• Kristen Gunthardt US EPA Office of Water
• [phone]
• gunthardt.kristen_at_epa.gov
• Deb Soule New Hampshire Department of
  Environmental Services
• [phone]
• Deb.Soule_at_des.nh.gov