* — \
*1 PROt^
Instructions for Submitting ONROAD Inputs
for the 2014 NEI
-------
-------
EPA-454/B-20-029
May 2015
Instructions for Submitting ONROAD Inputs for the 2014 NEI
U.S. Environmental Protection Agency
Office of Air Quality Planning and Standards
Air Quality Assessment Division
Research Triangle Park, NC
-------
May 19, 2015
Instructions for Submitting ONROAD Inputs for the 2014 NEI
The U.S. Environmental Protection Agency (EPA)'s Air Emissions Reporting Rule
(http://www.epa.gov/ttn/chief/aerr/) requires state and local agencies who submit to the National
Emissions Inventory (NEI) to submit model inputs for mobile sources, rather than emissions.
Emissions estimates may be submitted in addition, and emissions estimates will still be accepted
from tribes without inputs. For the 2014 NEI, EPA plans to use the most current version of the
Motor Vehicle Emission Simulator (MOVES) model to estimate both onroad and nonroad
emissions. Because the MOVES version that runs nonroad is not expected to be available, or
widely used, in time for most agencies to be familiar with its use for nonroad, we will be
accepting National Mobile Inventory Model (NMIM) National County Database (NCD) inputs
for nonroad and will be converting them for use in MOVES for the 2014 NEI estimation.
Agencies that want to submit MOVES nonroad inputs may do so. For information on submitting
NONROAD inputs as NCDs or CDBs, see the companion document "Instructions for Submitting
NONROAD Inputs to the 2014 NEI." If you do not submit onroad or nonroad input data, EPA
will generate emission estimates using EPA county specific estimates that may be based on
national defaults. This document addresses submitting onroad MOVES CDB inputs only.
Since the MOVES inputs are county-based, tribal agencies should run MOVES and submit
emissions. However, tribal agencies could use the input information from adjacent counties to
inform them on local inputs that may be suitable for their tribal area MOVES runs.
MOVES inputs must be submitted to EIS as a County Database (CDB) that is a set of MySQL
tables specifically formatted to store local inputs for MOVES. Prior to submitting MOVES
inputs, agencies will be able to download EPA's default CDBs containing MOVES inputs by
state from EIS. This default set will be in the proper format and, as detailed below, contains a
mix of state-supplied data from the 2011 NEI, MOVES national defaults, and placeholders
where 2014 data has not yet been collected or where EPA may use additional information
The emissions in the NEI are developed for all months of the year and all counties in every state,
plus District of Columbia, Puerto Rico, and US Virgin Islands. This means that EPA will need
to have county level information for every county for each month of the year. In order for
MOVES to utilize this county specific data, MOVES will be run at the County Scale. This
requires a separate county database for each county that contains the data specific to that county.
On a county by county basis, agencies can change the CDBs as needed to reflect their own input
data, or they may choose to use the provided EPA defaults. Agencies must check their CDBs
prior to submittal using the provided QA Tool that will generate a QA Report to include with
submittals. Agencies should submit their CDB submittal package to the EIS QA environment to
confirm there are no critical errors prior to submitting to the production environment. Agencies
1
-------
that want to accept EPA's defaults and submit nothing may do so as with any other category via
a 'support request' message through the EIS gateway.
It is important that each CDB submittal is a total replacement to any CDBs previously submitted
by that agency. Thus, if a state submits every county in one submittal, then submits only one
county in a subsequent submittal, only the one county will be present in EIS.
The following sections describe how to create/revise onroad CDBs, QA check them, and submit
to EIS.
Supporting tools and files referenced here can be downloaded from the 2014 webpage:
http://www.epa.gov/ttn/chief/net/2014inventory.html in the section "Onroad."
road County Database Content
States are asked to supply the CDB tables listed in Table 1 below. The table indicates the
contents of the CDBs and what data EPA uses as defaults. It is expected that states will have
more accurate information in most cases.
The EPA document, "Using MOVES to Prepare Emission Inventories in State Implementation
Plans and Transportation Conformity: Technical Guidance" (EPA-420-B-15-007, January 2015;
available at http://www.epa.gov/otaq/models/moves/index.htm), describes what data states are
being asked to provide and suggestions on how to obtain the needed information.
The CDB tables are intended to contain the complete set of information needed to run MOVES
for all of the vehicles in a county for all months of a single calendar year (2014) using county
specific information.
EPA has not yet created default CDBs for counties for calendar year 2014. The default CDBs
that EPA creates will be based on information found in the CDBs used for the 2011 NEI v2.
These 2011 CDBs are provided to states in the EIS and may be used by states as a starting point
for generating CDBs to provide to EPA or states may provide CDBs created independently. In
either case, the CDBs provided for the 2014 NEI should be complete and ready to run for the
calendar year 2014.
If you are planning to use the CDBs provided by EPA as a starting point, you will want to
carefully read Section 1.2.1.5.1 of this document to see how to update the year information and
other 2011 data to reflect the 2014 calendar year.
The EPA-provided CDBs from the 2011 NEI v2 may contain tables not shown in Table 1. These
tables are generated by scripts during the construction of the CDBs and do not affect the running
of MOVES or the results and can be removed from the CDBs if desired.
2
-------
Table 1: CDB
Tables and
ContentscDB
Table
Description of Content
EPA-Default CDB Table Content
auditlog**
Information about the
creation of the database
Carried forward from the creation of the tables
for 2011 by EPA. These records reflect the
earlier importing of CDBs and not necessarily
the current status of the data.
avft**
Diesel sales fractions
Normally this table is empty, but it may
contain state-provided data from the 2011 NEI.
avgspeeddi stributi on
Average speed distributions
State-provided data from the 2011 NEI or
national average EPA estimates.
county
Description of the county
EPA provided data.
dayvmtfraction
VMT distribution across the
type of day
State-provided data from the 2011 NEI or EPA
estimates.
fuelformulation
Fuel properties
Based on EPA estimates for each county from
calendar year 2011. EPA will provide the
2014 values, but states must provide
placeholder values to pass the QA script.
Fuel supply
Fuel differences by month of
the year
Based on EPA estimates for each county from
calendar year 2011. EPA will provide the
2014 values, but states must provide
placeholder values to pass the QA script.
fuelsupplyyear
Year for the fuel properties
Must be changed to 2011
fuelusagefraction
Fuel use by flexi-fuel
vehicles.
Based on EPA estimates for 2011.
Hotellingactivitydist
ribution**
Distribution of hotelling
hours using extended idle or
auxiliary power units.
Based on EPA estimates for 2011.
hotellinghours
Hours of hotelling by long
haul combination trucks.
County-specific hours for 2011.
hourvmtfraction
VMT distribution across the
hours of the day
State-provided data from the 2011 NEI or
national average EPA estimates.
hpmsvtypeyear
Total annual VMT by HPMS
vehicle type
VMT data values from the 2011 NEI. States
may provide VMT estimates by MOVES
source type in SMOKE format directly for use
in the 2014 NEI.
imcoverage
Description of the Inspection
and Maintenance program
I/M program description carried forward from
the 2011 data. The model year ranges will
need to be adjusted to reflect the effect of the
grace period on model year ranges for calendar
year 2014.
Importstartsopmode
distribution**
Distribution of engine starts
by engine soak time.
Empty (using national average EPA estimates).
3
-------
Table 1: CDB
Tables and
ContentscDB
Table
Description of Content
EPA-Default CDB Table Content
monthvmtfraction
VMT distribution across the
months of the year
State-provided data from the 2011 NEI or
national average EPA estimates.
onroadretrofit**
Emission adjustments to
reflect local retrofit
programs.
Empty (no retrofits assumed).
roadtypedi stributi on
VMT distribution across the
road types
State-provided data from the 2011 NEI or
national average EPA estimates.
sourcetypeagedi strib
ution
Distribution of vehicle ages
State-provided data from the 2011 NEI or EPA
defaults based on the CRC A-88 project for
calendar year 2011.
sourcetypeyear
Vehicle populations
Vehicle population data values from the 2011
NEI
starts**
Number of engine starts
Empty (using national average EPA estimates).
start shourfracti on* *
Distribution of starts by hour
of the day
Empty (using national average EPA estimates).
startsmonthadjust* *
Variation of the starts per
vehicles by month.
Empty (using national average EPA estimates).
startsperday**
Engine starts per day per
vehicle.
Empty (using national average EPA estimates).
startssourcetypefract
ion**
Allocation of total starts to
source types.
Empty (using national average EPA estimates).
state
Description of the state
EPA-provided data.
year
Year of the database
Set to 2011, but must be changed for calendar
year 2014
zone
Allocations of starts,
extended idle and vehicle
hours parked to the county
EPA-provided data. Allocations must all be
1.0 (100%). This data should not be changed.
zonemonthhour
Temperature and relative
humidity values
Temperature and humidity data are EPA-
provided data for each county from calendar
year 2011.
zoneroadtype
Allocation of road types to
the county
EPA-provided data. Allocations must all be
1.0 (100%). This data should not be changed.
countyyear*
Description of the Stage 2
program
State-provided data from the 2011 NEI or EPA
default values for each county.
emi ssi onrateby age *
Implementation of California
standards
The EmissionRateByAge tables for some
counties have been populated using the
appropriate data described in the guidance for
states adopting California emission standards.
See notes below ***
*Tables that are not created by MOVES County Database Manager (CDM), but are generated as empty tables by the
QA tool that checks CDBs for EIS submittal
4
-------
**Tables that can be empty but must be present in EIS submittal
*** The following states were given early NLEV programs in the EPA defaults :
• Connecticut (9)
• Delaware (10)
• District of Columbia (11)
• Maryland (24)
• New Hampshire (33)
• New Jersey (34)
• Pennsylvania (42)
• Rhode Island (44)
• Vermont (50)
• Virginia (51)
The following states were given California LEV programs beginning on the following dates:
FIPS State ID
State Name
LEV Program Start Year
6
California
1994
9
Connecticut
2008
10
Delaware
2014
23
Maine
2001
24
Maryland
2011
25
Massachusetts
1995
34
New Jersey
2009
36
New York
1996
41
Oregon
2009
42
Pennsylvania
2008
44
Rhode Island
2008
50
Vermont
2000
53
Washington
2009
ounty Database Naming Convention
In order to keep track of the thousands of CDBs used in the NEI calculations, EPA has
established a naming convention for the CDB names that will differentiate between databases
and make automation of running and processing the inputs and outputs from MOVES easier.
The naming convention for each CDB folder has 20 characters. The first 6 characters identify
the county, the next 5 indicate the calendar year of the county database, and the last characters
indicate the date on which the database was created.
The first 6 characters are the letter "c", followed by the 5 digit Federal Information Processing
Standard (FIPS) code for the county, including a leading zero when necessary. The next 5
characters are the letter "y", followed by a 4 digit calendar year. This calendar year indicates the
calendar year of the data contained in the database. A CDB can only contain data from a single
calendar year. The last 8 digits, following and underscore character, are the date on which the
database was created in a YYYYMMDD format.
5
-------
An example of a CDB name is "c26161y2014_20150601" where this CDB names indicates
"c26161" refers to the county FIPS code (in this case Washtenaw County, Michigan). "y2014"
refers to the calendar year for the county database and "20150601" identifies the database
modification date of June 1, 2015, in YYYYMMDD format.
1.2 Steps to Submit CDBs
1. Create CDBs
a. Edit existing CDBs to meet NEI requirements or
b. Create new CDBs from scratch or
c. Revise EPA defaults.
2. Run EPA's QA Tool to create a QA report that confirms no errors exist.
3. Create a Checklist that indicates where changes have been made to the EPA
CDBs
4. Provide documentation for the Agency- supplied inputs.
5. Submit the files to the EIS.
1,2.1 Creating Comity Databases
When submitted CDBs are used as input to MOVES during a run, MOVES will recognize the
tables contained in the folder (based on their name and format) and use the information they
contain instead of EPA default values. In this way, a MOVES run can be made more
representative of the county to be simulated.
There are many ways to create a county database for submission. We will discuss some of the
methods that we expect might be used and provide some suggestions. The methods we will
discuss are:
• Using the County Data Manager
• Using XML scripts
• Using MySQL
We will also discuss methods to automate the creation of multiple CDBs.
If you have existing CDBs that can be used for the NEI with little or no editing, using these as a
starting point would be your best option. Otherwise, EPA suggests starting with the EPA-
provided default databases and editing their contents as the easiest way to quickly produce a set
of CDBs for your state. However, any method that can generate appropriate tables containing
the county specific data in the appropriate format that can be recognized and used by MOVES
are acceptable.
Since the CDBs will be in a MySQL format, it is helpful to have some MySQL database
application skills, including using standard query language (SQL). However, there are tools
available that can help to view, alter and export MySQL tables. The MySQL Query Workbench
is distributed with the MOVES application and provides a graphical user interface (GUI) that
6
-------
allows tables to be viewed, altered and exported without SQL commands. We recommend that
this application be available for use when creating and editing databases.
Since MySQL CDBs are simply folders containing files that make up the tables, users can simply
move and copy the folders and the files they contain as they would any other files on their drives.
This feature provides some additional flexibility in generating new CDBs, since these folders and
files can be manipulated using standard DOS commands in batch files and scripts.
Step by step instructions of how to use MySQL query commands to alter the contents of tables is
beyond the scope of this document. This approach should only be considered by states with
available staff familiar with MySQL query language. The discussion here of creating and editing
CDBs will cover alternatives to using the query language alone.
1.2.1.1 Using Existing County Databases
-u -u '
For the NEI, EPA prefers to receive existing CDBs that states/local agencies have created for
their own use. These may need to be updated for the NEI year (2014) and possibly expanded to
cover all months of the year using some of the editing tools described below. They also need to
be checked for errors with our NEI CDB QA Tool as explained in Section 1.2.2.
Mug EPA Default Databases
: : s
EPA has provided a set of all CDBs for calendar year 2011 containing the values used for the
2011 NEI v2. States can download the 2011 NEI CDBs from EIS and make changes to make
them suitable for 2014. States only need to submit CDBs for counties for which they have made
changes. Counties that are not submitted will use EPA default estimates. However, EPA
encourages agencies to review the information found in all their county databases and make
changes where better county specific information is available.
To retrieve the EPA CDM default tables, log into the EIS Gateway, go to the "View/Add/Edit"
link, choose "CDB Activity Data", "EPA Default", and choose "2014" in the "Inventory Year"
drop down menu. Find your agency's CDB zipped CDB data base to download as indicated by
the figure below.
7
-------
si Driver. EIS. Content Manager Rote. Authenticated Role. Inventory Developer Role
i
"w.-i.. It
View/Add/Edit
» Facility Inventory and Poiru*^
Emissions
» Potential Duplicate Facilities
» Merge Processes
» Nonpoint/ Onroad/ Nonroad
Emissions <
» Event Emissions
» nrrf Artiultu
» CDB Activity Data ^
» inventory selection
» Schedule Augmentation
» Data Tagging
| Reports |
» Request Reports
» Report Downloads
>» Large File Download
» Feedback Reports
» Agency Submission History
Report
» Data Tagging
Administration Report
REFERENCE DATA
» Reporting Code Tables
CDB Data
EPA DEFAULT CDB DATA SETS
Inventory Year
|2014j£)
State
Submitted
File Size (KB)
Alabama
2015-05-05 11:48 AM
106.909
Download Slate CDB Data Set
View Submission History
Alaska
2015-05-05 12:35 PM
39,765
Download State CDB Data Set
View Submission History
Arizona
2015-05-05 11:52 AM
26.789
Download State CDB Data Set
View Submission History
Arkansas
2015-05-05 11:53 All
118,255
Download State CDB Data Set
View Submission History
California
2015-05-05 11:54 AM
103,746
Download State CDB Data Set
View Submission History
Colorado
2015-05-05 11:55 AM
101,228
Download State CDB Data Se!
View Submission History
Connecticut
2015-05-05 11:55 AM
14,202
Download State CDB Data Set
View Submission History
Delaware
2015-05-05 11:58 AM
6,062
Download Slate CD8 Data Set
View Submission History
District of Columbia
2015-05-05 11:58 AM
1.823
Download Slate CDB Data Set
View Submission History
Florida
2015-05-05 12:06 PM
113,231
Download State CDB Data Set
View Submission History
Georgia
2015-05-05 12:08 PM
249,079
Download State CDB Data Sel
View Submission History
Hawa*
2015-05-05 12:08 PM
7.484
Download State COB Data Set
View Submission History
Idaho
2015-05-05 12:08 PM
67,238
Download State CDB Data Set
View Submission History
Illinois
2015-05-05 12:10 PM
179,474
Download State CDB Data Set
View Submission History
Indiana
2015-05-0512:11 PM
158,313
Download State CDB Data Set
View Submission History
Iowa
2015-05-05 12:11 PM
138,854
Download State CDB Oata Set
View Submission History
Kansas
2015-05-05 12:14 PM
159.285
Download State CDB Daia Set
View Submission History
Kentucky
2015-05-05 12:16 PM
197,690
Download Slate CDB Data Sel
View Submission History
Louisiana
2015-05-05 12:16 PM
105,093
Download State CDB Data Set
View Submission History
Maine
2015-05-05 12:19 PM
24,779
Download State CDB Data Set
View Submission History
Maryland
2015-05-05 12:19 PM
46,818
Download State CDB Data Set
View Submission History
The EPA-provided CDBs should be loaded into your MySQL database system by copying the
CDB folders into the MySQLAdata directory.
The EPA CDBs can be edited directly in several ways. First, the MySQL Query Workbench
allows users to change values in database tables, one value at a time. If only a few changes are
needed, this may be the easiest way to make changes. If many changes are needed, the user
should consider other options.
The MOVES County Data Manager (CDM) can be used to export the contents of most of the
tables found in the CDBs into spreadsheets or comma separated value (CVS) text files to be
edited. Once the changes have been made, the CDM can clear the old data and import the new-
values from the files. This process does not involve using any query language code or
knowledge of database commands. However, it requires making the changes one table, one
database at a time. The XML scripts described below can also be used to import data from
spreadsheets or CSV text files containing altered information directly into existing EPA default
CDBs. By including only tables that contain county specific data, you can limit the tables
affected by the XML code to just the tables that will contain the county specific data and keep
the default data in the other tables. These updates can be automated to apply to multiple
databases at once to apply the changes quickly to all of the counties affected.
Of course, the data in any tables can be manipulated directly using MySQL query language.
8
-------
1,2.1.3 Iking ike County Data Manager
The MOVES graphical user interface (GUI) is designed to allow users to create and edit CDBs
without using database language commands (SQL) through the use of "importers" contained in
the CDM GUI panels. Once data has been imported into a county database, the CDM can
generate a XML script that can be used to recreate the database from the input files using the
command line interface option of MOVES. The XML feature is useful if many changes are
being made or are expected to be made to the input files. The process of creating CDBs using
the CDM is described in detail in the MOVES User Guide. Using the CDM to create CDBs will
require that you create a run specification for each county for which you wish to create a county
database. You can download an example run specification file (cl0001y2014.mrs) from the
2014 NEI webpage.
1.2.1.3.1 Tables not Created by the CDM
The CDBs created by the CDM do not contain all of the tables expected by EPA in an EIS
submittal. However, the CDB QA Tool that is required to be run on all CDBs submitted by
states will generate (empty) tables in all tested databases and provide an indication in the report
that the tables were initially missing. This script will not populate the tables with data. This will
need to be done manually if you want these tables to contain data for that county.
• The EmissionRateByAge table is intended to contain information related to the use of
California standards, which is not contained in the MOVES default database and must be
obtained separately. The default CDBs provided by EPA already have data in the
EmissionRateByAge table for all counties in states that we believe are using California
standards. You should not add data to the EmissionRateByAge table if you have not
implemented California standards in your state. You can find out more about how to
model California standards and obtain the emission rate data on the EPA web site at:
http://www.epa.gov/otaq/models/moves/tools.htm
• The CountyYear table includes fields describing a county's Stage II program to reduce
refueling emissions. The default CDBs provided by EPA already have data in the
CountyYear table for all counties that we believe have these programs.
"ng XML Scripts
XML was designed to carry data, not to display data. MOVES has a feature that allows users to
supply specific XML information that can be used by MOVES to construct CDBs from a set of
spreadsheets or CSV text files. This feature can be used to quickly create CDBs without using
the MOVES graphical user interface (GUI) and automate the process to create multiple CDBs
using a single command.
9
-------
The MOVES CDM Tools panel can create XML files that can be used to create CDBs. Since
XML files are simply text files, the XML file can be edited using any text editor (such as
Notepad) to create new XML files that can generate more CDBs. Using naming conventions for
the data files and CDBs, modelers may create a set of XML files that will create all of the CDBs
for a state. These XML files can be run together in a batch mode to create all of the CDBs for a
state using the command line option of MOVES and a DOS batch file. The creation and use of
importer XML files is addressed in the MOVES User Guide in Section 2.3.3.3 Tools
(http://www.epa.gOv/otaq/models/moves/index.htm#downloading).
EPA has created a XML template for creating CDBs for submission to the NEI. This XML
template can be used instead of creating a template XML using the MOVES CDM. A ZIP file
on the 2014 NEI webpage (Generic_County_2014.zip) includes the instructions for using the
XML template and how to edit the XML code for individual counties, using an example set of
spreadsheets. The example also includes an example batch file for creating multiple CDBs.
The XML approach does assume that the states will have all of the information needed for the
CDBs for every county to be processed and that this information can be found in the proper
format in a set of spreadsheets or CSV text files. The automated process of creating the
databases can be repeated whenever the contents of any of the spreadsheets or CSV text files is
updated.
•ng MySQL
All of the tables in the CDBs can be created or edited directly using MySQL commands. Step by
step instructions of how to do this are beyond the scope of this document. However, in some
cases, it may be useful to use MySQL directly, rather than the tools being provided by EPA.
There is no restriction on the use of MySQL or other tools in the creation of the CDBs, as long as
the resulting CDBs are properly formatted and populated for use with MOVES.
1.2.1.5.1 Converting Databases from Other Calendar Years
In some cases, states may have CDBs for other calendar years that would make a good start for
creating CDBs for the 2014 calendar year. As discussed above a database folder can be easily
copied and renamed to reflect a new calendar year. The contents of the tables can then be edited
to change the calendar year to 2014, which would allow the tables to be used with a run
specification that selected the 2014 calendar year. This editing can be done manually using the
MySQL Query Workbench, one entry at a time, but is better done using simple MySQL query
commands as shown below.
There are ten tables normally found in a CDB that contain calendar year information. These
values can be easily changed to a new calendar year using a series of "update" commands.
Below is an example of the set of update commands to change the calendar year of the tables to
2014. The "use" command selects the database to be altered. The "c26161y2014_20150601"
string refers to the database name, following the EPA naming convention for CDBs. The
10
-------
"c26161" refers to the county FIPS code. "y2014" refers to the calendar year for the county
database and " 20150601" as a database created on June 1, 2015, in YYYYMMDD format.
use c2616ly2014_20150601;
update countyyear set yearid=2014;
update hpmsvtypeyear set yearid=2014;
update hotellinghours set yearid=2014;
update imcoverage set yearid=2014;
update sourcetypeagedistribution set yearid=2014;
update sourcetypeyear set yearid=2014;
update starts set yearid=2014;
update startsperday set yearid=2014;
update year set yearid=2014;
update year set fuelyearid=2014;
Section 1.2.1.2 describes how to create and use a MySQL script to execute these commands.
Once the calendar year has been changed, the state or local agency is still left with the task of
updating the other values in the tables to reflect the 2014 calendar year. In particular, the model
year coverage in the IMCoverage table needs to properly reflect the change in the calendar year.
The age distribution values in the SourceTypeAgeDistribution table may also need adjustment to
reflect sales and scrappage. Changing the contents of the tables using the CDM is discussed in
the MOVES User Guide. The databases can be updated for 2014 using the XML importer
discussed above as well.
1.2.1,6 Automating ike creation of multiple databases
The XML importer scripts described in Section 1.2.1.4 above are the easiest way to create or
make changes to multiple existing CDBs without using MySQL commands. The new data for
the counties can reside in MS Excel spreadsheets or CSV text files and be edited in those formats
or output into those formats from other applications. Using batch files and command line
options in MOVES, the XML for each county can be run to create/alter the existing CDBs. A
compressed data file (Generic_County_2014.zip) can be obtained from the 2014 NEI webpage.
which contains example files and instructions (Generic_CDB_Creator_Instructions_2014.docx)
for using XML files in batch files for creating or editing multiple databases.
11
-------
Scripting tools (Perl, Python, etc.) can also be used to create scripts that will process multiple
databases using MySQL commands to make the desired changes. You will need staff who are
familiar with these tools. Detailed instructions for creating these tools is beyond the scope of
this document, however, you may contact EPA if you intend to use this approach for suggestions.
1.2.2 Use the' r, " -1 to Cr ' - • port to include with your submittal
From the 2014 NEI webpage. download and unzip "MOVES_Input_QA_Tools_vl.zip" which
contains the QA Tool (MOVES_Input_QA_Tool_2014_vl.sql ) and an example script
(Example_Run_QA_Checks_2014_vl.bat) that allows you to automate the QA process for more
than one county (discussed below). MOVES_Input_QA_Tool_2014_vl.sql will create a QA
Report that is required in your MOVES CDB submittal. The QA Report generated by the QA
Tool script verifies all table contents meet range, naming convention, format and other checks.
The report confirms that each CDB contains the appropriate number of tables and that the values
within those tables are valid. It is very important that the QA tool be properly run for each
submitted CDB, because if is not and the CDB contains errors that are not resolved, the data may
not work when we go to generate the NEI and we will need to follow up with you at that time, or
we may not be able to use your submitted data. EIS will check to see that each county listed in
this report has an associated CDB in the submission. Only include counties in the QA report that
you will be submitting. If the counties in the QA report do not match those in the CDB folder, or
if any errors are indicated in the QA report, EIS will indicate a critical error and will not accept
the submission. Format is .txt, but this file can be opened as a table using Microsoft Excel to
make it more readable.
IMPORTANT NOTE: All 32 tables listed in Table 1 above are required, although some may not
be populated. If you create the CDBs using the CDM (from scratch) the CDM will create 30
tables. Your CDB will be missing two tables:
o EmissionRateByAge
o County Year
Running the QA Tool will create these tables in your CDB, but the tables will all have zero rows
(i.e., they will be empty). Leaving these tables empty will not cause the CDBs to fail the script if
it is run again. Thus, if you create the CDBs using the CDM, your initial run of the QA will
indicate errors (missing tables). However, if you run the QA Tool again, the tables have been
created by the previous check and the "missing tables" error will no longer appear in the report.
You can send the second QA report (with no errors) to the EIS.
Also, the QA tool uses tables from the default MOVES database that was distributed with
MOVES2014. If you do not have MOVES2014 installed, you will need to obtain a copy of the
database folder and place it in your MySQL\data directory.
12
-------
Name your QA report with your agency Program System Code (PSC), such as
"PSC_QA_Report.txt". For example, Delaware's state agency PSC is "DEDNR" and their QA
report would be named "DEDNR_QA_Report.txt".
If your state has many counties, you may wish to automate the checking process. Below is an
example of a batch file written to check the three counties for the state of Delaware. The batch
file deletes the old version of the report text file (named with the PSC), clears the MySQL buffer
of previous work and drops the database used to store the aggregated results from the individual
checks. Then MySQL is directed to each county database (i.e., cl0001y2014_20150601) using
the QA Tool script (MOVES_Input_QA_Tool_2014_vl.sql). Note that the directory path must
be included so that the file can be properly located by MySQL. The path you use can be
different than the location (C:\MySQL\) shown in this example. The final line in the batch file
exports the results into the report text file. A directory path can be added to this file name as
well to help locate the file once it has been populated.
del DEDNR_QA_Report.txt
mysql -uXXXXX -pYYYYY -e "flush tables;"
mysql -uXXXXX -pYYYYY -e "drop database if exists all_cdb_checks;"
mysql -uXXXXX -pYYYYY cl0001y2014_20150601 < C:\MySQL\QA_Checks_2014_vl.sql
mysql -uXXXXX -pYYYYY cl0003y2014_20150601 < C:\MySQL\QA_Checks_2014_vl.sql
mysql -uXXXXX -pYYYYY cl0005y2014_20150601 < C:\MySQL\QA_Checks_2014_vl.sql
mysql -uXXXXX -pYYYYY -e "select * from all_cdb_checks.all_county_database_checks;" >
DEDNR_QA_Report.txt"
The XXXXX value is the MySQL user name and the YYYYY value is the MySQL password.
The script (QA_Checks_2014_vl.sql) is located in the C:\MySQL\ directory in this example.
The file generated by this script (DEDNR _QA_Report.txt) is the quality assurance (QA) report
required by the EIS process.
13
-------
Example of QA Report Results
8' « *>• o» ¦
HOME INSERT
a, y
™ Calibri
PAGE LAYOUT FORMULAS
L "| A* A' =
is I Alignment
DATA
w
VIEW
-[ [^Conditional Formatting ~ H° Insert -
» E§? Format as Table * Delete *
Sp Cell Styles * §p Format"
ri Styles Cells
Brzezinski, David *
Z - A
IS- z
~ H
Sort & Find &
Filter- Select ~
Editing
A1
A B
fx countylD
C
D
E
F
G
H I
1
countylD status
tableName
checkNumber
testDescription
testValue
count
dataBaseName
daylD
fuelForn
2
10001 Completed
startsHourFraction
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
3
10001 Completed
starts
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
4
10001 Completed
onRoadRetroFit
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
5
10001 Completed
importStartsOpmoDedistribution
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
6
10001 Completed
hotellingHours
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
7
10001 Completed
hotellingActivityDistribution
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
8
10001 Completed
EmissionRateByAge
NULL
Table Missing
NULL
NULL
cl0001y2014_20151211
NULL
NULL
9
10001 Completed
fuelUsageFraction
NULL
Table Check:
NULL
NULL
cl0001y2014_20151211
NULL
NULL
10
10001 Error
roadType
200
Number of Rows
0
NULL
cl0001y2014_20151211
NULL
NULL
11
10001 Completed
fuelSupplyYear
NULL
Table Missing
NULL
NULL
cl0001y2014_20151211
NULL
NULL
12
10001 Completed
countyYear
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
13
10001 Completed
emissionRateByAge
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
14
10001 Completed
zoneRoadType
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
15
10001 Completed
zoneMonthHour
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
16
10001 Completed
zone
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
17
10001 Completed
year
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
18
10001 Completed
state
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
19
10001 Completed
sourceTypeYear
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
20
10001 Completed
sourcetypeagedistribution
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
21
10001 Completed
roadTypeDistribution
NULL
Table Check
NULL
NULL
c10001y2014_20151211
NULL
NULL
22
10001 Completed
roadType
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
23
10001 Completed
monthVmtFraction
NULL
Table Check
NULL
NULL
cl0001y2014_20151211
NULL
NULL
In this example, there is an error with the AvgSpeedDistribution table. The submission has a
distribution that adds to zero (0.0). This entry (i.e., all zeros) can inadvertently eliminate any
VMT associated with that combination and cause an incorrect result. The sum of the fractions in
the AvgSpeedDistribution table must add to 1.0 for every combination of SourceTypelD,
RoadTypelD and HourDaylD, even if the Source Type, Road Type or HourDaylD do not have
VMT associated with that combination. Never fill distribution tables with zeros. You can use
the default distribution if no county specific values are available.
The "status" category may not contain any "Error" entries in order to successfully pass QA. The
report will pass if only any of these entries appear:
1) Completed - this indicates a successful check.
2) Warning - This indicates the user-supplied values that may not have been intended (i.e.,
distributions that sum to zero), but will not cause MOVES to generate inappropriate results.
14
-------
3) Comment - This will indicate that IMCoverage table contains no user supplied rows to be
checked.
Name your QA report with your agency ProgramSystem Code (PSC), such as
"PSC_QA_Report.txt". For example, Delaware's state agency PSC is "DEDNR" and their QA
report would be named "DEDNR_QA_Report.txt".
1.2.3 Create Your CDB Checklist
From the 2014 NEI webpage, download the QA checklist
"MOVES Onroad County Checklist.xlsx". This spreadsheet will contain rows for every
county in the nation. You can trim this list to only include the counties in your state.
This checklist is intended to indicate which tables your agency has revised from EPA's defaults
for each county's CDB. It also indicates counties for which the submitter accepts EPA default
estimates as equivalent to their submittal. The list you submit should include all of the counties
in your state, even if you are only submitting CDBs for some of the counties.
Example of Checklist
5
1 H
c*~
#
-
DEDNR_Checklist_2014.xlsx - Excel
?
ES
-
n
^1
FILE
HOME
INSERT PACE LAYOUT FORMULAS
DATA
REVIEW
VIEW
Brzezinski, David
¦m
A1
- :
fx County
V
I A I
B
c
D
E
F
G
H
1
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
2
AA AB AC AD AE
AF AG AH
Al
>
>
7*
m
1 |
County
Tables
~
2
stateid
statename
~untyid
countyname
Accept EPA Default Values
o
o
ss
w
3
re
1
avgspeeddistribution
county
dayvmtfractlon
c
o
s
3
E
=
0>
3
fuelsupply
fuelsupplyyear
fuelusagefraction
hotelllngactlvltydlstrlbution
hotellingbours
hourvmtfraction
hpmsvtypeyear
imcoverage
Import startsopmodedistributl
c
0
t>
re
1
*
o
E
onroadretroftt
roadtypedistributlon
sourcetypeagedistribution
re
-------
1.2.4 Documentation
All submissions must include a documentation document. At a minimum, the documentation
should address all of the changes made by the state to the EPA provided CDBs. For each
change, the document should state briefly the source of the state supplied information used to
populate the CDB. References to other documents with more detail are encouraged.
If you created the CDBs from scratch (not editing the EPA provided CDBs), please document
where local data were used and where default data from MOVES were used.
You may include additional documentation files which are referenced by the main
documentation. These additional files can be in any format (e.g., .pdf or .xls).
Name your documentation with your agency ProgramSystem Code (PSC), such as
"PSC_Documentation.doc (or docx)". For example, Delaware's state agency PSC is "DEDNR"
and their documentation would be named "DEDNR_Documentation.doc". Any additional files
provided can keep their original names and do not need to conform to any standard.
1.2.5 Bundle CDB Submittal Components unci Create the XML for EIS Submittal
Once you have prepared the parts of your submittal, you'll need to zip them together in a specific
way and reference them with the EIS/CDX required XML file. The CDBs are folders located in
the MySQL/data directory on your system. Once you have completed creating/editing the CDBs
for your state, these folders and their contents are to be included in the zip file for submission.
The graphic below shows that the individual CDBS are zipped into one zip file. That zip file and
the remaining parts (QA report, checklist, and documentation) are then zipped into a zip folder.
16
-------
CDB Submission Example
OnRoad CDB Example
jj Sample_CDB_Submission.zip
* = SampleCDBSubmission.xml
41
Sample_CDB_File.zip
y |
PSC_County_Databases.zip
cCCCCCy2014_ YYYYMMDD
cCCCCCv20U_YYYYMMDD
cCCCCCy2014YYYYMMDD
— PSC_QA_Report.txt
= PSCChecklist.xls(orxlsx)
= P5C_Documentation.doc (or docx)
*PSC = ProRram System Code
1.2.5.1 Creating the EIS/CDX-requirecl XML
There are two ways to create the xml file
1. Use notepad or other editing software to edit the sample xml
|cdb_sanipleheader.xml| which you can download from the 2014 NEI webpage.
17
-------
Edit the red text shown below:
YourName
Y our Organization
EIS
2014-10-10T14:02:39
EIS_v 1 _0
SubmissionType
Q A
DataCategory
Onroad
CDBDataFile
Sample_CDB_File.zip
Use the EIS Access Bridge Tool (as revised May in 2015).
1. Download the "Area Emissions Inventory Bridge Tool" from
http://www.epa.gov/ttn/chief/software/.
2. Open the file in Microsoft Access.
3. Choose the "Export Onroad/NonRoad XML Wrapper" from the Main Menu
=i1 Main Menu\^
Main Menu
Area Emissions Inventory System Bridge Tool
Import from CERS XML
Export To CERS XML
Edit Data Tables
View QA Report
View XML Component Scan Report
^ Export Onroad/Nonroad XML Wrapper
D«—
4. Fill out the form with the appropriate information
-------
| i=|] Main Menu j =H Export to CERS XML \
Export CERS XML Wrapper for Activity Data
Export CERS XML Wrapper for Activity Data
File Location: G:\USER\SHARE\EIAG\New EIS Bridge Tools\NonPoint_BridgeTool.xml
Complete / Verify Header Information
Data Category:
Emissions Year:
Submission Type:
Activity Database Type: |CDB p]
Activity Database File: YourZIPPEDFileName.zip
User Identifier: YourEISID
Author Name: Your Name
Organization Name: Your Organization
Program System Code: |your_PSC
EPA Dataset:
Keywords: |optionally Fill |
Comment: |optionally Fill
[ Begin Export ] [ Cancel
a. File Location = The name and location where the resulting XML file will
be generated.
b. Data Category: Choose either "Onroad"
c. Emissions Year: Set to 2014
d. Submission Type: Choose either "QA" or "Production"
e. Activity Database Type: Set to "CDB"
f. Activity Database File: The name of the packaged set of zip files. Using
the earlier example: "Sample_CDB_Submission.zip"
g. User Identifier: Your EIS User ID
h. Author Name: Your name (optional)
i. Organization Name: The name of your organization (optional)
j. Program System Code: The program system code of your organization
k. EPA Dataset: Leave blank
1. Keywords: Any keywords you would like to submit about the document
(optional)
m. Comment: Any comments about the document (optional)
5. Press the "Begin Export" button. Your file will be generated to the location
specified.
NOTE: Each CDB submittal is a total replacement to any CDBs previously submitted by
that agency. Thus, if a state submits every county in one submittal, then submits only one
county in a subsequent submittal, only the one county will be present in EIS.
19
-------
You can check the feedback on your submittal in EIS by choosing your agency and the
"Feedback Reports" tab as shown in the figure below:
EIS Gateway—Agency Organization Detail: Feedback
EIS - Windows Internet Explorer
01 https: //eis. epa. gov/eis-system-web/agency/organization/detail. html?agencyOrganizationId=-81 &div=8
File Edit View Favorites lools Help
* *
3E x ! powerpoint group picture
¦ EPpi Emissions Inventories | Cleari...
^ ' 0 ' li^Eage t Tools *
EIS Gateway
Laurel Driver EIS; Content Manager Role, Authenticated Role. Inventory Developer Role
xdJL. K,
View/Add/Edit
» Facility Inventory and Point
Emissions
» Potential Duplicate Facilities
» Merge Processes
» Nonpoint/ Onroad/ Nonroad
Emissions
» Event Emissions
» NCD Activity Data
» CDB Activity Data
» Inventory Selection
Reports
» Request Reports
» Report Downloads
» Large File Download
» Feedback Reports
» Agency Submission History
Report
Reference Data
» Reporting Code Tables
» QA Checks
» View Dataset Identifiers
» Inventory Cycle
Management
» EIS Bridge
Support
» Administration Console
» View All Announcements
Agency Organization Detail
Current Agency
Agency Description: Alabama Department of Environmental Management
Agency Type: State
Edit Agency Organization
Agency Responsibilities Agency Members Program System Codes Allow Ac&^s,
Submission History
:a Category Type
5504f8b4-133c-411 f-8848-3f9cd588efe0
COMPLETED
Elizabeth Tate
Point
PRODUCTION
2011-12-28 03:31 PM
Download Report
S6df631 b-988c-4b8d-b0 3a-1 b3176d74cf4
COMPLETED
Elizabeth Tate
Facility Inventory
PRODUCTION
2011-12-22 04:42 PM
Download Report
bf9(J5125-cbd4-4778-9699-7014a625de€f
COMPLETED
Elizabeth Tate
Facility Inventory
QA
2011-12-21 05:20 PM
Download Report
Download XML Submission
_ce1d6db1 -23f1 -4890-a7a2-02617«J49202
COMPLETED
Elizabeth Tate
Facility Inventory
PRODUCTION
2011-10-11 09:40 AM
Download Report
3036ai>41 -9662-4548-b56a-9fc-1509d6397a2d
COMPLETED
Elizabeth Tate
Point
PRODUCTION
2011-04-04 04:42 PM
Download Report
ff1203ab-07f3-451c-a558-8aa9258d195d
COMPLETED
Tracy Anderson
Nonpoint
PRODUCTION
2011-02-17 05:38 PM
Download Report
0b8e6e98-489t-4426-995d-801d0fc1746e
COMPLETED
Tracy Anderson
Nonpoint
PRODUCTION
2011-02-15 04:17 PM
Download Report
S541 c06S-1 ft>t-4bfa-t>056-74e03da37a8c
COMPLETED
Elizabeth Tate
Point
PRODUCTION
2011-02-02 03:19 PM
Download Report
5 f219709-0616-4d70-3o6a-cb6feS76791 e
COMPLETED
Elizabeth Tate
Point
PRODUCTION
2011-02-01 04:39 PM
Download Report
94ca60f4-Sc8a-40c8-9
-------
Additional Resources
For additional Assistance contact
Submittal Issues
-Sally Dombrowski - dombrowski.sally@epa.gov; 919-541-3269
Inventory Issues
-Laurel Driver - driver.laurel@epa.gov; 919-541-2859
CDB Content Issues
-David Brzezinski - brzezinski.david@epa.gov; 734-214-4235
MOVES Issues
-mobile@epa.gov
-------
United States Office of Air Quality Planning and Standards Publication No. EPA-454/B-20-029
Environmental Protection Air Quality Assessment Division May 2015
Agency Research Triangle Park, NC
------- |