* _ \
KWJ
*1 PRO1^
Instructions for Submitting ONROAD Inputs
for the 2017 NEI
-------
-------
EPA-454/B-19-031
July 2018
Instructions for Submitting ONROAD Inputs for the 2017 NEI
U.S. Environmental Protection Agency
Office of Air Quality Planning and Standards
Air Quality Assessment Division
Research Triangle Park, NC
-------
July 31, 2018
Instructions for Submitting ONROAD Inputs for the 2017 NEI
The U.S. Environmental Protection Agency (EPA)'s Air Emissions Reporting Rule
(http://www.epa.gov/air-emissions-inventories/air-emissions-reporting-requirements-aerr)
requires state and local agencies who submit to the National Emissions Inventory (NEI) to
submit model inputs for mobile sources, rather than emissions, in the form of county-specific
databases (CDBs). Emissions estimates may be submitted in addition to model inputs, and
emissions estimates will still be accepted from tribes without inputs. If you do not submit onroad
input data, EPA will generate emission estimates using EPA-developed county-specific inputs.
This document addresses submitting onroad MOVES CDB inputs only.
For the 2017 NEI, EPA plans to use the most current version of the Motor Vehicle Emission
Simulator (MOVES) model, MOVES2014b, to estimate both onroad and nonroad emissions.
Because onroad emissions calculated with MOVES2014b are identical to those calculated with
MOVES2014a, States may submit onroad CDBs that are used with either MOVES2014a or
MOVES2014b. Note: onroad CDB submissions must be checked using a QA script (provided by
EPA) that is specific to the version of MOVES - MOVES2014a or MOVES2014b - on which
the CDB is based. The MOVES2014b onroad CDB QA script will be available following the
release of MOVES2014b.
For information on submitting MOVES-Nonroad CDBs, see the companion document
"Instructions for Submitting MOVES-Nonroad Inputs to the 2017 NEI."
Since the MOVES inputs are county-based, tribal agencies should run MOVES and submit
emissions. However, tribal agencies may use the input information from adjacent counties to
prepare local inputs that may be suitable for their tribal area MOVES runs.
MOVES inputs must be submitted to EIS as a County Database (CDB), which consists of a set of
MySQL database tables specifically formatted to store county-specific inputs for MOVES. Prior
to submitting MOVES inputs, agencies may download EPA's draft 2017 CDBs from the 2017
NEI FTP site (ftp://newftp.epa.gov/air/nei/2017/doc/supporting data/onroad/draft inputs/) to use
as a template. The emissions in the NEI are developed for all months of the year and all counties
in every state, plus the 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.
For the NEI, MOVES will be run at the County Scale, which 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 EPA-provided defaults. Agencies must check their CDBs
prior to submittal using the provided QA Tool that will generate a QA Report to include with
submittals. EPA will provide two QA tools, one for MOVES2014a-compatible CDBs and one
for MOVES2014b-compatible CDBs. Agencies must ensure that the QA script used to generate
the required QA report matches the version of MOVES used to build the CDBs. Agencies should
1
-------
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 that want to accept EPA's
defaults and submit nothing may do so via a 'support request' message that states that intent,
through the EIS gateway. This same approach also applies to other data categories.
The following sections describe how to create/revise onroad CDBs, QA check them, and submit
them to EIS.
Supporting tools and files referenced here can be downloaded from the 2017 FTP site:
ftp://newftp.epa.gov/Air/nei/2017/doc/supporting data/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 the data EPA uses as defaults. It is expected that states will have more
accurate information in many cases.
The EPA document, "MOVES2014 and MOVES2014a Technical Guidance: Using MOVES to
Prepare Emission Inventories in State Implementation Plans and Transportation Conformity"
(EPA-420-B-15-093, November 2015; available at https://www.epa.gov/moves/moves2014a-
latest-versi on-motor-vehicle-emission-simulator-moves). 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 the vehicles in a county for all months of a single calendar year (2017) using county-
specific information.
Default 2017 CDBs based on information found in the CDBs used for the 2014 NEI v2 are
available here: ftp://newftp.epa.gov/Air/nei/2017/doc/supporting data/onroad/draft inputs.
These CDBs may be used by states as a starting point for generating CDBs to provide to EPA or
states may provide CDBs created independently. Any CDBs submitted to EIS for the 2017 NEI
should be complete and ready to run for the calendar year 2017.
The posted CDBs 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.
EPA will add 2017 fuels and meteorological data to CDBs before 2017 NEI MOVES runs, so
data do not need to be provided in those tables.
2
-------
Table 1: CDB
Tables and
Contents
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 2014 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 2014 NEI.
avgspeeddi stributi on
Average speed distributions
State-provided data from the 2014 NEI or
national average EPA estimates.
county
Description of the county
EPA provided data.
countyyear*
Description of the Stage 2
program
State-provided data from the 2014 NEI or EPA
default values for each county.
dayvmtfraction
VMT distribution across the
type of day
State-provided data from the 2014 NEI or EPA
estimates.
emi ssi onrateby age *
Emission rates to reflect
adoption of California
emission 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 ***
fuelformulation
Fuel properties
Based on EPA estimates for each county from
calendar year 2014. EPA will provide the
2017 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
2017 values that will be used, but states must
provide placeholder values to pass the QA
script.
fuelsupplyyear
Year for the fuel properties
Set to 2014 in default CDBs
fuelusagefraction
Fuel use by flexi-fuel
vehicles.
Based on EPA estimates for 2014.
Hotellingactivitydi st
ribution**
Distribution of hotelling
hours using extended idle or
auxiliary power units.
Based on EPA estimates for 2014.
Hotellinghours* *
Hours of hotelling by long
haul combination trucks.
County-specific hours for 2014.
hourvmtfraction
VMT distribution across the
hours of the day
State-provided data from the 2014 NEI or
national average EPA estimates.
hpmsvtypeday**
Daily VMT by HPMS
vehicle type
Alternate method to provide VMT by HPMS
vehicle types.
3
-------
Table 1: CDB
Tables and
Contents
Description of Content
EPA-Default CDB Table Content
hpmsvtypeyear
Total annual VMT by HPMS
vehicle type
VMT data values from the 2014 NEI. States
may provide VMT estimates by MOVES
source type in SMOKE format directly for use
in the 2017 NEI. Alternatively, the annual
VMT data may be provided by source type in
the sourcetypeyearvmt table.
imcoverage
Description of the Inspection
and Maintenance program
I/M program description carried forward from
the 2014 data. The model year ranges may
need to be adjusted to reflect the effect of the
grace period on model year ranges for calendar
year 2017.
Importstartsopmode
distribution**
Distribution of engine starts
by engine soak time.
Empty (using national average EPA estimates).
monthvmtfraction
VMT distribution across the
months of the year
State-provided data from the 2014 NEI or
national average EPA estimates.
onroadretrofit**
Emission adjustments to
reflect local retrofit
programs.
Empty (no retrofits assumed).
roadtype**
Describes the road types.
Empty (uses default values).
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 2014 NEI or EPA
defaults based on calendar year 2014
estimates.
sourcetypedayvmt* *
Daily VMT by source type.
Alternate method to provide VMT by source
types.
sourcetypeyear
Vehicle populations
Vehicle population data values from the 2014
NEI
sourcetypeyearvmt*
*
Total annual VMT by source
type.
Alternate method to provide VMT by source
types.
starts**
Number of engine starts
Empty (using national average EPA estimates).
startshourfraction* *
Distribution of starts by hour
of the day
Empty (using national average EPA estimates).
startsmonthadj ust * *
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 2017.
4
-------
Table 1: CDB
Tables and
Contents
Description of Content
EPA-Default CDB Table Content
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 based on
EPA-provided data for each county from
calendar year 2014.
zoneroadtype
Allocation of road types to
the county
EPA-provided data. Allocations must all be
1.0 (100%). This data should not be changed.
*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
**Tables that can be empty but must be present in EIS submittal
*** The following states are 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 are 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
5
-------
'oiintv Database Naming Convention
¦.
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 consist of 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.
An example of a CDB name is "c26161y2017_20180601" where this CDB names indicates
"c26161" refers to the county FIPS code (in this case Washtenaw County, Michigan). "y2017"
refers to the calendar year for the county database and "20180601" identifies the database
modification date of June 1, 2018, in YYYYMMDD format.
1.2 Steps to Submit CPUs
The steps for submitting CDBs are discussed in more detail in the subsections that follow:
1. Create CDBs
a. Edit existing CDBs to meet NEI requirements or
b. Create new CDBs from scratch or
c. Revise EPA defaults.
2. Select the appropriate QA Tool based on the version of MOVES used by the
submitting agency (MOVES2014a or MOVES2014b)
3. Run EPA's QA Tool to create a QA report that confirms no errors exist.
4. Create a Checklist that indicates where changes have been made to the EPA
CDBs
5. Provide documentation for the Agency- supplied inputs.
6. Submit the files to the EIS.
1,2.1 Creating County Patabases
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.
6
-------
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 the best option. Otherwise, EPA suggests starting with the 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
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
are 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.
/ •sting County Databases
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 (2017) 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 the NEI CDB QA Tool as explained in Section 1.2.2.
. 4iim - "mtli Databases
-u—5
EPA has provided a set of all CDBs as a draft default for 2017. They contain the values used for
the 2014 NEI v2. States only need to submit CDBs for counties for which they have made
7
-------
changes for 2017. Counties that are not submitted will use EPA default estimates. However,
EPA encourages agencies make changes where better county specific information is available.
Note: the CDBs submitted must match those in the submitted checklist to avoid a submittal error.
The EPA-provided CDBs should be loaded into your MySQL database system by copying the
CDB folders into the MySQL\data 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 the counties affected.
Of course, the data in any tables can be manipulated directly using the MySQL query language.
1,2. 1. 3 Using the 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) using "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 (cl0001y2017.mrs) from
ftp://newftp.epa.gov/Air/nei/2017/doc/supporting data/onroad/.
1.2.1.3.1 Tables Not Created toy 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 (provided at
ftp://newftp.epa.gov/Air/nei/2017/doc/supporting data/onroad/) that is required to be run on all
8
-------
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:
https://www.epa.gov/moves/tools-develop-or-convert-moves-inputs
• 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.
\ne 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.
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).
The XML approach assumes 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.
9
-------
States can contact EPA (see contact options at end of instructions) if they need further
instructions on how to use XML files to populate their CDBs.
1.2.1.5 Usine MySQL
All 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 2017 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 2017, which would allow the tables to be used with a run
specification that selected the 2017 calendar year. NOTE: This update has already been made
in the EPA 2017 default CDBs. If you want to use the CDBs already updated to 2017, skip
the rest of this subsection.
If you need to manually update the year of your CDBs, 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 2017. The "use" command selects the
database to be altered. The "c2616 ly2017_20180601" string refers to the database name,
following the EPA naming convention for CDBs. The "c26161" refers to the county FIPS code.
"y2017" refers to the calendar year for the county database and "_20180601" as a database
created on June 1, 2018, in YYYYMMDD format.
use c2616ly2017_20180601;
update countyyear set yearid=2017;
update hpmsvtypeyear set yearid=2017;
update hotellinghours set yearid=2017;
update imcoverage set yearid=2017;
update sourcetypeagedistribution set yearid=2017;
update sourcetypeyear set yearid=2017;
update starts set yearid=2017;
10
-------
update startsperday set yearid=2017;
update year set yearid=2017;
update year set fuelyearid=2017;
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 2017 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 2017 using the XML importer
discussed above as well.
1.2,1.6 Autonmtins the creation of multiple databases
!8888888888888888888 : i ,
XML importer scripts 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.
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 J :i... i .'I to Cr. w . " port to include with your submittal
For Agencies submitting onroad CDBs based on MOVES2014a, from
ftp://newftp.epa.gov/Air/nei/2017/doc/supporting data/onroad/ download and unzip
"2017vl_CDB_QA_Checks_MOVES2014a.zip", which contains the QA Tool
(CDB_QA_Checks_MOVES2014a_v2.sql ) and an example script
(Example_Run_QA_Checks_MOVES2014a_v2.bat) that allows you to automate the QA
process for more than one county (discussed below). CDB_QA_Checks_MOVES2014a_v2.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 it is not and the CDB contains errors that are
11
-------
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.
Agencies submitting onroad CDBs based on MOVES2014b, should follow the above
instructions for MOVES2014a, but should instead begin by downloading and unzipping the file
"2017v 1 _CDB_Q A_Checks_MO VES2014b. zip"
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. The format is .txt,
but this file can be opened as a table using Microsoft Excel to make it more readable.
IMPORTANT NOTE: All 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 most of
these 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 each contain 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 then send the second QA report (with no errors) to the EIS.
Also, the QA tool uses tables from the default MOVES onroad database that was distributed with
MOVES2014a and MOVES2014b . If you do not have MOVES2014a or MOVES2014b
installed, you will need to obtain a copy of the database folder specified in the QA script and
place it in your MySQL\data directory.
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., c 1000ly2017_20180601) using
the QA Tool script (CDB_QA_Checks_MOVES2014a_v2.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
12
-------
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 cl0001y2017_20180601 < C:\MySQL\CDB_QA_Checks_MOVES2014a_v2.sql
mysql -uXXXXX -pYYYYY cl0003y2017_20180601 < C:\MySQL\CDB_QA_Checks_MOVES2014a_v2.sql
mysql -uXXXXX -pYYYYY cl0005y2017_20180601 < C:\MySQL\CDB_QA_Checks_MOVES2014a_v2.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 (CDB_QA_Checks_MOVES2014a_v2.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.
Note that in the above example, "MOVES2014a" should be replaced with "MOVES2014b" if
MOVES2014b is the model version on which the submitted CDBs are based.
Example of QA Report Results
II
OS
DEDNR QA Report results.xlsx - Exce
Brzezinski, David
H
-
~ >
1 Home 1
Page Layout Formulas Data Review
View 9 TeU me what you want to do
Q. Share
^ Calibri
RJ if3 Insert • X "
nv _ . mm
ID
Paste
- Ill - A* A* — = = If-
General
ri
rw\
m 6
5t i~>
v b i u
i±j - - =:=::=
ill m
$ , % t *o§ -»o Conditional f
Formatting -
- g* Delete - I*]'
-ormat as Cell
Table' Styles- HI Format- -
Sort £
Filter
St Find 8t
- Select-
Clipboard ft
Font ft Alignment
ft
Number ft
Styles
Cells
Editing
A
A1
' : I
f* countylD
-
B
! C |
D
E
f
G
H
! !_
>
E
countylD jstatus
tableName
checkNumber
testDescription
testValue count dataBaseName
daylD
fuelFormulationID
2
10001 Completed
onRoadRetroFit
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
3
10001 Completed
importStartsOpmoDedistribution
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
4
10001 Completed
hotellingHours
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
5
10001 Completed
hotellingActivityDistribution
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
6
10001 Completed
fuelUsageFraction
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
7
10001 Error
avgSpeedDistribution
406
distribution error
0 NULL
cl0001y2017_20181211
NULL
NULL
8
10001 Completed
VMT Tables
217
VMT tables with data: OK
1 NULL
cl0001y2017_20181211
NULL
NULL
9
10001 Completed
sourceTypeYearVmt
216
Number of Rows
13 NULL
Cl0001y2017_20181211
NULL
NULL
10
10001 Completed
sourceTypeDayVmt
215
Number of Rows
0 NULL
cl0001y2017_20181211
NULL
NULL
11
10001 Completed
hpmsVTypeDay
214
Number of Rows
0 NULL
cl0001y2017_20181211
NULL
NULL
12
10001 Completed
hpmsVTypeYear
213 Number of Rows
0 NULL
cl0001y2017_20181211
NULL
NULL
13
10001 Warning
roadType
200 Number of Rows
0 NULL
Cl0001y2017_20181211
NULL
NULL
14
10001 Completed
emissionRateByAge
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
15
10001 Completed
zoneRoadType
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
16
10001 Completed
zoneMonthHour
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
17
10001 Completed
zone
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
18
10001 Completed
year
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
19
10001 Completed
sourceTypeYear
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
20
10001 Completed
roadTypeDistribution
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
21
10001 Completed
roadType j
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
22
10001 Completed
monthVmtFraction
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
23
10001 Completed
imCoverage
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
24
10001 Completed
hpmsVTypeYear
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
25
10001 Completed
hourVmtFraction
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
26
10001 Completed
fuelSupplyYear
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
27
10001 Completed
fuelSupply
NULL
Table Check:
NULL
NULL
Cl0001y2017_20181211
NULL
NULL
28
10001 Completed
fuelFormulation
NULL
Table Check:
NULL
NULL
cl0001y2017_20181211
NULL
NULL
DEDNR QA Report (+)
•' H
1
ffl ID El
—i—
1/VW
| r^couy
_J
13
-------
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 RoadType table has a warning (no rows). Since MOVES will use default values for this
table if no rows are supplied, the warning is provided to inform the user that no data has been
supplied, but MOVES will run successfully without user input.
The "status" category may not contain any "Error" entries to successfully pass QA. The report
will pass if only 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.
3) Comment - This will indicate that the table contains no user supplied rows to be checked.
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".
lecklist
From the 2017 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 the counties in
your state, even if you are only submitting CDBs for some of the counties.
Example of Checklist
14
-------
1
H.
ime
9
nsert
Page Layout Formulas
at a
Review
DEDNR_2017_Checklistxlsx - Excel
View Q Tell me what you want to do
Brzezinsk'i, Davie
m
~
~
A
X
Share
¦p *>
Calibri
*|12 'V a'
=
=
Wrap Text
General *
I
1
9
i
p
•¦CO
§
1
rr P
t|=)
Paste
.y
Clipboard
c»
B I
u - |±i - -
Font n
¦
-
t= ±= fH Merge & Center -
Alignment
$
-
% »
Number
«-.o
.00
.00
-».o
fi
Conditional Format as
Formatting * Table *
Styles
Cell
Styles-
Insert
Delete Format
Cells
* 1
Sort& Find&
Filter' Select-
Editing
A
A1
f* County
A
B
c
D
E
F
G
H
1
J
K
L
M
N
O
P
Q
R
s
T
U
V
w
X
Y
Z
AA
AB
AC
AD
AE
AF
AG
AH
Al |
AJ
AK
AL
AM
AN
ACR
1
County
Tables
2
stateid
statename
countyid
countyname
Accept EPA Default Values
U
M
as
8
|
.2
a
•fi
a
¦Q
«
1
|
8
a
ts
z
1
"O
fue Iform illation
%
a
=
jfl
c
I
a.
a
=
a
fuelusagefraction
ho t c llingact iuit yd b t ribu t io n
e
B
0
=
0
ho urvmt fraction
n
1
1
£
2.
I
g.
s
E
2.
&
2
s
s
E
.9
2
—
¦3
O
=
a
0
C
1
i
E
monthvmtfraction
-------
1.2.5 Bundle CDB Submittal Components and 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.
CDB Submission Example
OnRoad CDB Example
| Sample_CDB_Submission.zip
^ = SampleCDBSubmission.xml
M I
Sample_CDB_File.zip
'< [¦!
PSC_County_Databases.zip
cCCCCCy2017 YYYYMMDD
cCCCCCy2017_YYYYMMDD
cCCCCCy 2 017 YYYYMMDD
= PSC_QA_Report.txt
= PSC_Checklist.xls (or xlsx)
= PSC_Documentation.doc(ordocx)
*PSC = Program System Code
1.2.5.1 Creating the EIS/CDX-reguired XML
To create the xml file
2. Use the EIS Access Bridge Tool
1. Download the "Area Emissions Inventory Bridge Tool" from
http://www.epa.gov/air-emissions-inventories/emission-inventory-tools.
2. Open the file in Microsoft Access.
3. Choose the "Export Onroad/NonRoad XML Wrapper" from the Main Menu
16
-------
23 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
c
Export Onroad/Nonroad XML Wrapper
4. Fill out the form with the appropriate information
Export CERS XML Wrapper for Activity Data
Export CERS XML Wrapper for Activity Data
File Location: [C:\Your_Location\Filename.xml
Browse...
Complete / Verify Header Information
Data Category: |Onroad
Emissions Year: [2017
Su b m issi on Ty pe: p ro d u en i o n
Activity Database Type:
CDB
Activity Database File: [YourZippedFileName.zip
User Identifier: [YourEISID
Author Name: [Your Name
Organization Name:
Your Organization
Program System Code:
EPA Dataset:
Keywords:
Submission Comment
Your PSC
~
Optionally fill
Optionally fill
Begin Export
Cancel
a. File Location = The name and location where the resulting XML file will
be generated.
b. Data Category: Choose "Onroad"
c. Emissions Year: Set to 2017
17
-------
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.
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 Gateway
Jonathan Miller. EIS; Content Manager Role, Inventory Developer Role. Authenticated Role, Inventory Selector Role. Account Manager Role
i
'">r .'IvL -j iC-t • fl 1
View/Add/Edit
» Facility Inventory and Point
Emissions
» Potential Duplicate Facilities
» Merge Processes
» Non point/ On road/ N on road
Emissions
» Event Emissions
» NCD Activity Data
» CDB Activity Data
» Inventory Selection
» Schedule Augmentation
» Data Tagging
Reports
» Request Reports
» Report Downloads
» Large File Download
» Feedback Reports
» Agency Submission History
Report
RpFFRFMr.F Data
Agency Organization Detail
Current Agency
Agency Description: Alabama Department of Environmental Management
Agency Type: State
ETL Process Group: 2
Agency Responsibilities
Agency Members
Program System Codes
Allow Access
Feedback Reports
Nonpoint Survey
Submission History
CDX Tracking# J Status t Submitter Data Category ; Type ; Submitted
d438e5d0- f75a-4ca9-Sf98-728902dcc 17d
COMPLETED
Anna Wood
Point
PRODUCTION
2017/12/27 11:33:34 AM
Download Report
_ca820725-699O-477f-ada0-2b246dr75faS
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2017/12/27 10:35:20 AM
Download Report
_0d6c79ba-37e5-46d7-bb2e-038b2b6cb919
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2017/12/22 02:09:42 PM
Download Report
7261 d47d-debc-47 fa-aade-cb18a651 ece€
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2017/12/22 11:54:57 AM
Oownload Report
_ed 127e6a-d893-41 bc-894d-6327d2bb7d4e
COMPLETED
Anna Wood
Point
PRODUCTION
2016/12/28 02:42:37 PM
Download Report
29d88353-7eed-4ddd-8e2a-326de07bfa9S
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2016/12/28 11:45:08 AM
Download Report
al c61 Sfb-S184-4123-a01 o-0f3l 8e64f003
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2016/12/27 03:27:55 PM
Download Report
_a6c50cb0-446b-4207-adc3-e79c9c372b61
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2016/12/23 12:14:50 PM
Download Report
8ce82b67-6Sce-421 e-b903-S91 S8d704ee4
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2016/12/23 11:26:07 AM
Download Report
1 Sf28d93-c00S-4ffS-aaed-9b74391 ac64a
COMPLETED
Anna Wood
Facility Inventory
PRODUCTION
2016/12/23 10:49:04 AM
Download Report
_de€39aSf-4i ?e--eCc-a2fc-5713c66b870c
COMPLETED
Anna Wood
Point
PRODUCTION
2016/06/28 03:13:15 PM
Download Report
9717c949-0f2b-4f1 d-be3f-f83620e7d65b
COMPLETED
Anna Wood
Point
PRODUCTION
2016/04/08 10:41:11 AM
Download Report
f742ef9b-a5ae-49ad-be1 b-e7862427fe9f
COMPLETED
Anna Wood
Point
PRODUCTION
2016/04/08 10:20:29 AM
Download Report
_79f17007-e0f3-4c30-88cc-47293f9674ab
COMPLETED
Anna Wood
Point
PRODUCTION
2016/01/06 03:10:22 PM
Download Report
18
-------
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
-Jaehoon Han - han.iaehoon@epa.gov. 734-214-4299
MOVES Issues
-mobile@epa.gov
-------
United States Office of Air Quality Planning and Standards Publication No. EPA-454/B-19-031
Environmental Protection Air Quality Assessment Division July 2018
Agency Research Triangle Park, NC
------- |