Transcript Water Quality Metadata Database Presentation

SE Coastal Network Water Quality Inventory &
Monitoring Program Database Development
Wade Sheldon & John Carpenter
Dept. of Marine Sciences
University of Georgia
Project Background

NPS identified need to compile information on long-term
monitoring of water quality in SE coastal waters

In Fall 2007 we proposed to:




Conduct workshop to gather input, info on regional activities
Compile database of long-term monitoring program metadata
Produce report summarizing monitoring in region
Develop interactive web applications for querying database
Database Design Process - Research

Reviewed other environmental monitoring database efforts
 International: GTOS TEMS (http://www.fao.org/gtos/tems/)
 Regional: Gulf of Maine Council on the Marine Environment
(http://restoration.gulfofmaine.org/)
 OOS, regional consortia (SECOORA), …

Reviewed monitoring program/observation metadata standards
 MMI (http://marinemetadata.org/) – various schemas, drafts
 EPA STORET (parameters) – legacy metadata
 NEIEN (EPA/States/Tribes)
 Environmental Data Standards Council (2006 standards/schemas)
 Water Quality Exchange (WQX) – STORET replacement
 NWQMC - Water Quality Data Elements
Database Design Process - Evaluation

Concluded huge amount of work going on to standardize and
coordinate metadata/data exchange
 Regional consortia, EON/IOOS
 New NSF programs (INTEROP, FEON)

Differences in approaches, progress
 Detailed schemas & standards -> basic metadata content recommendations
without implementation tools -> plans in development
 Some controlled vocabularies, but not widely applied

Common threads
 Geospatial query/visualization (Google Maps API common)
 Thematic searches at varying granularity (topics, programs, parameter types)
Database Design Process - Approach

Decide on initial scope
 NPS goal: Water Quality monitoring in SE coastal zone (NC,SC,GA,FL)
 Geographic bounds
 Initial monitoring programs to target

Design, prototype relational database to support thematic and
geospatial queries for targeted content
 Support relevant metadata content standards (EDSC, NWQMC)
 Flexibility for adding new metadata fields, varying completeness
 Support varying granularity in searches (broad-to-narrow), hierarchies
Geographic Scope: Defining the Coastal Zone
^
_
^_
North
Carolina
South
Carolina
^_
^_
^_
Georgia
^_
^
_
^ ^_
_
^_ !(
^_
^_ !( !(
(!!(
!(
(!!(
!(
!(
^_
^_
^_
(!!(
(!!( !(
!(
!(
!( !(
!(
(!!(!(
!(
!(
!(
!(!(
!( !(
!(
!(
!(
!(
^_ ^_
(!
!(
!(
^_ ^_
Florida
^_
Gulf of Mexico
!(
!(
(!!(
Atlantic Ocean
^_
^_!(
^ _
_
^_ ^^_
Legend
!(
^_!(!(
!(
!(
^_
!(
USGS discharge stations
NDBC buoys
< 5 ft. elevation contours
Geographic Scope: Defining the Coastal Zone
^
_
^_
^_
North
Carolina
South
Carolina
^_
^_
^_
Georgia
^_
^
_
^ ^_
_
^_ !(
^_
^_ !( !(
^_
^_
(!!(
(!
!(!(
!(
(!!( !(
!(
(!!(
!(
!(
!( !(
!(
(!!(!(
!(
!(
!(
!(!(
!( !(
!(
!(
!(
!(
^_ ^_
(!
!(
!(
^_ ^_
Florida
^_
Gulf of Mexico
!(
!(
(!!(
^_
^_!(
^__
_
^
^ ^_
Atlantic Ocean
Legend
!(
!(
^_
!(
^_!(!(
!(
USGS discharge stations
NDBC buoys
< 5 ft. elevation contours
Coastal Zone
Initial Monitoring Programs

USGS National Water Information System
 Large amount of relevant, long-term data (water quality, streamflow,
groundwater)
 Excellent support for data mining (XML reports, RDB data)
 Use STORET parameter codes (basic groupings, units, methods)

NOAA National Estuarine Research Reserve SWMP
 Relevant, medium-long-term data
 Support for mining stations, parameters via SOAP

EPA STORET
 Problem: many sites without locations, complete metadata

“In house” programs (GCE-LTER, NPS)
Prototype Relational Database

Draft completed in May 2008, implemented in SQL Server 2000

Basic structure:
Research Projects
Monitoring Programs
Monitoring Stations/Sites
Parameters/Methods

Scalable metadata model used for projects, monitoring programs
 Some fixed fields (title, desc., dates, web url)
 Additional editable lists of available fields with web display options

Developed administrative forms (MS Access)

Prototype web interfaces under development (demo’d here)
Administrative Forms – Metadata Fields
Administrative Forms – Projects
Web Forms Interface
Next Steps

Gather workshop feedback on:




Overall approach
Metadata content to survey
Queries, output formats to support
Controlled vocabularies, hierarchies for parameters

Complete web interfaces for interactive input, queries,
map display

Solicit metadata from other groups

Continue to develop automated ingestion approaches
 XML/XSLT/SOAP
 Spreadsheet templates

Prepare NPS report
Map Visualizations – Draft Google Earth Display
Spatial Query – Google Maps & Place Names
Web Access

This workshop:
 Router: SSID = GCRC (passphrase = Water Quality)
 http://192.168.1.100/wqmeta/
 Register or use username = nps_workshop, pw = nps_workshop

After the workshop:
 http://www.gcrc.uga.edu/wqmeta/

Background materials:
 Database design (ERD)
 Candidate parameter list, hierarchy
 GIS resources for coastal zone determination