Development of Gasoline Fuel Effects in
the Motor Vehicle Emissions Simulator
(MOVES2009)
Draft Report
United States
Environmental Protection
Agency
-------
Development of Gasoline Fuel Effects in
the Motor Vehicle Emissions Simulator
(MOVES2009)
Draft Report
Assessment and Standards Division
Office of Transportation and Air Quality
U.S. Environmental Protection Agency
v>EPA
NOTICE
This technical report does not necessarily represent final EPA decisions or
positions. It is intended to present technical analysis of issues using data
that are currently available. The purpose in the release of such reports is to
facilitate the exchange of technical information and to inform the public of
technical developments.
United States EPA-420-P-09-004
Environmental Protection . ^ „„_
Agency August 2009
-------
Table of Contents
1. Introduction 1
2. Structure of the Draft MOVES2009 Fuel Algorithm Database 2
2.1 Fuel Formulation Table 2
2.2 Fuel Supply Table 3
2.3 Fuel Adjustment Table 3
2.4 Hydrocarbon Speciation Table 7
3. Process Used to Create MOVES Fuel Adjustments 10
3.1 Extracting the Primary Data from NMIM 10
3.2 MOVES Fuel Binner 11
3.3 Predictive Model Effects 14
3.4 Complex Model Effects 15
3.5 Sulfur Effects 16
4. Process Used to Create Air Toxic Adjustment Factors 16
5. Use of the MOVES Fuel Algorithm in RFS2 20
Appendix A MySQL Code for the Fuel Binner 23
-------
1. Introduction
This report discusses the development of the fuel adjustment factors to the basic
emission rates which are contained in the Draft MOVES2009 version released in April,
2009, and those fuel effects generated and used in the Draft RFS2 Notice of Proposed
Rulemaking (NPRM) modeling efforts of December, 2008. This report and its
appendices describes the technical development of the draft MOVES model fuel
parameters (version December 15, 2008) and the analysis / programming process used to
develop the fuel adjustment factors for the RFS2 inventory modeling. The first section
describes the structure and design of the MOVES model, and its accompanying data
structures. The second section gives a brief overview of the individual emission fuel
models, their use in the MOVES context, and the process used to generate fuel
formulations, fuel adjustment factors and fuel supply distributions. The third section is
an Appendix which contains all of the data scripts used in the MOVES process. The
detail oriented reader will find all of the calculations and database manipulations
documented in detail in these appendices. Because of the volume, such algorithms are
not reproduced in the general text. Finally, this document contains only limited and
generalized results from the model. It does not contain the specific results from
regulatory analysis runs or sensitivities.
EPA is planning, and has partially completed, a completely revised fuel effects
algorithm for inclusion in the proposed Final December, 2009 release of the MOVES
model. This new algorithm was also used for emission inventory analysis in the Final
RFS2 Rulemaking (August, 2009). In the new version, the entire fuel calculation
algorithm was rewritten (substantial new MOVES coding and database structures have
been added), and new fuel algorithm effects will be inserted based on recent Energy
Policy Act (EPAct testing). These new effects are mostly confined to the topics of
ethanol fuel effects and biodiesel effects. However, new fuel effects have also been
developed for conventional gasoline vehicles with Tier2 (2004+ MY) certifications. The
RFS2 rulemaking modeling also required that MOVES contain both a 'more sensitive'
and 'less sensitive' case for Tierl and later model years, whereas, Draft MOVES2009
(the algorithm in the current document) contains only the 'less sensitive' case. In this
context, 'more sensitive' means the fuel adjustment effects were a function of a range of
fuel parameters such as fuel sulfur level, RVP, oxygen content, distillation fractions, etc.,
and 'less sensitive' means the fuel adjustment effects were only a function of fuel sulfur
level. The basic structure of the new algorithm is also different and this change will also
likely cause some minor change in the overall fuel effects. A new report(s) which
discusses the Final December, 2009 model has yet to be written (expected in September,
2009), but is planned prior to the release of the Final MOVES model in December, 2009.
This new report will discuss the new fuel algorithm in detail and present some limited
results.
-------
2. Structure of the Draft MOVES2009 Fuel Algorithm
Database
The MOVES model is a data driven model that consists primarily of a central
database that is manipulated using a Java based GUI and a series of complex MySQL
scripts. The MOVES program also allows the use of alternative user defined data tables
in the program.
The Draft MOVES2009 Fuel Algorithm consists of four primary data tables.
These are:
FuelFormulation
Fuel Supply
Fuel Adjustment
HCSpeciation
2.1 Fuel Formulation Table
The FuelFormulation table contains a table of fuel formulations 'keyed' using an
arbitrary fuelformulationID variable, and a set of fuel. The fuel subtype designates fuel
classifications such as conventional gas, reformulated gas, gasohol (i.e, 10% ethanol),
and E-85/E70 ethanol-gasoline blends. The model contains several hundred individual
fuelformulationlDs which cover the range of all important fuel properties.
MOVES Fuel Formulation Table Structure
fuelFormulationID key field
fuelSubtypelD
RVP
sulfurLevel
ETOHVolume
MTBEVolume
ETBEVolume
TAMEVolume
aromaticContent
olefinContent
benzeneContent
e200
e300
volToWtPercentOxy
-------
2.2 Fuel Supply Table
The Fuel Supply table contains the market share data for each fuel formulation as
a function of calendar year, month and county (United States). This information varies
considerably by calendar year and county with new fuel formulations being phased-in
over time. The Draft MOVES2009 model contains fuel supply information for the 1999
through 2012 calendar years, and allows the user to input specific data for a county / year
/ month combination. Market share value for gasoline, diesel and ethanol based fuels are
available. The values typically vary by season. Fuel market share information is
provided for all 3,222 counties and is based on in-use fuel surveys and econometric
modeling projections for future calendar years (the primary data for the future
projections is taken from EPA 's NMIM model which contains data used in National
Emission Inventory process1. The market share values are used to weight the effects of
different fuelformulationID together to obtain an overall average fuel effect / fuel
adjustment. The market share information is a function of county, calendar year (i.e.,
fuelYearlD) and month. The marketShareCV is the coefficient of variation in the
marketShare variable and is used for uncertainty calculation in the model. It is currently
empty.
MOVES Fuel Supply Table Structure
CountylD key field
fuelYearlD key field
monthGroupID key field
fuelFormulationID key field
marketShare
marketShareCV
2.3 Fuel Adjustment Table
The Fuel Adjustment table contains all of the MOVES fuel adjustments. They are
a function of pollutant - total hydrocarbon, carbon monoxide and nitrogen oxides (Total
1 Document Id EPA-HQ-OAR-2005-0161-DRAFT-0920
Document Location Docket EPA-HQ-OAR-2005-0161 Phase NPRM - Notice of Proposed Rulemaking
Sequence 3
Document Title: Using MOVES to Generate Inventories for the RFS2 NPRM
Memorandum to Docket from Amanda Valente, Megan Beardsley, David Brzezinksi,
Ed Glover & Prashanth Gururaja
U.S. EPA/OTAQ/ASD/AQMC
November 2008
-------
HC, CO and NOx) - process (start, running, etc), fuel formulation, model year group and
source type. Fuel Adjustment factors were developed for each of the following model
year groups.
1974
19751986
19871989
19901993
1994
1995
1996
19972000
20012003
2004
2005
2006
2007
20082009
20102050
Fuel Adjustment factors were developed for the following sourcetypelDs:
11 Motorcycle
21 Passenger Car
31 Passenger Truck
32 Light Commercial Truck
41 Intercity Bus
42 Transit Bus
43 School Bus
51 Refuse Truck
52 Single Unit Short-haul Truck
53 Single Unit Long-haul Truck
54 Motor Home
61 Combination Short-haul Truck
62 Combination Long-haul Truck
The MOVES model fuel adjustment factor is also a strong function of specific
fuel properties. Most of these effects are non-linear and were derived from the EPA's -
Complex model, Predictive model and MOBILE6 Sulfur model (labeled as Primary EPA
Fuel Models in the remainder of this document). These effects are utilized in MOVES by
linking the fuel formulation table and the fuel adjustment table using the variable
fuelF ormul ati onID.
All of the MOVES fuel adjustment factors are multiplicative correction factors to
the basic emission factor in MOVES. This is shown mathematically in the simple
equation (Eq 1) below.
-------
Fuel Corrected Emissions = Fuel Adjustment Factor * Base Emissions Factor
Eql
The Base Emission Factor variable in Eq 1 represents the base emission rates
computed by MOVES. These were computed primarily from Arizona IM240 lane data,
for pre-2001 model years, and computed from EPA in-use vehicle surveillance testing for
model years 2001 through 2006. Base emission factors for 2007 and later model years
were computed from multiplicative adjustment factors that ratio the existing post-2000
calendar year emission, and the future Tier2 emission standard bins.
The Fuel Adjustment Factors are created from emission results obtained by
running the Primary EPA Fuel Models for all combinations of in-use fuel formulations.
The actual Fuel Adjustment Factor for a given fuel (i) is the ratio of the emissions from
fuel (i) and the emissions from the reference fuel (see Eq 2) using the Primary EPA Fuel
Models.
Fuel Adjustment Factor (fuel i) = emission result(fuel i) / emission result (reference fuel) Eq2
Two reference fuels were used in the development of the MOVES Fuel
Adjustment Factors, and their properties are shown in Table 1. The use of only two
average reference fuels clearly incorporates considerable uncertainty since the emission
rates are based on in-use testing of vehicles using many different and unknown fuels.
The two fuels selected as reference fuels were thought to be the most representative of
those in place where and when the vehicles were tested: Phoenix, AZ in summer months,
1995 through 2002.
Table 1
Reference Fuel Properties
Fuel Property
Fuel Subtype
RVP (psi)
Sulfur Level (ppm)
Ethanol Volume (vol%)
MTBE Volume (vol%)
TAME Volume (vol%)
Aromatic Content (vol%)
Olefm Content (vol%)
Benzene Content (vol%)
E200 (F)
E300 (F)
Pre-2001 Reference Fuel
Conventional Gasoline
6.9
90
0
0
0
26.1
5.6
1.0
41.1
83.1
2001+ Reference Fuel
Conventional Gasoline
6.9
30
0
0
0
26.1
5.6
1.0
41.1
83.1
-------
The two reference fuels are identical except for the fuel sulfur levels of 30 ppms
sulfur (fuelformulationid=98) and 90 ppm sulfur (fuelformulationid=99). The reference
fuel with a sulfur level of 30 ppm is used to create Fuel Adjustment Factors for all 2001
and later model years, and the value of 90 ppm is used for all 2000 and earlier model
years. The value of 30 ppm was chosen for the late model vehicles because it is likely
that these model years are more likely to be exposed to the lower sulfur fuel than the
older model years. Using a 90 ppm sulfur reference fuel on nLEV and Tier2 vehicles
which were generally certified or operated on lower sulfur fuels would over-state the
effects of sulfur on new and future vehicles.
The Fuel Adjustment table contains both the fuel Adjustment and the
fuelAdjustmentGPA variables. The "GPA" (Geographic Phase-in Area) refers to a
group of counties in the western U.S. that had special gasoline sulfur requirements under
Tier 2 regulations. See 65 Fed. Reg.6755-6759 for more information on the GPA. The
values are identical except for specific counties and fuelMYGroups 2004, 2005 and 2006.
In those cases, the fuelAdjustmentGPA is slightly higher than the fuel Adjustment
because it accounts for the irreversible effects of higher fuel sulfur levels
In MOVES, if a particular county / year / month bin has more than one fuel
formulation (a common situation), the fuel adjustment factors are weighted using the
market share data. See Equation 3 below. The exhaust emissions model contains no
logic to account for the mixing of various fuels in a given vehicle's fuel tank
(commingling). Thee Tank Fuel Generator used for TVV does adjust RVP to account for
commingling.
Fuel Adjustment Factor = Adjustmentl * market sharel + Adjustment! * market
share! + ... + Adjusment i * market share i Eq 3
MOVES Fuel Adjustment Table Structure
polProcessID key field
fuelMYGroupID key field
sourceTypelD key field
fuelFormulationID key field
fuel Adjustment
fuelAdjustmentCV
fuel Adj ustmentGP A
fuel Adj ustmentGP AC V
-------
2.4 Hydrocarbon Speciation Table
The HCSpeciation table provides factors which convert the base emission factors
which are in terms of total hydrocarbon emissions into hydrocarbon emissions of other
terms. These are volatile organic compounds (VOC), non methane hydrocarbons
(NMHC), total organic gases (TOG) and non methane organic gases (NMOG). See
Table 2 below for the relationship between the various hydrocarbon speciation types.
The MOVES model uses a 'chaining' method of calculation for the individual
hydrocarbon speciation types. All of them start with the Total Hydrocarbon (THC)
pollutant.
Table 2
Hydrocarbon Speciation Types
PollutantID
1
79
87
86
80
PollutantName
Total
Hydrocarbons
Non Methane
Hydrocarbons
Volatile Organic
Compounds
Total Organic
Gases
Non Methane
Organic Gases
FIDHC
Yes
Yes
Yes
Yes
Yes
Methane
Yes
No
No
Yes
No
Ethane
Yes
Yes
No
Yes
Yes
Aldehydes
No
No
Yes
Yes
Yes
The structure of the MOVES HCSpeciation table is:
MOVES HCSpeciation Table Structure
polProcessID key field
fuelMYGroupID key field
fuelFormulationID key field
speciationConstant
oxySpeciation
The HCSpeciation table contains data for only the hydrocarbon pollutants listed
above in Table 2. The fuelMYGroups and the fuelFormulationID keys have the same
definitions as those used in the Fuel Adjustment table. The HC speciated compounds of
VOC (volatile organic compounds) and NMOG (non methane organic gases) utilize the
coefficients speciationConstant and oxySpeciation in a linear equation. The factors are a
function of the oxygenate type (if present in the fuel). If a given target fuel formulation
contains a mixture of oxygenate types that includes ethanol, the model will use the
ethanol factors. If the mixture does not contain ethanol it will use the MTBE factors, and
if it does not contain either ethanol or MTBE, it will utilize the TAME factors. Non
methane hydrocarbons (NMHC) are computed by subtracting methane emissions from
total hydrocarbon emissions. The methane emissions are calculated in MOVES using
-------
independent emission factors derived from methane emission data (see MOVES Methane
emission calculation report). Total organic emissions (TOG) are calculated by adding
methane emissions to the NMOG emission factors. See Equations 3, 4, 5 and 6 for
details.
The HCSpeciation table contains data for only the hydrocarbon pollutants listed
above in Table 2. The fuelMYGroups and the fuelFormulationID keys have the same
definitions as those used in the Fuel Adjustment table. The HC speciated compounds of
VOC (volatile organic compounds) and NMOG (non methane organic gases) utilize the
coefficients speciationConstant and oxySpeciation in a linear equation. The factors are a
function of the oxygenate type (if present in the fuel). Non methane hydrocarbons
(NMHC) are computed by subtracting methane emissions from total hydrocarbon
emissions. The methane emissions are calculated in MOVES using independent emission
factors derived from methane emission data. Total organic emissions (TOG) are
calculated by adding methane emissions to the NMOG emission factors. See Equations
3, 4, 5 and 6 for details.
NMHC = THC - Methane Eq 3
VOC = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*ETOHVolume) Eq 4a
For fuels containing ethanol (fuelsubtypelD = 12, 13, 14, 51, 52, or 53)
VOC = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*MTBEVolume) Eq 4b
For fuels containing MTBE
VOC = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*ETBEVolume) Eq 4c
For fuels containing ETBE
VOC = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*TAMEVolume) Eq 4d
For fuels containing TAME
NMOG = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*ETOHVolume) Eq 5a
For fuels containing ethanol (fuelsubtypelD = 12, 13, 14, 51, 52, or 53)
NMOG = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*MTBEVolume) Eq 5b
For fuels containing MTBE
-------
NMOG = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*ETBEVolume) Eq 5c
For fuels containing ETBE
NMOG = NMHC *
(speciationConstant + oxySpeciation* volToWtPercentOxy*TAMEVolume) Eq 5d
For fuels containing TAME
TOG = NMOG + Methane Eq 6
-------
3. Process Used to Create MOVES Fuel Adjustments
This section is an overview of the process and algorithms used to develop the
MOVES fuel formulations, fuel supply information and fuel adjustments. Specific
details of individual database manipulations and calculations can be found in the
Appendix at the end of this document.
3.1 Extracting the Primary Data from NMIM
The primary fuel formulation and fuel supply data were obtained from EPA's
NMEVI model and national emission inventory (NET) processs. The NMEVI model is
currently used to develop national emission inventories every three years. NMIM
currently contains fuel formulation and fuel supply data and projections for all calendars
years from 1999 through 2030. Eventually, all these data will be extracted, updated if
necessary and fully processed into the MOVES model. However, as an initial product for
the 2008 / 2009 MOVES model release, and for support of EPA's RFS2 Rulemaking,
only fuel data from calendar years 2005 and 2022 were extracted and used. Draft
MOVES2009 and the RFS2 NPRM & AQ model used different fuel supplies. Draft
MOVES2009 goes only to 2012.
For calendar year 2005, a database called NMIMRFS2Fuels2005Base was
created. It contains detailed fuel formulation and fuel supply data for calendar year 2005
collected from in-use fuel surveys. It contains both fuelformulation data for several
thousand individual in-use fuels and fuel supply data from all 3,222 US counties on a
seasonal basis. The analysis process used to create this database was done under EPA
contract by Eastern Research Group and has been fully documented in the EPA 2007
Renewable Fuels Standard (RFS1) Rulemaking documentation.
Four additional databases were created for calendar year 2020 / 2022. These are:
Database: RFS2Frm2022Fuels.
Name
Rfs2Frm2022Aeo
Rfs2Frm2022Rfsl
Rfs2Frm2022E10
Rfs2Frm2022E85
From Spreadsheet
fuel parms for AQMC 11-
26.xls
fuel parms for AQMC 12-04 -
RFS1 refcase.xls
fuel parms for AQMC 12-01 -
RFS2 policy case.xls
fuel parms for AQMC 11-
19.xls
Actual Database Name
NmimRfs2Fuels2022AEO.zip
NmimRfs2Fuels2020rfsl .zip
NmimRf s2Fuel s2022e 1 0 .zip
NmimRf s2Fuel s2022e8 5 . zip
10
-------
E10 and E85 are for the EISA control case, but they will be run separately in
MOVES and weighted together. In NMIM, which will be used for motocycles,
diesel, and nonroad, the E10 case will be run. The E85 database will be prepared in
NMIM, because that is the basis for creating MOVES fuels tables.
The 2022 Reference fuel database containing fuel supply projections by county,
month and fuel formulation was created from two sources. These sources were the
output of EPA refinery modeling under contract, which provided the bulk of the
information, and projections from Advanced Energy Outlook (AEO). The AEO
projections were the volume of E10 (a 90% gasoline / 10% ethanol mixture) penetration
into the market. Ethanol market share information is important because the focus of the
RFS2 rulemaking is ethanol production, marketing and emission effects. The 2022
Control fuel database is virtually the same as the Reference fuel database except it
assumes that E10 fuels have 100 percent market share in 2022.
3.2 MOVES Fuel Binner
A real in-use gasoline fuel can vary continuously according to all of the fuel
properties listed in Table 1. However, the Draft MOVES2009 model design does not
currently process such fuel formulation information; the database lists it only for
reference and for use in some special cases such as sulfate emissions and tank vapor
venting. In general, the MOVES Fuel Binner organizes specific NMIM fuels into bins
according to the eleven fuel properties listed in Table 1, and assigns average values.
These average fuel property values are then processed outside the MOVES model in the
Predictive, Complex and EPA Sulfur models. This process creates fuel adjustment
factors and air toxic pollutant factors which are used in MOVES.
The MOVES fuel adjustment factors design was created as a compromise
between utilizing detailed fuel information and the need to streamline processing in the
MOVES model. Some pollutants are sensitive to certain fuel properties and detail is
required for accuracy. In these cases the MOVES Fuel Binner was designed to capture
these effects. On the other hand, every possible combination (i.e., greater than 10,000) of
eleven fuel properties, twelve source / vehicle types, three fuel types, nine pollutants and
ten model year groups quickly overwhelms the MOVES model making some aggregation
necessary.
We plan to revise final MOVES2009 to perform the predictive / complex model
calculations directly in the model. This will improve the performance issues associated
with the current version of MOVES and allow for accurate determination of fuel factors
for any reasonable fuel formulation. However, draft MOVES2009 does not contain this
capability.
11
-------
All 10,000+ NMIM fuel formulations were binned according to the eleven fuel
properties listed in Tables 1 and 3. Table 3 shows the bins which were created and their
definitions. A particular MOVES fuelformulationID has one and only one value for
each of the fuel property bins. The column Avg Value does not typically represent an
average value of the fuels in the bin. Instead it is more likely a 'mode' value (most of the
individual data points in the bin had the Avg Value).
12
-------
Table 3
Gasoline Fuel Property Bin Definitions
Fuel Property
Fuel Subtype
Conventional Gas
RFG
Ethanol 10vol%
Ethanol 8 vol%
Ethanol 5 vol%
Diesel
RVP (psi)
RVP (psi)
RVP (psi)
RVP (psi)
RVP (psi)
RVP (psi)
RVP (psi)
RVP (psi)
Sulfur Level (ppm)
Sulfur Level (ppm)
Sulfur Level (ppm)
Sulfur Level (ppm)
Sulfur Level (ppm)
Sulfur Level (ppm)
Sulfur Level (ppm)
Sulfur Level (ppm)
Sulfur Level (ppm)
Ethanol Volume (vol%)
Ethanol Volume (vol%)
Ethanol Volume (vol%)
Ethanol Volume (vol%)
MTBE Volume (vol%)
MTBE Volume (vol%)
Bin ID
10
11
12
13
14
20
1
2
3
4
5
6
7
8
1
2
3
4
5
6
7
8
9
1
2
3
4
1
2
Avg
Value
6.9
7.5
8.7
9.2
10.0
11.5
13.5
15.0
5.0
15.0
30.0
50.0
90.0
180.0
280.0
400.0
600.0
0
5.0
8.0
10.0
0
2.5
Lower Bound
(>)
0
7.2
8.2
9.0
10.0
11.4
13.4
14.9+
0
10
25
35
70
120
210
300
500+
0
0
6.0
8.0+
0
0
Upper Bound
(<=)
7.2
8.2
9.0
10.0
11.4
13.4
14.9
10
25
35
70
120
210
300
500
0
6.0
8.0
0
5.0
13
-------
MTBE Volume (vol%)
MTBE Volume (vol%)
TAME Volume (vol%)
TAME Volume (vol%)
TAME Volume (vol%)
Aromatic Content (vol%)
Aromatic Content (vol%)
Aromatic Content (vol%)
Olefin Content (vol%)
Olefin Content (vol%)
Olefin Content (vol%)
Benzene Content (vol%)
Benzene Content (vol%)
Benzene Content (vol%)
Benzene Content (vol%)
Benzene Content (vol%)
E200 (F)
E200 (F)
E200 (F)
E300 (F)
E300 (F)
E300 (F)
3
4
1
2
3
1
2
3
1
2
3
1
2
3
4
5
1
2
1
2
3
8.0
11.0
0
0.015
0.06
17.5
26.1
32.0
5.6
9.2
11.9
0.35
0.65
0.65
1.50
3.50
41.0
50.0
78.6
83.0
89.1
5.0
10.0+
0
0
0.03+
0
20.0
30.0+
0
8.0
10.0+
0
0.40
0.60
0.80
2.00
0
47.0+
0
80.0
87.0+
10.0
0
0.03
20.0
30.0
8.0
10.0
0.40
0.60
0.80
2.00
47.0
80.0
87.0
After binning the 10,000+ NMIM fuel formulations into a more manageable set of
less than 500 MOVES fuel formulations (in 2022 in some RFS2 runs there were less than
200 fuel formulations), the MOVES fuel adjustment factor process used an algorithm to
create the specific fuel adjustment factors for each of the MOVES fuel / pollutant-process
combinations. These fuel effects are contained in the EPA Predictive Model, the EPA
Complex Model and the EPA Sulfur Model.
3.3 Predictive Model Effects
The EPA Predictive Fuel model is one of EPA's more recent fuel models used to
predict the impact on HC and NOx emissions from varying gasoline fuel properties (i.e.,
six of the eleven fuel properties listed in Table 1). This model was developed by EPA
14
-------
from vehicle and fuel testing done prior to calendar year 2001. The details of the testing
and the statistical analysis of the results is quite complex and will not be reproduced in
this document. The reader is encouraged to consult the EPA technical document
EPA420-R-01-016, "Analysis of California's Request for Waiver of theReformulated
Gasoline Oxygen Content Requirement for California Covered Areas" of June, 2001 for
background on the Predictive Model. Appendix A of this current document also contains
a listing of the MySQL code used to implement the EPA Predictive Fuel Model.
In MOVES the EPA Predictive Fuel Model was used only to model fuel effects of
HC and NOx emissions. The MOVES Predictive Fuel Model differs from the standard
model in that some of the effects of sulfur were removed from the model by normalizing
to 30 ppm sulfur for 2001 and later model years and 90 ppm for pre-2001 model years.
The MOVES sulfur effects are the MOBILE6.2 sulfur effects. These include both the
short term and long term irreversible effects. For complete details see the EPA document
EPA420-R-01-039 "fuel Sulfur Effects on Exhaust Emission - Recommendations for
MOBILE6". The effect of High Emitters is also different between the EPA Predictive
Model and the MOVES Predictive model. The EPA Predictive Model assumes a 50
percent weighting for High Emitters, and MOVES does not use the concept of High and
Normal Emitters. For the purposes of this analysis, a weighting of 20 percent High
Emitters was used to generate the MOVES fuel effects. This lower percentage of High
Emitters better reflects the modern vehicle fleet. As for some of the RFS2 runs, (i.e., less
sensitive runs - fuel effects are a function of fuel sulfur level only for Tierl and later
model years). The effects of all fuel parameters except sulfur were removed from the
final fuel adjustment factors for Tierl (1994 and later model years) vehicles. This was
done to examine the effects of the assumption that the more advanced Tierl and later
vehicles are no longer sensitive to the effects of many of the individual fuel parameters.
3.4 Complex Model Effects
The MOVES fuel adjustment factors for CO emissions were developed from the
EPA Complex Fuel Model. This model is an older model and is based on data from late
1980's and early 1990's. It was used in MOVES only for non-sulfur fuel effects for CO
emissions, because it contains the most up-to-date assessment of these effects. The sulfur
CO emission effects were taken from MOBILE6 and are based on the same document
reference above in the Predictive Model section. For more details regarding EPA's
complex model, the reader is referred to the website :
http://www.epa.gov/otaq/regs/fuels/rfg/58-11722.txt
and the document
"Regulation of Fuels and Fuel Additives: Standards for Reformulated
Gasoline - Proposed Rule"
15
-------
Appendix A of this current document also contains a listing of the MySQL code used to
implement the EPA Complex Fuel Model for CO emissions.
3.5 Sulfur Effects
The sulfur effects were taken from the MOBILE6.2 document "Fuel Sulfur
Effects on Exhaust Emissions - Recommendations for MOBILE6. - EPA420-R-01-039".
These effects, based on more up to date testing, supercede the sulfur effects in the EPA
Predictive and Complex Models. These effects include the short term sulfur effects, the
long term effects and the effects of irreversibility. The report also included separate fuel
sulfur effects for running and start processes.
4. Process Used to Create Air Toxic Adjustment
Factors
The MOVES model reports air toxic emission inventories based on air toxic
emission adjustment factors which are built into the MOVES model. The air toxic
pollutants which are reported by Draft MOVES2009 are:
Benzene
Ethanol
MTBE
1,3 Butadiene
Formaldehyde
Acetealdehyde
Naphthalene
Acrolein
Other air toxic pollutants such as metals, and dioxin / furan compounds will likely
be added in the future. The Draft MOVES2009 model reports air toxic pollutants as a
function of gasoline, diesel and ethanol (E-85) fuels.
Equation 7 shows the general formula which is used to compute all the air toxic
pollutants in Draft MOVES2009 except Naphthalene (which is a function of total
particulate matter emissions). The equation is applied separately and with different
factors for both running and start emissions. Currently, in MOVES and for the RFS 2
Rulemaking, the air toxic pollutants are a function total hydrocarbon emissions (i.e., the
Hydrocarbon Emission Rate in Eq 7). In the future, all of them will be a function of
16
-------
volatile organic compounds (VOC). The Hydrocarbon Emission Rate used in Equation 7
is a fully adjusted emission rate, adjusted for fuel, temperature, I/M and all other
correction factors. The Air Toxic Factors are applied last in the chain of MOVES
multiplicative calculations.
Air Toxic Pollutant Emission Rate = Hydrocarbon Emission Rate * Air Toxic Factor Eq 7
In Draft MOVES2009, the Air Toxic Factor differs by pollutant, process, model
year, sourcetype (vehicle type) and fuel formulation for benzene, MTBE, 1,3 Butadiene,
Formaldehyde and Acetaldehyde. All of the values used in MOVES for these pollutants
were taken from the MOBILE6.2 model. The MOBILE6.2 air toxics model was
developed from the 1993 EPA Complex Model, and is based primarily on data from 1990
and earlier model year vehicles. The extraction of data from the MOBILE6.2 model was
an empirical process that involved running MOBILE6.2 over 20,000 times, and
computing and averaging the ratio of the various air toxic pollutant emissions and the
hydrocarbon emissions for both start and running emissions. To make the analysis
manageable, ratios were computed only at eight years of age for all source types (vehicle
types) and model years. The use of the ratio at eight years old for all vehicle ages could
lead to some small differences between the MOVES air toxic ratios and those computed
in the Complex model (MOBILE6.2 is based on the Complex model), but this difference
is expected to be small.
The Air Toxic Factors for Naphthalene and Acrolein differ only by source type
(vehicle type) and fuel type. These factors were also taken from MOBILE6.2 but are not
factors of model year and age, and are mostly constants in the MOVES model.
The Air Toxic Factors for ethanol (shown in Table 4) are based on a literature
search of new data and reports. The reports from three studies were used in the search /
analysis. They are:
• Southwest Research Institute. 2007. Flex Fuel Vehicles (FFVs) VOC/PM Cold
Temperature Characterization When Operating on Ethanol (E10, E70, E85).
Prepared for U. S. Environmental Protection Agency. Available in Docket EPA-
HQ-OAR-2005-0161
• Graham, L. A.; Belisle, S. L. and C. Baas. 2008. Emissions from light duty
gasoline vehicles operating on low blend ethanol gasoline and E85. Atmos.
Environ. 42: 4498-4516.
• Environment Canada. 2007. Comparison of Emissions from Conventional and
Flexible Fuel Vehicles Operating on Gasoline and E85 Fuels. ERM Report No.
05-039, Emissions Research Division. Available in Docket EPA-HQ-OAR-2005-
0161
Table 4 shows an average value for the air toxic to total hydrocarbon emissions
ratio and the range of values used in MOVES for calendar year 2005. These are for
17
-------
gasoline vehicles. The values represent the average of all 360 fuel formulations used in
the calendar year 2005 fuel dataset. The relatively large standard deviations for Benzene
and MTBE reflect the strong function of individual fuel formulation on the average
results. Benzene and MTBE emissions are a strong function of the benzene volume,
aromatic content or MTBE volume in the specific fuel.
Table 4
Typical Gasoline / Ethanol Air Toxic Ratios for Calendar Year 2005
Benzene
MTBE
Naphthalene
1,3 Butadiene
Formaldehyde
Acetaldehyde
Acrolein
Ethanol - EO
Ethanol -E10
Ethanol - E85
Gasoline Vehicles
MinAT
Ratio
0.032
0.00
0.088
0.0038
0.0097
0.0036
0.00061
AvgAT
Ratio
0.050
0.0017
0.088
0.0055
0.013
0.0070
0.00061
0.00
0.024
0.484
Max AT
Ratio
0.086
0.018
0.088
0.0066
0.016
0.013
0.00061
Std Dev
0.0082
0.0048
0.00
0.00063
0.0012
0.0032
0.00
Ethanol
Vehicles
E-85
Ratios
0.0041
0.00
0.086
0.00062
0.010
0.075
0.00027
Table 5 shows the air toxic to THC ratios for diesel vehicles as a function of
pollutant. These were taken from MOBILE6.2. They are constants due to a lack of test
data. Specific diesel fuel properties (i.e., sulfur level) are currently assumed not to affect
air toxic emission ratios.
18
-------
Table 5
Diesel Vehicle Air Toxic Ratios
Pollutant
Benzene
Ethanol
MTBE
Naphthalene
1,3 Butadiene
Formaldehyde
Acetaldehyde
Acrolein
Air Toxic Ratio
0.020
0.00
0.00
0.0037
0.0090
0.039
0.012
0.0035
19
-------
5. Use of the MOVES Fuel Algorithm in RFS2
The MOVE Fuel Algorithm was used in the RFS2 inventory development process
to estimate the effects of fuel parameters on HC, CO and NOx emissions from light and
heavy-duty gasoline vehicle applications. The fuel effects are a simple multiplicative
adjustment factor applied to the base emission factors that account for the effects of
various fuel properties and the distribution of particular gasoline fuel throughout the
United States. Separate factors were generated for HC, CO, NOx and the evaporative
process of permeation.
The analysis started with the development of three datasets of national fuels (fuel
supply and fuelformulation data) according to individual county, year and month. Thus,
each of the 3,222 individual United States counties had a set of specific fuels (with
market shares) for each of the twelve months and for the two years of analysis interest.
These years were the base year of 2005 and the control/reference year of 2022.
The base year of 2005 contained a set 261 fuel formulations that represent every
fuel used in the United States in calendar year 2005. These fuels run the spectrum for all
of individual fuel parameters (low to high sulfur, no ethanol to E-85, etc). Also, since the
fuel adjustment factors are also a function of model year group and source type (vehicle
type), the base year analysis contained a total of 51,285 different fuel adjustment factors.
These had the following range of values by pollutant.
Table 6
Average Fuel Adjustment for RFS2 Base Year 2005
Pollutant / Process
Running HC
Start HC
Running CO
Start CO
Running NOx
Start NOx
Average Adjustment
1.044
1.041
1.150
1.128
1.211
1.211
Std deviation
0.12
0.11
0.31
0.28
0.20
0.20
The fairly large standard deviations illustrate the fairly large range of possible fuel
adjustment factors. These factors range so much because in 2005 the individual fuel
properties such as sulfur, conventional gas versus E-10, aromatics, etc., varied widely.
For example, a low sulfur fuel has a lower adjustment factor versus a high sulfur fuel.
Also, the average fuel adjustments shown in Table 6 do not include E-70 or E-85 fuel
effects.
20
-------
Table 7
Average Fuel Adjustment for RFS2 Control Year 2022
Sensitivity Case
Pollutant / Process
Running HC
Start HC
Running CO
Start CO
Running NOx
Start NOx
Average Adjustment
0.94
0.98
0.97
1.03
1.11
1.10
Std deviation
0.05
0.04
0.25
0.24
0.04
0.04
The Control fuel dataset consisted of a set of fuels that were composed of all E-10
(10 vol% ethanol). Other fuel properties varied so there are 114 individual fuels in this
dataset. This dataset was developed to model the effect of 100 percent penetration of E-
10 in the fleet plus additional areas with E-85 fuel. The average fuel adjustments shown
in Table 7 do not include E-70 or E-85 fuel effects.
Table 8
Average Fuel Adjustment for RFS2 Reference Year 2022
Sensitivity Case
Pollutant / Process
Running HC
Start HC
Running CO
Start CO
Running NOx
Start NOx
Average Adjustment
0.95
0.99
0.99
1.05
1.10
1.09
Std deviation
0.06
0.04
0.24
0.23
0.05
0.05
The Reference fuel dataset consisted of a set of fuels that were composed of both
E-10 (10 vol% ethanol) and conventional gasoline fuels in 2022. Other fuel properties
varied so there are 139 individual fuels in this dataset. The majority (106 out of 139) of
the fuels in this dataset were E-10 fuels. This dataset was developed to model a case
where conventional gasoline fuels are also present in some degree. Ethanol 85 vol% (E-
85) fuels are also present in this scenario. However, the fuel adjustments shown in Table
8 do not include E-70 or E-85 fuel effects.
21
-------
For both the 2022 Control and Reference cases, an additional permutation was
done that created two additional fuel datasets. This permutation was the creation of a
Sensitivity and a Primary dataset. Tables 7 and 8 show the average effects from the
Sensitivity case since it includes effects from all of the EPA fuel models (Complex
model, Predictive model and the MOBILE6.2 Sulfur model). The Primary datasets
disable the Complex and Predictive models (set the adjustment factor to unity), and
contain only the MOBILE6.2 sulfur effects. The Primary datasets were created because
EPA believes that modern Tier2 vehicles are less sensitive to fuel properties, with the
exception of sulfur, than older vintage model years. This dataset was created to model
this scenario. Table 9 shows the mean fuel adjustment by pollutant / process for both the
Control and Reference datasets for the Primary case.
Table 9
Average Fuel Adjustment for RFS2 Reference and Control Year 2022
Primary Case
Pollutant / Process
Running HC
Start HC
Running CO
Start CO
Running NOx
Start NOx
Average Adjustment
Control
0.95
0.99
0.97
1.02
1.03
1.03
Average Adjustment
Reference
0.95
1.00
0.99
1.05
1.03
1.02
22
-------
Appendix A MySQL Code for the Fuel Binner
This appendix includes example code only for the 2005 calendar year. The
other calendar years are completely analogous and it would be redundant to
include them here.
FLUSH TABLES;
drop Database IF EXISTS MOVESFuelRFS_Base;
Create Database MOVESFuelRFS_Base;
USE MOVESFuelRFS Base;
****************
-- This script requires the successful completion of scrips
JAVA program NMIMFuelDatalmporter.java
***********************************************************************
****************
Required databases are:
NMIMRFS2FuelsMSAT2005Base
RFS2005Base
NMIMRFS2FuelsMSAT2005Reference
RFS2005Base
MOVESDB20080828
Required external text files are:
NONE
***********************************************************************
********************
— MOVES FuelFormulationID Tables
***********************************************************************
********************
— create blank fuelformulation table.
drop TABLE if exists fuelformulationl;
CREATE TABLE fuelformulationl
SELECT fuelformulation.* FROM RFS2005Base.fuelformulation
23
-------
WHERE fuelformulationID > 1000000
GROUP BY fuelFormulationID;
ALTER TABLE fuelformulationl MODIFY fuelformulationID INT(8);
-- read calendar year 2005 Base table. This can be changed to any year
the user desires.
drop TABLE if exists Tempi;
CREATE TABLE Tempi
SELECT fuelformulation.* FROM RFS2005Base.fuelformulation
GROUP BY fuelFormulationID;
ALTER TABLE Tempi MODIFY fuelformulationID INT(8);
INSERT INTO fuelformulationl
( fuelFormulationID,
fuelSubtypelD,
RVP,
sulfurLevel,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent,
olefinContent,
benzeneContent,
e200,
e300 )
SELECT
fuelFormulationID,
fuelSubtypelD,
RVP,
sulfurLevel,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent,
olefinContent,
benzeneContent,
e200,
e300
FROM Tempi
GROUP BY fuelFormulationID;
— this MUST create a unique index or you have a problem!
CREATE UNIQUE INDEX Indexl on fuelformulationl(fuelformulationID);
drop TABLE if exists Tempi;
Alter Table fuelformulationl ADD (
RVPbin INT(5),
24
-------
Sulfurbin
Etohbin
Mtbebin
Tamebin
Aromaticbin
Olefinbin
e200bin
eSOObin
benzenebin
INT(6),
INT(5),
INT(5),
INT(5),
INT(5),
INT(5),
INT(5),
INT(5),
INT(5)
UPDATE fuelformulationl SET e200bin = 1
UPDATE fuelformulationl SET e200bin = 2
UPDATE fuelformulationl SET eSOObin = 1
UPDATE fuelformulationl SET eSOObin = 2
and e300 < 86.99999;
UPDATE fuelformulationl SET eSOObin = 3
WHERE e200 < 46.99999;
WHERE e200 >= 47.00000;
WHERE eSOO < 80.00000;
WHERE e300 >= 80.00000
WHERE eSOO >= 87.00000;
WHERE
UPDATE fuelformulationl SET Aromaticbin = 1
aromaticcontent < 20.0000;
UPDATE fuelformulationl SET Aromaticbin = 2
WHERE aromaticcontent >=
20.00000 and aromaticcontent < 30.00000;
UPDATE fuelformulationl SET Aromaticbin = 3 WHERE
aromaticcontent >= 30.00000;
UPDATE fuelformulationl SET Olefinbin = 1
8.0000;
UPDATE fuelformulationl SET Olefinbin = 2
8.00000 and olefincontent < 10.00000;
UPDATE fuelformulationl SET Olefinbin = 3
10.00000;
WHERE olefincontent <
WHERE olefincontent >=
WHERE olefincontent >=
UPDATE fuelformulationl SET RVPbin = 1
and RVP <=7.2000;
UPDATE fuelformulationl SET RVPbin = 2
and RVP <=8.2000;
UPDATE fuelformulationl SET RVPbin = 3
and RVP <=8.9999;
UPDATE fuelformulationl SET RVPbin = 4
and RVP <=9.9999;
UPDATE fuelformulationl SET RVPbin = 5
and RVP <=11.400;
UPDATE fuelformulationl SET RVPbin = 6
and RVP <=13.400;
UPDATE fuelformulationl SET RVPbin = 7
and RVP <=14.900;
UPDATE fuelformulationl SET RVPbin = 8
WHERE RVP > 0.0
WHERE RVP > 7.20001
WHERE RVP > 8.20001
WHERE RVP > 8.99999
WHERE RVP > 9.99999
WHERE RVP > 11.40001
WHERE RVP > 13.40001
WHERE RVP > 14.90001;
UPDATE fuelformulationl SET Sulfurbin = 1 WHERE sulfurLevel >= 0.0000
and sulfurLevel <= 10.000;
25
-------
UPDATE fuelformulationl SET Sulfurbin
and sulfurLevel <= 25.000;
UPDATE fuelformulationl SET Sulfurbin
and sulfurLevel <= 35.000;
UPDATE fuelformulationl SET Sulfurbin
and sulfurLevel <= 70.000;
UPDATE fuelformulationl SET Sulfurbin
and sulfurLevel <= 120.000;
UPDATE fuelformulationl SET Sulfurbin
120.001 and sulfurLevel <= 210.000;
UPDATE fuelformulationl SET Sulfurbin
and sulfurLevel <= 300.000;
UPDATE fuelformulationl SET Sulfurbin
and sulfurLevel <= 500.000;
UPDATE fuelformulationl SET Sulfurbin
2 WHERE sulfurLevel >= 10.001
3 WHERE sulfurLevel >= 25.001
4 WHERE sulfurLevel >= 35.001
5 WHERE sulfurLevel >= 70.001
6 WHERE sulfurLevel >=
7 WHERE sulfurLevel >= 210.001
8 WHERE sulfurLevel >= 300.001
9 WHERE sulfurLevel >= 500.001;
— Per input from Rich Cook. These benzene bins were established.
UPDATE fuelformulationl SET Benzenebin = 3;
UPDATE fuelformulationl SET Benzenebin = 1
0.0000 and benzeneContent <= 0.40;
UPDATE fuelformulationl SET Benzenebin = 2
0.4001 and benzeneContent <= 0.80;
UPDATE fuelformulationl SET Benzenebin = 3
0.8001 and benzeneContent <= 2.00;
UPDATE fuelformulationl SET Benzenebin = 4
2.0001;
WHERE benzeneContent >=
WHERE benzeneContent >=
WHERE benzeneContent >=
WHERE benzeneContent >=
— add sulfur bins for diesel fuels. Diesel has only sulfur bins.
UPDATE fuelformulationl SET Sulfurbin = 21
WHERE sulfurLevel >= 0.0000 and sulfurLevel < 5.000 and
fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 22
WHERE sulfurLevel >= 5.0000 and sulfurLevel < 15.000 and
fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 23
WHERE sulfurLevel >= 15.0000 and sulfurLevel < 50.000 and
fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 24
WHERE sulfurLevel >= 50.0000 and sulfurLevel < 100.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 25
WHERE sulfurLevel >= 100.0000 and sulfurLevel < 200.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 26
26
-------
WHERE sulfurLevel >= 200.0000 and sulfurLevel < 300.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 27
WHERE sulfurLevel >= 300.0000 and sulfurLevel < 400.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 28
WHERE sulfurLevel >= 400.0000 and sulfurLevel < 500.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 29
WHERE sulfurLevel >= 500.0000 and sulfurLevel < 1000.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 30
WHERE sulfurLevel >= 1000.0000 and sulfurLevel < 2000.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 31
WHERE sulfurLevel >= 2000.0000 and sulfurLevel < 5000.000
and fuelsubtypeid=20;
UPDATE fuelformulationl SET Sulfurbin = 32
WHERE sulfurLevel >= 5000.0000 and fuelsubtypeid=20;
UPDATE fuelformulationl SET Etohbin = 1
0.00001;
UPDATE fuelformulationl SET Etohbin = 2
0.00001 and ETOHVolume <= 6.0000;
UPDATE fuelformulationl SET Etohbin = 3
6.00001 and ETOHVolume <= 8.0000;
UPDATE fuelformulationl SET Etohbin = 4
8.00001;
WHERE ETOHVolume <=
WHERE ETOHVolume >
WHERE ETOHVolume >=
WHERE ETOHVolume >=
UPDATE fuelformulationl SET Mtbebin = 1
0.00001;
UPDATE fuelformulationl SET Mtbebin = 2
0.00001 and MTBEVolume <= 5.0000;
UPDATE fuelformulationl SET Mtbebin = 3
5.00001 and MTBEVolume <= 10.000;
UPDATE fuelformulationl SET Mtbebin = 4
10.0001;
WHERE MTBEVolume <=
WHERE MTBEVolume >
WHERE MTBEVolume >=
WHERE MTBEVolume >=
UPDATE fuelformulationl SET Tamebin = 1
0.00;
UPDATE fuelformulationl SET Tamebin = 2
0.00 and TAMEVolume < 0.03000;
UPDATE fuelformulationl SET Tamebin = 3
0.03001;
WHERE TAMEVolume =
WHERE TAMEVolume >
WHERE TAMEVolume >=
27
-------
Average values within a bin are computed for the Air Toxics fuel
parameters.
This is done just to give these a placeholder in the new
database.
Eventually, better values will have to be inserted into the
MOVES database
— in-order to model toxic pollutants.
For this case DROP the diesel fuels. They are not needed for the
RFS2 rulemaking.
drop TABLE if exists Scratch;
CREATE TABLE Scratch
SELECT fuelformulationID,
fuelsubtypeid,
RVP,
sulfurLevel,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent,
olefinContent,
benzeneContent,
e200,
e300,
rvpbin,
sulfurbin,
tamebin,
etohbin,
mtbebin,
e200bin,
eSOObin,
aromaticbin,
olefinbin,
benzenebin,
count(*) as cnt
FROM fuelformulationl
Where fuelsubtypeid IN (10, 11, 12, 13, 14)
Group by fuelformulationID;
UPDATE Scratch SET benzeneContent = NULL;
UPDATE Scratch SET benzeneContent =0.35 WHERE benzenebin
= 1;
UPDATE Scratch SET benzeneContent =0.65 WHERE benzenebin
= 2;
UPDATE Scratch SET benzeneContent =1.50 WHERE benzenebin
= 3;
UPDATE Scratch SET benzeneContent =3.50 WHERE benzenebin
= 4;
UPDATE Scratch SET olefinContent = NULL;
28
-------
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
= 1;
UPDATE Scratch SET
= 2;
UPDATE Scratch SET
= 3;
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
UPDATE Scratch SET
olefinContent = 5.6
olefinContent = 9.2
olefinContent =11.9
aromaticContent = NULL;
aromaticContent = 17.5
aromaticContent = 26.1
aromaticContent = 32 . 0
e200
e200
e200
e300
e300
e300
e300
= NULL;
= 41.0
= 50.0
= NULL;
= 78.6
= 83.0
= 89.1
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
UPDATE
1;
UPDATE
2;
UPDATE
3;
UPDATE
4;
UPDATE
5;
UPDATE
6;
UPDATE
7;
UPDATE
8;
UPDATE
9;
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
RVP
RVP
RVP
RVP
RVP
RVP
RVP
RVP
RVP
NULL;
6.90
7.50
8.70
9.20
10.0
11.5
13.5
15.0
sulfurLevel
sulfurLevel
sulfurLevel
sulfurLevel
sulfurLevel
sulfurLevel
sulfurLevel
sulfurLevel
sulfurLevel
sulfurLevel
WHERE olefinbin = 1;
WHERE olefinbin = 2;
WHERE olefinbin = 3;
WHERE aromaticbin
WHERE aromaticbin
WHERE aromaticbin
WHERE e200bin = 1;
WHERE e200bin = 2;
WHERE eSOObin = 1;
WHERE eSOObin = 2;
WHERE eSOObin = 3;
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
NULL;
5.0
15.0
30.0
50.0
90.0
180.0
280.0
400.0
600.0
RVPbin
RVPbin
RVPbin
RVPbin
RVPbin
RVPbin
RVPbin
RVPbin
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
WHERE
= 1;
= 2;
= 3;
= 4;
= 5;
= 6;
= 7;
= 8;
sulfurBin
sulfurBin
sulfurBin
sulfurBin
sulfurBin
sulfurBin
sulfurBin
sulfurBin
sulfurBin
UPDATE Scratch SET
21;
sulfurLevel =
4.0
WHERE sulfurBin =
29
-------
UPDATE
22;
UPDATE
23;
UPDATE
24;
UPDATE
25;
UPDATE
26;
UPDATE
27;
UPDATE
28;
UPDATE
29;
UPDATE
30;
UPDATE
31;
UPDATE
32;
UPDATE
UPDATE
1;
UPDATE
2;
UPDATE
3;
UPDATE
4;
UPDATE
UPDATE
1;
UPDATE
2;
UPDATE
3;
UPDATE
4;
UPDATE
UPDATE
UPDATE
UPDATE
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
Scratch
TAMEbin =
Scratch
TAMEbin =
Scratch
TAMEbin =
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
SET
1;
SET
2;
SET
3;
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
sulfurLevel =
ETOHVolume =
ETOHVolume =
ETOHVolume =
ETOHVolume =
ETOHVolume =
MT BE Volume =
MT BE Volume =
MT BE Volume =
MT BE Volume =
MT BE Volume =
TAME Volume =
TAME Volume =
TAME Volume =
TAME Volume =
11
43
75
113
281
337
468
750
1500.0
3000.0
6000.0
.0 WHERE sulfurBin
.0 WHERE sulfurBin
.0 WHERE sulfurBin
.0 WHERE sulfurBin
.0 WHERE sulfurBin
.0 WHERE sulfurBin
.0 WHERE sulfurBin
.0 WHERE sulfurBin
WHERE sulfurBin
WHERE sulfurBin
WHERE sulfurBin
NULL;
0.
5.
8.
10.
00 WHERE Etohbin =
00 WHERE Etohbin =
00 WHERE Etohbin =
00 WHERE Etohbin =
NULL;
0.
2.
8.
11.
00 WHERE MTBEbin =
50 WHERE MTBEbin =
00 WHERE MTBEbin =
00 WHERE MTBEbin =
NULL;
0.
0.
0.
000 WHERE
015 WHERE
060 WHERE
30
-------
fuelsubtype 10 = Conventional Gasoline
fuelsubtype 11 = RFC
— fuelsubtype 12 = Ethanol/Gasahol Maximum ethanol 10% by volume
fuelsubtype 13 = Ethanol/Gasahol Maximum ethanol 8% by
volume
— fuelsubtype 14 = Ethanol/Gasahol Maximum ethanol 5% by
volume
— fuelsubtype 20 = Diesel
-- original data tables did not have fuelsubtypelD coded as gasahol.
— this binner sets all gasoline fuels with ethanol > 5% as
gasahol.
UPDATE Scratch SET fuelsubtypelD = 12 WHERE
ETOHVolume >= 10.0;
UPDATE Scratch SET fuelsubtypelD = 13 WHERE
ETOHVolume Between 7.9 and 8.1;
UPDATE Scratch SET fuelsubtypelD = 14 WHERE
ETOHVolume Between 4.9 and 5.1;
Alter Table Scratch ADD (
FFIDD BIGINT(19)
UPDATE Scratch SET FFIDD = fuelsubtypelD + RVPbin*100 +
sulfurBin*1000 +
Etohbin*100000 +
MTBEbin*1000000 + TAMEbin*10000000 +
e200bin*100000000 +
e300bin*1000000000 + Aromaticbin*10000000000 +
olefinbin*100000000000 +
benzenebin* 1000000000000
WHERE fuelsubtypeid IN (10, 11, 12, 13,
14) ;
UPDATE Scratch SET FFIDD = -10*sulfurbin WHERE fuelsubtypeid = 20;
CREATE INDEX Indexl on Scratch(ffIDD);
CREATE UNIQUE INDEX Index2 on Scratch(fuelformulationID);
drop TABLE if exists Scratch2;
CREATE TABLE Scratch2
Select FFIDD
FROM Scratch
WHERE FFIDD IS NOT NULL
GROUP BY FFIDD;
ALTER Table Scratch2 ADD FFIDDD INT Auto_Increment NOT NULL Primary
Key;
31
-------
drop TABLE if exists NMIMMasterFuelFormulation;
CREATE TABLE NMIMMasterFuelFormulation
SELECT Scratch.*,
Scratch2.FFIDDD
FROM Scratch LEFT JOIN Scratch2
ON Scratch.FFIDD = Scratch2.FFIDD
WHERE Scratch.FFIDD IS NOT NULL;
— FFID is the new fuelformulationID variable. All real values are
greater than 100.
Diesel fuels have values over 2000 to distinguish them.
— FFIDD is the BIGINT value that forms a unique variable for all
combinations of the
— individual binning variables.
— FFIDDD is the autoincrement of FFIDD. It runs from 101 to XXXX.
This is the current number
— of unique fuels.
Alter Table NMIMMasterFuelFormulation ADD (
FFID INT (6)
UPDATE NMIMMasterFuelFormulation SET FFID = FFIDDD + 2000
WHERE fuelsubtypeid = 20;
UPDATE NMIMMasterFuelFormulation SET FFID = FFIDDD + 99
WHERE fuelsubtypeid IN (10, 11, 12,
13, 14);
CREATE UNIQUE INDEX Indexl on NMIMMasterFuelFormulation
(fuelformulationID);
CREATE INDEX Index2 on NMIMMasterFuelFormulation (ffID);
*******************
— Create the MASTER fuelformulationID for the RFS rule. This will be
the one specified in
the RFS 1 rulemaking.
***********************************************************************
*******************
There will be two Master fuelformulationlDs in MOVES.
— Fuel One is the 30 ppm sulfur fuel that represents the
2001+ model years.
32
-------
Fuel Two is the 90 ppm sulfur fuel that represents pre-2001
model years.
fuelFormulationID = 98 is the 30 ppm sulfur master fuel.
— fuelFormulationID = 99 is the 90 ppm sulfur master fuel.
Randomly grab two fuelformulationlDs and change them to the
properties that are
required. A diesel fuel was selected because most of the
properties are currently
NULL and consequently will not need to be changed.
FuelFormulationlDs = 98 and 99
— will be the master fuelformulationlDs for all of the
subsequent scripts. They will
— may not actually exist in any USA county, but will
represent a fueladjustment factor
of unity for the respective model year groups.
drop TABLE if exists MasterFuel;
CREATE TABLE MasterFuel
SELECT * FROM NMIMMasterFuelFormulation WHERE FFID IN (101, 102);
Master FuelFormulationID #1 30 ppm sulfur *
**************************************************
UPDATE MasterFuel SET fuelformulationID = 99998 WHERE
FFID = 101;
UPDATE MasterFuel SET fuelsubtypelD = 10
WHERE FFID = 101;
UPDATE MasterFuel SET RVP = 6.9
WHERE FFID = 101;
UPDATE MasterFuel SET sulfurLevel = 30
WHERE FFID = 101;
— The base fuel has no oxygen so ETOHVolume, MTBEvolume, ETBEVolume
and TAMEVolume will
always be zero.
UPDATE MasterFuel SET ETOHVolume = 0.0
WHERE FFID = 101;
UPDATE MasterFuel SET MTBEVolume = 0.0
WHERE FFID = 101;
UPDATE MasterFuel SET ETBEVolume = 0.0
WHERE FFID = 101;
UPDATE MasterFuel SET TAMEVolume = 0.0
WHERE FFID = 101;
UPDATE MasterFuel SET aromaticContent = 26.1 WHERE
FFID = 101;
UPDATE MasterFuel SET olefinContent = 5.6
WHERE FFID = 101;
UPDATE MasterFuel SET benzeneContent = 1.0
WHERE FFID = 101;
33
-------
E200 = 147.91 - 0.49
E300 = 155.47 - 0.22 * T90
Reference Fuel T50 = 218
Reference Fuel T90 = 329
T50
UPDATE MasterFuel SET e200
FFID = 101;
UPDATE MasterFuel SET e300
FFID = 101;
UPDATE MasterFuel SET FFID
WHERE FFID = 101;
147.91 - 0.49 * 218. WHERE
155.47 - 0.22 * 329. WHERE
Master FuelFormulationID #2
90
ppm sulfur
**************************************************
UPDATE MasterFuel SET fuelformulationID
FFID = 102;
UPDATE MasterFuel SET fuelsubtypelD
WHERE FFID = 102;
UPDATE MasterFuel SET RVP
WHERE FFID = 102;
UPDATE MasterFuel SET sulfurLevel
WHERE FFID = 102;
99999 WHERE
10
6.9
90
— The base fuel has no oxygen so ETOHVolume, MTBEvolume, ETBEVolume
and TAMEVolume will
always be zero.
UPDATE MasterFuel SET ETOHVolume
WHERE FFID = 102;
UPDATE MasterFuel SET MTBEVolume
WHERE FFID = 102;
UPDATE MasterFuel SET
WHERE FFID = 102;
UPDATE MasterFuel SET
WHERE FFID = 102;
UPDATE MasterFuel SET
FFID = 102;
UPDATE MasterFuel SET olefinContent
WHERE FFID = 102;
UPDATE MasterFuel SET benzeneContent
WHERE FFID = 102;
ETBEVolume
TAMEVolume
aromaticContent
0.0
0.0
0.0
0.0
26.1 WHERE
5.6
1.0
UPDATE MasterFuel SET e200 =
FFID = 102;
UPDATE MasterFuel SET e300 =
FFID = 102;
UPDATE MasterFuel SET FFID =
WHERE FFID = 102;
147.91 - 0.49 * 218. WHERE
155.47 - 0.22 * 329. WHERE
99
34
-------
********************
— NOTES on the Reference fuel
fuelsubtypeid = 10
RFC = 'N' (conventional)
RVP = 6.9
Sulfur = 90
ETOH =0.0
Tame =0.0
MTBE =0.0
aromaticcontent = 26.1
olefincontent = 5.6
e200 = 50.0
e300 = 83.0
benzeneContent = 0.80
— This fuel best matches the fuel used in the Arizona test
program
— in the spring,summer and fall.
— The summer RVP is used instead of the winter one of 11.
This will minimize (the possibly dubious MOBILE6) RVP
correction factors in MOVES.
***********************************************************************
*********************
drop TABLE if exists FuelFormulation;
CREATE TABLE FuelFormulation (
fuelFormulationID smallint(6) NOT NULL default
'0',
fuelSubTypelD smallint(6) NOT NULL
default '0',
RVP float
default NULL,
sulfurLevel float
default NULL,
ETOHVolume float
default NULL,
MTBEVolume float
default NULL,
ETBEVolume float
default NULL,
TAMEVolume float
default NULL,
aromaticContent float
default NULL,
35
-------
olefinContent float
default NULL,
benzeneContent float
default NULL,
e200 float
default NULL,
e300 float
default NULL,
volToWtPercentOxy float
default NULL,
PRIMARY KEY (fuelFormulationID)
INSERT INTO fuelformulation
( fuelFormulationID,
fuelSubtypelD,
RVP,
sulfurLevel,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent,
olefinContent,
benzeneContent,
e200,
e300,
volToWtPercentOxy )
SELECT
FFID,
fuelSubtypelD,
RVP,
sulfurLevel,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent,
olefinContent,
benzeneContent,
e200,
e300,
NULL as volToWtPercentOxy
FROM MasterFuel
GROUP BY FFID;
INSERT INTO fuelformulation
( fuelFormulationID,
fuelSubtypelD,
RVP,
sulfurLevel,
ETOHVolume,
MTBEVolume,
36
-------
ETBEVolume,
TAMEVolume,
aromaticContent,
olefinContent,
benzeneContent,
e200,
e300,
volToWtPercentOxy )
SELECT
FFID,
fuelSubtypelD,
RVP,
sulfurLevel,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent,
olefinContent,
benzeneContent,
e200,
e300,
NULL as volToWtPercentOxy
FROM NMIMMasterFuelFormulation
GROUP BY FFID;
— copy all the non gasoline and non diesel fuelformulations from the
default MOVES program
into the fuelformulation table.
INSERT INTO fuelformulation
( fuelFormulationID, fuelSubtypelD, RVP, sulfurLevel, ETOHVolume,
MTBEVolume,
ETBEVolume, TAMEVolume, aromaticContent, olefinContent,
benzeneContent, e200, e300,
volToWtPercentOxy )
SELECT fuelFormulationID, fuelSubtypelD, RVP, sulfurLevel,
ETOHVolume, MTBEVolume,
ETBEVolume, TAMEVolume, aromaticContent, olefinContent,
benzeneContent, e200, e300,
volToWtPercentOxy
FROM MOVESDB20080828.fuelformulation
WHERE fuelsubtypelD >= 29
GROUP BY fuelFormulationID;
INSERT INTO fuelformulation
( fuelFormulationID, fuelSubtypelD, RVP, sulfurLevel, ETOHVolume,
MTBEVolume,
ETBEVolume, TAMEVolume, aromaticContent, olefinContent,
benzeneContent, e200, e300,
volToWtPercentOxy )
SELECT fuelFormulationID, fuelSubtypelD, RVP, sulfurLevel,
ETOHVolume, MTBEVolume,
37
-------
ETBEVolume, TAMEVolume, aromaticContent, olefinContent,
benzeneContent, e200, e300,
volToWtPercentOxy
FROM MOVESDB20080828.fuelformulation
WHERE fuelformulationID IN (10, 20)
GROUP BY fuelFormulationID;
UPDATE fuelformulation SET volToWtPercentOxy = NULL;
CREATE UNIQUE INDEX Indexl on FuelFormulation (fuelformulationID);
—drop TABLE if exists Scratch;
—drop TABLE if exists Scratch2;
********************
— Fuel Supply Tables
***********************************************************************
********************
create blank Fuel Supply table.
drop TABLE if exists fs;
CREATE TABLE fs
SELECT fuelsupply.*
FROM RFS2005Base.fuelsupply
WHERE fuelformulationID > 1000000
GROUP BY countylD, fuelyearlD, monthgroupID, fuelformulationID;
ALTER TABLE fs MODIFY fuelformulationID INT(8);
— read calendar year 2005 table. This can be changed to any year the
user desires.
drop TABLE if exists Tempi;
CREATE TABLE Tempi
SELECT fuelsupply.*
FROM RFS2005Base.fuelsupply
GROUP BY countylD, fuelyearlD, monthgroupID, fuelformulationID;
ALTER TABLE Tempi MODIFY fuelformulationID INT(8);
INSERT INTO fs
( countylD,
fuelYearlD,
monthGroupID,
fuelFormulationID,
marketShare,
38
-------
marketShareCV )
SELECT
countylD,
fuelYearlD,
monthGroupID,
fuelFormulationID,
marketShare,
marketShareCV
FROM Tempi
GROUP BY countylD, fuelyearlD, monthgroupID, fuelformulationID;
— this must create a unique index or problems are apparent.
CREATE UNIQUE INDEX Indexl on fs (countylD, fuelyearlD, monthgroupID,
fuelformulationID);
CREATE INDEX Index2 on fs (fuelformulationID);
drop TABLE if exists fsl CASCADE;
CREATE TABLE fsl
SELECT fs.*,
NMIMMasterFuelFormulation.FFID
FROM fs LEFT JOIN NMIMMasterFuelFormulation
ON fs.fuelformulationID =
NMIMMasterFuelFormulation.fuelformulationID
WHERE fs.fuelformulationID > 0
GROUP BY countylD, fuelyearlD, monthgroupID, fuelformulationID;
CREATE UNIQUE INDEX Indexl on fsl(countylD, fuelyearlD,
monthgroupID, fuelformulationID) ;
CREATE INDEX Index2 on fsl(fflD);
drop TABLE if exists fsNEW CASCADE;
CREATE TABLE fsNEW
SELECT fsl.countylD, fsi.fuelYearlD, fsl.monthGroupID, fsl.FFID as
fuelFormulationID,
fsl.marketShare, fsl.marketShareCV
FROM fsl;
CREATE UNIQUE INDEX Indexl on fsNEW(countylD, fuelyearlD,
monthgroupID, fuelFormulationID);
CREATE INDEX Index2 on fsNEW(fuelFormulationID);
— FuelSupply is one of the final products of the fuel binner scripts.
It goes
directly into the MOVES model.
drop TABLE if exists FuelSupply;
39
-------
CREATE TABLE FuelSupply (
countylD int(ll) NOT NULL,
fuelYearlD smallint(6) NOT NULL,
monthGroupID smallint(6) NOT NULL,
fuelFormulationID smallint(6) NOT NULL,
marketShare float,
marketShareCV float,
PRIMARY KEY (countylD, fuelYearlD,
monthGroupID, fuelFormulationID)
INSERT INTO FuelSupply (CountylD, FuelYearlD, MonthGroupID,
FuelFormulationID,
marketShare, marketShareCV)
SELECT CountylD, FuelYearlD, MonthGroupID, FuelFormulationID,
sum(marketshare), null
FROM fsNEW
GROUP BY CountylD, FuelYearlD, MonthGroupID, FuelFormulationID;
CREATE INDEX Indexl on
FuelSupply(countylD, fuelyearlD, monthgroupID,
FuelFormulationID);
CREATE INDEX Index2 on
FuelSupply(fuelformulationID);
— remove a Colorado county that exists only after CY 2001. MOVES2006
cannot handle this
— this county division.
DELETE FROM fuelsupply where countylD = 8014;
select countyid, sum(marketshare) FROM fuelsupply as A, fuelformulation
as B
WHERE A.fuelformulationID = B.fuelformulationID and
B.fuelsubtypelD < 20
group by countyid, fuelyearid, monthgroupID
HAVING Sum(marketshare) > 1.01 or Sum(marketshare) < 0.99 limit 100;
—drop TABLE if exists fs;
—drop TABLE if exists fsl;
—drop TABLE if exists tempi;
—drop TABLE if exists FSnew;
40
-------
Appendix B MySQL Code for the Predictive Model
This appendix contains MySQL code for the EPA Predictive Model. The
RFS Project included several versions of the Predictive model based on base
fuel levels and calendar years. Only the 2005 calendar year example is
provided so as to save space. All of the Predictive models are structurally
analogous.
FLUSH TABLES;
drop Database IF EXISTS RFSPredictive30_Base;
Create Database RFSPredictive30_Base;
USE RFSPredictiveSO Base;
****************
— This script requires the successful completion of scrips
MOVEFuelBinnerRFS Base.sql
***********************************************************************
****************
Required databases are:
MOVESFuelRFS_Base
MOVESDB20080828
— Required external text files are:
'C:\\Eds C Files\\MOVES FUELBinner\\SQL Fuel Binner
Code\\SulfurCoefficients.csv'
***********************************************************************
*******************
***********************************************************************
*******************
***********************************************************************
*******************
41
-------
This is the September 16, 2008 version of this script.
This version of the Predictive model uses 30 ppm as the
reference sulfur. This
corresponds to fuelformulationID = 98.
— The Predictive Model is used only to model HC and NOx
pollutant effects.
— It is used to model all the fuel effects for these two
pollutants except Sulfur.
Sulfur effects were removed from the EPA Predictive model
for the the MOVES exercise.
The sulfur effects are now at the end of this script and
are based directly on
MOBILE6.2 algorithms.
— Creating a new Predictive Model Table and populating it
with real values.
PollutantID 1 is HC,
PollutantID 3 is NOx
The predictive model is the average result of three HC
regression models and
six NOx regression models.
drop TABLE if exists PredictiveModelCoeff1;
CREATE TABLE PredictiveModelCoeffl (
pollutantID smallint(6),
predModellD smallint(6),
predCoefficientID smallint(6),
predCoefficientName CHAR(12),
predCoefficient float,
PRIMARY KEY (pollutantID, predModellD,
predCoefficientID)
INSERT INTO PredictiveModelCoeffl
(pollutantID, predModellD, predCoefficientID,
predCoefficientName, predCoefficient) VALUES
(1, 1, 1, 'Intercept', -1.5957),
(1, 1, 2, 'RVP', 0.008474),
(1, 1, 3, 'T50', 0.06125),
(1, 1, 4, 'T90', 0.02084),
(1, 1, 5, 'AROM', 0.008729),
(1, 1, 6, 'OLEF', -0.01426),
(1, 1, 7, 'OXYGEN', -0.01329),
(1, 1, 8, 'SULFUR', 0.05505),
(1, 1, 9, 'HI-EMIT', 1.6909),
(1, 1, 10, 'T90*T90', 0.01617),
(1, 1, 11, 'T50*T50', 0.02494),
(1, 1, 12, 'T90*OXY', 0.01589),
(1, 1, 13, 'SUL*HI', -0.03174),
42
-------
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
1,
1,
1,
1,
1,
1,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
14,
15,
16,
17,
18,
19,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
(1,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
18,
19,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
2
'OXY*OXY', 0.01256),
'T90*ARO', 0.006908),
'T50*HI', 0.000),
'OLE*OLE', 0.000),
'T90*OLE', 0.000),
'ARO*ARO' , 0.000),
, 1, 'Intercept', -1.5980),
, 2, 'RVP', 0.008971),
, 3, 'T50', 0.06499),
, 4, 'T90', 0.02104),
, 5, 'AROM', 0.008729),
, 6, 'OLEF', -0.01430),
, 7, 'OXYGEN', -0.01378),
, 8, 'SULFUR', 0.05495),
, 9, 'HI-EMIT' , 1. 6935) ,
, 10, 'T90*T90', 0.01604),
, 11, 'T50*T50', 0.02477),
, 12, 'T90*OXY', 0.01576),
, 13, 'SUL*HI', -0.03172),
, 14, 'OXY*OXY', 0.01353),
, 15, 'T90*ARO', 0.007013),
, 16, 'T50*HI', -0.02609),
, 17, 'OLE*OLE' , 0. 000) ,
, 18, 'T90*OLE', 0.000),
, 19, 'ARO*ARO', 0.000),
'Intercept', -1.6012),
'RVP', 0.007973),
'T50', 0.06046),
'T90', 0.02133),
' AROM ', 0.008759),
'OLEF', -0.01457),
'OXYGEN' , -0. 01391) ,
'SULFUR', 0.04696),
'HI-EMIT', 1.7091),
'T90*T90', 0.01633),
'T50*T50', 0.02469),
'T90*OXY', 0.01552),
'SUL*HI', 0.000),
'OXY*OXY', 0.01288),
'T90*ARO', 0.006814),
'T50*HI', 0.000),
'OLE*OLE' , 0. 000) ,
'T90*OLE', 0.000),
'ARO*ARO', 0.000),
1, 1, ' Intercept ' ,-0. 6603) ,
1, 2, 'RVP', 0.009093),
1, 3, 'T50', -0.00245),
1, 4, 'T90', 0.00719),
1, 5, 'AROM', 0.01587),
1, 6, 'OLEF', 0.01988),
1, 7, 'OXYGEN', 0.01240),
1, 8, 'SULFUR', 0.04171),
1, 9, 'HI-EMIT', 0.3960),
1, 10, 'OXY*SUL', -0.01506),
1, 11, 'OXY*T50', 0.000),
1, 12, 'OXY*T90', 0.000),
43
-------
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
2,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
4,
(3,
1,
1,
1,
1,
1,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
3,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
5,
13, 'OXY*ARO', 0.000),
14, 'OXY*OXY', 0.000),
15, 'T50*T50', 0.000),
16, 'SUL*SUL', 0.000),
17, 'SUL*T90', 0.000),
1 Intercept' ,-0. 6606) ,
'RVP', 0.01172),
'T50', 0.000084),
'T90' , 0.007879),
'AROM', 0.01431),
'OLEF', 0.01949),
'OXYGEN', 0.01728),
' SULFUR' , 0. 04387) ,
'HI-EMIT', 0.3963),
'OXY*SUL' , 0.000),
'OXY*T50', 0.000),
'OXY*T90', -0.00510),
'OXY*ARO' , 0.000),
'OXY*OXY', 0.000),
'T50*T50', 0.000),
' SUL*SUL' , 0. 000) ,
'SUL*T90', 0.000),
1, ' Intercept ' ,-0. 6656) ,
2, 'RVP', 0.009694),
3, 'T50', 0.001804),
4, 'T90', 0.005543),
5, 'AROM', 0.01524),
6, 'OLEF', 0.01940),
7, 'OXYGEN', 0.01333),
8, 'SULFUR', 0.04201),
9, 'HI-EMIT' , 0. 3965) ,
10, 'OXY*SUL', -0.01647),
11, 'OXY*T50', 0.000),
12, 'OXY*T90', 0.000),
13, 'OXY*ARO', 0.000),
14, 'OXY*OXY' , 0. 000) ,
15, 'T50*T50', 0.006974),
16, ' SUL*SUL' , 0. 000) ,
17, 'SUL*T90', 0.000),
'Intercept' ,-0.6651) ,
'RVP', 0.007673),
'T50', 0.001173),
'T90', 0.006239),
'AROM', 0.01407),
'OLEF', 0.01966),
'OXYGEN' , 0. 01371) ,
'SULFUR', 0.04201),
'HI-EMIT', 0.3960),
'OXY*SUL' , -0. 01627) ,
'OXY*T50', -0.00830),
'OXY*T90 ' , 0. 000) ,
'OXY*ARO', 0.000),
'OXY*OXY', 0.000),
'T50*T50', 0.000),
'SUL*SUL', 0.000),
'SUL*T90', 0.000),
1, 'Intercept' ,-0.6624) ,
44
-------
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
(3,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
6,
5,
5,
5,
5,
5,
5,
5,
5,
5,
5,
5,
5,
5,
5,
5,
5,
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11,
12,
13,
14,
15,
16,
17,
2, 'RVP', 0.008390),
3, 'T50', 0.000312),
4, 'T90', 0.006213),
5, 'AROM', 0.01501),
6, 'OLEF', 0.01990),
7, 'OXYGEN' , 0. 01351) ,
8, 'SULFUR', 0.04195),
9, 'HI-EMIT' , 0. 3961) ,
10, 'OXY*SUL', -0.01402),
11, 'OXY*T50', 0.000),
12, 'OXY*T90', 0.000),
13, 'OXY*ARO', -0.00547),
14, 'OXY*OXY' , 0. 000) ,
15, 'T50*T50', 0.000),
16, ' SUL*SUL' , 0. 000) ,
17, 'SUL*T90', 0.000),
'Intercept' ,-0.6737) ,
'RVP', 0.006188),
'T50', -0.00475),
'T90', 0.007587),
'AROM', 0.01209),
'OLEF', 0.01969),
'OXYGEN' , 0. 008245) ,
'SULFUR', 0.04205),
'HI-EMIT', 0.3969),
'OXY*SUL', -0.01325),
'OXY*T50', 0.000),
'OXY*T90 ' , 0. 000) ,
'OXY*ARO', 0.000),
'OXY*OXY', 0.01120),
'T50*T50', 0.000),
'SUL*SUL', 0.000),
'SUL*T90' , 0.000) ;
drop TABLE if exists PredictiveModelCoeff2;
CREATE TABLE PredictiveModelCoeff2 (
pollutantID smallint(6),
predCoefficientID smallint(6),
predCoefficientName CHAR(12),
predMean float,
predStdDev float,
PRIMARY KEY (pollutantID, predCoefficientID)
INSERT INTO PredictiveModelCoef f 2
(pollutantID, predCoef ficientID, predCoefficientName, predMean,
predStdDev) VALUES
0000, 0.000000),
8.51, 0.781459),
205.62, 17.612534),
310.65, 20.869732),
27.64, 6.561886),
6.93, 5.143184),
(1, 1,
(1, 2,
(1, 3,
(1, 4,
(1, 5,
(1, 6,
'Intercept' ,1.
'RVP',
'T50',
'T90',
'AROM',
'OLEF',
45
-------
(1, 7, 'OXYGEN', 1.49, 1.249356),
(1, 8, 'SULFUR', 183.14, 143.055894;
(3, 1, 'Intercept',1.0000,
(3, 2, 'RVP', 8.445335, 0.780184),
(3, 3, 'T50', 206.815503, 17.906267),
(3, 4, 'T90', 312.126198, 22.099331),
(3, 5, 'AROM', 28.082805, 7.383169),
(3, 6, 'OLEF', 6.974371, 4.932872),
(3, 7, 'OXYGEN', 1.347629, 1.251882),
(3, 8, 'SULFUR', 182.060319, 140.783197);
— Bring in MOVES Fuel Parameter information.
— Each individual MOVES Gasoline Fuel is run through the Predictive
Model.
Diesel fuels are not processed in any way for MOVES.
— This information was created in the Script MOVESFuelBinnerPR.sql
drop TABLE if exists FuelFormulation;
CREATE TABLE FuelFormulation
SELECT FuelFormulationID,
FuelSubtypelD,
RVP,
SulfurLevel as Sulfur,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent as AROM,
olefinContent as OLEF,
benzeneContent as BENZ,
e200,
e300,
volToWtPercentOxy
FROM MOVESFuelRFS_Base.FuelFormulation
WHERE fuelSubtypelD in (10, 11, 12, 13, 14);
CREATE UNIQUE INDEX Indexl ON FuelFormulation (FuelFormulationID);
alter TABLE FuelFormulation ADD (
T50 float,
T90 float,
Oxygen float,
PollutantID smallint(6)
46
-------
The Predictive model requires fuel Oxygen and T50 and T90
parameters. The MOVES
model contains ETOH and MTBE Volume data and e200 and e300
information.
These parameters must be converted.
UPDATE FuelFormulation SET Oxygen = 0.0
WHERE ETOHVolume <= 0.0 and MTBEVolume <= 0.0;
UPDATE FuelFormulation SET Oxygen = ETOHVolume / 2.900
WHERE ETOHVolume > 0.0 and MTBEVolume <= 0.0;
UPDATE FuelFormulation SET Oxygen = MTBEVolume / 5.600
WHERE ETOHVolume <= 0.0 and MTBEVolume > 0.0;
UPDATE FuelFormulation SET T50 = (147.91/0.49) - (e200/0.49);
UPDATE FuelFormulation SET T90 = (155.47/0.22) - (e300/0.22);
-- FuelFormulationOriginal saves the orginal data (sulfur levels) for
future use.
drop TABLE if exists FuelFormulationOriginal;
CREATE TABLE FuelFormulationOriginal SELECT * FROM FuelFormulation;
CREATE UNIQUE INDEX Indexl ON FuelFormulationOriginal
(FuelFormulationID);
-- Set all Sulfur levels to 30.0 ppm Sulfur. This is done to remove
the Predictive
Model Sulfur effects. The Complex model sulfur effects
will be used in MOVES.
UPDATE FuelFormulation SET Sulfur = 30.0;
******************
— The first table join does HC.
***********************************************************************
******************
UPDATE FuelFormulation SET PollutantID = 1;
drop TABLE if exists SI;
CREATE TABLE SI
47
-------
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.RVP,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.RVP - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as RVPdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 2
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON SI (FuelFormulationID, PollutantID);
drop TABLE if exists S2;
CREATE TABLE S2
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.T50,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.T50 - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as TSOdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 3
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S2 (FuelFormulationID, PollutantID);
drop TABLE if exists S3;
CREATE TABLE S3
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.T90,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.T90 - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as T90diff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 4
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S3 (FuelFormulationID, PollutantID) ;
drop TABLE if exists S4;
CREATE TABLE S4
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
48
-------
FuelFormulation.AROM ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.AROM - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as AROMdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 5
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S4 (FuelFormulationID, PollutantID);
drop TABLE if exists S5;
CREATE TABLE S5
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.OLEF ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.OLEF - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as OLEFdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 6
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S5 (FuelFormulationID, PollutantID) ;
drop TABLE if exists S6;
CREATE TABLE S6
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.Oxygen ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.Oxygen -
PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as
Oxygendiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 7
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S6 (FuelFormulationID, PollutantID);
drop TABLE if exists S7;
CREATE TABLE S7
49
-------
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.Sulfur ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.Sulfur -
PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as
Sulfurdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 8
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S7 (FuelFormulationID, PollutantID);
— Create the standardized Table.
drop TABLE if exists S;
CREATE TABLE S
SELECT SI.FuelFormulationID, SI.PollutantID,
SI.predCoefficientID,
Sl.RVPdiff as StandardCoeff
FROM S1
GROUP BY FuelFormulationID, PollutantID, predCoefficientID;
UPDATE S SET predCoefficientID = 1;
UPDATE S SET StandardCoeff = 1.000;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT SI.FuelFormulationID,
SI.PollutantID,
SI.predCoefficientID,
Sl.RVPdiff
FROM S1
GROUP BY SI.FuelFormulationID, SI.PollutantID, SI.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S2.FuelFormulationID,
S2.PollutantID,
S2.predCoefficientID,
S2.T50diff
FROM S2
GROUP BY S2.FuelFormulationID, S2.PollutantID, S2.predCoefficientID;
50
-------
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S3.FuelFormulationID,
S3.PollutantID,
S3.predCoefficientID,
S3.T90diff
FROM S3
GROUP BY S3.FuelFormulationID, S3.PollutantID, S3.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S4.FuelFormulationID,
S4.PollutantID,
S4.predCoefficientID,
S4.AROMdiff
FROM S4
GROUP BY S4.FuelFormulationID, S4.PollutantID, S4.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S5.FuelFormulationID,
S5.PollutantID,
S5.predCoefficientID,
SS.OLEFdiff
FROM S5
GROUP BY S5.FuelFormulationID, S5.PollutantID, S5.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S6.FuelFormulationID,
S6.PollutantID,
S6.predCoefficientID,
S6.Oxygendiff
FROM S 6
GROUP BY S6.FuelFormulationID, S6.PollutantID, S6.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
51
-------
predCoefficientID,
StandardCoeff )
SELECT S7.FuelFormulationID,
S7.PollutantID,
S7.predCoefficientID,
S7.Sulfurdiff
FROM S7
GROUP BY S7.FuelFormulationID, S7.PollutantID, S7.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
9 as predCoefficientID,
1.00 as HiEmit
FROM S7
GROUP BY FuelFormulationID, PollutantID, predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
10 as predCoefficientID,
T90diff*T90diff
FROM S3
GROUP BY FuelFormulationID, PollutantID, predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
11 as predCoefficientID,
T50diff*T50diff
FROM S2
GROUP BY FuelFormulationID, PollutantID, predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
52
-------
SELECT S3.FuelFormulationID,
S3.PollutantID,
12 as predCoefficientID,
S 3.T 9 Odi ff* S 6.Oxygendi ff
FROM S3 INNER JOIN S6
ON S3.FuelFormulationID = S6.FuelFormulationID AND
S3.PollutantID = S6.PollutantID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S7.FuelFormulationID,
S7.PollutantID,
13 as predCoefficientID,
S7.SulfurDiff*l.0
FROM S7;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
14 as predCoefficientID,
S6.Oxygendiff*S6.Oxygendiff
FROM S 6;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S3.FuelFormulationID,
S3.PollutantID,
15 as predCoefficientID,
S3.T90diff*S4.AROMdiff
FROM S3 INNER JOIN S4
ON S3.FuelFormulationID = S4.FuelFormulationID AND
S3.PollutantID = S4.PollutantID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
16 as predCoefficientID,
S2.T50diff*1.00
FROM S2;
53
-------
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
17 as predCoefficientID,
S5.0LEFdiff*S5.0LEFdiff
FROM S5;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S3.FuelFormulationID,
S3.PollutantID,
18 as predCoefficientID,
S3.T90diff*S5.0LEFdiff
FROM S3 INNER JOIN S5
ON S3.FuelFormulationID = S5.FuelFormulationID AND
S3.PollutantID = S5.PollutantID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
19 as predCoefficientID,
S4.AROMdiff*S4.AROMdiff
FROM S4;
CREATE UNIQUE INDEX Indexl ON S (FuelFormulationID, PollutantID,
predCoefficientID);
— develop the three individual HC predictive Models.
drop TABLE if exists Model?;
CREATE TABLE Model?
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeff1.predCoefficient,
S.StandardCoeff*PredictiveModelCoeff1.predCoefficient as Ml
54
-------
FROM S LEFT JOIN PredictiveModelCoeff1
ON S.PollutantID
PredictiveModelCoeff 1.PollutantID AND
S.predCoefficientID
PredictiveModelCoeff1.predCoefficientID
WHERE PredictiveModelCoeff1.predModellD = 1
GROUP BY
S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON Model? (FuelFormulationID, PollutantID,
predCoefficientID);
drop TABLE if exists ModelS;
CREATE TABLE Model8
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeff1.predCoefficient,
S.StandardCoeff^PredictiveModelCoeffl.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeffl
ON S.PollutantID
PredictiveModelCoeffl.PollutantID AND
S.predCoefficientID
PredictiveModelCoeffl.predCoefficientID
WHERE PredictiveModelCoeff1.predModellD = 2
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON Models (FuelFormulationID, PollutantID,
predCoefficientID);
drop TABLE if exists Modell2;
CREATE TABLE Model12
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeffl.predCoefficient,
S . StandardCoeff^PredictiveModelCoeffl.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeffl
ON S.PollutantID
PredictiveModelCoeffl.PollutantID AND
S.predCoefficientID
PredictiveModelCoeffl.predCoefficientID
WHERE PredictiveModelCoeffl.predModellD = 3
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON Modell2 (FuelFormulationID, PollutantID,
predCoefficientID);
55
-------
drop TABLE if exists Model7b;
CREATE TABLE Model7b
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM Model?
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON Model7b (FuelFormulationID, PollutantID);
drop TABLE if exists ModelSb;
CREATE TABLE Model8b
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM ModelS
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON ModelSb (FuelFormulationID, PollutantID);
drop TABLE if exists Modell2b;
CREATE TABLE Model12b
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM Model12
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON Modell2b (FuelFormulationID,
PollutantID);
drop TABLE if exists ModelHC;
CREATE TABLE ModelHC
SELECT Model7b.FuelFormulationID, Model7b.PollutantID,
Model7b.ECoeff as E7,
ModelSb.ECoeff as E8,
Modell2b.ECoeff as E12,
(SUM(Model7b.ECoeff + ModelSb.ECoeff + Modell2b.ECoeff)/3)
as E
FROM Model7b INNER JOIN ModelSb ON
Model7b.FuelFormulationID=Model8b.FuelFormulationID and
Model7b.PollutantID=Model8b.PollutantID
INNER JOIN Modell2b ON
Model7b.FuelFormulationID=Modell2b.FuelFormulationID and
Model7b.PollutantID=Modell2b.PollutantID
GROUP BY Model7b.FuelFormulationID, Model7b.PollutantID;
— clean up intermediate tables
drop TABLE if exists model!2;
drop TABLE if exists model!2b;
drop TABLE if exists mode!7;
56
-------
drop TABLE if exists mode!7b;
drop TABLE if exists modelS;
drop TABLE if exists modelSb;
drop TABLE if exists si;
drop TABLE if exists s2;
drop TABLE if exists s3;
drop TABLE if exists s4;
drop TABLE if exists s5;
drop TABLE if exists s6;
drop TABLE if exists s7;
******************
— The first table join does NOX.
***********************************************************************
******************
UPDATE FuelFormulation SET PollutantID = 3;
drop TABLE if exists SI;
CREATE TABLE SI
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.RVP,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.RVP - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as RVPdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 2
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON SI (FuelFormulationID, PollutantID);
drop TABLE if exists S2;
CREATE TABLE S2
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.T50,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.T50 - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as TSOdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
57
-------
WHERE PredictiveModelCoeff2.predCoefficientID = 3
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S2 (FuelFormulationID, PollutantID);
drop TABLE if exists S3;
CREATE TABLE S3
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.T90,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.T90 - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as T90diff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 4
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S3 (FuelFormulationID, PollutantID);
drop TABLE if exists S4;
CREATE TABLE S4
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.AROM ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.AROM - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as AROMdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 5
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S4 (FuelFormulationID, PollutantID);
drop TABLE if exists S5;
CREATE TABLE S5
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.OLEF ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.OLEF - PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as OLEFdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 6
58
-------
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S5 (FuelFormulationID, PollutantID);
drop TABLE if exists S6;
CREATE TABLE S6
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.Oxygen ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.Oxygen
PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as
Oxygendiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 7
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S6 (FuelFormulationID, PollutantID) ;
drop TABLE if exists S7;
CREATE TABLE S7
SELECT FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID,
FuelFormulation.Sulfur ,
PredictiveModelCoeff2.predCoefficientID,
(FuelFormulation.Sulfur -
PredictiveModelCoeff2.predMean)/
PredictiveModelCoeff2.predStdDev as
Sulfurdiff
FROM FuelFormulation LEFT JOIN PredictiveModelCoeff2
ON FuelFormulation.PollutantID =
PredictiveModelCoeff2.PollutantID
WHERE PredictiveModelCoeff2.predCoefficientID = 8
GROUP BY FuelFormulation.FuelFormulationID,
FuelFormulation.PollutantID;
CREATE UNIQUE INDEX Indexl ON S7 (FuelFormulationID, PollutantID);
— Create the standardized Table.
drop TABLE if exists S;
CREATE TABLE S
SELECT SI.FuelFormulationID, SI.PollutantID,
SI.predCoefficientID,
Sl.RVPdiff as StandardCoeff
FROM S1
59
-------
GROUP BY FuelFormulationID, PollutantID, predCoefficientID;
UPDATE S SET predCoefficientID = 1;
UPDATE S SET StandardCoeff = 1.000;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT SI.FuelFormulationID,
SI.PollutantID,
SI.predCoefficientID,
Sl.RVPdiff
FROM S1
GROUP BY SI.FuelFormulationID, SI.PollutantID, SI.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S2.FuelFormulationID,
S2.PollutantID,
S2.predCoefficientID,
S2.T50diff
FROM S2
GROUP BY S2.FuelFormulationID, S2.PollutantID, S2.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S3.FuelFormulationID,
S3.PollutantID,
S3.predCoefficientID,
S3.T90diff
FROM S3
GROUP BY S3.FuelFormulationID, S3.PollutantID, S3.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S4.FuelFormulationID,
S4.PollutantID,
S4.predCoefficientID,
S4.AROMdiff
FROM S4
GROUP BY S4.FuelFormulationID, S4.PollutantID, S4.predCoefficientID;
60
-------
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S5.FuelFormulationID,
S5.PollutantID,
S5.predCoefficientID,
SS.OLEFdiff
FROM S5
GROUP BY S5.FuelFormulationID, S5.PollutantID, S5.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S6.FuelFormulationID,
S6.PollutantID,
S6.predCoefficientID,
S6.Oxygendiff
FROM S 6
GROUP BY S6.FuelFormulationID, S6.PollutantID, S6.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S7.FuelFormulationID,
S7.PollutantID,
S7.predCoefficientID,
S7.Sulfurdiff
FROM S7
GROUP BY S7.FuelFormulationID, S7.PollutantID, S7.predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
9 as predCoefficientID,
1.00 as HiEmit
FROM S7
GROUP BY FuelFormulationID, PollutantID, predCoefficientID;
INSERT INTO S
( FuelFormulationID,
PollutantID,
61
-------
predCoefficientID,
StandardCoeff )
SELECT S6.FuelFormulationID,
S6.PollutantID,
10 as predCoefficientID,
S6.0xygendiff*S7.Sulfurdiff
FROM S6 INNER JOIN S7
ON S6.FuelFormulationID
S6.PollutantID
S7.FuelFormulationID
S7.PollutantID;
AND
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S6.FuelFormulationID,
S6.PollutantID,
11 as predCoefficientID,
S6.0xygendiff*S2.TSOdiff
FROM S6 INNER JOIN S2
ON S6.FuelFormulationID
S6.PollutantID
S2.FuelFormulationID
S2.PollutantID;
AND
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S6.FuelFormulationID,
S6.PollutantID,
12 as predCoefficientID,
S 6.Oxygendi ff* S 3.T 9 Odi ff
FROM S6 INNER JOIN S3
ON S6.FuelFormulationID
S6.PollutantID
S3.FuelFormulationID
S3.PollutantID;
AND
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S6.FuelFormulationID,
S6.PollutantID,
13 as predCoefficientID,
S6.0xygendiff*S4.AROMdiff
FROM S6 INNER JOIN S4
ON S6.FuelFormulationID
S6.PollutantID
S4.FuelFormulationID
S4.PollutantID;
AND
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
62
-------
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
14 as predCoefficientID,
S6.Oxygendiff*S6.Oxygendiff
FROM S 6;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
15 as predCoefficientID,
S2.T50diff*S2.T50diff
FROM S2;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT FuelFormulationID,
PollutantID,
16 as predCoefficientID,
S7.Sulfurdiff*S7.Sulfurdiff
FROM S7;
INSERT INTO S
( FuelFormulationID,
PollutantID,
predCoefficientID,
StandardCoeff )
SELECT S7.FuelFormulationID,
S7.PollutantID,
17 as predCoefficientID,
S7.Sulfurdiff*S3.T90diff
FROM S7 INNER JOIN S3
ON S7.FuelFormulationID = S3.FuelFormulationID AND
S7.PollutantID = S3.PollutantID;
CREATE UNIQUE INDEX Indexl ON S (FuelFormulationID, PollutantID,
predCoefficientID);
-- develop the six individual NOx predictive Models.
63
-------
drop TABLE if exists Model1;
CREATE TABLE Model1
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeff1.predCoefficient,
S.StandardCoeff^PredictiveModelCoeff1.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeff1
ON S.PollutantID
PredictiveModelCoeff1.PollutantID AND
S.predCoefficientID
PredictiveModelCoeff1.predCoefficientID
WHERE PredictiveModelCoeff1.predModellD = 1
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON Model1
predCoefficientID);
[FuelFormulationID, PollutantID,
drop TABLE if exists Model2;
CREATE TABLE Model2
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeff1.predCoefficient,
S.StandardCoeff^PredictiveModelCoeffl.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeffl
ON S. PollutantID
PredictiveModelCoeffl.PollutantID AND
S.predCoefficientID
PredictiveModelCoeffl.predCoefficientID
WHERE PredictiveModelCoeff1.predModellD = 2
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON Model2 (FuelFormulationID, PollutantID,
predCoefficientID);
drop TABLE if exists ModelS;
CREATE TABLE ModelS
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeffl.predCoefficient,
S.StandardCoeff^PredictiveModelCoeff1.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeffl
ON S.PollutantID
PredictiveModelCoeffl.PollutantID AND
S.predCoefficientID
PredictiveModelCoeffl.predCoefficientID
64
-------
WHERE PredictiveModelCoeffl.predModellD = 3
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON ModelS (FuelFormulationID, PollutantID,
predCoefficientID);
drop TABLE if exists Model4;
CREATE TABLE Model4
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeff1.predCoefficient,
S.StandardCoeff^PredictiveModelCoeffl.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeff1
ON S.PollutantID
PredictiveModelCoeff1.PollutantID AND
S.predCoefficientID
PredictiveModelCoeff1.predCoefficientID
WHERE PredictiveModelCoeff1.predModellD = 4
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON Model4 (FuelFormulationID, PollutantID,
predCoefficientID);
drop TABLE if exists ModelS;
CREATE TABLE ModelS
SELECT S.FuelFormulationID,
S.PollutantID,
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeff1.predCoefficient,
S.StandardCoeff^PredictiveModelCoeff1.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeffl
ON S.PollutantID
PredictiveModelCoeffl.PollutantID AND
S.predCoefficientID
PredictiveModelCoeffl.predCoefficientID
WHERE PredictiveModelCoeff1.predModellD = 5
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON ModelS (FuelFormulationID, PollutantID,
predCoefficientID);
drop TABLE if exists ModelG;
CREATE TABLE Model6
SELECT S.FuelFormulationID,
S.PollutantID,
65
-------
S.predCoefficientID,
S.StandardCoeff,
PredictiveModelCoeff1.predCoefficient,
S.StandardCoeff^PredictiveModelCoeff1.predCoefficient as Ml
FROM S LEFT JOIN PredictiveModelCoeff1
ON S. PollutantID
PredictiveModelCoeff1.PollutantID AND
S.predCoefficientID
PredictiveModelCoeff1.predCoefficientID
WHERE PredictiveModelCoeff1.predModellD = 6
GROUP BY S.FuelFormulationID, S.PollutantID, S.predCoefficientID;
CREATE UNIQUE INDEX Indexl ON ModelG (FuelFormulationID, PollutantID,
predCoefficientID);
drop TABLE if exists Modellb;
CREATE TABLE Modellb
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM Model1
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON Modellb (FuelFormulationID, PollutantID);
drop TABLE if exists Model2b;
CREATE TABLE Model2b
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM Model2
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON Model2b (FuelFormulationID, PollutantID);
drop TABLE if exists ModelSb;
CREATE TABLE ModelSb
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM ModelS
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON ModelSb (FuelFormulationID, PollutantID);
drop TABLE if exists Model4b;
66
-------
CREATE TABLE Model4b
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM Model4
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON Model4b (FuelFormulationID, PollutantID);
drop TABLE if exists ModelSb;
CREATE TABLE ModelSb
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM ModelS
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON ModelSb (FuelFormulationID, PollutantID);
drop TABLE if exists ModelGb;
CREATE TABLE Model6b
SELECT FuelFormulationID, PollutantID, SUM(Ml) as Coeff,
EXP( SUM(Ml) ) as ECoeff
FROM Model6
GROUP BY FuelFormulationID, PollutantID;
CREATE UNIQUE INDEX Indexl ON ModelGb (FuelFormulationID, PollutantID);
drop TABLE if exists ModelNOx;
CREATE TABLE ModelNOx
SELECT Modellb.FuelFormulationID, Modellb.PollutantID,
Modellb.ECoeff as El,
Model2b.ECoeff as E2,
ModelSb.ECoeff as E3,
Model4b.ECoeff as E4,
ModelSb.ECoeff as E5,
ModelGb.ECoeff as E6,
(SUM(Modellb.ECoeff + Model2b.ECoeff + ModelSb.ECoeff +
Model4b.ECoeff + ModelSb.ECoeff + ModelGb.ECoeff)/6)
as E
FROM Modellb INNER JOIN Model2b ON
Modellb.FuelFormulationID=Model2b.FuelFormulationID and
Modellb.PollutantID=Model2b.PollutantID
INNER JOIN ModelSb ON
Modellb.FuelFormulationID=Model3b.FuelFormulationID and
Modellb.PollutantID=Model3b.PollutantID
INNER JOIN Model4b ON
Modellb.FuelFormulationID=Model4b.FuelFormulationID and
67
-------
Modellb.PollutantID=Model4b.PollutantID
INNER JOIN ModelSb ON
Modellb.FuelFormulationID=Model5b.FuelFormulationID and
Modellb.PollutantID=Model5b.PollutantID
INNER JOIN ModelGb ON
Modellb.FuelFormulationID=Model6b.FuelFormulationID and
Modellb.PollutantID=Model6b.PollutantID
GROUP BY Modellb.FuelFormulationID, Modellb.PollutantID;
-- clean up intermediate tables
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
drop
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
TABLE
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
if
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
exists
model 1;
mode!2 ;
models ;
mode!4 ;
models ;
model 6;
model Ib;
mode!2b;
modelSb;
mode!4b;
modelSb;
model 6b;
model!2;
model!2b;
model? ;
mode!7b;
models ;
modelSb;
si;
s2;
s3;
s4;
sS;
s6;
s7;
End Predictive Model for HC and NOx.
No
Sulfur Effects
68
-------
drop TABLE if exists PlHCNOx;
CREATE TABLE PlHCNOx
SELECT HC.PollutantID,
HC.FuelFormulationID,
HC.E
FROM ModelHC as HC
GROUP BY PollutantID, FuelFormulationID;
INSERT INTO PlHCNOx
( PollutantID,
FuelFormulationID,
E )
SELECT NOx.PollutantID,
NOx.FuelFormulationID,
NOx.E
FROM ModelNOx as NOx
GROUP BY PollutantID, FuelFormulationID;
ALTER TABLE PlHCNOx ADD PRIMARY KEY (PollutantID, FuelFormulationID);
CREATE UNIQUE INDEX Indexl ON PlHCNOx (PollutantID, FuelFormulationID);
— Bring in the BASE or Reference
FuelFormulationID = 98
This fuel is based on Maricopa County,
Arizona.
—FuelSubType 10
Conventional Gasoline
—OXYGEN (wt%) 3.5
—SULFUR (ppm) 30.0
—RVP (psi) 8.7
—E200 (%) 41.0
—E300 (%) 83.0
—AROMATICS (vol%) 32.1
—OLEFINS (vol%) 7.7
—FUEL BENZENE (vol%) 0.8
—T50 218
—T90 329
drop TABLE if exists PlHCNOxref;
CREATE TABLE PlHCNOxref
69
-------
SELECT HC.PollutantID,
HC.FuelFormulationID,
HC.E
FROM ModelHC as HC
WHERE FuelFormulationID = 98
GROUP BY PollutantID, FuelFormulationID;
INSERT INTO PlHCNOxref
( PollutantID,
FuelFormulationID,
E )
SELECT NOx.PollutantID,
NOx.FuelFormulationID,
NOx.E
FROM ModelNOx as NOx
WHERE FuelFormulationID = 98
GROUP BY PollutantID, FuelFormulationID;
ALTER TABLE PlHCNOxref ADD PRIMARY KEY (PollutantID,
FuelFormulationID);
CREATE UNIQUE INDEX Indexl ON PlHCNOxref (PollutantID,
FuelFormulationID);
— Predictive Model creates the Fuel AdjustmentFactor for each
fuelformulationID and
— pollutantlD. The adjustment is the ratio of the
Predictive Model Output for
— a particular Target Fuel to the Predictive Model Output of
the Reference fuel.
The Predictive Model is not a function of vehicle model year
(fuel model year group)
or SourceTypelD.
drop TABLE if exists PI;
CREATE TABLE PI
SELECT PlHCNOx.PollutantID,
PlHCNOx.FuelFormulationID,
PlHCNOx.E / PlHCNOxref.E as AdjustmentFactor
FROM PlHCNOx LEFT JOIN PlHCNOxref
ON PlHCNOx.PollutantID
PlHCNOxref.PollutantID
GROUP BY PollutantID, FuelFormulationID;
ALTER TABLE PI ADD PRIMARY KEY (PollutantID, FuelFormulationID);
CREATE UNIQUE INDEX Indexl ON PI (PollutantID, FuelFormulationID);
alter TABLE PI ADD (
FuelMYGroupID INT(8)
70
-------
UPDATE PI SET FuelMYGroupID = 1974;
drop TABLE if exists PredictiveModelHCNOx;
CREATE TABLE PredictiveModelHCNOx
SELECT PI.PollutantID,
PI.FuelFormulationID,
PI.FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
19751986 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
19871989 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
19901993 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
71
-------
PI.FuelFormulationID,
1994 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
1995 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
1996 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
19972000 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
20012003 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
72
-------
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
2004 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
2005 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
2006 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
2007 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
73
-------
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
20082009 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
FuelFormulationID,
FuelMYGroupID,
AdjustmentFactor )
SELECT PI.PollutantID,
PI.FuelFormulationID,
20102050 as FuelMYGroupID,
PI.AdjustmentFactor
FROM PI
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
ALTER TABLE PredictiveModelHCNOx
ADD PRIMARY KEY (PollutantID, FuelMYGroupID,
FuelFormulationID);
CREATE UNIQUE INDEX Indexl ON
PredictiveModelHCNOx (PollutantID, FuelMYGroupID,
FuelFormulationID);
— Add in the other sourcetypelD categories. These are currently
duplicate adjustment factors.
drop TABLE if exists dummy;
CREATE TABLE dummy
SELECT PM.PollutantID,
PM.FuelFormulationID,
PM.FuelMYGroupID,
PM.AdjustmentFactor
FROM PredictiveModelHCNOx as PM
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
ALTER TABLE dummy
ADD (
SourceTypelD smallint(6)
) ;
UPDATE dummy SET SourceTypelD = 11;
drop TABLE if exists PredictiveModelHCNOx;
CREATE TABLE PredictiveModelHCNOx
74
-------
SELECT dummy.PollutantID,
dummy.SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
21 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
31 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
32 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
75
-------
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
41 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
42 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
43 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
51 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
76
-------
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
52 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
53 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
54 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
61 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
77
-------
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO PredictiveModelHCNOx
( PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
AdjustmentFactor )
SELECT dummy.PollutantID,
62 as SourceTypelD,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
ALTER TABLE PredictiveModelHCNOx
ADD PRIMARY KEY (PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID);
CREATE UNIQUE INDEX Indexl ON
PredictiveModelHCNOx (PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
do final Table clean-up.
drop TABLE if exists s;
drop TABLE if exists dummy;
drop TABLE if exists pi;
drop TABLE if exists plhcnox;
drop TABLE if exists plhcnoxref;
*******************
***********************************************************************
*******************
***********************************************************************
*******************
***********************************************************************
*******************
***********************************************************************
*******************
Add MOBILE6.2 Sulfur Effects into the Fuel Model for
HC and NOx
78
-------
The current PredictiveModelHCNOx table does not
contain sulfur effects.
*******************
***********************************************************************
*******************
***********************************************************************
*******************
***********************************************************************
*******************
***********************************************************************
*******************
— Obtain the Sulfur levels from the MOVESFuelRFS_Base.FuelFormulation
table. It is the original
data source. The RFSPredictive.FuelFormulation table
has all of the Sulfur
values set to 30 ppm.
This first section adds the SHORT TERM Sulfur effects to the HC
and NOx pollutants.
drop TABLE if exists PredictiveSulfurHCNOxl;
CREATE TABLE PredictiveSulfurHCNOxl
SELECT
P.PollutantID,
P.SourceTypelD,
P.FuelMYGroupID,
FF.FuelFormulationID,
P.AdjustmentFactor as OtherFactor,
FF.SulfurLevel as Sulfur,
FF.fuelSubtypelD
FROM PredictiveModelHCNOx P LEFT JOIN
MOVESFuelRFS_Base.FuelFormulation FF
ON P.FuelFormulationID = FF.FuelFormulationID
WHERE FF.fuelSubtypelD in (10, 11, 12, 13, 14)
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
PredictiveSulfurHCNOxl (PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
***********************************************************************
*******
— These sulfur coefficients were taken from the MOBILE6 report by
Tesh Rao. *
79
-------
emitterType is "H" for High emitter and "N" for Normal emitter.
Some equations are log-log and others are log-linear.
*******
drop TABLE if exists SulfurCoeff;
CREATE TABLE SulfurCoeff (
processID smallint(6),
pollutantID smallint(6),
emitterType CHAR ( 1 ) ,
sourceTypelD smallint(6),
fuelMYGroupID INT (8),
functionType CHAR (10),
SulfurCoeff float,
PRIMARY KEY (processID, pollutantID,
emitterType, sourceTypelD, fuelMYGroupID)
LOAD DATA INFILE 'C:\\Eds C Files\\MOVES FUELBinner\\SQL Fuel Binner
Code\\SulfurCoefficients.csv' REPLACE
INTO TABLE SulfurCoeff
FIELDS TERMINATED BY ' , '
OPTIONALLY ENCLOSED BY ""
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
CREATE UNIQUE INDEX Indexl ON
SulfurCoeff (processID, PollutantID, emitterType,
SourceTypelD, FuelMYGroupID);
This section is only the Normal emitters. This process was done
piecemeal
— rather than normals and high together that is why they are
in separate
— sections. Rational coding would have done this together.
drop TABLE if exists PredictiveSulfurHCNOx2;
CREATE TABLE PredictiveSulfurHCNOx2
SELECT
C.PollutantID,
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
80
-------
FROM
ON
SC.sulfurCoeff
PredictiveSulfurHCNOxl C LEFT JOIN SulfurCoeff
SC
C.PollutantID = SC.PollutantID
C.SourceTypelD = SC.SourceTypelD
C.FuelMYGroupID = SC.FuelMYGroupID
WHERE SC.emitterType = 'N' and SC.processID = 1
GROUP BY SC.ProcessID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
and
and
CREATE UNIQUE INDEX Indexl ON
Predict!veSulfurHCNOx2
SourceTypelD,
(ProcessID, PollutantID,
FuelMYGroupID, FuelFormulationID);
drop TABLE if exists Junk;
CREATE TABLE Junk
SELECT
C.PollutantID,
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
SC.sulfurCoeff
PredictiveSulfurHCNOxl
C.PollutantID
C.SourceTypelD
C.FuelMYGroupID
FROM
ON
C LEFT JOIN SulfurCoeff
SC.PollutantID
SC.SourceTypelD
SC.FuelMYGroupID
SC
and
and
WHERE
GROUP
SC.emitterType = 'N' and SC.processID = 2
BY SC.processID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
INSERT INTO PredictiveSulfurHCNOx2
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
OtherFactor,
processID,
functionType,
emitterType,
SulfurCoeff )
SELECT
PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
81
-------
OtherFactor,
processID,
functionType,
emitterType,
sulfurCoeff
FROM Junk
GROUP BY ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID;
drop TABLE if exists Junk;
Alter TABLE PredictiveSulfurHCNOx2 ADD (
SulfAdj float,
Sulfl float,
SulfSO float,
longTermSulfur float,
SulfIRR float,
SulfMax float,
FinalSulfAdj float,
IRFactor float,
SulfAdj2 float,
SulfGPA float,
SulflOOO float,
GPASulfAdj float,
AdjustmentFactorAll float
Calculate Short Term Sulfur Effects for Normal Emitters
*************************************************************
— TierO and LEV + vehicles use LOG - LOG algorithm.
UPDATE PredictiveSulfurHCNOx2 SET Sulfl
EXP(sulfurCoeff* LN(Sulfur));
UPDATE PredictiveSulfurHCNOx2 SET SulfSO
EXP(sulfurCoeff* LN(30));
-- Tierl vehicles use a LOG - Linear algorithm.
UPDATE PredictiveSulfurHCNOx2 SET Sulfl
EXP(sulfurCoeff*Sulfur)
WHERE FuelMYGroupID IN (1994, 1995, 1996,
19972000);
UPDATE PredictiveSulfurHCNOx2 SET SulfSO
EXP(sulfurCoeff*30)
WHERE FuelMYGroupID IN (1994, 1995, 1996,
19972000);
82
-------
— Used for both TierO and Tierl.
UPDATE PredictiveSulfurHCNOx2 SET SulfAdj
WHERE Sulfur = 30;
UPDATE PredictiveSulfurHCNOx2 SET SulfAdj
(Sulfl - SulfSO) / SulfSO
WHERE Sulfur <> 30;
Calculate Long Term Sulfur Effects *
*****************************************
Add the long term sulfur effects to the LEV and later vehicles for
sulfur levels greater
than 30 ppm. Sulfur levels below 30 ppm are assumed to
have no long term effects.
— There is no way to logically extrapolate these effects below 30
ppm. If 30 ppm has
— a zero long term sulfur effect then Sulfur < 30 ppm should
not have any effect
either.
UPDATE PredictiveSulfurHCNOx2 SET longTermSulfur = 1.0;
UPDATE PredictiveSulfurHCNOx2 SET longTermSulfur = 2.50
WHERE PollutantID = 1 and
FuelMYGroupID IN (20012003, 2004, 2005, 2006, 2007,
20082009, 20102050) and
Sulfur > 30.0;
UPDATE PredictiveSulfurHCNOx2 SET longTermSulfur = 1.47
WHERE PollutantID = 3 and
FuelMYGroupID IN (20012003, 2004, 2005, 2006, 2007,
20082009, 20102050) and
Sulfur > 30.0;
UPDATE PredictiveSulfurHCNOx2 SET SulfAdj2
SulfAdj^LongTermSulfur;
***********************************************************************
***********
— Sulfur irreversibility effects for 2004+ model years and Sulfur > 30
ppm only. *
***********************************************************************
***********
83
-------
-- compute the normal sulfur irreversibility effects
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(303))
WHERE FuelMYGroupID = 2004;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(303))
WHERE FuelMYGroupID = 2005;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(87))
WHERE FuelMYGroupID = 2006;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(87))
WHERE FuelMYGroupID = 2007;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(80))
WHERE FuelMYGroupID IN (20082009, 20102050);
-- compute the sulfur irreversibility effects if the selected sulfur
level is
— greater than the maximum sulfur level. Note these may not
exist in the
— fuel supply table (real world) but the model needs
fueladjustment effects
anyway as placeholders. Making the maximum sulfur level
equal to the
actual one assures that the sulfur irreversibility effects
are not
smaller than the actual effects.
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2004 and Sulfur > 303;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2005 and Sulfur > 303;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2006 and Sulfur > 87;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2007 and Sulfur > 87;
UPDATE PredictiveSulfurHCNOx2 SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID IN (20082009, 20102050) and
Sulfur > 80;
***********
— Sulfur irreversibility effects for 2004+ model years and Sulfur > 30
ppm only. *
84
-------
***********
UPDATE PredictiveSulfurHCNOx2 SET SulfMax
UPDATE PredictiveSulfurHCNOx2 SET SulfMax
(SulfIRR - SulfSO) / SulfSO WHERE
FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and Sulfur > 30.0;
UPDATE PredictiveSulfurHCNOx2 SET IRFactor = 0.000;
UPDATE PredictiveSulfurHCNOx2 SET IRFactor = 0.425
WHERE FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and
Sulfur > 30.0;
— SulfAdj2 is the fractional increase in emissions of the sulfur
compared with the base
— sulfur level 30 ppm. SulfMax is the increase due to the
sulfur irreversibility
effects.
UPDATE PredictiveSulfurHCNOx2
SET FinalSulfAdj = (IRFactor * SulfMax) + (1.0 -
IRFactor) * SulfAdj2;
UPDATE PredictiveSulfurHCNOx2 SET FinalSulfAdj = 1 +
FinalSulfAdj;
— This bottoms the sulfur effects at levels around 4 ppm Sulfur.
Lower gasoline sulfur
— levels will have no effect on the emissions. This cap is
needed because the
EXTRAPOLATED relationship is log-log in nature.
-- Currently the lowest default fuel sulfur level is 10 ppm S.
UPDATE PredictiveSulfurHCNOx2 SET FinalSulfAdj
0.50 WHERE FinalSulfAdj < 0.50;
************************
Sulfur GPA Effects *
************************
— Sulfur GPA is only applied to FuelMYGroupIDs of 2004, 2005 and 2006.
It is a phase-out
strategy for sulfur in the Rocky Mountain states.
UPDATE PredictiveSulfurHCNOx2 SET SulflOOO
EXP(sulfurCoeff* LN(IOOO.O))
85
-------
WHERE FuelMYGroupID IN (2004, 2005, 2006);
UPDATE PredictiveSulfurHCNOx2 SET SulfGPA
UPDATE PredictiveSulfurHCNOx2 SET SulfGPA
(SulflOOO - SulfSO) / SulfSO WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
1.00;
UPDATE PredictiveSulfurHCNOx2 SET GPASulfAdj
FinalSulfAdj;
UPDATE
* SulfAdj2
> 30.0;
PredictiveSulfurHCNOx2 SET GPASulfAdj
(IRFactor * SulfGPA) + (1.0 - IRFactor)
WHERE FuelMYGroupID IN (2004, 2005, 2006) and Sulfur
UPDATE PredictiveSulfurHCNOx2 SET GPASulfAdj = 1.0 +
GPASulfAdj WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
*********************************************************************
*********************************************************************
*********************************************************************
Sulfur effects for High emitters only
*********************************************************************
*********************************************************************
Add the sulfur coefficients to the existing Predictive model
results .
— This section is only the HIGH emitters. This process was done
piecemeal
— rather than normals and high together that is why they are
in separate
sections. Rational coding would have done this together.
drop TABLE if exists PredictiveSulfurHCNOxHigh;
CREATE TABLE PredictiveSulfurHCNOxHigh
SELECT
C.PollutantID,
86
-------
FROM
ON
WHERE
GROUP
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
SC.sulfurCoeff
Predict!veSulfurHCNOxl
C.PollutantID
C.SourceTypelD
C.FuelMYGroupID
C LEFT JOIN SulfurCoeff
SC.PollutantID
SC.SourceTypelD
SC.FuelMYGroupID
SC
and
and
SC.emitterType = 'H' and SC.processID = 1
BY SC.ProcessID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
PredictiveSulfurHCNOxHigh (ProcessID, PollutantID,
SourceTypelD,
FuelMYGroupID, FuelFormulationID);
drop TABLE if exists Junk;
CREATE TABLE Junk
SELECT
C.PollutantID,
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
SC.sulfurCoeff
PredictiveSulfurHCNOxl
C.PollutantID
C.SourceTypelD
C.FuelMYGroupID
FROM
ON
C LEFT JOIN SulfurCoeff
SC.PollutantID
SC.SourceTypelD
SC.FuelMYGroupID
SC
and
and
WHERE
GROUP
SC.emitterType = 'H' and SC.processID = 2
BY SC.processID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
INSERT INTO PredictiveSulfurHCNOxHigh
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
OtherFactor,
processID,
functionType,
87
-------
emitterType,
sulfurCoeff )
SELECT
PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
OtherFactor,
processID,
functionType,
emitterType,
sulfurCoeff
FROM Junk
GROUP BY ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID;
drop TABLE if exists Junk;
Alter TABLE PredictiveSulfurHCNOxHigh ADD
SulfAdj float,
Sulfl float,
SulfSO float,
longTermSulfur float,
SulfIRR float,
SulfMax float,
FinalSulfAdj float,
IRFactor float,
SulfAdj2 float,
SulfGPA float,
SulflOOO float,
GPASulfAdj float,
AdjustmentFactorAll float
Calculate Short Term Sulfur Effects for High Emitters *
*************************************************************
— TierO and LEV + vehicles use LOG - LOG algorithm.
UPDATE PredictiveSulfurHCNOxHigh SET Sulfl
EXP(sulfurCoeff* LN(Sulfur));
UPDATE PredictiveSulfurHCNOxHigh SET SulfSO
EXP(sulfurCoeff* LN(30));
— Tierl vehicles use a LOG - Linear algorithm.
UPDATE PredictiveSulfurHCNOxHigh SET Sulfl
EXP(sulfurCoeff*Sulfur)
-------
WHERE FuelMYGroupID IN (1994, 1995, 1996,
19972000);
UPDATE PredictiveSulfurHCNOxHigh SET SulfSO
EXP(sulfurCoeff*30)
WHERE FuelMYGroupID IN (1994, 1995, 1996,
19972000);
-- TierO and Tierl used these equations.
UPDATE PredictiveSulfurHCNOxHigh SET SulfAdj
0.0 WHERE Sulfur = 30;
UPDATE PredictiveSulfurHCNOxHigh SET SulfAdj
(Sulfl - SulfSO) / SulfSO
WHERE Sulfur <> 30;
Calculate Long Term Sulfur Effects *
*****************************************
— Add the long term sulfur effects to the LEV and later vehicles for
sulfur levels greater
than 30 ppm. Sulfur levels below 30 ppm are assumed to
have no long term effects.
There is no way to logically extrapolate these effects below 30
ppm. If 30 ppm has
a zero long term sulfur effect then Sulfur < 30 ppm should
not have any effect
— either.
UPDATE PredictiveSulfurHCNOxHigh SET longTermSulfur
1.0;
UPDATE PredictiveSulfurHCNOxHigh SET longTermSulfur
2.50 WHERE PollutantID = 1 and
FuelMYGroupID IN (20012003, 2004, 2005, 2006, 2007,
20082009, 20102050) and
Sulfur > 30.0;
UPDATE PredictiveSulfurHCNOxHigh SET longTermSulfur
1.47 WHERE PollutantID = 3 and
FuelMYGroupID IN (20012003, 2004, 2005, 2006, 2007,
20082009, 20102050) and
Sulfur > 30.0;
UPDATE PredictiveSulfurHCNOxHigh SET SulfAdj2
SulfAdj^LongTermSulfur;
89
-------
***********
-- Sulfur irreversibility effects for 2004+ model years and Sulfur > 30
ppm only. *
***********************************************************************
***********
— compute the normal sulfur irreversibility effects
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(303))
WHERE FuelMYGroupID = 2004;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(303))
WHERE FuelMYGroupID = 2005;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(87))
WHERE FuelMYGroupID = 2006;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(87))
WHERE FuelMYGroupID = 2007;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(80))
WHERE FuelMYGroupID IN (20082009, 20102050);
-- compute the sulfur irreversibility effects if the selected sulfur
level is
greater than the maximum sulfur level. Note these may not
exist in the
— fuel supply table (real world) but the model needs
fueladjustment effects
— anyway as placeholders. Making the maximum sulfur level
equal to the
actual one assures that the sulfur irreversibility effects
don't get
smaller than the actual effects.
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2004 and Sulfur > 303;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2005 and Sulfur > 303;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2006 and Sulfur > 87;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID = 2007 and Sulfur > 87;
UPDATE PredictiveSulfurHCNOxHigh SET SulfIRR
EXP(sulfurCoeff* LN(sulfur))
WHERE FuelMYGroupID IN (20082009, 20102050) and
Sulfur > 80;
90
-------
***********
-- Sulfur irreversibility effects for 2004+ model years and Sulfur > 30
ppm only. *
***********************************************************************
***********
UPDATE PredictiveSulfurHCNOxHigh SET SulfMax
0.0;
UPDATE PredictiveSulfurHCNOxHigh SET SulfMax
(SulfIRR - SulfSO) / SulfSO WHERE
FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and Sulfur > 30.0;
UPDATE PredictiveSulfurHCNOxHigh SET IRFactor
0.000;
UPDATE PredictiveSulfurHCNOxHigh SET IRFactor = 0.425
WHERE FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and
Sulfur > 30.0;
— SulfAdj2 is the fractional increase in emissions of the sulfur
compared with the base
— sulfur level 30 ppm. SulfMax is the increase due to the
sulfur irreversibility
effects.
UPDATE PredictiveSulfurHCNOxHigh
SET FinalSulfAdj = (IRFactor * SulfMax) + (1.0 -
IRFactor) * SulfAdj2;
UPDATE PredictiveSulfurHCNOxHigh SET FinalSulfAdj
1 + FinalSulfAdj;
— This bottoms the sulfur effects at levels around 4 ppm Sulfur.
Lower gasoline sulfur
— levels will have no effect on the emissions. This cap is
needed because the
EXTRAPOLATED relationship is log-log in nature.
-- Currently the lowest default fuel sulfur level is 10 ppm S.
UPDATE PredictiveSulfurHCNOxHigh SET FinalSulfAdj
0.50
WHERE FinalSulfAdj < 0.50;
91
-------
Sulfur GPA Effects *
************************
— Sulfur GPA is only applied to FuelMYGroupIDs of 2004, 2005 and 2006.
It is a phase-out
strategy for sulfur in the Rocky Mountain states.
UPDATE PredictiveSulfurHCNOxHigh SET SulflOOO
EXP(sulfurCoeff* LN(IOOO.O))
WHERE FuelMYGroupID IN (2004, 2005, 2006);
UPDATE PredictiveSulfurHCNOxHigh SET SulfGPA
1.00;
UPDATE PredictiveSulfurHCNOxHigh SET SulfGPA
(SulflOOO - SulfSO) / SulfSO WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
UPDATE PredictiveSulfurHCNOxHigh SET GPASulfAdj
FinalSulfAdj;
UPDATE PredictiveSulfurHCNOxHigh SET GPASulfAdj =
(IRFactor * SulfGPA) + (1.0 - IRFactor)
* SulfAdj2
WHERE FuelMYGroupID IN (2004, 2005, 2006) and Sulfur
> 30.0;
UPDATE PredictiveSulfurHCNOxHigh SET GPASulfAdj = 1.0 +
GPASulfAdj WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
-- MOBILE6.2 sulfur effects assume a 50/50 split between Highs and
Normals in weighting.
This is an extremely high split for modern vehicles in terms
numbers of high emitters
in the fleet. The impact of high emitters might still be 50% of
the total emissions.
— It was utilized for consistency with MOBILE6.2. It will reduce
the sensitivity of
— sulfur to emissions.
drop TABLE if exists PredictiveBlend;
CREATE TABLE PredictiveBlend
SELECT
B.ProcessID,
B.PollutantID,
B.FuelMYGroupID,
92
-------
B.FuelFormulationID,
B.SourceTypelD,
B.Sulfur,
B.OtherFactor,
B.FinalSulfAdj as SulfNorm,
H.FinalSulfAdj as SulfHigh,
(B.FinalSulfAdj*0.50+H.FinalSulfAdj*0.50) as FinalSulfAdj2,
B.GPASulfAdj as GPANorm,
H.GPASulfAdj as GPAHigh,
(B.GPASulfAdj*0.50 + H.GPASulfAdj*0.50) as FinalGPA
FROM PredictiveSulfurHCNOx2 B INNER JOIN
PredictiveSulfurHCNOxHigh H
ON B.PollutantID = H.PollutantID and
B.ProcessID = H.ProcessID and
B.FuelMYGroupID = H.FuelMYGroupID and
B.FuelFormulationID = H.FuelFormulationID and
B.SourceTypelD = H.SourceTypelD
GROUP BY B.ProcessID, B.PollutantID, B.SourceTypelD,
B.FuelMYGroupID, B.FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
PredictiveBlend (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
*****************
-- Add the reference fuel sulfur effects and compute the final sulfur
adjustment ratio. *
It is ratioed to Arizona's IM program and sulfur of 90 ppm.
***********************************************************************
*****************
drop TABLE if exists RefSulfur;
CREATE TABLE RefSulfur
SELECT
ProcessID,
PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
OtherFactor,
Sulfur,
FinalSulfAdj2 as RefSulfurAdj,
FinalGPA as RefGPA
FROM PredictiveBlend
WHERE FuelFormulationID = 98
GROUP BY ProcessID, PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID;
93
-------
CREATE UNIQUE INDEX Indexl ON
RefSulfur (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
— The Predictive name was used earlier and is recycled.
drop TABLE if exists Predictive;
CREATE TABLE Predictive
SELECT
P.ProcessID,
P.PollutantID,
P.SourceTypelD,
P.FuelMYGroupID,
P.FuelFormulationID,
P.OtherFactor,
P.Sulfur,
P.FinalGPA,
P.FinalSulfAdj2,
R. RefSulfurAdj,
P.FinalSulfAdj2 / R.RefSulfurAdj as SulfRatio,
P.OtherFactor * (P.FinalSulfAdj2 / R.RefSulfurAdj) as
fuelAdjustment
FROM PredictiveBlend P LEFT JOIN RefSulfur R
ON P.PollutantID = R.PollutantID
and
P.ProcessID = R.ProcessID
and
P. SourceTypelD = R.SourceTypelD
and
P.FuelMYGroupID = R.FuelMYGroupID
GROUP BY ProcessID, PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
Predictive (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
drop TABLE if exists PredictiveTest;
CREATE TABLE PredictiveTest
SELECT FuelFormulationOriginal.*,
P.PollutantID as PID,
P.ProcessID,
P.SourceTypelD,
P.FuelMYGroupID,
P.FuelFormulationID as FID,
P.OtherFactor,
P.FinalGPA,
P.FinalSulfAdj2,
P. RefSulfurAdj,
P.SulfRatio,
94
-------
P.FuelAdjustment
FROM Predictive P LEFT JOIN FuelFormulationOriginal
ON P.FuelFormulationID
FuelFormulationOriginal.FuelFormulationID
GROUP BY ProcessID, P.PollutantID, SourceTypelD, FuelMYGroupID,
P.FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
PredictiveTest (ProcessID, PID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
alter TABLE Predictive ADD (
polProcessID smallint(6),
fuelAdjustmentGPA float
UPDATE Predictive SET polProcessID = 101
WHERE pollutantID = 1 and ProcessID=l;
UPDATE Predictive SET polProcessID = 301
WHERE pollutantID = 3 and ProcessID=l;
UPDATE Predictive SET polProcessID = 102
WHERE pollutantID = 1 and ProcessID=2;
UPDATE Predictive SET polProcessID = 302
WHERE pollutantID = 3 and ProcessID=2;
UPDATE Predictive SET fuelAdjustmentGPA = fuelAdjustment *
(FinalGPA / FinalSulfAdj2);
drop TABLE if exists FuelAdjustment;
CREATE TABLE FuelAdjustment SELECT * from
MOVESDB20080828.FuelAdjustment;
-- This assumes that polProcessID = 111 is transfered in the
ComplexPredictivemodel script.
It should not be duplicated.
DELETE from FuelAdjustment WHERE polProcessID >= 0;
INSERT INTO FuelAdjustment
( polProcessID,
fuelMYGroupID,
SourceTypelD,
fuelFormulationID,
fuelAdjustment,
fuelAdjustmentCV,
fuelAdjustmentGPA,
fuelAdjustmentGPACV
SELECT
polProcessID,
fuelMYGroupID,
SourceTypelD,
fuelFormulationID,
95
-------
fuelAdjustment,
NULL as fuelAdjustmentCV,
fuelAdjustmentGPA,
NULL as fuelAdjustmentGPACV
FROM Predictive;
delete from FuelAdjustment WHERE fuelformulationID = 10;
96
-------
Appendix C MySQL Code for the Complex CO model and the
MOBILE6.2 Sulfur Model
FLUSH TABLES;
drop Database IF EXISTS RFSComplexC030_Base;
Create Database RFSComplexC030_Base;
USE RFSComplexCOSO Base;
****************
-- This script requires the successful completion of scrips ---
MOVEFuelBinnerRFS_Base . sql
Predictivemodel_RFS30_Base . sql
Predictivemodel_RFS90_Base . sql
***********************************************************************
****************
Required databases are:
MOVESDB20080828
MOVESFuelRFS Base
Required external text files are:
C:\\Eds C Files\\MOVES
FUELBinner\\ComplexModelCOCoefficients.csv
C:\\Eds C Files\\MOVES FUELBinner\\SQL Fuel Binner
Code\\SulfurCoefficients.csv
***********************************************************************
*******************
***********************************************************************
*******************
***********************************************************************
*******************
NOTES
97
-------
This script implements the EPA Complex model for the entire table
of MOVES FuelFormulations.
— Reference fuel sulfur = 30 ppm FuelFormulationID = 98
C:\\Eds C Files\\MOVES
FUELBinner\\ComplexModelHCNoxCoefficients.csv
Output TABLES
FuelAdjustment is the final output for this script. It contains
results for CO only.
It contains the Complex model adjustments for everything but
sulfur.
A section at the end contains the MOBILE6.2 sulfur adjustment
code.
These results will not match results from MOBILE6 or the official
complex model exactly.
TECHNOLOGY group weighting were assumed and changed for the 1996
model years.
For example, carbureted technology is gone.
*******************
— Create table containing the Complex Model Coefficients for the
Pollutant CO.
These coefficients are by TECH Group (1 - 10). Group 10 is the
High Emitter Group.
coCoefficient numbers are not consistent with other pollutant
coefficient numbers.
drop TABLE if exists ComplexModelCOCoeff;
CREATE TABLE ComplexModelCOCoeff (
pollutantID smallint(6),
techGroupID smallint(6),
coCoefficientID smallint(6),
coefficientName CHAR(12),
coCoefficient float,
PRIMARY KEY (pollutantID, techGroupID,
coCoefficientID)
LOAD DATA INFILE 'C:\\Eds C Files\\MOVES
FUELBinner\\ComplexModelCOCoefficients.csv1 REPLACE
INTO TABLE ComplexModelCOCoeff
FIELDS TERMINATED BY ' , '
OPTIONALLY ENCLOSED BY ""
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
98
-------
CREATE UNIQUE INDEX Indexl ON ComplexModelCOCoeff (pollutantID,
techGroupID, coCoefficientID);
-- Create Table with Complex Model Centering Values.
units are as follows:
Coefficients and
-OXYGEN
-SULFUR
-RVP
-E200
-E300
-AROMATICS
-OLEFINS
-BENZENE
(wt%)
(ppm)
(psi;
drop TABLE if exists ComplexModelCOCentering;
CREATE TABLE ComplexModelCOCentering (
pollutantID smallint(6),
techGroupID smallint(6),
c Oxygen float,
c_Sulfur float,
c_RVP float,
c_E200 float,
c_E300 float,
c_AROM float,
c_OLEF float,
c_BENZ float,
PRIMARY KEY (pollutantID, techGroupID)
INSERT INTO ComplexModelCOCentering
(pollutantID, techGroupID, c_0xygen, c_
c_E300, c_AROM, c_OLEF, c_BENZ ) VALUES
(2, 1, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
(2, 2, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
(2, 3, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
(2, 4, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
(2, 5, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
(2, 6, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
(2, 7, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
(2, 8, 1.774834, 204.577894, 8.6114785,
28.2610891, 7.3187162, 1.0666825),
Sulfur, c_RVP, c_E200,
46.7257717, 85.8961979,
46.7257717, 85.8961979,
46.7257717, 85.8961979,
46.7257717, 85.8961979,
46.7257717, 85.8961979,
46.7257717, 85.8961979,
46.7257717, 85.8961979,
46.7257717, 85.8961979,
99
-------
(2, 9, 1.774834, 204.577894, 8.6114785, 46.7257717, 85.8961979,
28.2610891, 7.3187162, 1.0666825),
(2, 10, 1.774834, 204.577894, 8.6114785, 46.7257717, 85.8961979,
28.2610891, 7.3187162, 1.0666825);
CREATE UNIQUE INDEX Indexl ON ComplexModelCOCentering (pollutantID,
techGroupID);
— Bring in MOVES Fuel Parameter information. No diesel fuels,
drop TABLE if exists FFX;
CREATE TABLE FFX
SELECT FuelFormulationID,
FuelSubtypelD,
RVP as P_RVP,
SulfurLevel as P Sulfur,
ETOHVolume,
MTBEVolume,
ETBEVolume,
TAMEVolume,
aromaticContent as P AROM,
olefinContent as P_OLEF,
benzeneContent as P_BENZ,
e200 as P_E200,
e300 as P_E300
FROM MOVESFuelRFS_Base.FuelFormulation
WHERE fuelSubtypelD in (10, 11, 12, 13, 14);
alter TABLE FFX ADD (
P Oxygen float,
pollutantID smallint(6),
techGroupID smallint(6)
UPDATE FFX SET pollutantID = 2;
UPDATE FFX SET techGroupID = 1;
The Predictive model requires the fuel Oxygen parameter. The MOVES
model contains ETOH and MTBE Volume data.
— These parameters must be converted.
UPDATE FFX SET P_0xygen =0.0
WHERE ETOHVolume <= 0.0 and MTBEVolume <= 0.0;
UPDATE FFX SET P_0xygen = ETOHVolume / 2.900
WHERE ETOHVolume > 0.0 and MTBEVolume <= 0.0;
UPDATE FFX SET P_0xygen = MTBEVolume / 5.600
WHERE ETOHVolume <= 0.0 and MTBEVolume > 0.0;
100
-------
ALTER TABLE FFX DROP ETOHVolume;
ALTER TABLE FFX DROP MTBEVolume;
ALTER TABLE FFX DROP ETBEVolume;
ALTER TABLE FFX DROP TAMEVolume;
CREATE UNIQUE INDEX Indexl ON FFX (FuelFormulationID);
**********
— The fuel sulfur values in the working table FFX are being changed to
30 ppm to *
remove the effects of sulfur. The actual values are being
stored in *
ActualFuelFormulation for later use.
*
***********************************************************************
**********
drop TABLE if exists ActualFuelFormulation;
CREATE TABLE ActualFuelFormulation SELECT * from FFX;
CREATE UNIQUE INDEX Indexl ON ActualFuelFormulation
(FuelFormulationID);
UPDATE FFX SET P Sulfur = 30.0;
drop TABLE if exists FuelFormulation;
CREATE TABLE FuelFormulation
SELECT PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P Sulfur,
101
-------
SELECT
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN )
PollutantID,
2 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN )
SELECT PollutantID,
3 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P BENZ,
102
-------
SELECT
P_E200,
P_E300,
P_OXYGEN )
PollutantID,
4 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN )
SELECT PollutantID,
5 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P OXYGEN )
103
-------
SELECT
PollutantID,
6 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN )
SELECT PollutantID,
7 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN )
SELECT PollutantID,
8 as TechGroupID,
FuelFormulationID,
104
-------
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN )
SELECT PollutantID,
9 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN
FROM FFX;
INSERT INTO FuelFormulation
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN )
SELECT PollutantID,
10 as TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P Sulfur,
105
-------
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN
FROM FFX;
CREATE UNIQUE INDEX Indexl ON FuelFormulation (FuelFormulationID,
TechGroupID);
drop TABLE if exists Cl;
CREATE TABLE Cl
SELECT FuelFormulation.PollutantID,
FuelFormulation.TechGroupID,
FuelFormulation.FuelFormulationID,
FuelFormulation.FuelSubtypelD,
FuelFormulation.P_RVP,
FuelFormulation.P Sulfur,
FuelFormulation.P_AROM,
FuelFormulation.P_OLEF,
FuelFormulation.P_BENZ,
FuelFormulation.P_E200,
FuelFormulation.P_E300,
FuelFormulation.P_OXYGEN,
ComplexModelCOCentering.c_0xygen,
ComplexModelCOCentering.c Sulfur,
ComplexModelCOCentering.c_RVP,
ComplexModelCOCentering.c_E200,
ComplexModelCOCentering.c_E300,
ComplexModelCOCentering.c_AROM,
ComplexModelCOCentering.c_OLEF,
ComplexModelCOCentering.c_BENZ
FROM FuelFormulation LEFT JOIN ComplexModelCOCentering
ON FuelFormulation.PollutantID
ComplexModelCOCentering.PollutantID and
FuelFormulation.TechGroupID =
ComplexModelCOCentering.TechGroupID
GROUP BY PollutantID, TechGroupID, FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON Cl (PollutantID, TechGroupID,
FuelFormulationID) ;
drop TABLE if exists C2;
CREATE TABLE C2
SELECT Cl.*,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 1
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
106
-------
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c_0xygen,
c Sulfur,
c~RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl .TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
C1.P_RVP,
Cl.P_Sulfur,
C1.P_AROM,
C1.P_OLEF,
Cl.P_BENZ,
C1.P_E200,
Cl.P_E300,
C1.P_OXYGEN,
Cl.c_0xygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 2
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
107
-------
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c_0xygen,
c Sulfur,
c_RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl. TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
C1.P_RVP,
Cl.P_Sulfur,
Cl.P_AROM,
C1.P_OLEF,
Cl.P_BENZ,
C1.P_E200,
C1.P_E300,
Cl.P_OXYGEN,
Cl.c_0xygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 3
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
108
-------
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c Oxygen,
c_Sulfur,
c_RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl.TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
Cl.P_RVP,
Cl.P_Sulfur,
Cl.P_AROM,
C1.P_OLEF,
C1.P_BENZ,
Cl.P_E200,
C1.P_E300,
Cl.P_OXYGEN,
Cl.c_0xygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 4
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P RVP,
109
-------
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c Oxygen,
c_Sulfur,
c_RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl.TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
Cl.P_RVP,
Cl.P_Sulfur,
C1.P_AROM,
Cl.P_OLEF,
C1.P_BENZ,
Cl.P_E200,
C1.P_E300,
C1.P_OXYGEN,
Cl.c Oxygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 5
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P OLEF,
110
-------
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c Oxygen,
c Sulfur,
c~RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl .TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
Cl.P_RVP,
Cl.P_Sulfur,
C1.P_AROM,
Cl.P_OLEF,
C1.P_BENZ,
C1.P_E200,
Cl.P_E300,
C1.P_OXYGEN,
Cl.c Oxygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 6
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P E300,
111
-------
P_OXYGEN,
c_0xygen,
c Sulfur,
c~RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl .TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
C1.P_RVP,
Cl.P_Sulfur,
C1.P_AROM,
Cl.P_OLEF,
Cl.P_BENZ,
C1.P_E200,
Cl.P_E300,
C1.P_OXYGEN,
Cl.c_0xygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 7
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c Oxygen,
c Sulfur,
112
-------
c_RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl.TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
C1.P_RVP,
Cl.P_Sulfur,
Cl.P_AROM,
C1.P_OLEF,
Cl.P_BENZ,
C1.P_E200,
Cl.P_E300,
Cl.P_OXYGEN,
Cl.c_0xygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 8
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c_0xygen,
c Sulfur,
c~RVP,
c_E200,
c E300,
113
-------
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl.TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
Cl.P_RVP,
Cl.P_Sulfur,
Cl.P_AROM,
C1.P_OLEF,
Cl.P_BENZ,
Cl.P_E200,
C1.P_E300,
Cl.P_OXYGEN,
Cl.c_0xygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 9
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c Oxygen,
c_Sulfur,
c_RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
114
-------
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl .TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
C1.P_RVP,
Cl.P_Sulfur,
C1.P_AROM,
Cl.P_OLEF,
Cl.P_BENZ,
C1.P_E200,
Cl.P_E300,
C1.P_OXYGEN,
Cl.c Oxygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 10
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c_0xygen,
c Sulfur,
c~RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl .TechGroupID,
115
-------
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
Cl.P_RVP,
Cl.P_Sulfur,
Cl.P_AROM,
Cl.P_OLEF,
C1.P_BENZ,
Cl.P_E200,
C1.P_E300,
Cl.P_OXYGEN,
Cl.c Oxygen,
Cl.c_Sulfur,
Cl.c_RVP,
Cl.c_E200,
Cl.c_E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 11
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
INSERT INTO C2
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
c Oxygen,
c_Sulfur,
c_RVP,
c_E200,
c_E300,
c_AROM,
c_OLEF,
c_BENZ,
coCoefficientID,
CoefficientName,
coCoefficient )
SELECT Cl.PollutantID,
Cl .TechGroupID,
Cl.FuelFormulationID,
Cl.FuelSubtypelD,
Cl.P_RVP,
Cl.P Sulfur,
116
-------
Cl.
Cl.
Cl.
Cl.
Cl.
Cl.
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P OXYGEN,
Cl.c_0xygen,
Cl.c_Sulfur,
Cl.c RVP,
Cl.
Cl.
E200,
~E300,
Cl.c_AROM,
Cl.c_OLEF,
Cl.c_BENZ,
ComplexModelCOCoeff.coCoefficientID,
ComplexModelCOCoeff.CoefficientName,
ComplexModelCOCoeff.coCoefficient
FROM Cl INNER JOIN ComplexModelCOCoeff
ON Cl.pollutantID = ComplexModelCOCoeff.pollutantID and
Cl.techGroupID = ComplexModelCOCoeff.techGroupID
WHERE ComplexModelCOCoeff.coCoefficientID = 12
GROUP BY PollutantID, TechGroupID, coCoefficientID, FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON C2 (PollutantID, TechGroupID,
coCoefficientID, FuelFormulationID);
alter TABLE C2 ADD (
P float
— Perform primary Complex Model CO calculation.
UPDATE C2
C_0xygen)
UPDATE C2
C_Sulfur)
UPDATE C2
WHERE
UPDATE C2
WHERE
UPDATE C2
WHERE
UPDATE C2
WHERE
UPDATE C2
WHERE
SET P
WHERE coCoefficientID
SET P
WHERE coCoefficientID
SET P
coCoefficientID = 3;
SET P
coCoefficientID = 4;
SET P
coCoefficientID = 5;
SET P
coCoefficientID = 6;
SET P
coCoefficientID = 7;
UPDATE C2 SET
(P RVP - C RVP)
coCoefficient * (P Oxygen -
1;
coCoefficient * (P Sulfur -
2;
coCoefficient * (P_RVP - C_RVP)
coCoefficient * (P_E200 - C_E200)
coCoefficient * (P_E300 - C_E300)
coCoefficient * (P_AROM - C_AROM)
coCoefficient * (P_OLEF - C_OLEF)
coCoefficient * (P_RVP - C_RVP) *
WHERE coCoefficientID
117
-------
UPDATE C2 SET P
(P_E200 - C_E200)
9;
UPDATE C2 SET P
(P_E300 - C_E300)
10;
UPDATE C2 SET P
(P_OLEF - C_OLEF)
11;
UPDATE C2 SET P
C_E300)*(P_OLEF - C_OLEF)
12;
coCoefficient * (P_E200 - C_E200) *
WHERE coCoefficientID =
coCoefficient * (P_E300 - C_E300) *
WHERE coCoefficientID =
coCoefficient * (P_OLEF - C_OLEF) *
WHERE coCoefficientID =
coCoefficient * (P_E300 -
WHERE coCoefficientID =
drop TABLE if exists C3;
CREATE TABLE C3
SELECT PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
SUM(P) as U
FROM C2
GROUP BY PollutantID, FuelFormulationID, TechGroupID;
alter TABLE C3 ADD (
expU float
UPDATE C3 SET expU = EXP(U);
CREATE UNIQUE INDEX Indexl ON C3 (PollutantID, TechGroupID,
FuelFormulationID);
-- Reference fuelformulationID = 98
drop TABLE if exists CReference;
CREATE TABLE CReference
SELECT PollutantID,
TechGroupID,
FuelFormulationID,
30 ppm Sulfur
118
-------
FuelSubtypelD,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU
FROM C3
WHERE FuelFormulationID = 98
GROUP BY PollutantID, TechGroupID, FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON CReference (PollutantID, TechGroupID,
FuelFormulationID);
drop TABLE if exists C4;
CREATE TABLE C4
SELECT C3.PollutantID,
C3. TechGroupID,
C3.FuelFormulationID,
C3.FuelSubtypelD,
C3.P_RVP,
C3.P_Sulfur,
C3.P_AROM,
C3.P_OLEF,
C3.P_BENZ,
C3.P_E200,
C3.P_E300,
C3.P_OXYGEN,
CS.expU,
CReference.expU as RexpU
FROM C3 LEFT JOIN CReference
ON C3.PollutantID = CReference.PollutantID
and
C3.TechGroupID = CReference.TechGroupID
GROUP BY PollutantID, FuelFormulationID, TechGroupID;
CREATE UNIQUE INDEX Indexl ON C4 (PollutantID, TechGroupID,
FuelFormulationID);
*******************
Add the TechWeighting Fractions from MOBILE6.2 and the original
Complex Model.
— The factor for High emitters was changed for all 1996 and
later groups from
the unrealistically high level of 55% to 20%.
Other Changes in the TechWeighting include:
1974 FuelMYGroupID All CARB and High Emitter
Combinations only. No significant
119
-------
Electronic fuel injection
penetration at that time.
1975-93 FuelMYGroupIDs Default values from Complex Model used.
High Emitter level reduced from 55% to
placed into Group 1
1996+ FuelMYGroupIDs
20%. Difference
2000 +
eliminated.
into Group 1
Group 4
FuelMYGroupIDs
Groups 2, 3, 6, 7, 8 and 9
Groups 2, 3, 8 and 9 mapped
Groups 6 and 7 mapped into
Group 5 left unchanged.
TechWeighting is NOT a function of SourceTypelD. Thus,
FuelAdjustments in MOVES will
NOT be a function of SourceTypelD (except GPA effects)
Taken from ATGROUP.FOR
— Group Tech Fractions:
- GROUP (I)
-
-
-
-
-
-
-
-
—
1
2
3
4
5
6
7
8
9
10
PFI , Sway , No Air, EGR
PFI , Sway , No Air, No EGR
TBI , Sway , No Air, EGR
PFI , 3way+0x, Air , EGR
PFI , Sway , Air , EGR
TBI , Sway , Air , EGR
TBI , 3way+0x, Air , EGR
TBI , Sway , No Air, No EGR
CARB, 3way+0x, Air , EGR
All High Emitters
- fuelmygroupid
-
1974
19751986
19871989
19901993
-
-
-
1994
1995
1996
19972000
20012003
-
-
-
-
2004
2005
2006
2007
20082009
20102050
*******************
drop TABLE if exists TechWeights;
120
-------
CREATE TABLE TechWeights
fuelMYGroupID
techGroupID
techWeighting
PRIMARY KEY
INT(8),
smallint(6) ,
float,
(fuelMYGroupID, techGroupID)
INSERT INTO TechWeights
(fuelMYGroupID, techGroupID, techWeighting) VALUES
(1974, 1, 0.00000),
(1974, 2, 0.00000),
(1974, 3, 0.00000),
(1974, 4, 0.00000),
(1974, 5, 0.00000),
(1974, 6, 0.00000),
(1974, 7, 0.00000),
(1974, 8, 0.00000),
(1974, 9, 0.44400),
(1974, 10, 0.55600),
(19751986, 1, 0.11942),
(19751986, 2, 0.10688),
(19751986, 3, 0.08190),
(19751986, 4, 0.08118),
(19751986, 5, 0.01260),
(19751986, 6, 0.00173),
(19751986, 7, 0.03334),
(19751986, 8, 0.00000),
(19751986, 9, 0.00695),
(19751986, 10, 0.55600),
(19871989, 1, 0.11942),
(19871989, 2, 0.10688),
(19871989, 3, 0.08190),
(19871989, 4, 0.08118),
(19871989, 5, 0.01260),
(19871989, 6, 0.00173),
(19871989, 7, 0.03334),
(19871989, 8, 0.00000),
(19871989, 9, 0.00695),
(19871989, 10, 0.55600),
(19901993, 1, 0.11942),
(19901993, 2, 0.10688),
(19901993, 3, 0.08190),
(19901993, 4, 0.08118),
(19901993, 5, 0.01260),
(19901993, 6, 0.00173),
(19901993, 7, 0.03334),
(19901993, 8, 0.00000),
(19901993, 9, 0.00695),
(19901993, 10, 0.55600),
(1994, 1, 0.47542),
(1994, 2, 0.10688),
(1994, 3, 0.08190),
(1994, 4, 0.08118),
(1994, 5, 0.01260),
(1994, 6, 0.00173),
(1994, 7, 0.03334),
(1994, 8, 0.00000),
121
-------
(1994, 9, 0.00695),
(1994, 10, 0.50000),
(1995, 1, 0.47542),
(1995, 2, 0.10688),
(1995, 3, 0.08190),
(1995, 4, 0.08118),
(1995, 5, 0.01260),
(1995, 6, 0.00173),
(1995, 7, 0.03334),
(1995, 8, 0.00000),
(1995, 9, 0.00695),
(1995, 10, 0.50000),
(1996, 1, 0.47542),
(1996, 2, 0.10688),
(1996, 3, 0.08190),
(1996, 4, 0.08118),
(1996, 5, 0.01260),
(1996, 6, 0.00173),
(1996, 7, 0.03334),
(1996, 8, 0.00000),
(1996, 9, 0.00695),
(1996, 10, 0.50000),
(19972000, 1, 0.47542),
(19972000, 2, 0.10688),
(19972000, 3, 0.08190),
(19972000, 4, 0.08118),
(19972000, 5, 0.01260),
(19972000, 6, 0.00173),
(19972000, 7, 0.03334),
(19972000, 8, 0.00000),
(19972000, 9, 0.00695),
(19972000, 10, 0.50000),
(20012003, 1, 0.67115),
(20012003, 2, 0.00000),
(20012003, 3, 0.00000),
(20012003, 4, 0.11625),
(20012003, 5, 0.01260),
(20012003, 6, 0.00000),
(20012003, 7, 0.00000),
(20012003, 8, 0.00000),
(20012003, 9, 0.00000),
(20012003, 10, 0.50000),
(2004, 1, 0.67115),
(2004, 2, 0.00000),
(2004, 3, 0.00000),
(2004, 4, 0.11625),
(2004, 5, 0.01260),
(2004, 6, 0.00000),
(2004, 7, 0.00000),
(2004, 8, 0.00000),
(2004, 9, 0.00000),
(2004, 10, 0.50000),
(2005, 1, 0.67115),
(2005, 2, 0.00000),
(2005, 3, 0.00000),
(2005, 4, 0.11625),
(2005, 5, 0.01260),
122
-------
(2005, 6, 0.00000),
7,
8,
(2005,
(2005,
(2005, 9,
(2005, 10
0.00000),
0.00000),
0.00000),
0.50000),
(2006, 1, 0.67115),
(2006, 2, 0.00000),
(2006, 3, 0.00000),
(2006,
(2006,
4, 0.11625),
5, 0.01260),
(2006, 6, 0.00000),
7,
8,
(2006,
(2006,
(2006, 9,
(2006, 10,
0.00000),
0.00000),
0.00000),
0.50000),
(2007, 1, 0.67115),
(2007, 2, 0.00000),
(2007, 3, 0.00000),
(2007, 4, 0.11625),
(2007, 5, 0.01260),
(2007, 6, 0.00000),
(2007,
(2007,
7,
0.00000),
0.00000),
(2007, 9, 0.00000),
(2007, 10, 0.50000),
(20082009, 1, 0.67115),
(20082009, 2, 0.00000),
(20082009, 3, 0.00000),
(20082009, 4, 0.11625),
(20082009, 5, 0.01260),
(20082009, 6, 0.00000),
(20082009, 7, 0.00000),
(20082009, 8, 0.00000),
(20082009, 9, 0.00000),
(20082009, 10, 0.50000),
(20102050, 1, 0.67115),
(20102050, 2, 0.00000),
(20102050, 3, 0.00000),
(20102050, 4, 0.11625),
(20102050, 5, 0.01260),
(20102050, 6, 0.00000),
7,
(20102050,
(20102050,
(20102050,
0.00000),
0.00000),
0.00000),
(20102050, 10, 0.50000)
CREATE UNIQUE INDEX Indexl ON TechWeights (fuelMYGroupID, techGroupID);
drop TABLE if exists C5;
CREATE TABLE C5
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
123
-------
FROM
ON
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
C4 LEFT JOIN TechWeights
C4.TechGroupID
TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 1974
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID
TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 19751986
124
-------
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID
TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 19871989
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P BENZ,
125
-------
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 19901993
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P E200,
126
-------
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 1994
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID
TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 1995
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
127
-------
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 1996
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
128
-------
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 19972000
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID
TechWeights.TechGroupID
129
-------
WHERE TechWeights.FuelMYGroupID = 20012003
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID
TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 2004
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P OLEF,
130
-------
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 2005
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P OLEF,
131
-------
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 2006
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P~AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 2007
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
132
-------
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
C4.TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 20082009
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
INSERT INTO C5
( PollutantID,
TechGroupID,
FuelFormulationID,
FuelSubtypelD,
FuelMYGroupID,
P_RVP,
P_Sulfur,
P_AROM,
P_OLEF,
P_BENZ,
P_E200,
P_E300,
P_OXYGEN,
expU,
RexpU,
techWeighting )
SELECT C4.PollutantID,
133
-------
C4 .TechGroupID,
C4.FuelFormulationID,
C4.FuelSubtypelD,
TechWeights.FuelMYGroupID,
C4.P_RVP,
C4.P_Sulfur,
C4.P_AROM,
C4.P_OLEF,
C4.P_BENZ,
C4.P_E200,
C4.P_E300,
C4.P_OXYGEN,
C4.expU,
C4.RexpU,
TechWeights.techWeighting
FROM C4 LEFT JOIN TechWeights
ON C4.TechGroupID = TechWeights.TechGroupID
WHERE TechWeights.FuelMYGroupID = 20102050
GROUP BY TechWeights.FuelMYGroupID, C4.PollutantID,
C4.FuelFormulationID, C4.TechGroupID;
alter TABLE C5 ADD (
COFactorA float
UPDATE C5 SET COFactorA = ((expU / RexpU) - 1) *
techWeighting * 100.0;
CREATE UNIQUE INDEX Indexl ON C5 (FuelMYGroupID, PollutantID,
TechGroupID, FuelFormulationID);
drop TABLE if exists ComplexModelCO;
CREATE TABLE ComplexModelCO
SELECT C5.PollutantID,
C5.FuelMYGroupID,
C5.TechGroupID,
C5.FuelFormulationID,
C5.FuelSubtypelD,
C5.P_RVP as RVP,
C5.P_Sulfur as Sulfur,
C5.P_AROM as AROM,
C5.P_OLEF as OLEF,
C5.P_BENZ as BENZ,
C5.P_E200 as E200,
C5.P_E300 as E300,
C5.P_OXYGEN as Oxygen,
(SUM(COFactorA)+ 100)/100 as AdjustmentFactor
FROM C5
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON ComplexModelCO (PollutantID,
FuelMYGroupID, FuelFormulationID);
134
-------
drop TABLE if exists dummy;
CREATE TABLE dummy
SELECT CM.PollutantID,
CM.FuelMYGroupID,
CM.FuelFormulationID,
CM.FuelSubtypelD,
CM. RVP,
CM.Sulfur,
CM.AROM,
CM.OLEF,
CM.BENZ,
CM.E200,
CM.E300,
CM.Oxygen,
CM.AdjustmentFactor
FROM ComplexModelCO as CM
GROUP BY PollutantID, FuelMYGroupID, FuelFormulationID;
ALTER TABLE dummy
ADD (
SourceTypelD smallint(6)
) ;
UPDATE dummy SET SourceTypelD = 11;
drop TABLE if exists ComplexModelCO;
CREATE TABLE ComplexModelCO
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
dummy.SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
135
-------
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
21 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
31 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
136
-------
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
32 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
41 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
137
-------
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
42 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
138
-------
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
43 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
51 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
139
-------
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
52 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
53 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
140
-------
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
54 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
61 as SourceTypelD,
dummy.FuelSubtypelD,
141
-------
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
INSERT INTO ComplexModelCO
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
RVP,
Sulfur,
AROM,
OLEF,
BENZ,
E200,
E300,
Oxygen,
AdjustmentFactor )
SELECT dummy.PollutantID,
dummy.FuelMYGroupID,
dummy.FuelFormulationID,
62 as SourceTypelD,
dummy.FuelSubtypelD,
dummy.RVP,
dummy.Sulfur,
dummy.AROM,
dummy.OLEF,
dummy.BENZ,
dummy.E200,
dummy.E300,
dummy.Oxygen,
dummy.Adj ustmentFactor
FROM dummy
GROUP BY PollutantID, SourceTypelD, FuelMYGroupID, FuelFormulationID;
drop TABLE if exists dummy;
CREATE UNIQUE INDEX Indexl ON
ComplexModelCO (PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID);
drop TABLE if exists ComplexCO;
CREATE TABLE ComplexCO
SELECT
C.PollutantID,
142
-------
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.RVP,
A.P_Sulfur as Sulfur,
C.AROM,
C.OLEF,
C.BENZ,
C.E200,
C.E300,
C.Oxygen,
C.AdjustmentFactor as OtherFactor
FROM ComplexModelCO C LEFT JOIN ActualFuelFormulation A
ON C.FuelFormulationID = A.FuelFormulationID
GROUP BY C.PollutantID, C.SourceTypelD, C.FuelMYGroupID,
C.FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
ComplexCO (PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID);
drop TABLE if exists ComplexSaved;
CREATE TABLE ComplexSaved SELECT * from ComplexCO;
CREATE UNIQUE INDEX Indexl ON
ComplexSaved (PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID);
******************
***********************************************************************
******************
***********************************************************************
******************
***********************************************************************
******************
***********************************************************************
******************
***********************************************************************
******************
***********************************************************************
******************
***********************************************************************
******************
143
-------
These sulfur coefficients were taken from the MOBILE6 report by
Tesh Rao.
— emitterType is "H" for High emitter and "N" for Normal emitter.
Some equations are log-log and others are log-linear.
drop TABLE if exists SulfurCoeff;
CREATE TABLE SulfurCoeff (
processID smallint(6),
pollutantID smallint(6),
emitterType CHAR(1),
sourceTypelD smallint(6),
fuelMYGroupID INT(8),
functionType CHAR(10),
SulfurCoeff float,
PRIMARY KEY (processID, pollutantID,
emitterType, sourceTypelD, fuelMYGroupID)
LOAD DATA INFILE 'C:\\Eds C Files\\MOVES FUELBinner\\SQL Fuel Binner
Code\\SulfurCoefficients.csv' REPLACE
INTO TABLE SulfurCoeff
FIELDS TERMINATED BY ' , '
OPTIONALLY ENCLOSED BY ""
ESCAPED BY '\\'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;
CREATE UNIQUE INDEX Indexl ON
SulfurCoeff (processID, PollutantID, emitterType,
SourceTypelD, FuelMYGroupID);
— This section is only the Normal emitters. This process was done
piecemeal
— rather than normals and high together that is why they are
in separate
— sections. Rational coding would have done this together.
drop TABLE if exists ComplexCOB;
CREATE TABLE ComplexCOB
SELECT
C.PollutantID,
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
SC.SulfurCoeff
FROM ComplexCO C LEFT JOIN SulfurCoeff SC
ON C.PollutantID = SC.PollutantID and
144
-------
C.SourceTypelD = SC.SourceTypelD and
C.FuelMYGroupID = SC.FuelMYGroupID
WHERE SC.emitterType = 'N' and SC.processID = 1
GROUP BY SC.processID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
ComplexCOB (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
drop TABLE if exists Junk;
CREATE TABLE Junk
SELECT
C.PollutantID,
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
SC.sulfurCoeff
ComplexCO C LEFT
C.PollutantID = SC.PollutantID and
C.SourceTypelD = SC.SourceTypelD and
C.FuelMYGroupID
SC.emitterType =
BY
FROM
ON
WHERE
GROUP
JOIN SulfurCoeff SC
SC.PollutantID
SC.SourceTypelD
SC.FuelMYGroupID
= 'N1 and SC.processID = 2
SC.processID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
INSERT INTO ComplexCOB
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
OtherFactor,
processID,
functionType,
emitterType,
sulfurCoeff )
SELECT
PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
OtherFactor,
processID,
functionType,
emitterType,
sulfurCoeff
145
-------
FROM Junk
GROUP BY ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID;
drop TABLE if exists Junk;
Alter TABLE ComplexCOB ADD (
SulfAdj float,
Sulfl float,
SulfSO float,
longTermSulfur float,
SulfIRR float,
SulfMax float,
FinalSulfAdj float,
IRFactor float,
SulfAdj2 float,
SulfGPA float,
SulflOOO float,
GPASulfAdj float,
AdjustmentFactorAll float
Calculate Short Term Sulfur Effects for Normal Emitters *
*************************************************************
— TierO and LEV + vehicles use LOG - LOG algorithm.
UPDATE ComplexCOB SET Sulfl = EXP(sulfurCoeff*
LN(Sulfur));
UPDATE ComplexCOB SET SulfSO = EXP(sulfurCoeff*
LN(30));
— Tierl vehicles use a LOG - Linear algorithm.
UPDATE ComplexCOB SET Sulfl = EXP(sulfurCoeff*Sulfur)
WHERE FuelMYGroupID IN (1994, 1995, 1996,
19972000);
UPDATE ComplexCOB SET SulfSO
EXP(sulfurCoeff*30)
WHERE FuelMYGroupID IN (1994, 1995, 1996,
19972000);
— Used for both TierO and LEV
UPDATE ComplexCOB SET SulfAdj = 0.0 WHERE
Sulfur = 30;
146
-------
UPDATE ComplexCOB SET SulfAdj
/ SulfSO WHERE Sulfur <> 30;
(Sulfl - SulfSO)
Calculate Long Term Sulfur Effects *
*****************************************
Add the long term sulfur effects to the LEV and later vehicles for
sulfur levels greater
than 30 ppm. Sulfur levels below 30 ppm are assumed to
have no long term effects.
There is no way to logically extrapolate these effects below 30
ppm. If 30 ppm has
— a zero long term sulfur effect then Sulfur < 30 ppm should
not have any effect
— either.
UPDATE
ComplexCOB SET longTermSulfur
1.0;
2.36 WHERE
UPDATE ComplexCOB SET longTermSulfur
PollutantID = 2 and
FuelMYGroupID IN (20012003, 2004, 2005, 2006, 2007,
20082009, 20102050) and
Sulfur > 30.0;
UPDATE ComplexCOB SET SulfAdj2
SulfAdj^LongTermSulfur;
***********************************************************************
***********
— Sulfur i
ppm only.
effects for 2004+ model years and Sulfur > 30
***********************************************************************
***********
-- compute the normal sulfur irreversibility effects
UPDATE
LN(303)
UPDATE
LN(303)
UPDATE
LN ( 8 7) )
ComplexCOB SET SulfIRR
WHERE FuelMYGroupID = 2004;
ComplexCOB SET SulfIRR
WHERE FuelMYGroupID = 2005;
ComplexCOB SET SulfIRR
WHERE FuelMYGroupID = 2006;
EXP(sulfurCoeff*
EXP(sulfurCoeffJ
EXP(sulfurCoeffJ
147
-------
UPDATE
LN ( 8 7) )
UPDATE
LN(8 0) )
ComplexCOB SET SulfIRR
WHERE FuelMYGroupID = 2007;
ComplexCOB SET SulfIRR
EXP(sulfurCoeffJ
EXP(sulfurCoeffJ
WHERE FuelMYGroupID IN (20082009, 20102050);
-- compute the sulfur irreversibility effects if the selected sulfur
level is
greater than the maximum sulfur level. Note these may not
exist in the
— fuel supply table (real world) but the model needs
fueladjustment effects
— anyway as placeholders. Making the maximum sulfur level
equal to the
actual one assures that the sulfur irreversibility effects
don't get
smaller than the actual effects.
ComplexCOB SET SulfIRR
EXP(sulfurCoeffJ
UPDATE
LN(sulfur) )
WHERE FuelMYGroupID = 2004 and Sulfur > 303;
UPDATE ComplexCOB SET SulfIRR = EXP(sulfurCoeff*
LN(sulfur))
WHERE FuelMYGroupID = 2005 and Sulfur > 303;
UPDATE ComplexCOB SET SulfIRR = EXP(sulfurCoeff*
LN(sulfur))
WHERE FuelMYGroupID = 2006 and Sulfur > 87;
ComplexCOB SET SulfIRR = EXP(sulfurCoeff*
UPDATE
LN(sulfur)
UPDATE
LN(sulfur)
Sulfur > 8
WHERE FuelMYGroupID = 2007 and Sulfur > 87;
ComplexCOB SET SulfIRR = EXP(sulfurCoeff*
WHERE FuelMYGroupID IN (20082009, 20102050) and
UPDATE ComplexCOB SET SulfMax = 0.0;
UPDATE ComplexCOB SET SulfMax = (SulfIRR -
SulfSO) / SulfSO WHERE
FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and Sulfur > 30.0;
UPDATE ComplexCOB SET IRFactor = 0.000;
UPDATE ComplexCOB SET IRFactor = 0.425
WHERE FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and
Sulfur > 30.0;
-- SulfAdj2 is the fractional increase in emissions of the sulfur
compared with the base
148
-------
sulfur level 30 ppm. SulfMax is the increase due to the
sulfur irreversibility
— effects.
UPDATE ComplexCOB
SET FinalSulfAdj = (IRFactor * SulfMax) + (1.0 -
IRFactor) * SulfAdj2;
— Converts the delta increase into a multiplicative factor, (i.e., 30%
increase = 1.30)
UPDATE ComplexCOB SET FinalSulfAdj = 1 + FinalSulfAdj;
— This bottoms the sulfur effects at levels around 4 ppm Sulfur.
Lower gasoline sulfur
— levels will have no effect on the emissions. This cap is
needed because the
EXTRAPOLATED relationship is log-log in nature.
-- Currently the lowest default fuel sulfur level is 10 ppm S.
UPDATE ComplexCOB SET FinalSulfAdj = 0.50 WHERE
FinalSulfAdj < 0.50;
Sulfur GPA Effects *
************************
— Sulfur GPA is only applied to FuelMYGroupIDs of 2004, 2005 and 2006.
It is a phase-out
strategy for sulfur in the Rocky Mountain states.
UPDATE ComplexCOB SET SulflOOO = EXP(sulfurCoeff*
LN(10 0 0.0) )
WHERE FuelMYGroupID IN (2004, 2005, 2006);
UPDATE ComplexCOB SET SulfGPA = 1.00;
UPDATE ComplexCOB SET SulfGPA = (SulflOOO -
SulfSO) / SulfSO WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
UPDATE ComplexCOB SET GPASulfAdj = FinalSulfAdj;
UPDATE ComplexCOB SET GPASulfAdj = (IRFactor * SulfGPA) +
(1.0 - IRFactor) * SulfAdj2
WHERE FuelMYGroupID IN (2004, 2005, 2006) and Sulfur
> 30.0;
149
-------
UPDATE ComplexCOB SET GPASulfAdj = 1.0 + GPASulfAdj WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
*********************************************************************
*********************************************************************
*********************************************************************
Sulfur effects for High emitters only
*
*********************************************************************
*********************************************************************
drop TABLE if exists ComplexCOHigh;
CREATE TABLE ComplexCOHigh
SELECT
C.PollutantID,
C.FuelMYGroupID,
C.FuelFormulationID,
C.SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
SC.sulfurCoeff
FROM ComplexCO C LEFT JOIN SulfurCoeff SC
ON C.PollutantID = SC.PollutantID and
C.SourceTypelD = SC.SourceTypelD and
C.FuelMYGroupID = SC.FuelMYGroupID
WHERE SC.emitterType = 'H' and SC.processID = 1
GROUP BY SC.processID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
ComplexCOHigh (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
drop TABLE if exists Junk;
CREATE TABLE Junk
SELECT
C.PollutantID,
C.FuelMYGroupID,
C.FuelFormulationID,
150
-------
FROM
ON
WHERE
GROUP
C. SourceTypelD,
C.FuelSubtypelD,
C.Sulfur,
C.OtherFactor,
SC.processID,
SC.functionType,
SC.emitterType,
SC.sulfurCoeff
ComplexCO C LEFT
C.PollutantID
C.SourceTypelD
C.FuelMYGroupID
SC.emitterType =
BY
JOIN SulfurCoeff SC
SC.PollutantID
SC.SourceTypelD
SC.FuelMYGroupID
'H' and SC.processID = 2
SC.processID, C.PollutantID, C.SourceTypelD,
C.FuelMYGroupID, C.FuelFormulationID;
and
and
INSERT INTO ComplexCOHigh
( PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
OtherFactor,
processID,
functionType,
emitterType,
sulfurCoeff )
SELECT
PollutantID,
FuelMYGroupID,
FuelFormulationID,
SourceTypelD,
FuelSubtypelD,
Sulfur,
OtherFactor,
processID,
functionType,
emitterType,
sulfurCoeff
FROM Junk
GROUP BY ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID;
drop TABLE if exists Junk;
Alter TABLE ComplexCOHigh ADD (
SulfAdj float,
Sulfl float,
SulfSO float,
longTermSulfur float,
SulfIRR float,
SulfMax float,
FinalSulfAdj float,
IRFactor float,
151
-------
SulfAdj2 float,
SulfGPA float,
SulflOOO float,
GPASulfAdj float,
AdjustmentFactorAll float
Calculate Short Term Sulfur Effects for High Emitters
*************************************************************
-- All High emitting vehicles use a LOG - Linear algorithm.
UPDATE ComplexCOHigh SET Sulfl
EXP(sulfurCoeff*Sulfur);
UPDATE ComplexCOHigh SET SulfSO
EXP(sulfurCoeff*30);
UPDATE ComplexCOHigh SET SulfAdj = 0.0 WHERE
Sulfur = 30;
UPDATE ComplexCOHigh SET SulfAdj = (Sulfl -
SulfSO) / SulfSO WHERE Sulfur <> 30;
*****************************************
Calculate Long Term Sulfur Effects *
*****************************************
— Add the long term sulfur effects to the LEV and later vehicles for
sulfur levels greater
than 30 ppm. Sulfur levels below 30 ppm are assumed to
have no long term effects.
There is no way to logically extrapolate these effects below 30
ppm. If 30 ppm has
a zero long term sulfur effect then Sulfur < 30 ppm should
not have any effect
— either.
UPDATE ComplexCOHigh SET longTermSulfur = 1.0;
UPDATE ComplexCOHigh SET longTermSulfur = 2.36 WHERE
PollutantID = 2 and
FuelMYGroupID IN (20012003, 2004, 2005, 2006, 2007,
20082009, 20102050) and
Sulfur > 30.0;
UPDATE ComplexCOHigh SET SulfAdj2
SulfAdj^LongTermSulfur;
152
-------
***********
-- Sulfur irreversibility effects for 2004+ model years and Sulfur > 30
ppm only. *
***********************************************************************
***********
-- normal sulfur irreversibility effects
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*303)
WHERE FuelMYGroupID = 2004;
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*303)
WHERE FuelMYGroupID = 2005;
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*87)
WHERE FuelMYGroupID = 2006;
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*87)
WHERE FuelMYGroupID = 2007;
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*80)
WHERE FuelMYGroupID IN (20082009, 20102050);
— compute the sulfur irreversibility effects if the selected sulfur
level is
— greater than the maximum sulfur level. Note these may not
exist in the
fuel supply table (real world) but the model needs
fueladjustment effects
anyway as placeholders. Making the maximum sulfur level
equal to the
actual one assures that the sulfur irreversibility effects
don't get
— smaller than the actual effects.
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*sulfur)
WHERE FuelMYGroupID = 2004;
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*sulfur)
WHERE FuelMYGroupID = 2005;
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*sulfur)
WHERE FuelMYGroupID = 2006;
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*sulfur)
WHERE FuelMYGroupID = 2007;
153
-------
UPDATE ComplexCOHigh SET SulfIRR
EXP(sulfurCoeff*sulfur)
WHERE FuelMYGroupID IN (20082009, 20102050);
UPDATE ComplexCOHigh SET SulfMax = 0.0;
UPDATE ComplexCOHigh SET SulfMax = (SulfIRR
SulfSO) / SulfSO
WHERE FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and Sulfur > 30.0;
UPDATE ComplexCOHigh SET IRFactor = 0.000;
UPDATE ComplexCOHigh SET IRFactor = 0.425
WHERE FuelMYGroupID IN (2004, 2005, 2006, 2007, 20082009,
20102050) and
Sulfur > 30.0;
-- SulfAdj2 is the fractional increase in emissions of the sulfur
compared with the base
— sulfur level 30 ppm. SulfMax is the increase due to the
sulfur irreversibility
— effects.
UPDATE ComplexCOHigh
SET FinalSulfAdj = (IRFactor * SulfMax) + (1.0 -
IRFactor) * SulfAdj2;
UPDATE ComplexCOHigh SET FinalSulfAdj = 1 +
FinalSulfAdj;
Sulfur GPA Effects *
************************
— Sulfur GPA is only applied to FuelMYGroupIDs of 2004, 2005 and 2006.
It is a phase-out
strategy for sulfur in the Rocky Mountain states. No GPA
effects for sulfur levels
that are less than 30 ppm.
UPDATE ComplexCOHigh SET SulflOOO
EXP(sulfurCoeff*1000)
WHERE FuelMYGroupID IN (2004, 2005, 2006);
UPDATE ComplexCOHigh SET SulfGPA = 1.00;
UPDATE ComplexCOHigh SET SulfGPA = (SulflOOO -
SulfSO) / SulfSO WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
UPDATE ComplexCOHigh SET GPASulfAdj = FinalSulfAdj;
154
-------
UPDATE ComplexCOHigh
SET GPASulfAdj = (IRFactor * SulfGPA) + (1.0
- IRFactor) * SulfAdj2
WHERE FuelMYGroupID IN (2004, 2005, 2006) and
Sulfur > 30.0;
UPDATE ComplexCOHigh SET GPASulfAdj = 1 + GPASulfAdj
WHERE
FuelMYGroupID IN (2004, 2005, 2006) and Sulfur >
30.0;
-- MOBILE6.2 sulfur effects assume a 50/50 split between Highs and
Normals in weighting.
— This is an extremely high split for modern vehicles in terms
numbers of high emitters
— in the fleet. The impact of high emitters might still be 50% of
the total emissions.
It was utilized for consistency with MOBILE6.2. It will reduce
the sensitivity of
sulfur to emissions.
drop TABLE if exists ComplexBlend;
CREATE TABLE ComplexBlend
SELECT
B. ProcessID,
B. PollutantID,
B. FuelMYGroupID,
B. FuelFormulationID,
B. SourceTypelD,
B. Sulfur,
B.OtherFactor,
B. FinalSulfAdj as SulfNorm,
H.FinalSulfAdj as SulfHigh,
(B. FinalSulfAdj *0. 50+H. FinalSulfAdj *0 . 50)
B. GPASulfAdj as GPANorm,
H. GPASulfAdj as GPAHigh,
50 + H . GPASulfAdj *0 . 50 ) as FinalGPA
INNER JOIN ComplexCOHigh H
= H . PollutantID
= H
as FinalSulfAdj 2 ,
FROM
ON
(B. GPASulfAdj *0.
ComplexCOB B
B. PollutantID
B. ProcessID
B. FuelMYGroupID
B . FuelFormulationID
B. SourceTypelD
ProcessID
H . FuelMYGroupID
H . FuelFormulationID
H . SourceTypelD
and
and
and
and
GROUP BY B. ProcessID, B . PollutantID, B . SourceTypelD,
B. FuelMYGroupID, B. FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
155
-------
ComplexBlend (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
— Add the reference fuel sulfur effects and compute the final sulfur
adjustment ratio. *
It is ratioed to Arizona's IM program and sulfur of 30 ppm.
***********************************************************************
*****************
drop TABLE if exists RefSulfur;
CREATE TABLE RefSulfur
SELECT
ProcessID,
PollutantID,
SourceTypelD,
FuelMYGroupID,
FuelFormulationID,
OtherFactor,
Sulfur,
FinalSulfAdj2 as RefSulfurAdj,
FinalGPA as RefGPA
FROM ComplexBlend
WHERE FuelFormulationID = 98
GROUP BY ProcessID, PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
RefSulfur (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
— The ComplexCO name was used earlier and is recycled.
drop TABLE if exists ComplexCO;
CREATE TABLE ComplexCO
SELECT
P.ProcessID,
P.PollutantID,
P.SourceTypelD,
P.FuelMYGroupID,
P.FuelFormulationID,
P.OtherFactor,
P.Sulfur,
P.FinalGPA,
P.FinalSulfAdj2,
R.RefSulfurAdj,
156
-------
P.FinalSulfAdj2 / R.RefSulfurAdj as SulfRatio,
P.OtherFactor * (P.FinalSulfAdj2 / R.RefSulfurAdj) as
fuelAdj ustment
FROM ComplexBlend P LEFT JOIN RefSulfur R
ON P.PollutantID = R.PollutantID
and
P.ProcessID = R.ProcessID
and
P. SourceTypelD = R.SourceTypelD
and
P.FuelMYGroupID = R.FuelMYGroupID
GROUP BY ProcessID, PollutantID, SourceTypelD, FuelMYGroupID,
FuelFormulationID;
CREATE UNIQUE INDEX Indexl ON
ComplexCO (ProcessID, PollutantID, SourceTypelD,
FuelMYGroupID, FuelFormulationID);
alter TABLE ComplexCO ADD (
polProcessID smallint(6)
fuelAdjustmentGPA float
UPDATE ComplexCO SET polProcessID = 201
WHERE pollutantID = 2 and ProcessID =1;
UPDATE ComplexCO SET polProcessID = 202
WHERE pollutantID = 2 and ProcessID =2;
UPDATE ComplexCO SET fuelAdjustmentGPA = fuelAdjustment *
(FinalGPA / FinalSulfAdj2);
drop TABLE if exists FuelAdjustment;
CREATE TABLE FuelAdjustment SELECT * from
MOVESDB20080828.FuelAdjustment;
DELETE from FuelAdjustment WHERE polProcessID < 100000;
INSERT INTO FuelAdjustment
( polProcessID,
fuelMYGroupID,
SourceTypelD,
fuelFormulationID,
fuelAdjustment,
fuelAdjustmentCV,
fuelAdjustmentGPA,
fuelAdjustmentGPACV
SELECT
polProcessID,
fuelMYGroupID,
SourceTypelD,
fuelFormulationID,
157
-------
fuelAdjustment,
NULL as fuelAdjustmentCV,
fuelAdjustmentGPA,
NULL as fuelAdjustmentGPACV
FROM ComplexCO;
158
------- |