Note: this essay was originally posted at the Smart Chicago Collaborative blog as part of my work as Civic Innovation Program Manager.
Chicago Works For You (CWFY) is comprised of three components: a AngularJS/Jekyll frontend, a custom Go API and worker layer, and a PostgreSQL/PostGIS database. This post will dive into the mechanics of the system and explain why it works the way it does.
CWFY started with only a handful of requirements. It had to pull service requests from Chicago's Open311 API, save them to a database, do some math, and expose the results to a pretty user interface.
The first sketch of the system was pretty rudimentary, but it matches very closely the end result. We implemented the worker and API in Go, a powerful and fun language that is well-suited for this type of work. We are hosting the Go applications and database on a m1.medium EC2 instance on the Smart Chicago Apps infrastructure.
The worker application finds the timestamp of the most recently updated service request in the CWFY database and asks the Open311 API endpoint for all service requests created or updated since that point. This repeats every thirty seconds. That interval is short enough that we're not clobbering the Open311 API, the number of requests in the response is small (usually less than a dozen), and we still have near-real-time data on our site. We've found that the Open311 API is very reliable — and fast — and can stand sustained load, as it did when we backfilled our database.
The database schema is very simple. There is a single service_requests table with more than three million rows. That's all service data stretching back to January 2008. This table is very similar to the service_requests schema used by Code for America's 311.fm application. In fact, we used that schema to bootstrap this application.
There are two tables of ward boundary data, one for the current ward boundaries, and one for the new ward boundaries, to be instated in 2015. But the city's service request records only include the ward the request is currently in. Using the popular PostGIS PostgreSQL extension, we're able to use the latitude and longitude associated with each service request to geo-locate it within its 2015 ward-to-be and determine if it's in one the 233 areas of the city that are changing from one ward to another. These calculations allow CWFY to compute service delivery information for current and future wards, as well as individual transition areas.
Finally, there's a daily_counts table, populated with counts of service requests opened for each day, ward, and service type combination. This table is populated automatically by a Postgres trigger, and allows CWFY to perform quick aggregate calculations.
The site is hosted in a Amazon Web Service S3 bucket, making it very inexpensive to host, highly available, and simple to update.
We relied heavily on third-party libraries to speed our development. We acknowledge the following and offer our thanks:
- Leaflet, Cloudmade, and OpenStreetMap: powering our beautiful maps
- Gorilla: Golang tools
- SCSS: the only way to write CSS
- PostgreSQL and PostGIS: simple data munging
- lib/pq: Golang + PostgreSQL
- CapistranoRb: simple deployments
- Jekyll: smart static-site creation
- Jim Pravetz: Jekyll page generator
- AngularJS: easy dynamic webapps
- Bootstrap: simple grid framework
- Highcharts: beautiful charts and graphs