A Coastal Water Quality Metadata Database for the Southeast U.S.A.

1. 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

2. 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)

3. 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

4. Monitoring Station Search Examples

5. 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)

6. Geographic Coverage

7. 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)

8. Data Model • 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

9. 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)

10. 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

11. Targeted Organizations

12. Data Entry Forms – MS Access

13. Data Entry Forms – Web (ASP)

14. Database Statistics (Apr 2011) • Organizations: 22 • Monitoring Programs: 42 • Monitoring Locations: 44,057 • Parameters Measured: 2266 • Location-Parameter Records: 764,887

15. 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)

16. Station Search

17. Search Results

18. Search Results

19. Station Detail View

20. Google Earth KML

21. XML (Web Service Output)

22. 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

23. 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

24. Questions?