Check out five federal databases that you can download for free, pull into Microsoft Excel, and analyze to identify trends and problems in your community. Start with these easily obtainable databases and then use your FOI skills to request more public data specific to your beat.
TRAIN WRECKS
See Safetydata.fra.dot.gov/OfficeofSafety
Find the most dangerous train crossings in your community by looking at Federal Railroad Administration data going back to 1975. Click on the “Download Accident Data” link and under the “Table name” pull-down menu, choose “Railroad Casualties.” Then choose a year, your state and a format, such as Excel, then click “Generate File.” Sort by county, city and highway to identify trouble spots (find your state in the STCNTY field). Check out the “TOTKLD” column for fatalities, and the “GXID” column to identify specific crossings. Download the past five years to spot trends.
AGRICULTURAL TRENDS
See Nass.usda.gov/Data_and_Statistics/index.asp
The U.S. Department of Agriculture provides statistics about all sorts of agricultural commodities by state, county or ZIP code. You can look at trends going back to 1997 in farm production to identify what is on the rise and decline in your area. The data retrieval site is a little complicated and clunky, but with a little experimentation you should be able to get what you need.
DETERIORATING BRIDGES
See Fhwa.dot.gov/BRIDGE/britab.cfm
You’d be surprised how many bridges, including overpasses, are falling apart. Click on “Download NBI ASCII files,” then “Recording and Coding Guide” to save a PDF to your desktop. You’ll need this because the data are heavily coded. Go back and click on “Record Format” and then File “Save As.” Save this Web page to the desktop. Click on “2012 Data” then your state, then “Proceed to data.” Go to File and then Save As a text file to your desktop. Open Excel. Go to File “Open” and look on the desktop for the file. Make sure Excel is looking for “all files,” not just Excel files. Import as a fixed file. Refer to the record layout in the PDF to set up the columns (the most tedious part of this, as well as understanding the fields). Key fields to look at include latitude/longitude (you can map out with Google Fusion Tables), deck rating, superstructure rating and substructure rating.
NURSING HOME INSPECTIONS
See Medicare.gov/Download/DownloadDB.asp
Under “Which database would you like to download?” choose “Nursing Home Compare – About the Nursing Home Inspection Results.” Save as a CSV file and pull into Excel. The database contains two main files, one for complaints (CMPLNT_DFCNCY) and the other for inspections (SRVY_DFCNCY). Pull out your state and analyze to find the nursing homes in your area with the biggest problems.
TOXIC RELEASES
See Epa.gov/tri/tridata/index.html
The EPA’s Toxic Release Inventory contains more than 650 toxic chemicals released in communities. They even launched the “myRight-To-Know” app to identify toxic releases from a smartphone. Browse your town online or download detailed data by going to “TRI Form R & A Download.” Call up data by town name, state or ZIP code and export as a CSV file to pull into Excel. It will take a little reporting to figure out what is meaningful, but you — and your readers/viewers — will be more informed about the nasties released around you!
