A Coastal Water Quality Metadata Databasefor the Southeast U.S.A.

Wade Sheldon, Christine Laporte, Travis Douce

and Merryl Alber

Georgia Coastal Research CouncilDept. of Marine Sciences

University of Georgia

Background NPS needed information on water quality monitoring in SE USA

Developing SE Coast Network monitoring program Avoid duplication of effort Identify comparable data to leverage Discover data gaps for future expansion Information proved very difficult for NPS to obtain

GCRC funded to identify long-term monitoring activities Workshop to gather input, info on regional activities Developed database of long-term monitoring program metadata Developed interactive web applications, services for querying database Phase 1 completed in 2009

GCRC funded in 2010 to extend scope, functionality (Phase 2)

Rationale

Numerous agencies and institutions collecting water quality data

Information highly dispersed Large federal databases (USGS, NOAA, EPA) Program/project web pages (NERR, LTER, USFS, NPS, SAML, ...) State/municipal web pages Offline sources (reports, brochures)

Information diverse in scope, format, accessibility Web pages, text files, XML, maps/visualizations Online sources have radically-different interfaces, export capabilities

Unified database needed to facilitate discovery

Monitoring Station Search Examples

Project Scope and Goals Targeted information

Programs monitoring water quality (and related measurements) Sponsoring organization information Description (mission, goals, policies) Points of contact (web site, names, email) Time period

Monitoring locations/stations Geographic location (state, county, HUC, latitude/longitude) Parameters measured Time period Web links for information and data downloads

Geographic Scope Phase 1: coastal zone of NC, SC, GA, East FL Phase 2: South Atlantic Landscape Conservation Cooperative (SALCC)

Geographic Coverage

Database Design Process Designed relational database to store content

Used SQL Server 2000/2005 Support for relevant metadata content standards (EDSC) Support for adding new metadata fields, varying completeness Support for varying granularity in searches (thematic & geospatial)

Developed “middleware” for connecting to database Standard queries (views) to abstract structure, simplify querying Stored procedures for updates and programmatic queries XML middleware for Google Maps, web services (SQLXML)

Developed user interfaces for update/query MS Access forms for GCRC staff Web portal for public access (IIS/Active Server Pages)

Hierarchical design with strong referential integrity Organizations Monitoring Programs Monitoring Stations Measured Parameters

Flexible content model for Organization/Program metadata Fixed fields for critical content

(name, acronym, web URL, ...) Controlled vocabularies of optional

fields for descriptive metadata Supports wide range of detail based

on reporting practices

Data Model

Data Model

Monitoring station metadata Site name, description Lat/Lon State/County/location 8-digit HUC Probabilistic or long-term site Dates of operation Data download/request URL

Measurement parameters Linked to stations or programs Linked to USGS NWIS STORET codes Parameter groups for multiple levels of

detail (nutrients > nitrogen > ammonium)

Populating the Database

List of candidate organizations/programs from NPS, workshop input and local knowledge

GCRC staff entered organization and program info from web

Monitoring locations, dates, parameters entered multiple ways Mined from program databases if online, queryable

Federal: USGS NWIS, NOAA NWS (NCDC), ... National Programs: NERR CDMO, LTER, ...

Requested in spreadsheet form if not online NPS, state agencies, municipalities, ...

Program reps asked to check entries

Targeted OrganizationsCAMA NE - Florida Department of Environmental Protection: Coastal and Aquatic Managed Areas

CAMA SE - Florida Department of Environmental Protection: Coastal and Aquatic Managed Areas

CAMA SFL - Florida Department of Environmental Protection: Coastal and Aquatic Managed Areas

CCEHBR - NOAA Centers for Coastal Ocean Science, Coastal Environmental Health and Biomolecular Research

FDEP NE - Florida Department of Environmental Protection: Northeast District

FerryMon - University of North Carolina FerryMon

GCE-LTER - Georgia Coastal Ecosystems Long Term Ecological Research

GCRD - Georgia Department of Natural Resources: Georgia Coastal Resources Division

JAX - Florida: City of Jacksonville

MAREX - University of Georgia Marine Extension Service: MAREX Marine Advisory Services

NC DENR DEH - North Carolina Department of Environment and Natural Resources: Division of Environmental Health

NC DENR DWQ - North Carolina Department of Environment and Natural Resources: Division of Water Quality

NERRS - NOAA National Estuarine Research Reserve System

NOAA NWS - NOAA National Weather Service

SC DNR - South Carolina Department of Natural Resources: Marine Division

SC EQC - South Carolina Department of Health and Environmental Control

SECN - National Park Service: Southeast Coast Network

SECOORA - Southeast Coastal Ocean Observing Regional Association

SERC - Florida International University: Southeast Environmental Research Center

SFWMD - Florida Water Management Districts: South Florida Water Management District

SJRWMD - Florida Water Management Districts: St. Johns River Water Management District

USGS - U.S. Geological Survey

Data Entry Forms – MS Access

Data Entry Forms – Web (ASP)

Database Statistics (Apr 2011)

Organizations: 22

Monitoring Programs: 42

Monitoring Locations: 44,057

Parameters Measured: 2266

Location-Parameter Records: 764,887

Accessing the Database Prototype web portal: http://www.gcrc.uga.edu/wqmeta/ Browseable lists of organizations, programs with links to info Search for monitoring locations by multiple criteria

Program name, site name, site type Parameter name (or category, tag, ...) Date range Hydrologic Unit (8-digit HUC) Geographic bounding box (with map input)

Multiple output formats Paged web table with links to station details Spreadsheet (CSV text) Google Earth KML with informational balloons, time-span tags XML text (web service)

Station Search

Search Results

Search Results

Station Detail View

Google Earth KML

XML (Web Service Output)

Ongoing Work Continue adding programs, stations, parameters

US EPA STORET/WQX (?) NADP, NPS, USFS

Enhance web portal appearance and usability Organization and program metadata search/retrieval Geographic placename search (state, county, location) Additional parameter search options (USGS NWIS code, ...)

Coordinate with other regional efforts

SE Coastal Ocean Observing Regional Association (SECOORA) NPS OpenParks GRID

Conclusions

Extensive effort is required to acquire comparable information about water quality monitoring programs

Providing unified access to monitoring metadata on a web portal, with links to program contacts and data downloads, is a valuable service for researchers, resource managers

Questions?