Civic Innovations

Democratizing government data will help change how government operates—and give citizens the ability to participate in making government services more effective, accessible, and transparent.

— Peter Orszag, OMB Director

This post is a continuation in a series on building a transit data application using GTFS data recently released by the State of Delaware.

If you missed my first post, go back and check it out. You can get a MySQL database loaded up with all of the Delaware GTFS data in just a couple of minutes. Once you do that, you’ll be ready to follow along.

Continuing our work from the last post, in this post we’ll finish building out our database and set up an environment to run a web application – for the purposes of the demo app I’m building for this series, I’ll assume you have a standard LAMP set up to work with.

Finish the Database Setup

In the last post, we downloaded the GTFS data from the State of Delaware, unzipped it and loaded it into a MySQL database. Now, we need to set up some stored procedures so that we can extract data from our MySQL database and present it to an end user.

You can see the stored procedures I created for this demo application on GitHub. To load them into our shiny new database, simply run:

  ~$ wget http://gist.github.com/raw/632306/
	    9860651ba2a61cd5af1c18529cdbab5f8c6f8e97/dartfirststate_de_us_procs.sql 

  ~$ mysql -u user_name -p transitdata < dartfirststate_de_us_procs.sql

Thats it!

If you look at these procedures, you’ll see that they are set up to answer two different questions from users. The first one – getDepartureTimesAndRoutesByStopID – will query our database and get a set of routes and departure times by the ID of a transit stop. The other – GetClosestStopsByLocation – accepts a lat/lon and returns the stop ID and name of the transit stops closest the the requesting location.

In practice, you can see these two procedures working in tandem – the later procedure would be used by someone wishing to find the transit stop closest to their present location. The former would provide information on the next buses to reach that stop, the routes they serve and the scheduled departure time from that location.

There are certainly many more potential queries that could be used to extract valuable information from the GTFS data in our database, but these two should suffice for our demo application. Also, both are pretty well suited for use from a text messaging (SMS) application, which is what we’ll build in the last post in this series.

Setting up the Application Environment

I assume for this series of posts that you have access to a LAMP server. This server should be hosted somewhere where it can receive HTTP posts from a third party platform (this is required in order to build an SMS application).

While it is not a requirement that you code your transit application in PHP, I will do so in this series. Feel free to use the development language of your choice in building your own application – just about every web development language can work with MySQL.

Before we start writing code, lets finish a few last items. First, lets create a user for our web application – remember to give this user only the privileges they need. For our demo application, the web app user only needs to EXECUTE stored procedures. So, we want to do this at the MySQL shell:

mysql> GRANT EXECUTE ON transitdata.* TO username@'localhost' IDENTIFIED BY 'password'; 

mysql> FLUSH PRIVILEGES;

Be sure to replace the ‘username’ and ‘password’ above with values of your choosing. Now, let’s put our database access credentials in a safe and convenient place.

When writing a web application, I prefer not to store this inforamtion in my code (as a config item or declared constant). Instead, I like to keep this information in my Apache configuration.

If you’re using Apache on Ubuntu, you can typically just store this inforamtion in your VirtualHost file (located in /etc/apache2/sites-available/). Use the Apache SetEnv directive to set the values you want to store:

SetEnv TRANSIT_DB_HOST localhost
SetEnv TRANSIT_DB_USER username
SetEnv TRANSIT_DB_PASS password
SetEnv TRANSIT_DB_NAME transitdata
SetEnv TRANSIT_DB_PORT 3306

Again, be sure to replace the ‘username’ and ‘password’ above with the values used when creating your MySQL user. Once you have entered these values into your VirtualHost file, save it and reload Apache:

 ~$ sudo /etc/init.d/apache2 reload

Now we’re all set to start writing code!

In the next post we’ll build a simple, yet powerful PHP-based SMS application that anyone with a cell phone can use to find a transit location nearest to them in the State of Delaware, and find out the departure times / routes from that location.

Stay tuned!