Working with Land and Water Conservation Fund data

Earlier this week, my colleagues at InvestigateWest published ‘Parks for Sale,’ a story about the Land and Water Conservation Fund. One of the core issues is that the National Park Service hasn’t kept good track of what parks are federally protected. Even the database of grants on their website is a pain to access and work with. So we tackled the question of how to make it not just public, but also useful for readers.

So how do publish a list of 42,000 or so grants in the most useful way?

The Problem

Maps! Sounds good. These are parks, which are located somewhere. And readers will likely look for parks located nearby. The problem is that the geographic information in each record is poor-to-non-existant. For example:

PORTAGE ISLAND ACQUISITION	WHATCOM COUNTY & LUMMI BUSINESS COUNCIL	WHATCOM	WA	$189,901.33	1966	1967	Acquisition

There’s a place called Portage Island, but try geocoding “Portage Island Acquisition” and that “Acquisition” is going to cause problems. Lumni refers to the Lumni Nation, a Native American tribe. We do know it’s in Whatcom County, Washington.

There is so much variation between states that even looking for patterns in the data with Grep and a text editor, we couldn’t clean it up in a way that made it easily mappable.

False Starts

Not that we didn’t try. We did a demo with just the Washington State records in Google Fusion Tables. And after fixing a dozen or so obvious errors — pins dropped in South Africa or Eastern Europe — it looked pretty good.

But replicate that with 42,000 records and it starts to miss a lot more often than it hits. And in many cases, the best-case scenario was hitting at the city level. What we were saying with the story, essentially, was “These places are protected under the law,” and pointing to a city center, we decided, was benign but at some level wrong.

We also tried using Crowdmap. And if it had worked I think we still might have gone ahead with mapping, even at that level of imprecision. Crowdmap (and its parent, Ushahidi) was built to collect reports from the public. We wanted to use it differently. We wanted to upload the reports ourselves, and invite the public to comment. But a bug in the bulk uploader limited upload to about 10 records at a time.

Two Solutions

We ended up using two tools — for two different situations — that I’ll talk about here.¬†First, msnbc.com’s OpenChannel blog published the feature story. And thanks to the work of Bill Dedman, they also published a database of every $100,000+ grant using freeDive.

In general, it worked pretty well. You can see the outcome here.

The second tool was a hand-rolled layout using CSS, HTML and jQuery. Every state got its own page, and our intention is that these can be long-standing resources for the public to search. I hope we can update it every six or twelves months with new grants.

The first script we used is tablesorter.js. Aside from calling the script, the code looks like this:

<script type="text/javascript">
  $(function() {
  $("#grant-table").tablesorter({sortList:[[6,0],[3,0]]});
  });
</script>

That sorts the HTML table by column #6, then column #3. Both columns are assorted in ascending (0), rather than descending (1), order. Tablesorter has a particularly nice built-in UI. Clicking on column headers re-sorts them. Shift-click adds a second sort criteria.

The only modification to a standard HTML table (thanks for turning my Excel doc into HTML, Tableizer!) that tablesorter requires is <thead> and <tbody> elements. Add a #grant-table id and .tablesorter class.

I also wanted a filter. The NPS database isn’t searchable, and that’s essential when some states have more than 1,000 records. After testing a couple options, I picked Greg Weber’s uiTableFilter.js.

I edited the FlavorZoom example to point to the same table as tablesorter:

$(function() { 
  var theTable = $('table.tablesorter')

  theTable.find("tbody > tr").find("td:eq(1)").mousedown(function(){
    $(this).prev().find(":checkbox").click()
  });

  $("#filter").keyup(function() {
    $.uiTableFilter( theTable, this.value );
  })

  $('#filter-form').submit(function(){
    theTable.find("tbody > tr:visible > td:eq(1)").mousedown();
    return false;
  }).focus(); //Give focus to input field
});

Reference the scripts in the head of the page, and you have a sortable, filterable table.¬†(For states with the most records, like California, the filtering can hang for several seconds. I wouldn’t use this for more than a couple thousand rows.)

The Final Product

An overview of the database, with links to tables for all 50 states. Here a direct link to Land and Water Conservation Fund grants in Washington state.

Leave a Reply

Your email address will not be published. Required fields are marked *