San Francisco Graffiti and Police Incidents Data Mashup
San Francisco Graffiti and Police Incidents Data Mashup

The open data sets previously featured at Open Data Bits were both from the city of San Francisco, and they both contained latitude and longitude coordinates.  It is only the next logical step to mash-up the SFPD Reported Incidents and San Francisco Graffiti Reports data sets so that the geographical distribution of both data sets can be viewed on a map with dynamic parameters.

UPDATE: This data set has a new documentation page that is designed to provide a step-by-step guide for integration into a business intelligence solution.  Click here to visit that new page for SFPD Incidents, and click here for the new page about San Francisco Graffiti Reports.

For details and guidance about accessing and modeling the SFPD Reported Incidents and San Francisco Graffiti Reports open data sets please refer to the following posts and pages on this site:

San Francisco Graffiti Reports Open Data

San Francisco Police Department Reported Incidents Open Data

Open Data Bits Sources and Modeling

Open Data Bits Logical Model

Mashing Up the Data Sets

Both the San Francisco Graffiti Reports and SFPD Reported Incidents data are at the granularity of a single reported incident per row of data.  Although the two data sets are different types of events, displaying a count of each event on a map can yield an interesting geographical data visualization.  For the data mashup, Open Data Bits performed the equivalent of a SQL “Union All” statement using Power Query for Excel 2013.  Various business intelligence tools will have different methods to union tables together, but the following columns from both tables can be used to reproduce the visualizations for this feature:

SF Graffiti SFPD visualization 1


The conformed Open Data Bits Date and Time dimensions can also be used with the mashed-up data table in order to provide hierarchical rollups in the reports.

Visualizing the Data

There are numerous business intelligence tools that can be used to visualize the data mashup. For the example below, the solution was built in Power Pivot and is displayed using Power View for Excel 2013.

The map below contains slicers for [Week Year], [Date], [Category], and [Description].  The [Week Year] is selected for the fourth week of 2013.  Any of these slicers can be used to dynamically change the data that is displayed on the map.  The map contains blue circles representing a count of graffiti reports, and red circles for SFPD incidents.  Notice the warning at the top of the map stating that there are too many data points to visualize on the map simultaneously:

SF Graffiti SFPD visualization 2

Selecting the [Category] slicer members “DRUG/NARCOTIC” (from SFPD) and “Graffiti Private Property” (from Graffiti Reports) narrows down the events that are mapped for week 4 of 2013:

SF Graffiti SFPD visualization 3

Selecting a few members of the [Descript] slicer (SFPD data) related to cocaine and hard drugs then filters the map to show what appears to be a tendency for both graffiti and SFPD incidents to happen in close proximity to one another:

SF Graffiti SFPD visualization 4

Zooming in on the cluster in the northeast part of the city reveals more details about the geographic locations of both the SFPD and San Francisco graffiti events:

SF Graffiti SFPD visualization 5

Feedback and suggestions are always welcome to help Open Data Bits improve our content to better suit your needs.