Steps to import Philly311 data into an SQLite DB to enable SQL queries

philly311-sqlite.md

Here are some steps to bring the bulk Philly311 CSV file into a local SQLite database to enable SQL queries.

Tools you'll use

• sqlite3 (installed by default on OS X - try typing in ~$ which sqlite3 at a command prompt).
• csvsql (follow installation instructions here if not already installed).
• SQLite Manager for Firefox.

Steps

Download the bulk CSV file for Philly311 requests. Note, there is a bug in the standard CSV download link. Use the alternate method described here.

Create a new sqlite table for the data:

~$ head -n 1000 311_Requests.csv | csvsql -i sqlite --tables requests | pbcopy
~$ sqlite3 philly311

Paste the table creation SQL code into the sqlite command prompt (cmd + v):

sqlite> CREATE TABLE requests (
  ...>	address VARCHAR(32), 
  ...>	agency_responsible VARCHAR(62), 
  ...>	expected_datetime DATETIME NOT NULL, 
  ...>	lat FLOAT, 
  ...>	lon FLOAT, 
  ...>	media_url VARCHAR(84), 
  ...>	requested_datetime DATETIME NOT NULL, 
  ...>	service_code VARCHAR(7), 
  ...>	service_name VARCHAR(38) NOT NULL, 
  ...>	service_notice VARCHAR(16), 
  ...>	service_request_id INTEGER NOT NULL, 
  ...>	shape VARCHAR(28), 
  ...>	status VARCHAR(6) NOT NULL, 
  ...>	updated_datetime DATETIME NOT NULL, 
  ...>	zipcode VARCHAR(10)
  ...>);

sqlite> .table
requests 

Note - you may also use csvsql to insert data from a csv directly into a database if preferred. See the docs for more info. if you want to use this method.

Execute the following commands to import data from the downloaded CSV to sqlite:

sqlite> .mode csv
sqlite> .import 311_Requests.csv requests
sqlite> .quit

Open Firefox and launch SQLite Manager. Open the philly311 database.

Now you can run SQL queries, like the following which shows average number of days to resolve a request for all closed requests by request category (see results below):

SELECT service_name, sum(1) as "num_requests", round(avg(julianday(updated_datetime) - julianday(requested_datetime)),2) 
AS 'resolved_time' from requests 
WHERE status = 'Closed' 
GROUP BY service_name 
ORDER BY resolved_time DESC; 

results.csv