Transcript VIEWS_NADP_Poster - Colorado State University

The Visibility Information Exchange Web System (VIEWS):
An Approach to Air Quality Data Management and Presentation
Data Acquisition System:
• Accepts submission of new data in a variety of formats
• Can automatically extract data from known online sources
• Uses database replication where possible
• Initially imports data “as-is” into the source database
Introduction
The Visibility Information Exchange Web System
(VIEWS) is a database and website system that
provides access to air quality data, data summaries,
and research tools in support of the Regional Haze
Rule enacted by the U.S. Environmental Protection
Agency (EPA) to reduce regional haze in national
parks and wilderness areas.
NADP NTN Data Import
Validation
Metadata Import System
Metadata Import System:
• Facilitates the entry of new metadata
• Validates new metadata entries
• Detects overlap with existing metadata
Data
Acquisition
System
Data Import System
Data Import System:
• Extracts data from the source database
• Scrubs data and performs conversions
• Maps source metadata to integrated metadata
• Transforms the data into an integrated schema
• Verifies and validates imported data
• Loads data into the back-end OLTP system
AIRDATA_OLTP
Validation
AIRDATA_OLTP
Backup
and
Restore
System
Metadata Browser
•
Query Wizard
•
Third Party Tools
•
Webcams
•
Resource Catalogs
•
Annual Summary
NTN Methods
Source
Result Table
Transformation
Validation
Mapping
Validation
- Duplication
- DB Integrity
OLTP
- Checksums
- Record Counts
OLTP
A Basic Comparison between NTN and IMPROVE:
Wet Deposition (NADP/NTN)
 Aqueous rain water concentration (mg/L).
 Precipitation weighted mean concentration (mg/L).
 Deposition (kg/ha) - the product of aqueous SO42- concentration in collected rain water and total precipitation over a
given time period (e.g. season, year).
AIRDATA_OLTP
ASCII Data File Collection:
A collection of data and
metadata in ASCII text file
format.
Metadata Browser:
For exploring the attributes,
history, and organization of
monitoring networks and sites.
Database Query Wizard:
For building, formatting, and
submitting queries to the
VIEWS air quality database.
Charts
HTML
Text
Third Party Tools:
A collection of relevant air quality
research tools provided by various
organizations and institutions.
OLTP
NTN Parameters
Notes and Issues Regarding Comparisons Between Aerosol and Deposition Data:
Replication
And
Archival
System
Some VIEWS Tools:
•
NTN Sites
AIRDATA_SOURCE
Replication and Archival System:
• Vertically partitions the warehouse by time period
• Takes a full “snapshot” of the data warehouse at
regular intervals
• Creates a historical audit trail for verifying archive
integrity
ASCII Files
Entry
Validation
Backup and Restore System:
• Automatically and periodically backs-up critical VIEWS
databases
• Restores database backups on demand
•
Extraction
Data Warehouse Generation System
Data Warehouse:
• Functions a the “front-end” database
• Uses a de-normalized star schema
• Used for querying and archiving data
• Automatically generated from OLTP
Site Browser
SOP
AIRDATA_DSS
Browsing NTN Metadata:
Data Warehouse Generation System:
• Extracts data from the OLTP
• De-normalizes and transforms data
• Archives snapshots of existing data
• Loads data into the data warehouse
• Builds indexes on relevant tables
•
SQL Sever DTS
SOURCE DB
Metadata Import:
• Used SQL scripts to extract unique
metadata from source data set
• Created new records for NTN metadata
using the extracted source metadata
Data Import:
• Used stored procedures and VB routines to
transform the data from its source format
• Applied DB integrity constraints to verify the
transformations
• Mapped source codes to relational primary
keys (IDs)
• Loaded the results into a new table and
validated the data using a series of row and
column checksums and record counts
AIRDATA_DSS
 Web Address: http://vista.cira.colostate.edu/views
 Sponsor: Five EPA Regional Planning Organizations
(RPOs)
 Guiding Body: VIEWS Steering Committee
 Location: Cooperative Institute for Research in the
Atmosphere (CIRA), Colorado State University, Fort
Collins, CO
 Staff: Scientists, researchers, and IT professionals
 Affiliations: Interagency Monitoring of Protected
Visual Environments (IMPROVE)
Data
NTN Flags
OLTP:
• Functions as the “back-end” database
• Fully relational and in third normal form
• Used for data import, validation, and management
Site Browser:
For exploring detailed
monitoring site metadata,
history, photographs.
Sites
Entry Forms
AIRDATA_SOURCE
In a broader sense, VIEWS facilitates the research and
understanding of air quality issues in general. To fulfill
these goals, the VIEWS team developed a generalized
relational data model for air quality data and
implemented a database system, website, and
supporting software infrastructure for importing,
managing, and presenting air quality data from a wide
variety of sources. A primary focus of these efforts was to
design a system capable of integrating diverse data sets
into a common schematic and semantic framework in
order to more easily manage and compare the
constituent data. The data model was designed to
support the mapping of source metadata onto a common
collection of integrated metadata where possible, and an
extensive software system was developed to import and
transform the source data into a common relational
schema while performing the associated metadata
mappings. A carefully designed system of relational
constraints and database rules was developed to ensure
the accuracy, integrity, and relational consistency of all
imported data. To make the data readily available for
browsing, download, and analysis, the VIEWS team
implemented a suite of online tools and resources in the
form of the VIEWS website. Scientists, researchers, and
policy makers from a broad range of organizations now
use the website as a primary source of air quality data
and resources. The VIEWS team intends to continually
import new air quality data and improve its suite of tools
for accessing and viewing this data.
Data Acquisition:
• DTS Wizard used to import NTN Sites table
• DTS Wizard used to import NTN Data table
• NTN SOP information entered manually
Class I Area Webcams:
A growing collection of links to Class I Area
webcams that provide a visual method for
assessing visibility and general air quality in
national parks and wilderness areas.
Air Quality Catalogs:
A compilation of carefully selected
and described air-quality-related
resources.
IMPROVE Annual Summary:
Data summaries based upon Regional
Haze Rule-adjusted IMPROVE data and
intended to guide the RPOs in assessing
their compliance progress.
Dry Deposition (CASTNet)
 Dry deposition (kg/ha) for atmospheric particles and gas phase species (e.g. SO2, HNO3, NH3) - the product of the
species’ deposition velocity and the ambient air concentration integrated over time (e.g. season, year).
Air Concentrations (IMPROVE, CASTNet, STN, other speciated networks)
 Aerosol and gas phase air concentrations (ug/m3).
Inter-comparisons (Sulfur)
 Compare raw concentration data (ug/m3 to mg/L)
 Compare slopes in respective trends, for example S (SO2 plus SO42-) air concentrations to S deposition expressed as
%/season, %/yr.
Composition
Contour
Maps
Other
Analyses
Conclusion
With deposition data now incorporated into a common schematic and semantic format
with aerosol data in the VIEWS database system, the issues involved in performing
meaningful comparisons between the two types of data can be more easily identified,
explored, and resolved.