Tutorials

Porting Data from SQL to Maps

A introduction to moving around SQL and exporting the data to a visualization.

Assignment

Due: Tuesday, October 13
Points: 5

Deliverables

Create a Google document (i.e. like any other word document) in your PADJO2015 folder named: PADJO2015-SFPD-SQL-TO-MAP

In this document, simply:

  1. A short paragraph explaining to me what is going on in the map you made.

  2. Copy and paste the SQL code you used to filter the SFPD crime dataset I gave you.

  3. Include a link to your online map visualization

  4. Include a screenshot of the online map visualization

Requirements

  • Download this zipped sqlite database of SFPD incident reports (2012-2013) and open it in SQLite browser: sfpd_incident_reports_2012_2013.sqlite.zip

  • The descriptive paragraph just needs to describe what the map purports to show.

  • The SQL statement you use should look fairly similar to the one in the tutorial, but try to filter on some other condition at least. And you’ll also need to include the two columns for geo-coordinate data.

  • You can use whatever mapping solution you like, though I recommend either: CartoDB, Google MyMaps, or Fusion Tables. Check the tutorial here for more info

This exercise is simply to make sure you can get the SQLite Database Browser installed.

Download the SPFD dataset for 2012 and 2013.

Do a SQL query statement similar to this:

SELECT Category, Descript, Resolution, Date, Time
  FROM sfpd_incidents 
  WHERE Time > "18:00"

Export the data and make into a map, similar to this:

References

A quick how-to on just getting started with the SQLite Browser

Online visualization tools make it easy to create a map from a spreadsheet. Here’s a brief overview of current tools.