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 ------- |