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
Attachment:
SCC_Mapping_Area.xls
Description: MS-Excel spreadsheet