This Tool Will Quickly Provide Journalism's Most Forgotten W

 

By DAVID POULSON

EPA had just announced new mercury limits for cement factories and we were short on art to go with our story on the Great Lakes Echo, an environmental news service operated by Michigan State University's Knight Center for Environmental Journalism.

No problem. Well before deadline we had in place a map locating every cement factory in the eight-state region. And when clicked, each point listed the factory name and the most recent report of the amount of mercury it put into the air.

You can't beat Google maps for providing the most forgotten of journalism's five Ws — the where. They are fast, efficient, informative and are another point of entry into those messy environmental stories.

If you haven't fooled around with Google maps, go to www.maps.google.com and click on Help. The tutorials will get you rolling with basic maps. That's a good start. If nothing else, such maps help break up those long, ugly text blocks.

But what if you need to plot a lot of points precisely and fast? That is surprisingly easy with some basic spreadsheet skills, a set of data that includes latitude and longitude, and an online tool discussed below.

Mapping is increasingly important for environmental journalists to evaluate and display data. And a surprising number of environmental data sets contain latitude and longitude.

When we were in a jam with the cement story, we turned to the Toxics Release Inventory (TRI), a federal data set used by many environmental reporters. They often use it once a year to identify a region's greatest polluters. But what many overlook is the data's utility when covering day-to-day stories involving a particular contaminant or industry.

In our case we wanted data on cement plants. But we only wanted cement plants in eight states. And we only wanted to know how much mercury they emitted. What's more, we wanted to know exactly where each factory was located. Here's how you can do something similar:

Preparing the Spreadsheet Data 

Download spreadsheets of the most recent TRI data for the entire country or individual states here.

The files are zipped packages of seven spreadsheets. Windows computers should unzip the files automatically. Macintosh users will need a program like Stuffit Expander. Once you have access to all seven spreadsheets, use Excel to open the file with the number "1" in the file name. If Excel asks you any questions about how the file should be opened, just click "Next." The default options should work fine.

The spreadsheet you open will be big — dozens of columns wide and likely thousands of rows long. Each row is given a number, and each column is given a letter or set of letters.

You first need to limit your data to the facilities and chemicals you want to map. Click the Data menu at the top of the Excel window. Under the Filter option, pick AutoFilter. This will stick drop-down menus at the top of each column. Use these to filter by county or city. Navigate to the column called "CHEMICAL NAME" (column BC) and select a chemical from the drop-down list to pare the spreadsheet to a single pollutant.

Limit to a specific industry by filtering for its North American Industry Classification System (NAICS) code, a six-digit number that identifies what a facility does. Look for a column that says "PRIMARY NAICS CODE" (column AI). If you filter this column for the code 221112, for example, you'll only see power plants burning fossil fuels. Look up NAICS codes here.

Or you can find a facility in the spreadsheet that you know is in the appropriate industry. Find the six-digit code in the facility's NAICS column, and then find it in the drop-down menu at the top of the NAICS column.

Once you've limited the data to the facilities and chemicals you're after, create a new spreadsheet (File>New). This will be the spreadsheet that gets converted into a map file. It will be identified as the map sheet for the rest of this exercise.

In the map sheet, type into the top first five columns the words Latitude, Longitude, Name, Description, Icon. If you don't use these exact column headers, the map won't work. It should look like this:

Latitude and Longitude: Back on your TRI spreadsheet, highlight (click and drag with the mouse) and copy (CTRL+C, Command+C, or right click) the latitude data found in column AO. Paste (Ctrl+V, Command+V, or right click) the data into the first column of the map sheet, below the word Latitude. Do the same with the longitude data in column AP.

Name: Whatever goes here will be the big text at the top of the bubble that pops up when you click on a placemark in a Google map. Depending on your map, either the Facility Name (column J) or the Facility City (column L) are good choices. Whatever your choice, copy it from the TRI sheet to the map sheet.

Description: This is for the information bubble that pops up on the Google map. If you didn't include the facility name in the Name column, you'll want to get that in here. Depending on your mapping goals, this is also a good place for displaying how many pounds (the default measurement unit for TRI data) of a chemical a facility released. The TRI data contains air, land and water emissions, so you'll have to poke around to find what is most relevant to your story. In our cement case, we wanted total air emissions (column CT in the TRI sheet).

You could just copy and paste the raw numbers into this column, but it only takes a few extra steps and some pretty basic Excel maneuvering to come up with something that looks and communicates much better.

Copy the emissions numbers from the TRI spreadsheet into an unused column (F should work) on the map sheet, starting at the second row. Now go back to the Description column, and in the first empty cell type:

= "Reported "&F2&" pounds of mercury emissions in 2007."

Press enter. That's an Excel formula that will display the sentence "Reported X pounds of mercury emissions in 2007," but we'll replace X with the appropriate number. Be sure to leave a space after "Reported" and before "pounds." This formula uses F2 because that's the cell where the emissions data should start. If you put the emissions data somewhere else, just replace F2 with whatever cell your data starts in. Finally, if you're not tracking mercury emissions, be sure not to use the word 'mercury.' Replace it with the contaminant you are tracking.

Now, if you click the cell in which you just typed that formula, a black box should appear around the cell. Click and hold the bottom right corner of that box and drag it down the column until you've filled as many rows as you need. This should magically fill in the rest of the Description column with a sentence about emissions.

Icon: If you're fine with the default Google map location marker, which looks like an upside-down blue teardrop, leave this blank. If not, head to the Web site you'll be using to convert the spreadsheet into a map file, found here. At the very bottom of the page is a table of all the possible icons and a corresponding number. Pick an icon and plug its number into the Icon column. Again, drag the corner of the cell down the column to fill out the rest of the rows.

The spreadsheet work is done. Save it with a relevant name in a location you'll remember.

Make It a Map

Head to the Earth Point Excel to KML converter here. KML is a type of file used by mapping programs. Like Microsoft Word uses a .doc file to put words on your screen, Google Earth and Google maps use a .kml file to put maps on your screen. The Earth Point Excel to KML converter will make a .kml file out of the map sheet. You can then load the .kml file into a Google map. The Earth Point site lets you create an unlimited number of free maps with up to 200 locations. Want more locations? Set up a paid account. They're inexpensive.

To get started, click the "Browse" or "Choose File" button on the Earth Point Web site. Find where you saved your map sheet and click "Open." Now click "View in Google Earth." This will either open your new map in Google Earth, or give you the option of downloading the file. If it opens in Google Earth, click File, Save and Save Place As, and save it somewhere you remember.

Now go to Google Maps. If you don't already have a Google account (if you use Gmail, you do), you'll have to make one.

From the Google Maps site, click "My Maps," then "Create new map." Above the box where you enter the map's title, find the word "Import." Click it. A box that says "Import KML" will pop up. Click "Browse" or "Choose Files," locate your KML file, which is likely named something like EarthPointExcel.kml. Click "Upload from file."

In a few seconds, all of the coordinates from your spreadsheet should be plotted on your Google map. Each point is clickable and will contain the relevant info from the Description column. Be sure to replace the default Earth Point mumbo jumbo in the Title and Description boxes to the left.

Use the zoom bar on the left of the map and the Map, Satellite and Terrain buttons above the map to make everything look how you want it. The "Link" button on the top right corner of the map will give you an address that will send others to the map. Even better, "Customize and preview embedded map" will let you pick the dimensions and zoom level of a map that you can plunk down on any web page where you can use HTML.

David Poulson is the associate director and Jeff Gillies is a graduate assistant at the Knight Center for environmental Journalism at Michigan State University.

**From the quarterly newsletter SEJournal Fall 2009 issue.