Harnessing the power of City data with SpatialKey

20 October 2009

Cities are opening up and providing access to data as part of an initiative to improve the accessibility, transparency, and accountability of City governments. Several cities, including New York City, Washington DC and San Francisco, are among a few to lead this initiative in an effort to serve the public by creating “data mines” of public information. The driving factor behind this initiative is the Government 2.0 work being spearheaded by the White House and President Barack Obama’s mandate that government data must be made available for public consumption on the Internet.

With the abundance of this raw data new challenges arise. How do cities display the information in meaningful ways without complex and costly software? This data is typically shared in the format of CSV (comma separated value ), spreadsheet or XML files containing many thousands of rows. Extracting meaning from the data can be a daunting task requiring multiple pivot tables, graphs, and filters along with the expertise in doing so. And assuming you are able to get this far, you will still be left without an easy solution in which to share this data with others.

How do you expose the location based information within the data? It is possible take a handful of the items in the spreadsheet and plot them in a web based mapping solution but most web based maps fall short in their ability to plot thousands of points in a meaningful way.

With SpatialKey you can take nearly any of these data feeds and transform them into an interactive report in minutes. You don’t need to be a specialist to create and share time- and location-based analyses.

To demonstrate the power and flexibility of SpatialKey an example from the New York Department of Sanitation containing graffiti locations is shown below. You can find this data in the NYC Data Mine by searching for the keyword “graffiti”. This dataset contains requests to clean graffiti (other than bridges or highways) received from the public in the last 12 months. They include location information, open and closed dates, and details about the community. A small snapshot of this data is shown below

A sample of graffiti data from the NYC dataset

Figure 1.0 - Sample of graffiti data from the NYC dataset

It is important to note that SpatialKey was not developed for this specific data and no programming was required to build the reports. It is as simple as exporting a CSV from excel and importing the data into SpatialKey. SpatialKey inspects the data during the import process and detects the data types (text, numbers and dates) and builds a custom user interface tailored to the data structure from the spreadsheet. SpatialKey also handles the geocoding as long as you have address information or X/Y in the data. The import process can be performed with thousands of rows in just a few minutes.

After importing the data a full screen map report is opened as shown in Figure 2.0. The report contains a timeline that highlights the trends of open graffiti reports over the last twelve months and the map highlights hot spots for reported graffiti locations. You can instantly identify these trends and hotspots quickly in SpatialKey then start to drill down to identify additional trends with the filtering tools.

Figure 2.0 - A map and timeline of reported graffiti

In Figure 2.1 a categorical pod for the “status” field in our data is opened and the data is aggregated by the unique statuses in that field. By clicking on the “Closed” status you can filter out all closed incidents and the map reflects only open and pending incidents. In addition you can switch the timeline to show unfiltered data to see the trend of open/pending vs closed incidents. Within the stacked bar chart the open incidents are displayed with the filled area and the closed incidents are shown in the unfilled area.

Figure 2.1 - Displaying the trend of open versus closed reports over time

A custom interface to display and filter data can be built in seconds with no programming or development needed. In Figure 3.0 four categorical pods have been added from different fields available in the graffiti dataset these pods can be used for both display and filtering.