Making a Data Portal With WordPress

I’ve been toying with the idea of making an open data portal for Mecklenburg, ala DataSF or Vancouver, for a while now, but I didn’t get around to starting one until last month. It’s pretty much done now, and you can see it here.

I say pretty much done because if you tried to make a comment right now our security device would likely give you a unhelpful 404. We have a security device tuned so tight it would report Gandhi to homeland security, and I haven’t gotten up with our security guys to tone it down yet. But it works enough to get the general idea.

I knew GeoServer would be the back end, because…well, it’s GeoServer. But I vacillated on what I’d use for the front end. I spend almost as much thumb twiddling time on that as I did making the whole thing.

I dismissed SharePoint early on. I don’t like SharePoint. SharePoint is an OK document management platform with aspirations. The selling point is it does just about everything. The problem is it does everything badly.

I thought I might take a stab at Drupal, which I’ve been threatening to do for ages. I saw a good Drupal talk at SELF that peaked my interest again. It’s powerful and complicated stuff. If I were to do a County-wide CMS today I would eat the learning curve and use Drupal. Using it for this project would be like squirrel hunting with a bazooka.

DataSF uses a modified version Pligg, which is a social publishing/rating site, ala Digg. It’s advertised as a “social networking CMS”, and I wasn’t looking to build one of those.

I finally fell back to trusty old WordPress. Most of what I wanted is just how WordPress works – posts, categories, RSS feeds, etc. But there were a few things I wanted to do that were a bit out of the ordinary (at least for how I usually use WordPress):

• I wanted a rating system, ala DataSF.
• I wanted a catalog page, ala Vancouver. And I didn’t want to have to maintain that by hand.
• I wanted consistently formatted data and API links stored outside the normal post content.

All that turned out to be pretty easy. Here’s how I did it.

First, pick a WordPress theme you like. I’ve made my own WordPress themes before for the experience of it, but unless you’re a hardcore designer you’re probably better off grabbing a free one. I rather like Arjuna X, so I used it. When you use free themes like this, just make sure you leave a link back to the designer in the footer.

Now for the easy part – ratings. I liked the way DataSF let users rate data (note to self: may not like it so much after users actually rate the data). There are a number of WordPress plugins for this, but I went with GD Star Rating. It’s highly customizable and did exactly what I needed it to. Done.

Next I wanted a standard way to enter and present links to a WMS service or to a Geo API service or what have you. I looked at a number of options, but in the end I decided to do WordPress Custom Fields. It means a little more work for each layer, but there could be so much variability in each data feed it seems like the most logical approach. And most of the work is of the copy-paste-tweak variety.

So each post has its own custom fields for data links. Perfect.

Next I had to show those custom fields with the post. First you need to plan on editing any place a post might appear if you want the meta tags to appear there as well. Depending on your theme and what you want to do you’ll need to edit single.php at a minimum, but you may also want to edit index.php, archive.php archives.php, category.php, and search.php.

Basically you’re looking through what in WordPress parlance is “the loop”, and you’re specifically looking for the spot where the post content is dumped. It’ll be something like this:

<?php the_content(); ?>

Then you’ll want to add the a function called the_meta, either above or below the post content. I stuck mine at the top.

<?php the_meta(); ?>
<?php the_content(__('continue reading...', 'Arjuna')); ?>

The the_meta() function outputs the custom fields like this:

<ul class='post-meta'>
<li><span class='post-meta-key'>Curently Reading:</span> Calvin and Hobbes</li>
<li><span class='post-meta-key'>Today's Mood:</span> Jolly and Happy</li>
</ul>

Which won’t look very good unless you style it up a bit. I did something like this:

.postContent .post-meta  { width: 500px;  box-shadow: 3px 3px 5px #666;  -webkit-box-shadow: 3px 3px 5px #666;  -moz-box-shadow: 3px 3px 5px #666;  margin: 0 0 35px  0; }
.postContent .post-meta  li {  list-style: none;   margin-left: 0px; padding: 0;  border-style: outset;  border-width: 1px;  font-size: 14px; }
.post-meta-key { padding-left: 4px; margin-right: 4px;  display: block;  float: left; width: 150px; font-weight: bold; background: #fff }

Which gets you a much nicer table-looking presentation for the unordered list:

So there we have links to data feeds and ratings. Now I wanted a catalog page.

I created a new page template for the data catalog by coping another page template. To make a new page template in WordPress, you just make a PHP file with the template name at the top and it will show up in the page template list in wp-admin:

<?php
/*
Template Name: Data Catalog
*/
?>

I made a new page in wp-admin called Catalog using this template and left the page content empty. Then I snuck into the template page and did this where the the_content() would normally sit:

<!-- Catalog Table -->
<table style="width: 100%">
	<tr>
		<th>Dataset</th>
		<th>Category</th>
	</tr>
	<?php
		// SQL Call
		$sql = "select p.post_title, p.post_name, wp_terms.name as category
					from wp_terms
					inner join wp_term_taxonomy on wp_terms.term_id =
					wp_term_taxonomy.term_id
					inner join wp_term_relationships wpr on wpr.term_taxonomy_id =
					wp_term_taxonomy.term_taxonomy_id
					inner join wp_posts p on p.ID = wpr.object_id
					where taxonomy= 'category' and p.post_type = 'post' and p.post_status = 'publish'
					and wp_terms.name <> 'Blog'
					order by p.post_title";

		$results = $wpdb->get_results($sql, OBJECT);

		// Build table
		foreach ($results as $post):
			echo "<tr>";
			echo '<td><a href="/dataportal/' .   $post->post_name . '">' . $post->post_title . '</a></td>';
			echo '<td><a href="/dataportal/category/' . $post->category . '">' . $post->category . '</a></td>';
			echo "</tr>";
		endforeach;
	?>
</table>

The ugly bit of SQL is getting the post name, post title, and category for every post that isn’t the the category ‘Blog’ (in case I make a general blog post that I don’t want showing up in the catalog). The result is this:

And that’s it. Once I get our security device tuned I’ll take the Beta moniker off and start adding a new layer or API every week. Viva la open data revolucion and all that nonsense.

Is there nothing WordPress can’t do?

*Note once you start editing a theme like this, you’ve officially left the reservation. You won’t want to automatically pull up dates to the theme from WordPress, as they could overwrite your changes.