Overview

The Air Quality System contains data from a variety of organizations, including state and local agencies, tribes, and federal organizations. It includes descriptions of air monitoring sites and monitoring equipment, measured concentrations of air pollutants and related parameters, and calculated summary and statistical information. This is called air quality (AQ) data throughout the rest of this volume.

Reporting agencies submit AQ data as formatted transactions by first uploading the transaction file(s) to EPA’s Central Data Exchange (CDX). Files loaded to CDX are then available to AQS for the batch load process. (Note that online, interactive data entry via AQS application is also available.) The user then employs AQS software to process the data through five steps of “batch loading”, starting with file loading to AQS and ending with posting the data to the database. A Web browser is used to access both CDX and AQS applications.

AQS Transactions

Many types of transactions are used to provide data and control information for updating the AQS database. Detailed instructions for assembling individual transactions are presented in the remainder of this document.

The remainder of this document contains sections related to the different types of data AQS collects, which are divided into “Transaction Types”.

  • Common Fields that appear in all transactions

  • Site Transactions (AA, AB, AC, AD, AE)

  • Monitor Transactions (MA, MB, MC, MD, ME, MF, MG, MH, MI, MJ, MM, MN, MO, MP, MX)

  • Raw Data Transactions (RB, RC, RD)

  • Quality Assurance Transactions (QA)

For quick reference, the AQS Transaction Formats document is a summary of the pipe (“|”) delimited input formats and is available on the AQS website. Code tables of allowable values for all fields are also available from the web site.

The remainder of the format instructions in this document refer to generating AQS transaction records in a text file. Instructions for generating XML instance documents containing this data are available from the AQS flow section of the Exchange Network website: http://www.exchangenetwork.net/. All of data assembly, processing, and “coding” rules are the same, only the final format of the submitted document differ between the transaction format and the XML format.

General Data Assembly Instructions

Here is an example of an AQS “monitor basic” transaction:

MA|I|01|005|0002|88502|5||||||2||||||||||||||||20060312|

And a “raw data” transaction:

RD|I|16|001|0010|44201|1|1|007|087|20100902|00:00|0.02|||||||||||||||

AQS transaction formats make use of field delimiters, rather than column position, to determine which field is to be processed. The delimiter character is the vertical bar “|” also known as the “pipe” character. Transactions always start with the two-character transaction type, followed by the delimiter character, followed by the one-character Action Indicator (I, U, or D), followed by the delimiter character, followed by the next field (usually state FIPS code), etc. To submit a null (no value) in a non-key field, then the delimiters are included directly following the last value supplied (with no spaces). Missing delimiters at the end of the transaction record are treated as null fields, i.e. the missing delimiters are assumed to be there by the batch update software.

The transactions also use “codes” to increase the density of data in a submission. For example, instead of a state name, we use a state FIPS code which is much shorter yet captures the same information.

Four general types of values are used to submit air quality transactions:

  • Codes,

  • Dates and Times,

  • Numbers, and

  • Text.

For every transaction in this document, a table showing the fields in the transaction, a short description of each, and other important information is shown. For a more detailed description of a field and all the business rules, click on the field name, which is linked to the full description of that field.

Codes

Codes are numerical values used to replace longer text values. Codes must be entered on transactions exactly as they are stored in the AQS database. For example, a County Code is three digits, and you must code all three digits of the code, including any leading zeros. The instructions for fields that take a code value include the term “code” in the name. Where codes are required, a link to a website with valid AQS codes is provided. Code values are also available as drop-down lists at each field where they are used in the AQS software. Example, the state code for Alabama is 01.

Dates

Dates are entered in YYYYMMDD format. YYYY is year, MM is month number, DD is day number. Fields that take a date value include the term “date” in the name. If only a “year” is in the name, provide the 4 digit year (YYYY format). For example, February 11th, 2021 would be entered as: 20210211.

Times

Time is entered as HH:MM where HH is hour and MM is minute. Hours are on a 24-hour clock (so values from 00 through 23 are allowed). All times are in Local Standard Time (no daylight adjustment should be made). Hour values less than 10 need a leading zero. The colon character (“:”) is normally required. For example, 1:00 pm is 13:00.

Numeric Values

The instructions for fields that take a numeric value will include either “integer” for whole numbers or “decimal” for real numbers. (And something like “must be a number” in the business rules section of the longer description.)

Text Values

Some transactions have free-form text fields, or text fields which allow one of several values (“list of values”). These fields may contain any alphabetic character, punctuation, or number. Most, but not all, special characters are allowed so only use if you must. The length of allowable text should be specified for each field. Many text fields are validated against reference tables in AQS. For these fields, the values entered must match exactly one of the values on the appropriate reference list.

Mandatory Fields

Certain fields are mandatory (required), and they must have a value on the transaction. Some fields are always mandatory; other fields are mandatory only under certain conditions. For example, Action Indicator is always mandatory; it must be coded on every transaction. The coding instructions in subsequent chapters identify fields as Mandatory or Optional, and specify the conditions when a value is mandatory on the transaction. Also, the detailed descriptions contain notes showing various dependencies and required fields in the business rules section.

Key Fields

Certain mandatory fields are also labelled as “key” fields. These fields are used to uniquely identify specific data in the database. For example, for a Monitor Sampling Period, the Date Sampling Began is a key field. It, along with the other key fields {state, county, site, parameter, and POC} uniquely identify one row of data in the database. Key fields are always mandatory for all Action Indicators, and key fields cannot be modified by an update transaction.

A key field is used to identify a record. For inserting data, it is used to determine if data with the same keys already exists in AQS before inserting (and the user will get an error if so). For updating data, it is used to find the data that will be updated. For deleting a record, only the key fields need to be provided.

Deletions

The deletion of non-key field values (without deleting the entire record) is not supported by AQS transactions. The AQS user interface may be used to delete a single field.

Transaction Dependencies and Data Completeness

More than one transaction type may be required to insert (add) new data to the database. The data and activities required will vary according to the type of data being inserted. Sometimes the value set in one field may require related data in additional fields or require an additional transaction.

There are various checks performed within AQS to ensure that all required associated data is present before the information can be fully loaded (become accessible to those outside the group responsible for loading it). Once data is complete, a system controlled flag on each record called the Status Indicator is set to “P” to indicate that the information is at “production” status.

Examples of transaction dependencies and data completeness requirements:

  • In order for a site to be at “P” status it must have:

  • values in all required fields;

  • a Supporting Agency;

  • a monitor associated with it that is at “P” status.

  • A monitor at “P” status must have:

  • at least one sampling start period;

  • at least one objective;

  • monitoring type assignments to cover all sampling periods;

  • PQAO role assignments to cover all sampling periods since 2007-01-01;

  • for criteria pollutant monitors, reporting organization role assignments to cover all portions of sample periods up to 2006-12-31;

  • for PM10 and PM2.5 monitors, required collection frequency assignments to cover all portions of sample periods since 1987-07-01;

  • for monitors whose Unrestricted Air Flow Indicator value is ‘N’ or ‘W’, at least one probe obstruction;

  • for PM2.5, a primary monitor period defined for the site.

  • Data values at “P” status must have been statistically evaluated and reviewed (passed the AQS “Stat CR” process).

Validation

Many fields in the database have a limited set of accepted values. Limitations are imposed based on a number of factors such as data type, size, range or value set. These limitations are specified in this document for each field as described above. In cases that the validation is defined by a table of acceptable values a link is provided to a website with the current list. You can also find it by clicking on the drop down menu associated with that field in the Maintain or Correct forms in the AQS application.

Status indicators are used in many of the tables used for data validation. The status indicator is often used to distinguish between past and present accepted values. Values that are actively accepted for inputting data are set to “P” while values that are inactive (obsolete or deprecated) are set to “I” to allow for old data to be displayed or interpreted but rejected with new data. A common “I” status value is “UNKNOWN”. Reports of legacy data use this value when production status records are encountered with data missing from fields that currently require data. While “UNKNOWN” can be an AQS output value it is not accepted as valid for input.

You may encounter situations where the data you wish to load does not pass the AQS validation procedures. Usually, this is because of errors in the formation of the transaction; possibly typographical errors or missing required data, etc. If you find that the edits imposed by AQS are preventing you from submitting valid data, you should contact your regional contact or open a ticket with AQS.

Action Indicators - Inserting, Updating, and Deleting Data

The Action Indicator in each transaction is the instruction you are giving to AQS for how to process this data. There are three valid values: I, U, and D.

I = Insert

U = Update

D = Delete

For inserting data, the transaction must contain enough information for AQS to ensure this will not duplicate data in AQS. The system also checks to make sure all required fields are submitted. The table with the transaction format, in the “Required” field will say either “Always” or “Insert” for fields that are required when inserting data.

For updating and deteling data, the transaction must contain enough information for AQS to find the exact, unique, matching data in AQS. AQS makes use of “key fields” to determine this. Key fields are those that uniquely identify data. There is a column in the transaction format table indicatin if a field is key. For update and delete transactions, key fields must be populated.

For update transactions, all key fields must be included (to match the existing data record) and any other fields included will be updated in AQS. Any field left null will be ignored. That is, they will not be deleted. To delete a field, delete the record and insert a new one rather than using the update transaction.

Note

AQS is a “transaction processing system”, like a bank account. It starts out empty and only contains what you put into it (minus what you take out of it). This also means that you must know what data is already in AQS to perform updates and deletes (like knowing what you checking account balance is before writing a check). That is, you cannot update or delete a record that does not already exist in AQS. Likewise, you cannot insert a record that already exists. If you attempt these, you will get an error message.

Example Transactions and Assembling Files

Thie section contains samples of transactions to give context for the rest of the document.

The detailed steps for loading data into AQS can be found in other documentation on the AQS website. This section only discusses how that may impact how you decide to assemble transaction files.

Any kind of data can be included in the same transaction file, however there are some ways you can reduce the chance of getting errors on load.

A file of transactions can contain any grouping of transactions described in this document. However, there are (described in each section) requirements about the order in which transactions must submitted. For example, a site must be created before monitors are added, a monitor must be created before data can be added, etc. If you do put multiple transaction types in a single file, we recommend that you put them in the order they are described in this document. That is, site transactions in order AA, AB, etc. then monitor transactions, then raw data transactions and so on. Within transaction types, we recommend sorting transactions by the key fields indicated on each transaction.

When submitting site and monitor data, it is recommended that you (1) group the data by each site and monitor (e.g., don’t mix data from different monitors in with one another) and (2) order the transactions in alphabetical order; that is, AA, AB, MA, MB, etc. Because creating a monitor in AQS requires several types of data to exist simultaneously, this will reduce the likeklihood of getting errors related to AQS not being able to group data together for processing.

We also recommend putting site and monitor transactions, raw data transactions, and QA transactions in separate files. This is not required, but since these data take different paths with different possible statuses through the batch load process, it will reduce the likelihood of one error early in a file cascading into many others. For example, if a single monitor transaction is invalid and prevents the monitor from being set up in AQS, all of the raw data transactions in the same file will raise errors and not be loaded since the monitor does not exist. Thus one “real” error in the file can cause thousands of load errors. When defining a monitor and submitting raw or QA data for it in the same file, put the monitor transactions ahead of the other transactions.

You can also include comment lines in a transaction file. A comment line will be ignored by AQS and are usually included to help human readers of the file understand the contents. Any line beginning with the hash character and a space (’# ‘) is ignored by AQS as a comment line. (If you add just the hash without the space, you will get an invalide transaction error.)

Transaction file with site data
AA|I|16|001|0010|43.600699|-116.347853||||028|WGS84|1|20|826|MOUNTAIN|0511|520 S. EAGLE ROAD,  MERIDIAN||1080|064|COMMERCIAL|URBAN AND CENTER CITY|20060511||83642|1|1101||010321|||Meridian - St. Luke's||||||||||008|NAVD88|.25
AB|I|16|001|0010|1|S. WORTH|LOCAL ST OR HY|||E|
AB|I|16|001|0010|2|E. PROMENADE|LOCAL ST OR HY|||S|
AB|I|16|001|0010|3|S. TOUCHMARK|LOCAL ST OR HY|||N|
AD|I|16|001|0010|MET-ONE-A|0511|Met One Instruments|SASS||4|20060701|
AD|I|16|001|0010|URG-A|0511|URG|3000N||1|20070503|
AE|I|16|001|0010|MET-ONE-A|1|TEFLON|6.7|118|20060701|
AE|I|16|001|0010|MET-ONE-A|2|NYLON|6.7|118|20060701|
AE|I|16|001|0010|MET-ONE-A|3|QUARTZ|6.7|118|20060701|
AE|I|16|001|0010|URG-A|1|QUARTZ|22|118|20070503|
Transaction file with monitor data and some comments
MA|I|16|001|0010|44201|1|01||NEIGHBORHOOD||GROUND LEVEL SUPPORT|2.44||||Y||||||||||||
# Sample period begin date
MB|I|16|001|0010|44201|1|20130426|
MC|I|16|001|0010|44201|1|SLAMS|20130426|
# Agency role assignments
MD|I|16|001|0010|44201|1|ANALYZING|0511|20130426|
MD|I|16|001|0010|44201|1|COLLECTING|0511|20130426|
MD|I|16|001|0010|44201|1|REPORTING|0511|20130426|
MD|I|16|001|0010|44201|1|PQAO|0511|20130426|
MD|I|16|001|0010|44201|1|CERTIFYING|0511|20130426|
ME|I|16|001|0010|44201|1|POPULATION EXPOSURE|1080||||
MF|I|16|001|0010|44201|1|1|20130426||||||||||||||0
MM|I|16|001|0010|44201|1|087|20070501|
MN|I|16|001|0010|44201|1|NCORE|20130426|
MO|I|16|001|0010|44201|1|20130426||
One day of hourly RD (raw data) transactions
RD|I|16|001|0010|44201|1|1|007|087|20100902|00:00|0.02|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|01:00|0.019|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|02:00|0.013|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|03:00|0.007|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|04:00|0.002|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|05:00|0.002|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|06:00|0.002|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|07:00|0.009|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|08:00|0.021|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|09:00|0.025|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|10:00|0.031|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|11:00|0.036|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|12:00|0.036|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|13:00|0.04|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|14:00|0.044|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|15:00|0.046|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|16:00|0.043|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|17:00|0.041|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|18:00|0.031|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|19:00|0.011|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|20:00||AQ||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|21:00||AQ||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|22:00|0.005|||||||||||||||
RD|I|16|001|0010|44201|1|1|007|087|20100902|23:00|0.019|||||||||||||||
A quarter’s worth of blanks transactions
RB|I|16|001|0010|88101|1|7|077|118|FIELD|20100314|00:00|1|||||||||||||
RB|I|16|001|0010|88101|1|7|077|118|FIELD|20100401|00:00|2|||||||||||||
RB|I|16|001|0010|88101|1|7|077|118|FIELD|20100419|00:00|-1|||||||||||||
RB|I|16|001|0010|88101|1|7|077|118|FIELD|20100507|00:00|4|||||||||||||
RB|I|16|001|0010|88101|1|7|077|118|FIELD|20100525|00:00|-1|||||||||||||