­
1­
Change
Log
for
05/
14/
2004
NMIM
CountyDB
database
Same
as
5/
8,
but
with
5
tables
copied
from
County20040412:
BaseYearVMT,
CountyMap99,
CountyMap0730,
CountyMonthHour1999,
and
CountyMonthHour2001.
Also
includes
Dave
Brzezinski's
new
County
table
with
Stage
2
from
App.
E2.
BaseYearVMT
for
1999,
2001,
07,
10,
15,
20,
30.
CountyYearMonthHour
has
1999­
2003.

Change
Log
for
04/
12/
2004
NMIM
CountyDB
database
Changes
in
Dave's
"
H:\
amd\
mobile\
nmim\
countydb\
April
2004
Update".
Fuels
fixed
for
CA,
CT,
?,?:
countymonthhour,
countymonthhouravg,
countyyearmonth,
countyyearmonthour,
countyyearmonthnr,
countyyearmonthrecm,
datasource,
diesel,
and
gasoline.
BaseYearVMT
left
as
previous
for
IAQR,
because
Dave's
change
left
only
1999
and
2002.
2030
VMT
added
4/
30/
04.

April
2004
Update
Changes
Needed
to
the
NMIM
County
Database
(
from
2/
18/
2004
version)

Changes
in
Table
structure:

1.
The
VMTGrowthRate
field
in
the
VMTGrowth
table
must
be
changed
to
a
float.

2.
The
VMT
field
in
the
BaseYearVMT
table
must
be
changed
to
a
float.

3.
The
PollutantCode
field
value
of
"
NOx"
in
the
PollutantCode
table
must
be
changed
to
"
NOX"
to
be
consistent
with
NIF3.

4.
The
HwyDieselId
and
NRDieselId
fields
in
the
CountyYearMonth
table
must
be
changed
from
tinyint(
2)
to
smallint.

5.
The
DieselId
field
in
the
Diesel
table
must
be
changed
to
smallint.

Changes
to
Values
in
Tables:

CountyMonthHour
Replace
all
old
values
with
new
values.

The
values
supplied
by
the
contractor
(
AIR)
were
rounded
to
integers
in
the
spreadsheet
­
2­
they
provided.
The
new
values
have
one
more
significant
digit
(
one
decimal).

CountyYearMonthHour
Add
the
1999,
2000,
2001
and
2003
calendar
year
data
from
this
table.

The
database
already
has
the
2002
data.
All
of
the
data
was
obtained
from
AIR.

Diesel
Replace
all
old
values
with
new
values.

Many
new
fuels
were
added
and
all
of
the
DieselId
values
were
changed.
The
changes
are
discussed
in
the
CountyYearMonth
table
changes.
All
DieselId
values
were
changed
to
be
the
same
as
the
integer
diesel
sulfur
content
value
to
make
it
easier
to
add
new
diesel
sulfur
values.
Since
there
are
now
more
than
99
sulfur
values,
the
DieselSulfur
field
was
changed
from
a
tinyint
to
a
smallint.

CountyYearMonth
Replace
all
old
values
with
new
values.

A
set
of
nonroad
diesel
sulfur
values
used
in
recent
nonroad
rule
analysis
was
provided
by
Craig
Harvey.
These
values
were
used
to
replace
all
previous
values
in
all
calendar
years.
A
separate
set
of
diesel
sulfur
values
specifically
for
nonroad
recreational
marine
was
used
to
create
a
separate
table.

The
2000
Alliance
fuel
survey
data
was
used
by
Pechan
to
develop
county
specific
diesel
sulfur
values
for
highway
vehicles.
These
values
are
used
for
all
calendar
years
from
1999
through
May
of
2006.
Starting
in
June
of
2006,
new
highway
diesel
sulfur
values
are
phased
in
based
on
estimates
of
the
effects
of
new
rules
provided
by
Dave
Korotney.
California
moves
to
11
ppm
in
June
2006.
Non­
California
moves
to
43
ppm
in
June
2006
and
then
to
11
ppm
in
June
2010.
Since
there
are
now
more
than
99
sulfur
values,
the
HwyDieselId
and
NRDieselID
fields
were
changed
from
a
tinyint
to
a
smallint.

The
gasolines
used
by
California,
New
York,
Connecticut
and
Arizona
between
2004
and
2006
were
changed
to
reflect
the
phase
out
of
MTBE.
These
fuel
descriptions
were
obtained
through
Rich
Cook,
from
Chris
Brunner
and
Lester
Wyborny.

This
file
is
identical
to
the
CountyYearMonthNR
table
added
below.

VMTGrowth
­
3­
Replace
all
old
values
with
new
values.

Warning
:
This
file
has
been
reduced
to
only
include
growth
rates
for
the
1999
through
2002
calendar
years.
The
growth
rates
were
calculated
using
the
new
1999
base
year
VMT
and
the
2002
VMT
from
Pechan.
All
growth
rates
for
categories
with
zero
VMT
in
1999
are
zero,
even
if
there
is
non­
zero
VMT
in
calendar
year
2002.
All
1999
growth
rates
are
zero.

BaseYearVMT
Replace
all
old
values
with
new
values.

Only
base
years
1999
and
2002
are
included.
Base
year
1999
contains
all
new
values
recently
obtained
from
Pechan.

Gasoline
Add
the
new
gasoline
descriptions
from
this
table.

Many
new
gasoline
descriptions
have
been
added
to
account
for
the
MTBE
phase
out
in
California,
New
York,
Connecticut
and
Arizona.

DataSource
Replace
all
old
values
with
new
values.

Many
new
data
sources
were
added
and
obsolete
data
sources
were
deleted
to
reflect
the
changes
in
other
tables.

CountyMap
Replace
all
old
values
with
new
values.

This
map
applies
only
to
2002
calendar
year
and
was
provided
by
Harvey
Michaels.

Tables
to
be
added
to
the
database:
­
4­
CountyYearMonthRecM
Same
as
CountyYearMonth,
but
with
the
nonroad
recreational
marine
diesel
sulfur
values.

CountyYearMonthNR
Same
as
CountyYearMonth,
but
with
the
diesel
sulfur
values
for
all
equipment
except
recreational
marine.

CountyMonthHourAvg
Same
as
CountyMonthHour,
used
as
a
backup
for
special
modeling.

Change
Log
for
05/
08/
2004
NMIM
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20040508.
zip
This
version,
based
on
the
previous
2/
18/
2004
version
and
the
ERwin
design
CountyDB20040508.
ER1,
includes
the
following
changes.

Changes
in
Table
structure:

1.
The
VMTGrowthRate
field
in
the
VMTGrowth
table
must
be
changed
to
a
float.

2.
The
VMT
field
in
the
BaseYearVMT
table
must
be
changed
to
a
float.

3.
The
HwyDieselId
and
NRDieselId
fields
in
the
CountyYearMonth
table
must
be
changed
from
tinyint(
2)
to
smallint.

4.
The
DieselId
field
in
the
Diesel
table
must
be
changed
to
smallint.

Changes
to
Values
in
Tables:

PollutantCode
The
PollutantCode
field
value
of
"
NOx"
in
the
PollutantCode
table
must
be
changed
to
"
NOX"
to
be
consistent
with
NIF3.
UPDATE
PollutantCode
SET
PollutantCode="
NOX"
WHERE
PollutantCode="
NOx"
\
p;

CountyMonthHour
­
5­
Replace
all
old
values
with
new
values.

The
values
supplied
by
the
contractor
(
AIR)
were
rounded
to
integers
in
the
spreadsheet
they
provided.
The
new
values
have
one
more
significant
digit
(
one
decimal).

CountyYearMonthHour
Add
the
1999,
2000,
2001
and
2003
calendar
year
data
from
this
table.

The
database
already
has
the
2002
data.
All
of
the
data
was
obtained
from
AIR.

Diesel
Replace
all
old
values
with
new
values.

Many
new
fuels
were
added
and
all
of
the
DieselId
values
were
changed.
The
changes
are
discussed
in
the
CountyYearMonth
table
changes.
All
DieselId
values
were
changed
to
be
the
same
as
the
integer
diesel
sulfur
content
value
to
make
it
easier
to
add
new
diesel
sulfur
values.
Since
there
are
now
more
than
99
sulfur
values,
the
DieselSulfur
field
was
changed
from
a
tinyint
to
a
smallint.

CountyYearMonth
Replace
all
old
values
with
new
values.

A
set
of
nonroad
diesel
sulfur
values
used
in
recent
nonroad
rule
analysis
was
provided
by
Craig
Harvey.
These
values
were
used
to
replace
all
previous
values
in
all
calendar
years.
A
separate
set
of
diesel
sulfur
values
specifically
for
nonroad
recreational
marine
was
used
to
create
a
separate
table.

The
2000
Alliance
fuel
survey
data
was
used
by
Pechan
to
develop
county
specific
diesel
sulfur
values
for
highway
vehicles.
These
values
are
used
for
all
calendar
years
from
1999
through
May
of
2006.
Starting
in
June
of
2006,
new
highway
diesel
sulfur
values
are
phased
in
based
on
estimates
of
the
effects
of
new
rules
provided
by
Dave
Korotney.
California
moves
to
11
ppm
in
June
2006.
Non­
California
moves
to
43
ppm
in
June
2006
and
then
to
11
ppm
in
June
2010.
Since
there
are
now
more
than
99
sulfur
values,
the
HwyDieselId
and
NRDieselID
fields
were
changed
from
a
tinyint
to
a
smallint.

The
gasolines
used
by
California,
New
York,
Connecticut
and
Arizona
between
2004
and
2006
were
changed
to
reflect
the
phase
out
of
MTBE.
These
fuel
descriptions
were
obtained
through
Rich
Cook,
from
Chris
Brunner
and
Lester
Wyborny.

This
file
is
identical
to
the
CountyYearMonthNR
table
added
below.
­
6­
VMTGrowth
Replace
all
old
values
with
new
values.

Warning
:
This
file
has
been
reduced
to
only
include
growth
rates
for
the
1999
through
2002
calendar
years.
The
growth
rates
were
calculated
using
the
new
1999
base
year
VMT
and
the
2002
VMT
from
Pechan.
All
growth
rates
for
categories
with
zero
VMT
in
1999
are
zero,
even
if
there
is
non­
zero
VMT
in
calendar
year
2002.
All
1999
growth
rates
are
zero.

BaseYearVMT
Replace
all
old
values
with
new
values.

Only
base
years
1999
and
2002
are
included.
Base
year
1999
contains
all
new
values
recently
obtained
from
Pechan.

Gasoline
Add
the
new
gasoline
descriptions
from
this
table.

Many
new
gasoline
descriptions
have
been
added
to
account
for
the
MTBE
phase
out
in
California,
New
York,
Connecticut
and
Arizona.

DataSource
Replace
all
old
values
with
new
values.

Many
new
data
sources
were
added
and
obsolete
data
sources
were
deleted
to
reflect
the
changes
in
other
tables.

CountyMap
Replace
all
old
values
with
new
values.

This
map
applies
only
to
2002
calendar
year
and
was
provided
by
Harvey
Michaels.

Tables
to
be
added
to
the
database:
­
7­
CountyYearMonthRecM
Same
as
CountyYearMonth,
but
with
the
nonroad
recreational
marine
diesel
sulfur
values.

CountyYearMonthNR
Same
as
CountyYearMonth,
but
with
the
diesel
sulfur
values
for
all
equipment
except
recreational
marine.

CountyMonthHourAvg
Same
as
CountyMonthHour,
used
as
a
backup
for
special
modeling.
­
8­
Change
Log
for
02/
18/
2004
NMIM
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20040218.
zip
This
version,
based
on
the
previous
1/
29/
2004
version,
includes
the
following
changes.
It
is
intended
to
be
used
for
Cimulus
Task
30
and
later.

Change
1)
Loaded
the
new
temperature
data
provided
by
David
Brzezinski
into
the
CountyYearMonthHour
table.
Change
2)
Replaced
the
barometric
pressure
values
in
the
County
table
with
the
values
of
average
barometric
pressure
provided
by
David
Brzezinski
(
the
average
pressure
is
the
straight
average
of
all
hours
in
all
months
for
each
county).
Change
3)
Eliminated
cases
in
the
county
database
where
the
market
share
is
greater
than
zero,
but
the
oxygen
content
of
the
gasoline
is
zero.
This
causes
MOBILE6
errors.

update
gasoline
set
etohmktshare=
0
where
etohmktshare>
0
and
etohvolume=
0;
update
gasoline
set
mtbemktshare=
0
where
mtbemktshare>
0
and
mtbevolume=
0;
update
gasoline
set
etbemktshare=
0
where
etbemktshare>
0
and
etbevolume=
0;
update
gasoline
set
tamemktshare=
0
where
tamemktshare>
0
and
tamevolume=
0;

Change
4)
Dropped
the
fields
TotEtherMktShare
and
TotEtherVolume
in
the
Gasoline
table.
They
are
redundant
and
never
used
by
MOBILE6
or
NONROAD.

alter
table
gasoline
drop
totethermktshare;
alter
table
gasoline
drop
totethervolume;

Change
5)
Deleted
temporary
tables,
County1999,
County2002,
CountyBase,
CountyMonth1999,
CountyMonth2002,
and
CountyMonthAvg.
­
9­
Change
Log
for
01/
29/
2004
NMIM
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20040129.
zip
This
version,
based
on
the
previous
1/
21/
2004
version,
includes
the
following
changes.
It
is
intended
to
be
used
for
Cimulus
Task
30
and
later.

Change
1)
Added
a
new
description
field
"
HourName"
in
Hour
table.
Change
2)
Kept
empty
table
CountyYearMonthHour.
(
data
of
years
1999
and
2000
in
1/
23/
2004
version
was
not
loaded
in
this
version
per
David
B's
suggestion)
Change
3)
Added
barometric
pressure
information
in
County
table
from
Dave
B.
(
data
was
copied
from
the
County
table
of
01/
23/
2004
version)
­
10­
Change
Log
for
01/
23/
2004
NMIM
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20040123.
zip
This
version
was
the
one
actually
sent
to
Cimulus
for
Task
30.

Further
changes
were:

Removal
of
alternative
versions
of
CountyMap
and
County
that
would
be
confusing
to
Cimulus
Addition
of
barometric
pressure
information
in
County
table
by
Dave
B.
Population
of
CountyYearMonthHour
table
for
1999
and
2000
by
Dave
B.
­
11­
Change
Log
for
01/
21/
2004
NMIM
CountyDB
database
(
a.
k.
a.
Expanded
County
database)

Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20040121.
EXE
This
version,
an
expanded
County
database
based
on
the
previous
12/
04/
2003
version,
has
major
structure
modifications
(
see
rtf
files
in
readme
folder
for
details)
and
includes
the
following
changes.
It
is
intended
to
be
used
for
Cimulus
Task
30
and
later.

Change
1)
Added
new
tables:
CountyNRFile,
FileType,
CountyMap,
CountyVMTMonthAllocation,
CountyMonthHour,
Hour,
and
CountyYearMonthHour.
Change
2)
Loaded
the
average
temperature
and
humidity
data
into
table
CountyMonthHour.
Change
3)
Made
data
changes
to
VMTGrowth.
(
suggested
by
Dave
B.)
Change
4)
Removed
Hg
and
As
from
the
NMIMOutput
PollutantCode
and
SCCToxics
tables.
Change
5)
Dropped
table
Gasoline2
,
which
also
means
the
deletion
of
the
two
foreign
key
fields
in
HwyGasolineA
and
HwyGasolineB
in
CountyYearMonth.
Change
6)
Dropped
AverageSpeed
table.
­
12­
Change
Log
for
12/
04/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20031204.
EXE
This
version,
based
on
the
previous
11/
25/
2003
version,
includes
the
following
changes
and
is
intended
to
be
used
for
Cimulus
Task
29
and
later.

Change
1)
Added
a
new
table
County2002.

Change
2)
Updated
County
and
County1999
tables
because
the
representing
counties
have
changed
due
to
the
changes
to
fuels
and
temperatures.

Updates
to
County
table:
FIPSStateId
FIPSCountyId
Old
county.
NRRepFIPSCntyId
New
county.
NRRepFIPSCntyId
­­­­­­­­­­­­
­­­­­­­­­­­­­­­
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­­
6
15
3
15
6
23
3
15
6
45
3
15
6
53
3
15
6
79
3
15
6
83
3
15
6
87
3
15
6
41
1
41
6
75
1
41
6
81
1
41
6
97
1
41
Updates
to
County1999
table:
FIPSStateId
FIPSCountyId
Old
county1999
NRRepFIPSCntyId
New
county1999
NRRepFIPSCntyId
6
15
3
15
6
23
3
15
6
45
3
15
6
53
3
15
6
79
3
15
6
83
3
15
6
87
3
15
6
41
1
41
6
75
1
41
6
81
1
41
6
97
1
41
41
13
3
1
41
17
3
1
41
21
3
1
41
23
3
1
­
13­
41
25
3
1
41
27
3
1
41
31
3
1
41
37
3
1
41
45
3
1
41
49
3
1
41
55
3
1
41
59
3
1
41
61
3
1
41
63
3
1
41
65
3
1
41
69
3
1
41
35
5
35
53
3
3
1
53
5
3
1
53
7
3
1
53
13
3
1
53
17
3
1
53
19
3
1
53
21
3
1
53
23
3
1
53
25
3
1
53
37
3
1
53
39
3
1
53
43
3
1
53
47
3
1
53
51
3
1
53
65
3
1
53
71
3
1
53
75
3
1
53
77
3
1
53
9
3
9
53
15
3
9
53
27
3
9
53
31
3
9
53
35
3
9
53
41
3
9
53
45
3
9
53
49
3
9
53
53
3
9
53
55
3
9
53
57
3
9
53
59
3
9
53
67
3
9
53
69
3
9
53
73
3
9
53
63
11
63
Change
2
was
done
by
running
a
script
file
fix20031204.
sql.
­
14­
Change
Log
for
11/
25/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20031125.
EXE
This
version,
based
on
the
previous
11/
18/
2003
version,
includes
the
following
changes
and
is
intended
to
be
used
for
Cimulus
Task
29
and
later.

Change
1)
Added
three
new
tables
to
the
NMIM
County
database.
These
tables
can
be
handled
similarly
to
the
County1999
and
CountyBase
tables,
in
that
they
are
not
used
by
NMIM
and
simply
store
data
relevant
to
the
database.
countymonthavg
­
containing
the
multi­
year
averages
from
Pechan.
countymonth2002
­
containing
the
2002
temperatures
from
Pechan.
countymonth1999
­
containing
the
1999
temperature
values.
(
this's
also
a
backup
of
original
CountyMonth
table)

Change
2)
Replaced
the
default
values
in
the
CountyMonth
table
with
the
values
in
the
CountyMonth2002
table.
That
is,
empty
CountyMonth
table,
then
load
data
from
CountyMonth2002.

Changes
1
and
2
were
done
by
running
a
script
file
fix20031125.
sql.

Change
3)
This
script
fixes
the
THC
to
VOC
problem
in
the
five
original
air
toxic
pollutants
in
SCCTOXICS
table.
It
also
fixes
the
zero
values
for
exhEthGas,
exhMTBEGas,
and
exhRFGGas
for
some
dioxin
pollutants.
Change
3
was
done
by
running
a
script
file
fixVOCTHC.
sql.

Change
4)
Dropped
four
NMIMOutput
tables
listed
below,
which
belong
to
NMIMOutput
database,
not
CountyDB..
Harvey
M.
pointed
out
that
there's
a
potential
problem
of
making
NMIM
application
users
think
that
NIMIOutput
and
CountyDB
database
share
the
same
database
path/
location,
which
may
cause
problem
and
NMIN
application
currently
is
not
able
to
prevent
users
from
doing
so.

NMINRun
NMIMVMTOutput
NMIMError
NMIMPollutantoutput
­
15­
Change
Log
for
11/
18/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20031118.
EXE
This
version,
based
on
the
previous
11/
07/
2003
version,
includes
the
following
changes
and
is
intended
to
be
used
for
Cimulus
Task
29
and
later.

Change
1)
Corrected
the
fraction
value
of
benz(
a)
anthracene
for
diesel
in
table
SCCTOXICS.
by
running
the
following
SQL
statement:
(
91
records
affected)

update
scctoxics
set
exhdiesel
=
0.00000071
where
pollutantcodeid
=
32
and
(
exhdiesel
>
0.0000070
and
exhdiesel
<
0.0000072);

Change
2)
Ran
script
to
fix
problems
(
a)
There
is
an
error
in
the
value
for
the
ETOHMktShare
for
GasolineID=
3001
and
GasolineID=
3002.
The
current
value
is
100
and
should
be
1.00.
And
(
b)
Removed
unused
gasoline
descriptions.
There
are
289
fuel
descriptions
in
the
Gasoline
table
which
are
never
used.
Although
the
database
design
allows
for
gasolines
to
be
described
but
not
used.
(
see
C:\
MySQL\
data\
CountyDB\
readme\
NMIMGasoline20031118.
txt)

Change
Log
for
11/
07/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20031107.
EXE
This
version,
based
on
the
previous
10/
09/
2003
version
(
see
next
page),
includes
the
following
changes
and
is
intended
to
be
used
for
Cimulus
Task
29
and
later.

Change
1)
Corrected
the
dioxin
emission
factors
from
units
of
TEQ
to
g/
mi
and
fixed
the
incorrect
propionaldehyde
emission
factor
in
SCCTOXICS
table.
The
fix
was
done
by
running
the
script
file
"
fixPropAldehyde.
sql"
created
by
Ed
Glover
(
Gwo
made
a
little
modification).
­
16­
Change
Log
for
10/
09/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
County20031009.
EXE
For
the
purpose
of
easier
to
set
up
CountyDB/
Output
databases
for
running
NMIM
applications,
all
the
NMIM
Output
files
were
included
in
this
version.
This
version,
based
on
the
previous
7/
24/
2003
version
(
see
next
page),
includes
the
following
changes
and
is
intended
to
be
used
for
Cimulus
Task
29
and
later.

Change
1)
Fixing
data
based
on
Pechan's
"
Comparison
of
NMIM
County
Database
to
NEI
Modeling"
final
report
dated
9/
15/
2003.
The
fix
was
done
by
running
two
scripts,
pechanfix.
sql
and
mainefix.
sql.

Change
2)
Added
a
new
record
into
emissiontype
table
to
include
emissiontypeid=
124,
emissiontype=
All.
(
suggested
by
Harvey
M.)

Change
3)
Corrected
PollutantCode,
Pollutant_
Name
in
PollutantCodeID
Table
as
suggested
by
Harvey
M.
by
running
the
following
code:

UPDATE
PollutantCode
SET
PollutantCode
=
'
16065831',
PollutantName='
Chromium
(
Cr3+)'
WHERE
PollutantCode
=
'
7440473'
AND
PollutantName='
Chromim
(
Cr3+)';

UPDATE
PollutantCode
SET
PollutantCode
=
'
18540299',
PollutantName='
Chromium
(
Cr6+)'
WHERE
PollutantCode
=
'
18540299'
AND
PollutantName='
Chromim
(
Cr6+)';

UPDATE
PollutantCode
SET
PollutantCode
=
'
SOA',
PollutantName='
Secondary
Organic
Aerosol'
WHERE
PollutantCode
=
'
SOA'
AND
PollutantName='
Soluble
Organic
Aerosol';

UPDATE
PollutantCode
SET
PollutantCode
=
'
1746016',
PollutantName='
2,3,7,8­
Tetrachlorodibenzo­
p­
Dioxin'
WHERE
PollutantCode
=
'
600'
AND
PollutantName='
2,3,7,8­
TCDD
TEQ';
­
17­
Change
Log
for
7/
24/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
Create_
db\
County20030724.
EXE
This
version
is
based
on
the
previous
version
dated
7/
18/
2003
(
see
next
page).

Change
1)
Run
the
following
SQL
code
to
update
County
database
for
the
"
Ethyl
Benzene"
issue
reported
by
Megan:

UPDATE
PollutantCode
SET
RunSpecPollutantIndex=
21
WHERE
PollutantName='
Ethyl
Benzene';

Change
2)
Delete
the
field
"
BenzExhDies"
from
table
"
SCC"
by
running
the
following
SQL
code:
alter
table
scc
drop
column
BenzExhDies;
­
18­
Change
Log
for
7/
18/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
Create_
db\
County20030718.
EXE
Note:
(
1)
This
version
works
with
the
NMIM
application
of
Cimulus
Task
29.
It
will
not
work
with
any
earlier
versions
of
NMIM
application
due
to
the
structure
changes
of
NMIMOUTPUT
database.
(
2)
This
version
of
CountyDB
is
based
on
6/
24/
2003
CountyDB
database
design
(
see
C:\
MySQL\
data\
CountyDB\
readme\
countydb624.
rtf
for
details)

There're
four
changes
made
in
this
version
of
CountyDB:

Change
1)
The
three
tables
(
as
nine
files),
County,
County1999,
and
Countybase
should
be
copied
into
the
CountyDB
in
order
to
provide
a
minimal
number
of
representing
counties
for
1999
and
2002.
These
three
tables
are
currently
configured
so
that
County
and
County1999
are
identical.
NMIM
only
uses
County.
Countybase
contains
the
set
of
241
NR
representing
counties
developed
by
Mitch
so
that
it
will
work
for
any
year.
County1999
has
106
representing
counties
and
has
been
assured
to
be
correct
only
for
1999
and
2002.
If
the
user
wants
to
do
a
run
for
2010,
she
should
copy
Countybase
into
County.
This
operation
requires
copying
three
files:

Copy
countybase.
frm
county.
frm
Copy
countybase.
myd
county.
myd
Copy
countybase.
myi
county.
myi
To
go
back
to
the
1999
configuration,
copy
County1999
into
County.

If
there
is
any
doubt,
county1999
has
an
extra
field
called
year,
which
is
set
to
1999
for
all
rows.
Countybase
does
not
have
this
extra
field.

Change
2)
Modified
the
NMIM
OUTPUT
design
to
reduce
the
NMIM
output
size:
(
see
C:\
MySQL\
data\
CountyDB\
readme\
Output624.
rtf
for
details)

°
Added
a
new
key
field
"
PollutantCodeID"
(
TINYINT(
2))
to
PollutantCode
table.
And
changed
the
field
"
PollutantCode"
to
non­
key.
°
Added
a
new
table
"
EmissionType".
°
Added
a
new
key
field
"
SCCID"
(
SMALLINT)
to
SCC
table.
Changed
the
field
"
SCC"
to
non­
key.
°
Changed
"
RunId"
to
SMALLINT.
°
Modified
the
FK/
relationships
affected
by
the
above
changes.

Change
3)
All
the
external
files/
folders
were
deleted
from
the
CountyDB
folder,
that
is
C:\
MySQL\
data\
CountyDB
(
note
that
you
may
have
a
different
folder
name
assigned
by
you
during
installing
CountyDB).
­
19­
Change
4)
Further
Changes
made
to
the
5/
12/
2003
version
of
the
ERG
County
Database
In
addition
to
the
serious
problems
which
prevented
the
use
of
the
database,
there
are
smaller
problems
with
the
gasolines.
The
serious
problems
are
addressed
in
a
separate
document.
This
document
describes
the
changes
to
the
gasolines.

1)
California
is
aggressively
phasing
out
MTBE
in
its
fuels.
This
is
reflected
in
the
methodology
that
was
to
be
used
to
generate
future
fuels
for
California.
However,
the
adjustments
were
*
not*
applied
to
California
gasolines.
So,
all
of
the
California
gasolines
need
to
be
regenerated
using
the
correct
adjustment
profiles.

select
year,
month,
fipsstateid,
hwygasolineid
from
countyyearmonth,
gasoline
where
fipsstateid=
6
group
by
fipsstateid,
hwygasolineid
order
by
year,
month,
hwygasolineid;

select
*
from
gasoline
where
gasolineid
in
(
171,172,173,1907,1908,1909,2193,2202,2205,2196,2208,2199,2194,2203,2206,2197,2209,220
0,2195,2204,2207,2198,2210,2201);

The
base
year
gasolines
for
California
(
171,172,173,1907,1908
and
1909)
should
be
the
same
for
calendar
years
1999
through
2003.
The
adjustment
factors
for
all
of
the
California
gasolines
should
be
from
factor
set
"
W"
from
the
spreadsheet
"
030425_
gasoline
assignments
and
parameters.
xls"
provided
by
ERG
for
all
calendar
years
after
2003.
After
proper
adjustment,
the
adjusted
fuels
are
substituted
for
the
existing
fuels
in
the
Gasoline
table.

delete
from
gasoline
where
gasolineid
in
(
2193,2202,2205,2196,2208,2199,2194,2203,2206,2197,2209,2200,2195,2204,2207,2198,2210,
2201);

load
data
infile
'
c:/
mysql/
data/
california.
csv'
into
table
gasoline
ignore
1
lines;
2)
Puerto
Rico
and
the
Virgin
Islands
are
assumed
to
have
the
same
fuels
as
Hawaii.
This
is
the
same
assumption
used
for
the
1999
NEI
inventory
work.
However,
this
is
not
very
likely,
since
the
mainland
sources
for
Hawaii
are
likely
to
be
quite
different
for
Puerto
­
20­
Rico
and
the
Virgin
Islands.
Instead,
for
NMIM,
Puerto
Rico
and
the
Virgin
Islands
will
be
assumed
to
have
the
same
fuels
as
Collier
County,
Florida
(
FIPS
12021).

create
table
florida
select
*
from
countyyearmonth
where
fipsstateid
in
(
12)
and
fipscountyid=
21
limit
1000;

CREATE
TABLE
 
newcym 
(
 
FIPSCountyId 
smallint(
3)
unsigned
NOT
NULL
default
'
0',
 
Year 
smallint(
4)
unsigned
NOT
NULL
default
'
0',
 
FIPSStateId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
Month 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
HwyFuelDataSource 
smallint(
3)
unsigned
default
NULL,
 
HwyGasolineId 
smallint(
3)
unsigned
NOT
NULL
default
'
0',
 
NRGasolineId 
smallint(
3)
unsigned
NOT
NULL
default
'
0',
 
NRFuelDataSource 
smallint(
3)
unsigned
default
NULL,
 
HwyDieselId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
NRDieselId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
NGId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
HwyGasolineIdA 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
HwyGasolineIdB 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
PRIMARY
KEY
( 
FIPSCountyId , 
Year , 
FIPSStateId , 
Month ),
KEY
 
XIF22Count 
( 
NRFuelDataSource ),
KEY
 
XIF43CountyYearMonth 
( 
NRGasolineId ),
KEY
 
XIF10Count 
( 
HwyGasolineId ),
KEY
 
XIF44CountyYearMonth 
( 
NRDieselId ),
KEY
 
XIF8County 
( 
NGId ),
KEY
 
XIF5County 
( 
Year , 
FIPSCountyId , 
FIPSStateId ),
KEY
 
XIF46CountyYearMonth 
( 
HwyGasolineIdB ),
KEY
 
XIF26Count 
( 
HwyFuelDataSource ),
KEY
 
XIF6County 
( 
HwyDieselId ),
KEY
 
XIF45CountyYearMonth 
( 
HwyGasolineIdA ),
KEY
 
XIF4County 
( 
FIPSCountyId , 
FIPSStateId , 
Month )
)
TYPE=
MyISAM;

insert
into
newcym
(
fipsstateid,
fipscountyid,
year,
month,
ngid,
hwydieselid,
hwygasolineid,
nrgasolineid,
nrdieselid,
hwyfueldatasource,
nrfueldatasource,
hwygasolineida,
hwygasolineidb)
select
a.
fipsstateid
as
fipsstateid,
a.
fipscountyid
as
fipscountyid,
­
21­
a.
year
as
year,
a.
month
as
month,
if(
a.
fipsstateid
in
(
72,78),
b.
ngid,
a.
ngid)
as
ngid,
if(
a.
fipsstateid
in
(
72,78),
b.
hwydieselid,
a.
hwydieselid)
as
hwydieselid,
if(
a.
fipsstateid
in
(
72,78),
b.
hwygasolineid,
a.
hwygasolineid)
as
hwygasolineid,
if(
a.
fipsstateid
in
(
72,78),
b.
nrgasolineid,
a.
nrgasolineid)
as
nrgasolineid,
if(
a.
fipsstateid
in
(
72,78),
b.
nrdieselid,
a.
nrdieselid)
as
nrdieselid,
if(
a.
fipsstateid
in
(
72,78),
b.
hwyfueldatasource,
a.
hwyfueldatasource)
as
hwyfueldatasource,
if(
a.
fipsstateid
in
(
72,78),
b.
nrfueldatasource,
a.
nrfueldatasource)
as
nrfueldatasource,
if(
a.
fipsstateid
in
(
72,78),
b.
hwygasolineida,
a.
hwygasolineida)
as
hwygasolineida,
if(
a.
fipsstateid
in
(
72,78),
b.
hwygasolineidb,
a.
hwygasolineidb)
as
hwygasolineidb
from
countyyearmonth
as
a,
florida
as
b
where
a.
year
=
b.
year
and
a.
month
=
b.
month
limit
5000000;

drop
table
florida;
drop
table
countyyearmonth;

rename
table
newcym
to
countyyearmonth;

create
table
newcym
select
a.
fipsstateid,
a.
fipscountyid,
a.
year,
a.
month,
if(
a.
fipsstateid
in
(
72,78),
b.
ngid,
a.
ngid)
as
ngid,
if(
a.
fipsstateid
in
(
72,78),
b.
hwydieselid,
a.
hwydieselid)
as
hwydieselid,
if(
a.
fipsstateid
in
(
72,78),
b.
hwygasolineid,
a.
hwygasolineid)
as
hwygasolineid,
if(
a.
fipsstateid
in
(
72,78),
b.
nrgasolineid,
a.
nrgasolineid)
as
nrgasolineid,
if(
a.
fipsstateid
in
(
72,78),
b.
nrdieselid,
a.
nrdieselid)
as
nrdieselid,
if(
a.
fipsstateid
in
(
72,78),
b.
hwyfueldatasource,
a.
hwyfueldatasource)
as
hwyfueldatasource,
if(
a.
fipsstateid
in
(
72,78),
b.
nrfueldatasource,
a.
nrfueldatasource)
as
nrfueldatasource,
if(
a.
fipsstateid
in
(
72,78),
b.
hwygasolineida,
a.
hwygasolineida)
as
hwygasolineida,
if(
a.
fipsstateid
in
(
72,78),
b.
hwygasolineidb,
a.
hwygasolineidb)
as
hwygasolineidb
from
countyyearmonth
as
a,
florida
as
b
where
a.
year
=
b.
year
and
a.
month
=
b.
month
limit
5000000;

drop
table
florida;
drop
table
countyyearmonth;

rename
table
newcym
to
countyyearmonth;
­
22­
3)
I'm
not
sure
this
is
documented
anywhere,
but
the
gasoline
maximum
sulfur
level
for
all
gasolines
is
303
ppm
in
2004
and
2005,
87
ppm
in
2006
and
2007,
and
80
in
2008
and
later
calendar
years
in
all
counties
of
all
states.
­
23­
Change
Log
for
6/
20/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
Create_
db\
County20030620.
EXE
Added
a
new
column,
RunSpecPollutantIndex
TINY
(
2)
NOT
NULL
,
to
the
PollutantCode
table
in
the
CountyDB.
This
change
applies
to
Task
26
and
later.
There
three
files
involved
in
this
change,
namely
pollutantcode.
frm,
pollutantcode.
myd,
and
pollutantcode.
myi.

Attached
below
is
the
new
PollutantCode
table
as
of
6/
20/
2003
from
Cimulus.

PollutantCodeID
PollutantName
RunSpecPollutantIndex
100414
Ethyl
Benzene
17
100425
Styrene
21
106990
1,3­
Butadiene
18
107028
Acrolein
16
108883
Toluene
21
110543
Hexane
21
120127
Anthracene
21
123386
Propionaldehyde
21
129000
Pyrene
21
1330207
Xylene
21
1634044
MTBE
20
18540299
Chromim
(
Cr6+)
21
191242
Benzo(
g,
h,
i)
perylene
21
193395
Indeno(
1,2,3,
c,
d)
pyrene
21
19408743
1,2,3,7,8,9­
22
205992
Benzo(
b)
fluoranthene
21
206440
Fluoranthene
21
207089
Benzo(
k)
fluoranthene
21
208968
Acenaphthylene
21
218019
Chrysene
21
3268879
Octachlorodibenzo­
p­
22
35822469
1,2,3,4,6,7,8­
22
39001020
Octachlorodibenzofuran
22
39227286
1,2,3,4,7,8­
22
40321764
1,2,3,7,8­
22
50000
Formaldehyde
19
50328
Benzo(
a)
pyrene
21
51207319
2,3,7,8­
22
53703
Dibenzo(
a,
h)
anthracene
21
540841
2,2,4­
Trimethylpentane
21
55673897
1,2,3,4,7,8,9­
22
56553
Benz(
a)
anthracene
21
57117314
2,3,4,7,8­
22
57117416
1,2,3,7,8­
22
57117449
1,2,3,6,7,8­
22
­
24­
57653857
1,2,3,6,7,8­
22
600
2,3,7,8­
TCDD
TEQ
22
60851345
2,3,4,6,7,8­
22
67562394
1,2,3,4,6,7,8­
22
70648269
1,2,3,4,7,8­
22
71432
Benzene
17
72918219
1,2,3,7,8,9­
22
7439965
Manganese
21
7439976
Mercury
21
7440020
Nickel
21
7440382
Arsenic
21
7440473
Chromim
(
Cr3+)
21
75070
Acetaldehyde
15
83329
Acenaphthene
21
85018
Phenanthrene
21
86737
Fluorene
21
91203
Naphthalene
21
CH4
Methane
21
CO
Carbon
Monoxide
9
CO2
Carbon
Dioxide
10
HC
Total
Hydrocarbons
11
NH3
Ammonia
12
NMHC
Non­
Methane
11
NMOG
Non­
Methane
Organic
11
NOx
Nitrogen
Oxides
13
PM10­
PRI
Primary
PM10
(
Filterables
100
PM25­
PRI
Primary
PM2.5
(
Filterables
100
SO2
Sulfur
Dioxide
14
SOA
Soluble
Organic
Aerosol
8
TOG
Total
Organic
Gases
11
VOC
Volatile
Organic
11
­
25­
Change
Log
for
6/
17/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
db_
history\
County20030617.
EXE
This
version
of
CountyDB
dated
6/
17/
2003
was
modified
and
to
be
used
by
Task
26
and
after.
The
SCC
table
has
been
changed
by
deleting
fields
as
follows:

alter
table
scc
drop
column
BenzExhGas;
alter
table
scc
drop
column
BenzExhEth;

alter
table
scc
drop
column
BenzExhMTBE;
alter
table
scc
drop
column
BenzExhRFG;

alter
table
scc
drop
column
BenzEvapGas;
alter
table
scc
drop
column
BenzEvapEth;

alter
table
scc
drop
column
BenzEvapMTBE;
alter
table
scc
drop
column
BenzEvapRFG;

alter
table
scc
drop
column
ButaExhGas;
alter
table
scc
drop
column
ButaExhEth;

alter
table
scc
drop
column
ButaExhMTBE;
alter
table
scc
drop
column
ButaExhRFG;

alter
table
scc
drop
column
FormExhGas;
alter
table
scc
drop
column
FormExhEth;

alter
table
scc
drop
column
FormExhMTBE;
alter
table
scc
drop
column
FormExhRFG;

alter
table
scc
drop
column
AcetExhGas;
alter
table
scc
drop
column
AcetExhEth;

alter
table
scc
drop
column
AcetExhMTBE;
alter
table
scc
drop
column
AcetExhRFG;

alter
table
scc
drop
column
AcroExhGas;
alter
table
scc
drop
column
AcroExhEth;

alter
table
scc
drop
column
AcroExhMTBE;
alter
table
scc
drop
column
AcroExhRFG;

alter
table
scc
drop
column
MTBEExhGas;
­
26­
alter
table
scc
drop
column
MTBEExhEth;

alter
table
scc
drop
column
MTBEExhMTBE;
alter
table
scc
drop
column
MTBEExhRFG;

alter
table
scc
drop
column
MTBEEvapGas;
alter
table
scc
drop
column
MTBEEvapEth;

alter
table
scc
drop
column
MTBEEvapMTBE;
alter
table
scc
drop
column
MTBEEvapRFG;

alter
table
scc
drop
column
ButaExhDies;
alter
table
scc
drop
column
FormExhDies;

alter
table
scc
drop
column
AcetExhDies;
alter
table
scc
drop
column
AcroExhDies;
­
27­
Change
Log
for
6/
12/
2003
CountyDB
database
Production
Database:
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
db_
history\
County20030612.
EXE
This
version
of
CountyDB
dated
6/
12/
2003
was
created
based
on
Dave
B.'
s
6/
16/
2003
County
Database
and
is
intended
to
be
used
by
NMIM
applications
that
are
the
deliverables
of
Cimulus
Task
25
and
earlier.

Deleted
unwanted
tables
and
their
myd
and
myi
files:
°
cymmtbephsout.
frm,
cymmtbephsout.
myd,
and
cymmtbephsout.
myi.
°
gasmtbephsout.
frm,
gasmtbephsout.
myd,
and
gasmtbephsout.
myi.
°
gas2mtbephsout.
frm,
gas2mtbephsout.
myd,
and
gas2mtbephsout.
myi.
°
imfilenames.
frm,
imfilenames.
myd,
and
imfilenames.
myi.
°
mainegas.
frm,
mainegas.
myd,
and
mainegas.
myi.

Added
three
new
tables
and
populated
data:
pollutantcode,
scctoxics,
and
scc.

Modified
CountyDB
database:
°
M6VClass
table:
added
two
fields
"
Vtype
TinyInt(
2)
Unsigned
Not
NULL"
and
"
p5class
TinyInt(
2)
Unsigned
Not
NULL"
and
populated
data.
°
County
table:
Added
4
new
fields
required
by
Task
28:

T
OzoneSeasonStartMonth
TINYINT(
2),
NOT
NULL,
all
records
have
value
6.

T
OzoneSeasonStartDay
TINYINT(
2),
NOT
NULL,
all
records
have
value
1.

T
OzoneSeasonEndMonth
TINYINT(
2),
NOT
NULL,
all
records
have
value
8.

T
OzoneSeasonEndDay
TINYINT(
2),
NOT
NULL,
all
records
have
value
31.

Modified
Erwin
CountDB
design:
°
Changed
data
length
to
(
6,4)
for
the
following
numeric
fields
in
gasoline
table:
etohmktshare,
mtbemktshare,
tamemktshare,
and
totethermktshare.
°
Changed
data
length
to
(
6,4)
for
field
mktshare.
°
Added
4
new
fields
into
County
table:

T
OzoneSeasonStartMonth
TINYINT(
2),
NOT
NULL.

T
OzoneSeasonStartDay
TINYINT(
2),
NOT
NULL.

T
OzoneSeasonEndMonth
TINYINT(
2),
NOT
NULL.

T
OzoneSeasonEndDay
TINYINT(
2),
NOT
NULL.

Data
corrected:
°
Changed
the
value
of
mtbemktshare
for
gasoline
id=
188
from
68.92
to
0.6892.
°
Set
CountyYear.
Stage2Pct
=
0
for
all
records.
That
is
,
changes
values
of
95
to
0.
(
this
from
team's
6/
9/
2003
meeting
conclusion)
°
SCC
table:
deleted
two
records
with
ids
=
2268008005
and
2260008005.
­
28­
Changes
made
to
the
5/
12/
2003
version
of
the
ERG
County
Database
are:
(
Created
6/
6/
2003
by
Dave
B.,
Last
updated
6/
12/
2003
by
Gwo
S.)

1)
Set
RVPOxyWaiver=
1
in
Gasoline
for
all
cases.
This
will
force
MOBILE6
to
always
use
the
RVP
value
stored
for
that
fuel
regardless
of
the
ETOH
oxygenated
volume
percent.
Otherwise,
MOBILE6
will
adjust
the
RVP,
assuming
splash
blending
of
the
alcohol,
whenever
the
ETOH
volume
percent
is
not
zero.

UPDATE
gasoline
SET
rvpoxywaiver=
1;

2)
The
oxygenate
market
share
for
GasolineID=
188
is
greater
than
one
(
131.08%).
This
fuel
is
only
used
in
89
counties
in
Indiana.
This
fuel
is
only
used
in
calendar
years
1999
(
base
year)
through
2003.
Only
9
fuels
are
ever
assigned
to
counties
in
Indiana
in
any
calendar
year
(
48,
49,
50,
188,
189,
190,
297,
298
and
299).
Fuel
188
has
a
ETOHMktShare
of
31.08%,
which
is
exactly
the
same
ETOHMktShare
in
fuels
48,
49,
50,
189
and
190.
The
other
fuels
in
Indiana
all
have
100%
ETOHMktShare
and
zero
MTBEMktShare.
All
fuels
used
by
counties
that
use
fuel
188
have
a
MTBEMktShare
of
68.92%
for
all
other
fuels
used
in
other
calendar
years.
Also,
fuel
188
is
the
combination
of
two
fuels,
68
and
69,
from
the
Gasoline2
table.
The
MTBE
fuel
(
69)
has
a
68.92%
market
share.

This
leads
me
to
believe
that
the
MTBEMktShare
of
100%
for
GasolineID=
188
is
an
isolated
error.
I
conclude
that
this
fuel
should
have
the
same
MTBEMktShare
as
the
other
fuels
in
Indiana
which
share
the
same
ETOHMktShare.
I
have
set
the
MTBEMktShare
for
GasolineID=
188
to
be
68.92%.

UPDATE
gasoline
SET
MTBEMktShare=
68.92
WHERE
GasolineID=
188;
­
29­
3)
Some
values
for
MTBEVolume
exceed
the
MOBILE6
maximum
value
of
15.1%.
This
problem
only
affects
future
(
not
1999
base
year)
gasoline.
This
is
due
to
the
generic
multiplicative
adjustment
of
MTBE
volumes
in
future
calendar
years.
The
MTBE
volumes
in
some
counties
of
Nevada,
Maine,
Hawaii,
Puerto
Rico
and
the
Virgin
Islands
are
too
large.
This
is
not
an
error
in
applying
the
EPA
supplied
methodology.
However,
these
results
were
not
expected
and
must
be
changed
in
order
for
NMIM
to
use
the
data.

In
Nevada,
the
MTBE
volume
adjustment
for
2007
and
later
calendar
years
(
5.18)
is
so
large
that
some
counties
with
significant
MTBE
volumes
in
the
base
year
end
up
with
extreme
MTBE
volumes
in
2007.

Only
five
counties
(
7,11,13,15
and
33)
are
affected.
Only
six
gasolines
(
1510,
1511,
1512,
1514,
1515
and
1516)
have
extreme
MTBE
volume
values.
The
MTBEvolumes
for
these
six
gasolines
were
capped
at
the
MOBILE6
maximum
(
15.1%).
Three
additional
gasolines
with
high
MTBE
volumes
(
2103,
2104
and
2105)
are
never
used
and
were
deleted.

update
gasoline
set
mtbevolume=
15.1
where
gasolineid
in
(
1510,1511,1512,1514,1515,1516);

delete
from
gasoline
where
gasolineid
in
(
2103,2104,2105);

In
Maine,
the
RFG
program
was
eliminated
early
in
1999,
which
listed
Maine
as
a
"
non­
RFG"
area
for
adjustments.
However,
fuels
delivered
in
1999
to
some
counties
still
contained
large
amounts
of
MTBE,
likely
due
to
fuel
already
contracted
before
the
RFG
requirement
was
lifted.
So,
rather
than
continuing
to
receive
MTBE
containing
fuel,
Maine
should
get
less
MTBE
in
future
calendar
years
in
those
counties.

There
are
only
two
sets
of
gasoline
used
in
Maine
counties,
low
and
high
MTBE.
The
solution
is
to
set
the
HwyGasolineID
(
and
NRGasolineID)
in
all
2000
and
later
calendar
years
in
the
high
MTBE
counties
to
the
same
HwyGasolineID
values
used
in
other
Maine
counties.
FIPSCountyID=
1
is
representative
of
high
MTBE
counties
and
FIPSCountyID=
3
is
representative
of
low
MTBE
counties.
Match
the
gasolines
by
year
and
month
in
these
two
counties
and
replace
the
HwyGasolineID
and
NRGasolineID
values
for
the
high
MTBE
counties
with
the
corresponding
low
MTBE
HwyGasolineID
values
in
all
calendar
years
after
1999.

CREATE
TABLE
MaineGas
SELECT
a.
hwygasolineid
as
baseid,
b.
hwygasolineid
as
newid
FROM
countyyearmonth
as
a,
countyyearmonth
as
b
­
30­
WHERE
a.
fipsstateid=
b.
fipsstateid
AND
a.
year=
b.
year
AND
a.
month=
b.
month
AND
a.
fipsstateid=
23
AND
a.
fipscountyid=
1
AND
b.
fipscountyid=
3
GROUP
BY
a.
hwygasolineid;

CREATE
TABLE
 
cymnew 
(
 
FIPSCountyId 
smallint(
3)
unsigned
NOT
NULL
default
'
0',
 
Year 
smallint(
4)
unsigned
NOT
NULL
default
'
0',
 
FIPSStateId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
Month 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
HwyFuelDataSource 
smallint(
3)
unsigned
default
NULL,
 
HwyGasolineId 
smallint(
3)
unsigned
NOT
NULL
default
'
0',
 
NRGasolineId 
smallint(
3)
unsigned
NOT
NULL
default
'
0',
 
NRFuelDataSource 
smallint(
3)
unsigned
default
NULL,
 
HwyDieselId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
NRDieselId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
NGId 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
HwyGasolineIdA 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
 
HwyGasolineIdB 
tinyint(
2)
unsigned
NOT
NULL
default
'
0',
PRIMARY
KEY
( 
FIPSCountyId , 
Year , 
FIPSStateId , 
Month ),
KEY
 
XIF22Count 
( 
NRFuelDataSource ),
KEY
 
XIF43CountyYearMonth 
( 
NRGasolineId ),
KEY
 
XIF10Count 
( 
HwyGasolineId ),
KEY
 
XIF44CountyYearMonth 
( 
NRDieselId ),
KEY
 
XIF8County 
( 
NGId ),
KEY
 
XIF5County 
( 
Year , 
FIPSCountyId , 
FIPSStateId ),
KEY
 
XIF46CountyYearMonth 
( 
HwyGasolineIdB ),
KEY
 
XIF26Count 
( 
HwyFuelDataSource ),
KEY
 
XIF6County 
( 
HwyDieselId ),
KEY
 
XIF45CountyYearMonth 
( 
HwyGasolineIdA ),
KEY
 
XIF4County 
( 
FIPSCountyId , 
FIPSStateId , 
Month )
)
TYPE=
MyISAM;

Insert
into
cymnew
(
fipsstateid,
fipscountyid,
year,
month,
ngid,
hwydieselid,
hwygasolineid,
nrgasolineid,
nrdieselid,
hwyfu
eldatasource,
nrfueldatasource,
hwygasolineida,
hwygasolineidb)
select
fipsstateid,
fipscountyid,
year,
month,
ngid,
hwydieselid,
if(
isnull(
baseid),
hwygasolineid,
if(
year>
1999,
newid,
hwygasolineid))
as
hwygasolineid,
if(
isnull(
baseid),
nrgasolineid,
if(
year>
1999,
newid,
nrgasolineid))
as
nrgasolineid,
­
31­
nrdieselid,
hwyfueldatasource,
nrfueldatasource,
hwygasolineida,
hwygasolineidb
from
countyyearmonth
left
join
mainegas
on
hwygasoliineid=
baseid;

drop
countyyearmonth;

rename
table
cymnew
to
countyyearmonth;

delete
from
gasoline
where
gasolineid
in
(
423,424,425,938,939,940,941,942,943,944,945,946);

In
addition
to
these
serious
problems
we
have
identified
problems
which,
although
serious,
do
not
prevent
the
use
of
the
database.
These
problems,
and
changes
in
the
fuel
assumptions
since
the
delivery
of
the
database,
will
require
a
much
more
expansive
recalculation
of
gasoline
parameters
than
can
be
attempted
now.
So,
the
following
gasoline
problems
were
*
not*
fixed
in
the
database:

California
is
aggressively
phasing
out
MTBE
in
its
fuels.
This
is
reflected
in
the
methodology
that
was
to
be
used
to
generate
future
fuels
for
California.
However,
the
adjustments
were
*
not*
applied
to
California
gasolines.
So,
all
of
the
California
gasolines
need
to
be
regenerated
using
the
correct
adjustment
profiles
(
V
and
W).

Hawaii
gets
most
of
it's
gasoline
from
the
mainland
(
California).
In
1999,
Hawaiian
gasoline
has
large
amounts
of
MTBE.
However,
California
is
aggressively
phasing
out
MTBE.
Therefore,
Hawaii
should
use
the
same
future
year
profiles
as
California
(
V
and
W).

Puerto
Rico
and
the
Virgin
Islands
are
assumed
to
have
the
same
fuels
as
Hawaii.
This
is
not
very
likely,
since
the
mainland
sources
for
Hawaii
are
likely
to
be
geographically
quite
different
than
for
Puerto
Rico
and
the
Virgin
Islands.
­
32­
4)
There
are
21
gasoline
oxygenate
volume
sums
that
are
slightly
greater
than
1.0.
This
may
cause
problems
in
MOBILE6.
In
all
cases
the
MTBE
volume
is
the
largest
fraction.
The
MTBEVolume
in
all
cases
was
reduced
so
that
the
sum
of
the
oxygenate
volumes
was
equal
to
100.
Some
sums
are
slightly
less
than
100.
These
values
were
not
changed.

UPDATE
gasoline
SET
mtbemktshare
=
mtbemktshare­(
etohmktshare+
mtbemktshare+
etbemktshare+
tamemktshare­
100)
WHERE
etohmktshare+
mtbemktshare+
etbemktshare+
tamemktshare
>
100;

UPDATE
gasoline
SET
mtbemktshare=
mtbemktshare+
0.01
WHERE
(
etohmktshare+
mtbemktshare+
etbemktshare+
tamemktshare)
>
99
and
(
etohmktshare+
mtbemktshare+
etbemktshare+
tamemktshare)
<
99.999;
­
33­
5)
All
of
the
1858
records
in
Gasoline
and
all
81
records
in
Gasoline2
have
market
share
values
in
percent
format,
rather
than
the
fraction
format
required
by
MOBILE6.
In
order
to
retain
the
number
of
significant
digits
in
the
values,
the
market
share
fields
must
be
modified
before
dividing
the
values
by
100.

ALTER
TABLE
gasoline
MODIFY
etohmktshare
decimal(
6,4)
not
null,
MODIFY
mtbemktshare
decimal(
6,4)
not
null,
MODIFY
etbemktshare
decimal(
6,4)
not
null,
MODIFY
tamemktshare
decimal(
6,4)
not
null,
MODIFY
totethermktshare
decimal(
6,4)
not
null;

The
CountyDB
Erwin
design
also
has
been
modified
to
include
the
above
data­
length
changes
of
market
shares.
See
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
CountyDB20030612.
ER1.

UPDATE
gasoline
SET
etohmktshare
=
etohmktshare/
100,
mtbemktshare
=
mtbemktshare/
100,
etbemktshare
=
etbemktshare/
100,
tamemktshare
=
tamemktshare/
100,
totethermktshare=
totethermktshare/
100;

ALTER
TABLE
gasoline2
MODIFY
mktshare
decimal(
6,4)
not
null;

The
CountyDB
Erwin
design
also
has
been
modified
to
include
the
above
data­
length
change
of
market
share.
See
H:\
AMD\
MOBILE\
NMIM\
CountyDB\
CountyDB20030612.
ER1.

UPDATE
gasoline2
SET
mktshare
=
mktshare/
100;
­
34­
6)
Some
counties
have
representing
counties
that
do
not
match
their
basic
parameters
(
as
they
should).
Rather
than
investigate
to
determine
what
the
appropriate
representing
county
should
be,
each
of
the
mis­
matched
counties
will
be
set
to
represent
itself.
This
will
significantly
increase
the
number
of
representing
counties.
If
NMIM
performance
is
a
problem,
it
can
be
improved
by
investigating
these
counties
to
determine
their
appropriate
representing
county.

A.
Check
that
counties
within
groups,
both
highway
and
nonroad,
do
not
contain
conflicting
values,
identifying
any
counties
that
have
such
problems.

B.
Modify
the
county
groupings
to
correct
any
such
problems
discovered
by
making
more
counties
represent
themselves.

On
the
current
NMIM
county
database
this
process
roughly
doubles
the
number
of
county
groups.

The
script
files
are
meant
to
be
run
in
sequence
as
follows:

1.
checkhwygroups.
sql
and/
or
checknrgroups.
sql
carry
out
function
A
above.
2.
makegroupstofix.
sql
constructs
consolidated,
but
not
completely
unique,
lists
of
counties
with
problems.
3.
makenewcountytable.
sql
constructs
new
County
table.
This
is
the
only
script
file
which
modifies
the
database.
4.
step
1
can
be
repeated
if
desired
at
this
point
to
confirm
that
the
database
no
longer
has
problems.
5.
cleanupaftercountygroupfix.
sql
deletes
all
files
created
in
the
process
that
do
not
belong
in
county
database.
­
35­
#
checkhwygroups.
sql
/*
MySQL
script
file
to
check
that
counties
in
the
same
highway
group
have
same
information
*/

USE
CountyDB;

/*
Look
for
Counties
in
the
same
highway
group
with
different
altitude
values
*/

DROP
TABLE
IF
EXISTS
hwyaltitudeprob;
CREATE
TABLE
hwyaltitudeprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
C.
altitude,
C.
HWYRepFIPSCntyID,
C2.
altitude
AS
REPaltitude
FROM
County
AS
C,
County
AS
C2
WHERE
C.
FIPSStateID
=
C2.
FIPSStateID
AND
C2.
FIPSCountyID
=
C.
HWYRepFIPSCntyID
AND
C2.
altitude
<>
C.
altitude;

/*
Look
for
Counties
in
the
same
highway
group
with
different
IMFileName
values
*/

DROP
TABLE
IF
EXISTS
imfileprob;
CREATE
TABLE
imfileprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
CY1.
year,
CY1.
IMFileName,
C.
HWYRepFIPSCntyID,
CY2.
IMFileName
AS
REPIMFileName
FROM
County
AS
C,
CountyYear
AS
CY1,
CountyYear
AS
CY2
WHERE
C.
FIPSStateID
=
CY1.
FIPSStateID
AND
C.
FIPSCountyID
=
CY1.
FIPSCountyID
AND
C.
FIPSStateID
=
CY2.
FIPSStateID
AND
C.
HWYRepFIPSCntyID
=
CY2.
FIPSCountyID
AND
CY1.
year
=
CY2.
year
AND
CY1.
IMFileName
<>
CY2.
IMFileName;

/*
Look
for
Counties
in
the
same
highway
group
with
different
ATPFileName
values
*/

DROP
TABLE
IF
EXISTS
atpfileprob;
CREATE
TABLE
atpfileprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
CY1.
year,
CY1.
ATPFileName,
C.
HWYRepFIPSCntyID,
CY2.
ATPFileName
AS
REPATPFileName
FROM
County
AS
C,
CountyYear
AS
CY1,
CountyYear
AS
CY2
WHERE
C.
FIPSStateID
=
CY1.
FIPSStateID
AND
C.
FIPSCountyID
=
CY1.
FIPSCountyID
AND
C.
FIPSStateID
=
CY2.
FIPSStateID
AND
C.
HWYRepFIPSCntyID
=
CY2.
FIPSCountyID
AND
CY1.
year
=
CY2.
year
AND
CY1.
ATPFileName
<>
CY2.
ATPFileName;
­
36­
/*
Look
for
Counties
in
the
same
highway
group
with
different
maxTemp
values
*/

DROP
TABLE
IF
EXISTS
maxtempprob;
CREATE
TABLE
maxtempprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
CM1.
month,
CM1.
maxTemp,
C.
HwyRepFIPSCntyID,
CM2.
maxTemp
AS
REPMaxTemp
FROM
County
AS
C,
CountyMonth
AS
CM1,
CountyMonth
AS
CM2
WHERE
C.
FIPSStateID
=
CM1.
FIPSStateID
AND
C.
FIPSCountyID
=
CM1.
FIPSCountyID
AND
C.
FIPSStateID
=
CM2.
FIPSStateID
AND
C.
HwyRepFIPSCntyID
=
CM2.
FIPSCountyID
AND
CM1.
month
=
CM2.
month
AND
CM1.
maxTemp
<>
CM2.
maxTemp;

/*
Look
for
Counties
in
the
same
highway
group
with
different
minTemp
values
*/

DROP
TABLE
IF
EXISTS
mintempprob;
CREATE
TABLE
mintempprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
CM1.
month,
CM1.
minTemp,
C.
HwyRepFIPSCntyID,
CM2.
minTemp
AS
REPminTemp
FROM
County
AS
C,
CountyMonth
AS
CM1,
CountyMonth
AS
CM2
WHERE
C.
FIPSStateID
=
CM1.
FIPSStateID
AND
C.
FIPSCountyID
=
CM1.
FIPSCountyID
AND
C.
FIPSStateID
=
CM2.
FIPSStateID
AND
C.
HwyRepFIPSCntyID
=
CM2.
FIPSCountyID
AND
CM1.
month
=
CM2.
month
AND
CM1.
minTemp
<>
CM2.
minTemp;

/*
Look
for
Counties
in
the
same
highway
group
with
different
highway
gasolineID
values
*/

DROP
TABLE
IF
EXISTS
hwygasprob;
CREATE
TABLE
hwygasprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
CYM1.
year,
CYM1.
month,
CYM1.
HwyGasolineID,
C.
HWYRepFIPSCntyID,
CYM2.
HwyGasolineID
AS
REPHwyGasolineID
FROM
County
AS
C,
CountyYearMonth
AS
CYM1,
CountyYearMonth
AS
CYM2
WHERE
C.
FIPSStateID
=
CYM1.
FIPSStateID
AND
C.
FIPSCountyID
=
CYM1.
FIPSCountyID
AND
C.
FIPSStateID
=
CYM2.
FIPSStateID
AND
C.
HWYRepFIPSCntyID
=
CYM2.
FIPSCountyID
AND
CYM1.
year
=
CYM2.
year
AND
CYM1.
month
=
CYM2.
month
AND
CYM1.
HwyGasolineID
<>
CYM2.
HwyGasolineID;
­
37­
#
checknrgroups.
sql
/*
MySQL
script
file
to
check
that
counties
in
the
same
nonroad
group
have
same
information
*/

USE
CountyDB;

/*
Look
for
Counties
in
the
same
nonroad
group
with
different
altitude
values
*/

DROP
TABLE
IF
EXISTS
nraltitudeprob;
CREATE
TABLE
nraltitudeprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
C.
altitude,
C.
NRRepFIPSCntyID,
C2.
altitude
AS
REPaltitude
FROM
County
AS
C,
County
AS
C2
WHERE
C.
FIPSStateID
=
C2.
FIPSStateID
AND
C2.
FIPSCountyID
=
C.
NRRepFIPSCntyID
AND
C2.
altitude
<>
C.
altitude;

/*
Look
for
Counties
in
the
same
nonroad
group
with
different
avgTemp
values
*/

DROP
TABLE
IF
EXISTS
avgtempprob;
CREATE
TABLE
avgtempprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
CM1.
month,
CM1.
avgTemp,
C.
NRRepFIPSCntyID,
CM2.
avgTemp
AS
REPAvgTemp
FROM
County
AS
C,
CountyMonth
AS
CM1,
CountyMonth
AS
CM2
WHERE
C.
FIPSStateID
=
CM1.
FIPSStateID
AND
C.
FIPSCountyID
=
CM1.
FIPSCountyID
AND
C.
FIPSStateID
=
CM2.
FIPSStateID
AND
C.
NRRepFIPSCntyID
=
CM2.
FIPSCountyID
AND
CM1.
month
=
CM2.
month
AND
CM1.
avgTemp
<>
CM2.
avgTemp;

/*
Look
for
Counties
in
the
same
nonroad
group
with
different
nonroad
gasolineID
values
*/

DROP
TABLE
IF
EXISTS
nrgasprob;
CREATE
TABLE
nrgasprob
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
CYM1.
year,
CYM1.
month,
CYM1.
NRGasolineID,
C.
NRRepFIPSCntyID,
CYM2.
NRGasolineID
AS
REPNRGasolineID
FROM
County
AS
C,
CountyYearMonth
AS
CYM1,
CountyYearMonth
AS
CYM2
WHERE
C.
FIPSStateID
=
CYM1.
FIPSStateID
AND
C.
FIPSCountyID
=
CYM1.
FIPSCountyID
AND
C.
FIPSStateID
=
CYM2.
FIPSStateID
AND
C.
NRRepFIPSCntyID
=
CYM2.
FIPSCountyID
AND
CYM1.
year
=
CYM2.
year
AND
CYM1.
month
=
CYM2.
month
AND
CYM1.
NRGasolineID
<>
CYM2.
NRGasolineID;
­
38­
#
makegroupstofix.
sql
/*
MySQL
script
file
to
prepare
two
lists
of
NMIM
counties
*
(
one
for
highway,
one
for
nonroad)
identified
to
*
become
their
own
County
Group
*/

USE
CountyDB;
DROP
TABLE
IF
EXISTS
HwyCountyToFix;
CREATE
TABLE
HwyCountyToFix
(
FIPSStateID
SMALLINT,
FIPSCountyID
SMALLINT
);

INSERT
INTO
HwyCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
hwyaltitudeprob;
INSERT
INTO
HwyCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
imfileprob;
INSERT
INTO
HwyCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
atpfileprob;
INSERT
INTO
HwyCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
maxtempprob;
INSERT
INTO
HwyCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
mintempprob;
INSERT
INTO
HwyCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
hwygasprob;

DROP
TABLE
IF
EXISTS
NRCountyToFix;
CREATE
TABLE
NRCountyToFix
(
FIPSStateID
SMALLINT,
FIPSCountyID
SMALLINT
);

INSERT
INTO
NRCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
nraltitudeprob;
INSERT
INTO
NRCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
avgtempprob;
INSERT
INTO
NRCountyToFix
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
nrgasprob;
­
39­
#
makenewcountytable.
sql
/*
MySQL
script
file
to
update
NMIM
County
table
to
make
more
counties
represent
themselves
(
where
they
have
different
data).
Assumes
that
checkhwygroups.
sql,
checknrgroups.
sql,
and
makegroupstofix.
sql
have
been
run.
*/
USE
CountyDB;

/*
First,
eliminate
duplicates
FROM
LISTS
of
counties
to
fix.
*/

DROP
TABLE
IF
EXISTS
HwyCountyToFix2;
CREATE
TEMPORARY
TABLE
HwyCountyToFix2
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
HwyCountyToFix;

DROP
TABLE
IF
EXISTS
NRCountyToFix2;
CREATE
TEMPORARY
TABLE
NRCountyToFix2
SELECT
DISTINCT
FIPSStateID,
FIPSCountyID
FROM
NRCountyToFix;

/*
Second,
make
new
copy
of
County
table
with
HwyRepCountyID
changed
where
necessary
*/

DROP
TABLE
IF
EXISTS
County2;
CREATE
TABLE
County2
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
C.
Altitude,
C.
CountyName,
C.
NRRepFIPSCntyID,
IF(
ISNULL(
Hwy.
FIPSCountyID),
C.
HwyRepFIPSCntyID,
C.
FIPSCountyID)
AS
HwyRepFIPSCntyID
FROM
County
AS
C
LEFT
JOIN
HwyCountyToFix2
AS
Hwy
USING
(
FIPSStateID,
FIPSCountyID);

/*
Now
repopulate
County
Table
from
County2
with
NRRepCountyID
changed
where
necessary
*/

TRUNCATE
TABLE
County;

INSERT
INTO
County
(
FIPSStateID,
FIPSCountyID,
Altitude,
CountyName,
NRRepFIPSCntyID,
HwyRepFIPSCntyID)
SELECT
C.
FIPSStateID,
C.
FIPSCountyID,
C.
Altitude,
C.
CountyName,
IF(
ISNULL(
NR.
FIPSCountyID),
C.
NRRepFIPSCntyID,
C.
FIPSCountyID),
C.
HwyRepFipsCntyID
FROM
County2
AS
C
LEFT
JOIN
NRCountyToFix2
AS
NR
USING
(
FIPSStateID,
FIPSCountyID);
­
40­
#
cleanupaftercountygroupfix.
sql
/*
MySQL
script
file
to
cleanup
extraneaous
tables
created
by
checkhwygroups.
sql,
checknrgroups.
sql,
makegroupstofix.
sql,
and
makenewcountytable.
sql
*/

USE
CountyDB;

DROP
TABLE
IF
EXISTS
hwyaltitudeprob;
DROP
TABLE
IF
EXISTS
imfileprob;
DROP
TABLE
IF
EXISTS
atpfileprob;
DROP
TABLE
IF
EXISTS
maxtempprob;
DROP
TABLE
IF
EXISTS
mintempprob;
DROP
TABLE
IF
EXISTS
hwygasprob;
DROP
TABLE
IF
EXISTS
nraltitudeprob;
DROP
TABLE
IF
EXISTS
avgtempprob;
DROP
TABLE
IF
EXISTS
nrgasprob;
DROP
TABLE
IF
EXISTS
hwycountytofix;
DROP
TABLE
IF
EXISTS
nrcountytofix;
DROP
TABLE
IF
EXISTS
county2;
