Exploring Code for America’s Open311 Data Set with the new Dataclips

Heroku Dataclips enable you to create SQL queries for your Heroku Postgres databases and share the results with your team, third-party tools, and with the world. The recent release of the new Dataclips introduces a number of new features, including a schema explorer, editor autocomplete, chart visualizations, and more options for sharing your queries. Let’s look at some of the new features by exploring some really interesting data from Code for America’s Open311 data set.

Introduction to Open311

People call their city’s 311 number to make municipal service requests and report community issues. Many cities also offer mobile apps to report issues along with photos and location data. Take a look at the report below from a San Francisco resident asking the city to clean up multiple cardboard boxes left on the street. What interesting things can this data tell us about our city or others?

Cities share this data online using the Open311 open standard. Code for America’s Open311 Status service aggregates Open311 data from cities around the world and displays it on a public web page. This allows you to see requests from places like San Francisco, Chicago, and more. It also shows a chart with the number of requests filed over time by residents in each city. Let’s see what interesting things we can learn about these service requests using Dataclips.

The Open311 Status page, available at https://status.open311.org

Exploring the Open311 Data with Dataclips

When we open Dataclips and select the database for the Open311 Status app, we then see a screen allowing us to pick a datastore, the query editor and a Schema Explorer.

The Schema Explorer makes it easy to see what data is available in the form of tables, columns, and column data types, even if we are not familiar with the application. This is very useful when you are exploring data from an unfamiliar application or team.

We can see there is a table called service_requests. Let’s use Dataclips to query it to get a feel for the data inside. From the Schema Explorer, we can see when requests were made in the requested_datetime column, what city they came from in city_id, and more. As I type, I see editor autocomplete suggestions which help me write my query.

Analyzing the Open311 Data

Now let’s analyze the total number of requests over time for the entire Open311 dataset. We’ll calculate the total request count for each week since the start of the data set, and thanks to the new charting functionality, we can see trends and outliers. For example, we can see clear periods where the volume increases, possibly due to the addition of new cities to the data set:

Overall volume of Open311 requests by date

We can easily share this data with our coworkers in a secure way by inviting them by email or by team. We can also share our Dataclip publicly.

Next, let’s dive deeper into what caused that big spike in reports around the middle of the year. We’ll modify our query to group by city name.

It may be helpful to analyze this data using familiar tools like a spreadsheet. Thankfully, Dataclips also allows us to import the data into Google Sheets, or your favorite BI tool as CSV or JSON data.

Let’s import the data into Google Sheets so that we can plot the number of requests made by each city. We’ll create a pivot table on the city and then plot a stacked area chart. Below, we can see that the spike around the middle of the year was due to a large increase in reports primarily from Boston (in purple) and Ottawa (in pink). This is an easy way to make data accessible to management, finance, or business operations teams.

Conclusion

Open311 Status is just one example of a database you can explore and share using Dataclips. Dataclips can be used with any of your Heroku Postgres databases. This takes the pressure off your development and business operations teams to build custom analytics tools and write custom code alongside or in your app to answer business and product questions. Instead, they can just share a dataclip allowing teammates to access it in the web UI or import the data into their favorite tool for further analysis.

Now that you’ve seen what the new Dataclips are capable of, you can start using it with your own databases. Head over to https://data.heroku.com/dataclips, pick your favorite Heroku Postgres database, and create a new dataclip in just a few seconds.

If you’re interested in contributing to Open311 Status check out the project on GitHub or find your local Code for America Brigade where community organizers, developers, and designers put technology to work in service to their local communities.

In the coming weeks, we’ll take a deeper look at data from the Open311 project and explore what it can tell us about cities around the world.