Re: Mapping of SCCs for Reporting

["Dennis McGeen" <MCGEENDD@michigan.gov>]

Jon, this looks like a good solution to the problem you described.  

Dennis

>>> "Jon Dettling" <dettling@glc.org> 05/18/07 1:55 PM >>>
Steering Committee,

This is in follow-up to our discussion at last week's meeting
regarding
establishing a grouping of SCCs to use for reporting of the regional
data.
I've done some more work on the topic following our meeting and have
come up
with the following proposal for how to handle this.

It should be pointed out at the beginning that this doesn't affect in
any
way the set of SCCs that people in the group can or should use for
calculating data or reporting data to the regional repository. This is
intended to be a mapping of SCCs that takes place at the regional level
for
purposes of reporting. I've tried to come up with a method for doing
this
that won't impact the ability of people to follow the current practice
within their agency. A good analogy to make is the current list of 211
project pollutants and the ~400 material codes that we accept and map
to
these 211 categories for reporting. 

The intention, as discussed at the meeting, is to both improve the
consistency of which SCCs are reported at the regional level for
analogous
source types and to reduce confusion arising from reporting partial
lists of
SCCs (e.g., the "top 10 SCCs" for a given pollutant or "SCCs
individually
>5%"), in which case some categories may not appear because they are
broken
up into many separate SCCs. 

This is my recommendation: We have a mapping table within the database
that
maps each possible SCC to one of a select list of more generic SCCs
that can
be used for reporting data from the repository. When we want to run
queries
to do reports on the more generic listing of SCCs, we can just include
the
mapping in the query. No changes will be made to the data as it is
reported
by the states/province, so if we want to look in more detail at some
point
at the more specific breakdown of SCCs, that information will still be
there.

As an example, I've attached a table that shows how the mapping might
be
done for all the Area Source SCCs in the EPA's official table. Here's
an
explanation of the highly cryptic way I've annotated the file. Those
records
with a "1" in column C would be the SCCs used for reporting. The
records
with Column C blank would be mapped to the SCC above them with a 1.
(There
is one case where the SCC being mapped to is below and I've noted this
with
a "0" in those records). This takes 1139 possible Area Source SCCs and
maps
them to 196. Many of these 196 do not appear to be used by our group,
but it
doesn't hurt to include them in the mapping system. There are a few
cases
where I wish there were some more generic SCCs available, but in
general I
think this works out fairly well.

An example of how this would work: For Dry Cleaning, all SCCS starting
2420... are mapped to 2420000000, which is a catch-all SCC for Dry
Cleaning.
In our reports, all emissions states/provinces report using the other
2420... SCCs would be mapped to this one for compiling reports and
called
something like "Dry Cleaning: All Processes, All Solvent Types."

Having worked on reporting the regional data in a number of cases, I
think
this will be a helpful tool and a major time-saver. For example, in
compiling the BaP report, I did quite a bit of similar mapping in order
to
best represent the data by source category, but had to do this by hand.
This
would give us a standard system that can be used easily and
consistently. I
anticipate making similar tables of mappings for Mobile Source SCCs
and
other categories. 

Any thoughts on this?  

______________________
Jon Dettling
Great Lakes Commission
734-971-9135
dettling@glc.org 

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
airtoxics is hosted by the Great Lakes Information Network:
http://www.great-lakes.net
To unsubscribe from this list: send mail to majordomo@great-lakes.net
with the command 'unsubscribe airtoxics' in the body of your message. No
quotes or subject line are required.
About : http://www.great-lakes.net/lists/airtoxics/airtoxics.info
* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *