This article was originally published to a blog which has been decommissioned.
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.
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:
Date and Time dimensions can 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:
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:
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:
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:
Leave a Reply