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.

Stumped for what to develop? Here are a few suggestions:

• Police crime data. Using police crime reports, develop a database that maps local crimes by type, address, date or location, such as an ATM machine. This prize-winning example, http://www.chicagocrime.org/, also locates reported crimes on Google maps. This database takes advantage of Chicago’s Citizen ICAM, which allows the public to query the Chicago Police Department's database of reported crimes.  But the same data is available through the police department’s Records Division.
• School test results. Build a database of local school test results – or link to one already created by your local school district. Encourage parents to look up their local schools and post comments or links. School "report cards" from the No Child Left Behind Act could be used as a source. Here’s an example from the Seattle School District.
• Local election returns. There could be an entire site devoted to this topic, but one very basic tool you can create is this: Ask candidates their position on a list of 10-15 issues.  Create a database.  Ask your readers to choose their position on each issue, then match them to the candidate advocating that position. See an example.
• Local sports team data. Again, sporting information is an enormous opportunity. You could offer schedules for local league games. Here’s an example from the Lawrence (Kansas) Journal World. Or you could allow users to nominate outstanding players for interactive baseball cards.
• Housing prices. A database of prices for recent homes would be of interest both to those about to buy and those about to sell a home.  House sales are usually accessible at the county level in a database format. Tied to a map, or presented in a searchable way, this could be a very popular feature. Example: http://www.azcentral.com/class/marketplace/homes.php 
• Urban Development. Consider mapping forthcoming building projects in your community with a clickable map that gives more details on each project. Here’s an example, showing a building boom that will remake Miami’s skyline.
• Gas prices. A database of best prices at local gas stations would deliver a useful service. See below for more on this...

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. Another interesting reference is the News and Observer of Raleigh, N.C., which has embedded a low-price gas map on its front page, which you may want to examine for comparison's sake.

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.

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 50,000 page views 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.)

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.

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

Be the first to comment on this page. You must register before you can leave a comment.

You must be registered (free) in order to post a comment.