# Presentation: 513338

## Surface Water Quality Monitoring Information System (SWQMIS) 

- Cindi Atwood
- Tetra Tech, Inc.
- (703) 385-6000
- cynthia.atwood@tetratech.com

- Nancy Ragland
- TCEQ
- (512) 239-6546
- NRagland@tceq.state.tx.us

**Notes:**
Cindi

Welcome to SWQMIS!  
SWQMIS is the Surface water quality monitoring information system developed for the Texas Commission on Environmental Quality, represented by Nancy Ragland.
It was developed by Tetra Tech of Fairfax, VA, represented by me, Cindi Atwood.

## TCEQ’s New Enterprise Data System for Managing Ambient Water Quality Data 

**TCEQ’s New Enterprise Data System for Managing Ambient Water Quality Data **

**Notes:**
Cindi

We would like to do a quick review of this Enterprise Data system for managing ambient water quality data and discuss some of the lessons learned and still leave a few minutes for questions.

## Benefits

**Notes:**
Cindi

This was a significant project for TCEQ.  They went through a very comprehensive evaluation of their processes and data. It took a lot of effort to develop requirements and develop a thorough design  and then to build and test this application, but TCEQ is already reaping the benefits.

## Benefits

- Consolidate and centralize data
- Increase data consistency and integrity
- Assist your agency with internal and external data requests
- Help management to turn around urgent, high priority requests quickly
- Save time and money

**Notes:**
Cindi
Assists communications

## One Week vs  One Day

- 44 Hours down to 8 Hours
- 2 person task down to a 1 person task

**Notes:**
Cindi

I spoke with one of the Project Managers, for water quality, who works with the Brazos basin.  She gets a lot of water quality questions and data requests, regarding municipalities, dairy CAFO (concentrated area feeding organization) or wastewater treatment. These requests are from city, or state officials or others, some of which have political implications. 

These periodic data requests would involve another person several hours to pull the data and this project manager a week to analyze and organize the data.   Since SWQMIS has been implemented, she can now do an even more comprehensive job in about a day.  

During the design phase, she customized a report, the “Single Parameter Report”  to gathered most of the data that she needs and present it in a manner that helps her look at trends.  She can associate flow with each data point.  She can compare station by station and look at up-stream and down-stream differences such as in determining the impact of a wastewater treatment plant.   It helps determining trends and on a monthly basis. 
The report helps her tremendously and helps upper management in dealing more timely and efficiently with the public.

## System Deployment

**Notes:**
Nancy

## System Deployment

- Deployed the system successfully in early 2007
- Developed the system as:
- Web-based
- J2EE and Oracle
- Integrated the system with web-based GIS functionalities using ESRI ArcIMS and ArcSDE

**Notes:**
Nancy

Web-based is necessary to be accessible to all of the field offices

We have trained 100+ people.
Currently have approximately 40 people using the system on a weekly basis

## Core System Modules

- Monitoring Stations
- Equipment
- Geographical Areas
- Constituents
- Sampling
- Comparison Information

- Projects
- Reports and Extracts
- Maps (GIS)
- Assessment
- Administration

**Notes:**
Nancy

## Manage Ambient Water Quality Data

**Notes:**
Nancy

When developing this system, we made sure that we designed the system in such a way that it enhanced our business processes, rather than dictating them.

## Manage Ambient Water Quality Data

- Organize data in a way that enhances business processes
- Protect data integrity through clean system design
- Involve stakeholders at all levels of organization in design and test
- Keep system flexible to meet future needs

**Notes:**
Nancy

We have already tested the flexibility of the system by adding on two new modules, one to flow data to EPA and one to aid the Assessment process.  We had no core integration problems with these modules.

## Consolidate & Centralize Databases

**Notes:**
Nancy

/It was important to gather all the water data in one location.  This ensured everyone was using the same data.

## Consolidate & Centralize Databases

- Allow core data submittal from various data owners
- Consolidate and centralize databases containing :
- Field measurements
- Toxics and organics in a variety of media
- Biological
- Habitat parameters, etc.
- Spatial data

**Notes:**
Nancy
Media -- Sediment, tissue and water
Biological – benthic macroinvertebrates and fish

Data submitted in pipe delimited file format

Spatial data for station data (lat/long, elevation, etc.)

## Various Data Sources

**Notes:**
Nancy

## Various Data Sources

- Supply custom interfaces for manual entry
- Administrators
- Data Managers
- Field Personnel
- Assessors
- Provide mass data loaders for various data sources including
- Continuous data from data loggers
- LIMS
- USGS
- River and municipal authorities in Texas

**Notes:**
Nancy

Laboratory Information Management Systems

Allows different sources to have different input formats.

## Data Integrity

**Notes:**
Cindi

It is important to us that we have data integrity.

## Data Integrity

- Provide custom interfaces for manual entry
- Enforce consistent data entry of all types of data
- Typographical errors
- Outlier checks
- Validate data from manual and mass loaded data
- Review of submitted data by data managers to approve or deny

**Notes:**
Cindi

Custom interfaces follow the flow of how data is processed and insures required content...

We avoid typographical errors by using as many drop-down lists as possible.
Trust that data is in similar units, significant digits.  
Enforce validation and consistency.

After validating the data, potential problems can be flagged for review by a data manager.

As smart as we can make systems, people can still notice issues that are difficult to capture in business rules, so we also allow a manual review of submitted sample data, monitoring station requests and mass loaded data by the data managers.

## Mass Loaded Data Validation

- The automated validation checks are all run, without stopping after the first “failure.”  In this way, a complete list of errors can be shown.
- Over 50 validation checks run on mass loaded data
**Check for outliers. ** Check measurements against predefined ranges (min/max).  Compares verify flag in the Results file to see if the outlier value has already been verified by the collector.
- If sample **collected when raining** or has rained within the last 24 hours, report a value of <1.  In other words, is days since precipitation (72053) < 1?
**Check for duplicates** in data set based on station ID, sample date, sample time, and parameter code.

**Notes:**
Cindi

## GIS Integration

**Notes:**
Cindi

## GIS Integration

- Allow map-based creation of sites
- Automatically assign geographic attributes such as basin, county and ecoregion to a site
- Provide map-based search and reporting features
- Synchronize geographic data with tabular data

**Notes:**
Cindi

Station Location Requests
Button to synch attributes based on lat / long

## GIS Integration

**Notes:**
Cindi
Station Location Requests from field personnel
Monitoring Stations
Segments
Streets
DRG photos from USGS  digital raster graphic is a scanned image of a U.S. Geological Survey (USGS) standard series topographic map, including all map collar information. The image inside the map neatline is georeferenced to the surface of the earth and fit to the Universal Transverse Mercator projection. The horizontal positional accuracy and datum of the DRG matches the accuracy and datum of the source map. The map is scanned at a minimum resolution of 250 dots per inch. 
DOQQ – Digital Ortho Quarter Quadrangles and Aerial photography from USGS
An aerial photograph and an orthophoto or orthoimage may look alike, but there are several important differences that allow an orthophoto to be used like a map. A conventional perspective aerial photograph contains image distortions caused by the tilting of the camera and terrain relief (topography). It does not have a uniform scale. You cannot measure distances on an aerial photograph like you can on a map. An aerial photo is not a map. The effects of tilt and relief are removed from the aerial photograph by a mathematical process called rectification. An orthophoto is a uniform-scale image. Since an orthophoto has a uniform scale, it is possible to measure directly on it like other maps. An orthophoto may serve as a base map onto which other map information may be overlain.  A DOQ can be used on-screen to collect, review, and revise other digital data, especially digital line graphs (DLG) and topographic maps. When the DOQ is combined with other digital products, such as digital raster graphics (DRG) or digital elevation models (DEM), the resulting image provides additional visual information for the extraction and revision of base cartographic information. 

## GIS Integration

**Notes:**
Cindi

During training, field personnel commented that in many cases, this could save them a physical trip to a location as they could get accurate lat/longs from the appropriate use of layers and zoom and our automated monitoring station location request process.

Can attach this image as a jpg.  Can also attach any other document that helps clarify something about the monitoring station.

## Public Access

**Notes:**
Cindi

## Public Access

- Allow the public and other organizations to retrieve water quality data

**Notes:**
Cindi

## Public Access

**Notes:**
Cindi

This GIS interface is based on ArcIMS and is dynamic.  It grabs the latest data from the reporting database.  However, it was greatly simplified, using a drill-down approach, from the typical ArcIMS interface.

## Public Access – Drill Down

- 100 x 100
- Mile Square

**Notes:**
Cindi

## Public Access – Drill Down

- 25 x 25
- Mile Square

**Notes:**
Cindi

## Public Access – Drill Down

- 6.25 x 6.25
- Mile Square

**Notes:**
Cindi

## Public Access – Select Stations

**Notes:**
Cindi

Once the sites of interest are located, the user can select the parameters of interest at those sites.

## Public Access -- Reports

**Notes:**
Cindi

Easy to read HTML reports are available via the browser.

## Public Access – Text Reports

**Notes:**
Cindi

It is not pretty, but it can be extremely useful.
You can also save a Comma separated values file for use in a spreadsheet or a pipe delimited file that is useful for data loading  for importing and exporting purposes. 

## Reports & Data Extractions

**Notes:**
Nancy

Internal reports and data extractions

## Reports & Data Extractions

- Avaliable in:
- HTML
- CSV (Comma Separated Values)
- Pipe delimited “|” (text) format

**Notes:**
Nancy
HTML – pretty reports
CSV – for display in a spreadsheet
Pipe – for importing and exporting data

And yes, if you were wondering, it can actually export out the whole database, ~7Million results and about 20G of data in about 20 minutes.

## Reports & Data Extractions

- Station Inventory Report

**Notes:**
Nancy
CSV for display in excel
Pipe for import or export of data

The monitoring station inventory report is highly customizable.  It has ~50 attributes that can be selected to report on.  Here, we have selected 5 of the 50.

## Reports & Data Extractions

- Sampling History Report

**Notes:**
Nancy
CSV for display in excel
Pipe for import or export of data

## Assessments

**Notes:**
Cindi

## Assessments

- Integrates SWQMIS with the statistical SAS tools in order to:
- Summarize data
- Calculate various statistics
- Provide reports for assessors

**Notes:**
Cindi

Assessments is a module that was added on to the SWQMIS core.  It integrates SWQMIS with
SAS--which is a statistical tool, used by assessors, to summarize data, calculate statistics and provide reports.

## Assessments

**Assessments**

**Notes:**
Cindi

This area represents the core SWQMIS application.  It consists of a transactional database, a reporting database and a spatial database.  This is integrated with application servers and an ArcIMS server to make the data accessible to our users via the web client.
SAS is a statistical package.  The TCEQ assessors wanted to keep the statistical processing in the SAS environment, so we just enhanced their analytical processes and connected the SAS server to the core data. 
The assessors can grab raw data from the core databases, use SAS to form datasets and generate statistics and then submit the final processed assessment results back to the core for reporting purposes.  This can be done as often as desired, but will most likely result in one coordinated dump each year. 

## Assessments – Business Logic

- Identify and convert preferred parameters to correct units
- Handle profile samples appropriately
- Identification of the mixed surface layer
- Summarize data and calculate statistics
- Mean, geometric mean, median, standard deviation, percentiles, confidence intervals

**Notes:**
Cindi

A manual assessment process typically involves assessors looking through books, manuals and reports to determine the appropriate criteria for a given evaluation.  This assessments module primarily is used to automate the logic of most of the tedious steps via a series of business rules

As the system goes through the business logic, it (slide), most steps and decision points are generated automatically, but the assessors can override any process.

For example, there might be multiple nitrate measurements, which is the best nitrate measurement to select?  The system evaluates the possibilities and suggests the correct one using the built in business rules. It fully documents its decision making process. The data that is summarized, is fully traceable. The assessors can review that result and keep or override that suggestion. If a value is not used in the assessment, it has a flag and a comment as to why it was not used. E.g. Instrument failure or extreme weather made the measurement an outlier.  

One area that it saves a lot of time with is dealing with profile samples, like from a reservoir, to identify appropriate measurements and summarize the profile data.

## Assessments – Business Logic (cont.)

- Determine appropriate criteria and identify exceedances
- Basin-specific
- Segment-specific
- Site-specific
- Formulas (acute & chronic metals)
- Ascertain waterbody’s support status
- Provide full documentation for all steps of the process

**Notes:**
Cindi

The assessment process is about finding the appropriate criteria. 

It might be Geographic based – different levels for basin, segment or monitoring station may have default levels.

It might be formula base.  Formulas are very dynamic and based on date and time.  If measuring metals, a low pH may prove toxic and a high pH may not.  Therefore it combines many elements to determine on the fly what the criteria should be.

## Data Flow

**Notes:**
Nancy

## Data Flow

- Transfer of core data to EPA automatically on a periodic basis through web services and the Central Data Exchange (CDX via the Water Quality Exchange (WQX)

**Notes:**
Nancy

This is how we report data to EPA (STORET).

## Data Flow

- Actively flowing data to the EPA since September, 2007
- Fully automated process
- Currently transfer physical and chemical data
- Will transfer biological data at a later date

**Notes:**
Nancy

First time we had reported SWQM data to EPA since 1998.

Automated process that can be manipulated manually, if necessary.

Participating in Biological/Habitat pilot.

## Administration

**Notes:**
Nancy

## Administration

- Track the “history” of data changes
- Provide interfaces that allow a database administrator to manage:
- Users
- Security
- Reference data

**Notes:**
Nancy

History of changes important for data integrity.

Managing reference data important for system flexibility.

## Lessons Learned

- Allow design time that is adequate for the scope
- Employ tried technology
- Use agile development techniques
- Start third party integration communication and testing early

**Notes:**
Cindi and Nancy

Our design time was very tight.  Make sure that you are generous with your design time.
For example, we were directed to use JSF.  It was not yet a mature technology.  Things worked out fine, but would have been smoother if it had been more mature.
We started out with a waterfall or system wide development approach.  We switched to a more agile approach and things worked much more smoothly.
Flowing data to the through CDX and WQX and working with SAS took longer than we expected.  Remember that you don’t have control over third parties.  No matter how helpful people are, they are not working to your time table.  Integrate early.