This website is an initiative of J-Lab

Searchable Databases

There are many kinds of community data available. Much of it exists in electronic format. With a little work, you can turn these into interesting interactive tools and news exercises for your readers.

Let's pick an easy searchable database, such as gas prices.

It's easy to build with free tools, everyone needs gas, the data can be gathered easily by local site readers, and there are several good examples online you can learn from. 

Our primary example will be the Middletown, N.Y., Times Herald-Record Online's "Record Gas Watch" page.

Sign Up for Google Maps

Basically, a map is a good way for your users to interact with a database of local gas prices. Luckily, there's a powerful mapping solution you can integrate into your site for no cost.  Google Maps has an open API that lets you put a Google Map on your Web site.  All you need to do is sign up for a Google developer key, which is free.

What's an API?

API stands for "application program interface."  It means that there's an established, documented way to "talk" to Google's map tools and access its functions. A good API makes it easier to develop a program by providing standard, well-understood ways to communicate and control an application.

 

What's a developer key?

Some Web applications will only allow you to use their API if you have a developer key - basically a password that keeps track of your usage and ensures that you have permission to be using the service in the way you're attempting.

There are no significant restrictions on the basic key Google gives to developers, except that you need to get special permission if you expect to have more than 15,000 geocodes (map interactions) per day, and your service must be freely accessible to end users. Sign up here.

Create a Data File

Now, you need a data file with some gas station information in it: Specifically, where the gas station is, what it's called, how much the gas price is there, and any notes about the station. (Don't worry about where you'll get the data for now. We'll cover filling it in later in this section.) Most importantly, it needs to have the longitude and latitude that would locate various gas stations in your region. If you have the station addresses you can get the longitude and latitude.

The format of the data file is easy to understand. It's in XML, a type of flexible HTML that is perfect for situations like this where you have data that needs to be displayed online in a format that is easily managed by a Web browser.  XML - which stands for eXtensible Markup Language - looks a lot like HTML. The big difference is you must never use bare quote marks or ampersand signs ("&").

The format of the data file required by Google Maps starts with a main tag, usually called the container tag, and a series of item tags with the data for each station:

<listing>
<item name="Fred Fuel" note="Updated: September 2" lat="41.467106733215836" lng="-74.41361904144287" >$3.119 - Mobil</item>
</listing>

In this file, "listing" is the main container tag, "item" is the tag for each station, "name" is the name of the station, "Note" is a field with general notes about the station, including when the station's price was updated.  "Lat" and "Lng" are the latitude and longitude - the east/west and north/south coordinates of the station - and content of the "item" tag is the price.

Creating this data file by hand is a simple job.  It can be done by hand where you just type each new station into the file in a text editor, or the file can be created by storing the incoming gas station data in any local database you have and then creating a "report" or output file with the gas station data. (For details on how to create a database file, see our section on databases.)

Where In the World is-?

Getting latitude and longitude of locations in your community is pretty easy. There are several free or cheap services that convert street addresses to longitude/latitude coordinates. Non-commercial folks can use http://geocoder.us/ for free, while commercial operations pay $50 for 20,000 lookups.

 

Fill 'Er Up

Where do you get the data from? One way is to create a link on your Web page asking your readers to volunteer the data.  Have your staff participate, too. Information from this form can populate the database immediately or can just go into an e-mail box that you check regularly. Make sure to differentiate between updates and new listings so that you can be sure your information is current.

Many government agencies will make data available to you - often times in an awkward or outdated format. You may be asked to receive the files on an FTP site (in other words, they'll use FTP to deliver the data). Sometimes, you may be asked to pick up a CD with the information. It's possible that the more up-to-date departments will be able to give you a Web feed in some sort of XML format. The key is always to have a document that explicitly explains how the data is formatted so that you don't end up making any mistaken assumptions about how the data should be used. It's embarrassing to assume that a field contains last names when really it contains maiden names, for example.

When it comes to reading the data, you can encounter difficulties at first. Perhaps it's in a format that Excel can handle and you can just open it like any regular Excel file.  Other times, it'll be in a database-ready format, like a SQL backup [link to database section]. More likely, it'll just be a bunch of plain text or Word or PDF documents that don't have the data structured in a very easily managed format. If this is the case, you need to think seriously about how much effort it will take to get the data into a consistent format, and keep it updated.

Find a Buddy

A popular site for gas prices is GasBuddy.com. You might be able to work out an arrangement with them to display their data.

 

Stick It Together

Once you have your data file and a developer key, you need to put together a Web page that has:

  1. The map, integrated into the layout.
  2. The proper JavaScript code to display the map (Google's documentation on this is excellent).
  3. A clear, simple explanation of what the map is
  4. A way for your users to give you feedback. For gas prices, a form that asks for more station prices would be a good idea. But even if your data is not the kind that benefits from community updates, a general feedback form right on the page of the database will ensure that you get suggestions, complaints and updates from helpful readers.

The Google Map API allows you to do all sorts of customization, including selecting the size and shapes of markers, what controls appear on the map, what text appears in the marker when you click on it, and more.  With such flexibility comes, of course, some learning necessary to take complete advantage of it.

For example, it would be necessary to customize the JavaScript code to properly read and display the data you've included in the data file.  The JavaScript code for the page needs to specify how each part of the XML file should be displayed on the final map.

Other Formats

In the case of the gas station data, it might also be nice to create a list of the 10 cheapest stations. To do this in addition to the map, it would be better to keep the data in a database program and display the data in real time in response to user requests.  If you then also wanted to keep historical data, such as an XML file for each day's prices, you could make that happen fairly quickly by creating a database query that showed, for instance, the most expensive stations for each of the preceding 30 days.

In general, by making available to the community "raw" data in a structured, user-friendly format, you give readers more than just written stories and your own analysis.  You give them the opportunity to study and learn from the source materials, and in a best-case scenario, they can even communicate back to you and each other through your site.

> READ NEXT ARTICLE: Getting Familiar with SWiSHmax