The First Amendment is under attack. Fight back with us. Visit fight.spj.org to find out how.

Member Login | Join SPJ | Benefits | Rates

> Latest News, Blogs and Events (tap to expand)


Advertisement
— ADVERTISEMENT —
Advertise with SPJ
1

News and More
Click to Expand Instantly

SPJ News
Events and Deadlines
SPJ Blogs
Quill Online
Journalist's Toolbox

— ADVERTISEMENT —


Stay in Touch
Twitter Storify Facebook Google Plus
RSS Pinterest Pinterest Flickr



Current Issue
Browse Archive
About Quill
Advertising Info
Back Issue Request
Reprint Permission Form
Pulliam/Kilgore Internship Info

Search Quill


Publications
SPJ Blogs
Quill
SPJ Leads
The EIJ News
Press Notes
SPJ News
Open Doors
Geneva Conventions
Annual FOI Reports

Home > Publications > Quill > FOI Toolbox



Current Issue | Browse Archive | About Quill | Advertising Info
Back Issues | Reprint Permission Form | Pulliam/Kilgore Internship Info

Search Quill


Wednesday, April 10, 2013
FOI Toolbox

Download government databases for free

By David Cuillier

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!

Stay in Touch
Twitter Storify Facebook Google Plus RSS Pinterest Pinterest
Flickr LinkedIn Tout



Current Issue
Browse Archive
About Quill
Advertising Info
Back Issue Request
Reprint Permission Form
Pulliam/Kilgore Internship Info

Search Quill


Publications
SPJ Blogs
Quill
SPJ Leads
The EIJ News
Press Notes
SPJ News
Open Doors
Geneva Conventions
Annual FOI Reports
 

Copyright © 1996-2017 Society of Professional Journalists. All Rights Reserved.

Legal | Policies

Society of Professional Journalists
Eugene S. Pulliam National Journalism Center
3909 N. Meridian St.
Indianapolis, IN 46208
317/927-8000 | Fax: 317/920-4789

Contact SPJ Headquarters
Employment Opportunities
Advertise with SPJ