A Real-time Data Management System for Aerometric Data Collection, Automated Quality Control, Visualization, and Reporti

1. A Real-time Data Management System for Aerometric Data Collection, Automated Quality Control, Visualization, and Reporting • Prepared by: • Mark Stoelting • Bay Area Air Quality Management District • San Francisco, CA • and • Chris Ovard • Sonoma Technology, Inc. • Petaluma, CA • Presented to: • U.S. EPA 2005 National Air Quality Conference • San Francisco, CA • February 13-16, 2005 STI-2648

2. Overview • Current data collection system • New data system requirements • Status of new system • Technical information • Future direction

3. Current Data Collection System • Analog-only commercial dataloggers • Telephone polling • DOS-based data management system • Only processes hourly air quality data • Limited data Quality Check (QC) tools • No graphical editing tools • Old data reporting formats (SAROAD) • Paper chain-of-custody

4. New Data AcquisitionSystem (DAS) Requirements • Computer-based instruments • Instrument diagnostic data • Multiple parameters • Real time digital data stream • Internet use for continuous site access for data collection and management • One minute temporal resolution

5. New Data ManagementSystem (DMS) Requirements • Centralized collection and QC for all real-time data • Automated export to AIRNow and BAAQMD web server • Management of all ambient data (air quality, met, filter, toxics, etc.) • Flexible naming convention, expandable, scalable

6. New DMS Requirements (cont.) • Graphical viewing and editing tools • Final data export to • EPA’s Air Quality System (AQS) in AQS delimited or XML format • National Environmental Information Exchange Network (NEIEN) • Electronic chain-of-custody

7. New BAAQMD Data Collection System Air Monitoring Stations Any RemoteLocation BAAQMD District Office Data Management System (DMS) SQL database Viewing/editing tools Data Acquisition System (DAS) with Internet access Remote user with Internet access DAS “pushes” data to DMS The Internet Local user with LAN access

8. Data Acquisition System • PC-based DAS is inexpensive, easy to use • Data parsing commands pick data out of digital records • Supports large number of parameters • Script command language to control automatic zero/span/precision checks, and diagnostic work • Uploads (pushes) real-time data through the Internet to the DMS • DAS development for BAAQMD use is largely complete

10. New BAAQMD Data Collection System Air Monitoring Stations Any RemoteLocation BAAQMD District Office Data Management System (DMS) SQL database Viewing/editing tools Data Acquisition System (DAS) with Internet access Remote user with Internet access DAS “pushes” data to DMS The Internet Local user with LAN access

11. Real-time DMS Features • Imports data from DAS into Microsoft SQL Server database • Performs automated QC on incoming data to identify erroneous and suspect data • Min/max, zero drift, sticking, rate-of-change, compare • QC conditions can also activate a process or alarm, or send email messages • Database records QC status changes • Sends hourly data to AIRNow and BAAQMD web server

12. Ambient Air Quality Data Raw data Self checks Instrument checks AIRNow Site checks Network (off-site) checks BAAQMD Web Server Automated QA Checks

13. DMS Auto-QC Log

14. DMS Features • Graphical editing/QC tools for staff data review • Data access security • Database records all manual QC status changes and adjustments to data values • Generates final data for submission to AQS

15. DMS Edit/QC Screen

16. Other DMS Features • Maintains instrument database • Maintains record of instrument performance (zero/span/precision data, etc.) • Supports electronic instrument and site logs • Current development effort concentrating on processing hourly data

17. DMS Origins • Combination of features and requirements • CRPAQS and CCOS field studies • AIRNow • Air Quality System • EPA regulations for data quality, review, and documentation • Most features have successful track record in other database applications

18. DMS Implementation • Windows 2000/2003 Server • .NET 1.1 Architecture • VB.NET used to develop graphical editing and QC tool • LINUX portable using Mono framework (www.mono-project.com) • Microsoft SQL Server database • Possible future port to Oracle and PostgreSQL

19. DMS Ingest and Auto-QC

20. DMS Architecture • File Ingest and Auto-QC • Developed using T/SQL as stored procedures • Deployed as SQL Server Job • Performance • 500,000+ records ingested and auto-QC per day on modest (single-processor) server • 500 lines of code

21. DMS Conceptual Model • Simple database design • Scalable • Efficient processing

22. Initial DMS Development • $100,000 supplemental EPA 103 Grant funding from EPA Region 9 to support real-time data collection • System design (35%) • Requirements and functional specifications • System implementation (45%) • Initial testing and validation (10%) • Documentation (10%)

23. Current DMS Development • $279,000 EPA Grant from the National Environmental Information Exchange Network (NEIEN) • System testing and deployment for hourly data • Parallel deployment by Ventura Co. APCD • 1-minute data management and hourly averaging • Enhanced view/edit features • Support for toxics, PM filter data, monitoring source emissions • Expanded reporting capabilities (XML formats, NEIEN)

24. Deployment Timeline • Current: limited PC-DAS deployment in parallel with existing data collection system • 2005: DAS and DMS operational for hourly data; 1-min data collection and processing • 2006: support for other data (filter, toxics); expanded reporting

25. Summary • Simple to operate and maintain • Flexible, expandable and scalable design • Fast, secure, and efficient operation • Comprehensive quality control tools • Applicable to a wide variety of data

26. Contact Information • Mark Stoelting • Bay Area Air Quality Management District • mstoelting@baaqmd.gov • (415) 749-4619 • Chris Ovard • Sonoma Technology, Inc. • covard@sonomatech.com • (707) 665-9900