Instructions for Using the EPA Nonpoint Oil and Gas Emissions Estimation Tool


* _ \
iWJ
PRO^
Instructions for Using the EPA Nonpoint Oil
and Gas Emissions Estimation Tool

-------

-------
EPA-454/B-19-033
November 2014
Instructions for Using the EPA Nonpoint Oil and Gas Emissions Estimation Tool
U.S. Environmental Protection Agency
Office of Air Quality Planning and Standards
Air Quality Assessment Division
Research Triangle Park, NC

-------
Instructions for Using the EPA Nonpoint Oil and Gas Emissions Estimation Tool
1.0 Introduction
Under Work Assignment with U.S. EPA, Eastern Research Group, Inc. (ERG) was tasked to develop a
tool that state, local, and tribal (SLT) agencies could use to develop a nonpoint source emission inventory
for upstream oil and natural gas activities. To this end, ERG prepared the EPA Nonpoint Oil and Gas
Emissions Estimation Tool for the 2011 base year to assist agencies in compiling, allocating, and
adjusting upstream oil and natural gas activity data, and developing county-level nonpoint source
emission estimates. The tool generates estimates for 54 source classification codes (SCCs) and 51
pollutants.
2.0 MS-Access Database
The Nonpoint Oil and Gas Emissions Estimation Tool was programmed in MS-Access. This platform
offered several advantages, particularly in accessibility (software is available to most users), familiarity
(MS-Access is used by most SLT agencies in preparation of Emission Inventory System (EIS) data files),
and portability (the tool can be e-mailed as a zipped file that is less than 6 MB in size). Included with the
tool is the Nonpoint Emissions blank staging tables which are to be used for preparation of EIS data files.
The MS-Access database contains 10 primary groups of tables and queries.
1)	Group 1 A: Input Activity Data Table
a.	Inputs Activity
b.	Glossary of terms in the Inputs Activity table
c.	Data Field References
2)	Group 1B: Emission Factor Data Tables
a.	Emission factors for each source category
b.	Glossary of terms in the Emission Factors tables
3)	Group 1C: Basin Factor Data Tables
a.	Basin factors for each source category
b.	Glossary of terms in the Basin Factor tables
4)	Group 2: Category and Geographic Selection - allows the User to select the upstream oil and gas
source categories and geographic level (e.g., nationwide, state, RPO, basin, EPA Region, etc.) of
interest
5)	Group 3: Point Source Activity Adjustments
a.	Nonpoint sources SCC to point sources SCC crosswalk
b.	Input tables for point source activity adjustments by source category
6)	Group 4: Run Estimation Queries for each source category
7)	Group 5: Point Source Emission Adjustments
8)	Group 6: Finalize Emission Estimates
9)	Group 7A: Compiled Emissions and Summary Data
10)	Group 7B: EIS Data Tables (linked to the Nonpoint Emissions Staging Tables)
11)	Group 7C: EIS Coding Tables
12)	Group 8: Clear Source Category and EIS Data tables
13)	Group 9: Emission Estimation Queries by Source Category and Compiling Emissions Data
14)	Group 10: Background Data Tables and Queries

-------
3.0 Tool Data Flow
The basic concept of the tool is to calculate the source category emissions using the data from Groups
1A, 1B, and 1C input data sheets. Emissions and activity data are summarized in the Group 7A and 7B
data tables. A conceptual flow is:
Source Category Emissions
Basin Factors
Inputs-Activity
Emission Factors
EIS Base Table
Compiled Emissions
4.0 Steps for Using the Oil arid Natural Gas Tool to Generate Emissions
Prior to running the tool, the User must properly link the data tables in the Nonpoint Emissions
Staging Tables within the tool.
1) Place both the "Tool_O&G_V2_20141121 .accdb" and the "Nonpoint Emissions.mdb" database
tables in the same directory. It is recommended that the User creates an "EPA_OIL_GAS"
directory on their hard drive.
2) Open the "Tool_O&G_V2_20141121 .accdb" database. You will need to "Enable Macros" if the
message pops up.
3) Open up the "GROUP_7B_EIS_TABLES" dropdown. Right-click anywhere on the left pane. A
pop-up list will be shown (see figure below). Click on "Linked Table Manager".
A.TABIt
Custom
GROUP1A.INPUT. ACTIVITY
.ZD OOU.PKVTS.ACTMTY
.13 001b.GlOSSAKIJIS'UIS. ACTIVITY
.13 001t.MASTER.REFEBEMa.UBU
GROUP, lB.EMlSSlOW.EACTORDAIA.TABlES s
GROUP K.BASIN. IACTORS.DAIA.IABIIS s_
GROUP_?_CATEGOKV_AND.GEOGRAPHIC.SllKTIO« =
.3 MACRO.RESn.WTEOORT.ANO.GEOOWmC.lEVElS.O.,.
.3 STEP.Ol.KCK.SOURCE.CATEGOm
.J STW.OJ_RUN.SOU«CE.C*TEGOR»_UVEl
.3 STEP.03_Ft«_SPEaRC. SOURCE. CATEGORIES [ClOSE AE
.3} SWR04_Pl«.«O6RMWC_UVEl (ClOSt AFTER PIOWSl
.'J. STEP.OS.RUH.Ha.GEOGRARHlC.lEVEl
.13 STER_06.PI«.OEOGRAI*K.$«aFKK-T'
.3 STEP.OJ.RUM.Pta.GEOGMPHK.SPECl)^
GR0UP.J.P01N1 .SOURCE. ACTIVITY. ADA
GROUP.4.RUN {STtMATION.QUlRltS
. 2 STEP.Oa.RUH.ESTIMATKMI.QUERIES
GROUP_5.PO!NT,SOURCl.IMISSION.AOJ
GROUP.fc. I 1NA11ZE EMISSION ESIIMAT t
.2 STEP.09.RUH_POlllT.SOURCE.EM!$5IOf
.2 5TEP.10_MACRO.TO.OCVEIOP.E5. FIE'
GROUP.7A.COMPllfD.SUMMARV.EMKS A
.13 compiieo.emissiohi.au -J
.^P query - POllUTANT.EMiSSIOHS
.JJ query - SOURCE. CATEGORT.PQUUTANT
at
'.J c«s
J!I3 ConltofjMMonn
J13 ContflXMeMUlt
123 CenuoJPollutwM
! 3 EnMuloniProtess
' 3 E>cluno«H
-------
4) A "Linked Table Manager" window will appear listing all the EIS data tables. Check mark all the
boxes by pushing the "Select All" button on the right (see below), including the "Always prompt for
new location" selection. Click "OK"
m Linked Table Manager
L
Select the linked tables to be updated:
17
[7
1^0
17 *~
17 o
17
17 O
17
17 O
(7 43
(j/i Always prompt for new location
CERS (C:\work\EPA OIL GAS\tooWatesftNonpointEmissions.mdb)
ControlApproach (C:\worlAEPA_OIL_GAS\iooNatest\Nonpoint Emissions.mdb)
Contra Measure (C:\work\EPA_OIL_GASVtoofUatesftNonpoint Emissions.mdb)
ControIPotlutant (C:\workSEPA_0IL_GAS\tooUatest\Nonpoint Emtssionsmdb)
Emissions (C:\work\EPA_OIL_GAS\tool\latesnNonpoint Emissions.mdb)
EmissionsProcess (C:\work\EPA_OlL_GAS\tooNatesttNonpoint Emissions.mdb)
ExchangeHeader (C:\work\EPA_OIL_GAS\tooMatesfiNonpoint Emissions.mdb)
Exclu d ed Lo cation Pa ra meter (C:\work\E PA_0 IL_G AS\tooWa test\N o n po in t E miss ions.mdb)
Location (C:\work\EPA_OIL_GAS\tooMatest\Nonpoint Emissions.mdb)
0 pe ratin g Deta iis (C:\wo rk\EPA_0 IL_G AS\to o MatesttN on point Emtssionsmdb)
Regulation (C:\work\EPA_OIL_GAS~rtooMatesfiNonpoirjt Emissions.mdb)
ReportingPeriod (C:\work\EPA_OIL_GASVtool\latesf\Nonpoint Emissions.mdb)
SupplementalParameter (C:\work\EPA_OIL_GAS\tooNatestWonpoint Emissions.mdb)
OK
Cancel
Select All
Deselect AH
5) After clicking "OK", a new window will appear. Map to the directory which contains the "Nonpoint
Emissions.mdb" database and select the "Nonpoint Emissions.mdb" file. After the tables are
linked, a message box will appear confirming that the tables were successfully refreshed. Click
"OK". Close the "Linked Table Manager" window. The tool is ready to generate emissions.
6) In the "Inputs-Activity" table (Group 1 A); Users can update the data fields in this table. The data
sources for the 2011 base year are presented in the Documentation Report. Emission factors
(Group 1B) and basin factors (Group 1C) can also be updated by the User.
7) In Group 2, Users can select the source categories (all or select) and geographic area of interest
(Steps 1-7). Users can run the
"MACRO_RESET_CATEGORY_AND_GEOGRAPHIC_LEVELS_OF_INTEREST" macro to reset
choices.
8) After the source categories and geographic area have been chosen, proceed to the Group 3
which includes point source activity adjustment tables by source category. Currently, ALL point
source activity adjustments (e.g. county-level point source gas well counts, county-level point
source oil well counts, county-level barrels of oil, etc.) are defaulted to zero (i.e., no point source
activity adjustments) It is encouraged that point source activity adjustments have priority
over point source emission adjustments. Additionally, Users should pay careful attention
to ensure that the point source activity data is entered in the same units as the nonpoint
activity data. Users should refer to the "004_NONPOINT_POINT_SOURCE_CROSSWALK" to
identify point source SCCs.
9) After any point source activity adjustments have been made directly in the Group 3 data tables,
proceed to Group 4 "Run Estimation Queries". Run Step 8 to generate the unadjusted nonpoint
emission estimates.
10) Proceed to Group 5 to make any point source emission adjustments directly in the emission
tables. Point source emission estimates are to be entered in the "POINT_EMISSIONS_TPY" field
It is important to note that if point source activity adjustments were made in the Group 3
tables, then point source emission adjustments should NOT be made in these tables for
-------
overlapping SCCs. Also, point source emission adjustments need to be entered as tons
per year (TPY).
11) After point source emission adjustments are made (if applicable), then the User should proceed
to Group 6.
12) Run Step 9 to finalize the county-level emissions. Run Step 10 to generate the Emission
Inventory System (EIS) data tables.
13) After running Step 10, the User will be prompted to enter information in the CERS and
ExchangeHeader staging tables. Once completed, the EIS Staging Tables are ready.
Additional notes:
1) Group 7 A provides the emissions in standardized format, as well as selected summary queries.
2) In the EIS data tables (Group 7B), the CERS, ControlApproach, ControlMeasure,
ControlPollutant, Emissions, EmissionsProcess, ExchangeHeader, Location, and
ReportingPeriod are populated.
3) EPA's EIS Bridge Tool (https://eis.epa.gov/eis-svstem-web/downloads/bridae.inlp) can be used to
generate the .xml file needed for EIS upload.
4) Group 7C are the EIS code tables provided for reference.
5) If the User wishes to reset the tool, and regenerate the emissions, the following steps are
recommended:
a. Run the Group 8 Macros
b. Compact and Repair the database
c. Proceed to the Group 2 Macros to reset the geographic levels of interest.
6) Groups 9 and 9A-9R contains the algorithm queries needed to generate the emissions.
7) Group 9Z contains routines to compile emissions into the "COMPILED_EMISSIONS_ALL" table.
8) Group 10 contains additional background information and routines needed to populate the
emissions in standardized formats.
-------
United States Office of Air Quality Planning and Standards Publication No. EPA-454/B-19-033
Environmental Protection Air Quality Assessment Division November 2014
Agency Research Triangle Park, NC
-------