Crime rates in Vancouver falling – or are they?

Crime records recently released by the City of Vancouver as part of their Open Data Catalogue would have you believe crime rates are falling – but dig a little deeper and the statistics are a little misleading.


This is just the latest in a series of over 130 data sets released by the City – others range from bicycle lanes to traffic signals, from inventories of deceased animals to graffiti. In May of 2009, Vancouver City Council passed a motion referred to as Open3 declaring that the City of Vancouver will endorse the principles of open and accessible data and will freely share with citizens, business, and other jurisdictions the greatest amount of data possible while respecting privacy and security concerns.

To date, the data sets released have been rather mundane so I was surprised and excited when the crime records were released.

This data set, with over 300,000 records,  is HUGE. The data was extracted from the Vancouver Police Department’s computer systems and covers crime between 2003 and 2011.Each dataset contains seven different crime types and each record includes information on the type of incident, the year and month it happened and a general location of where the event took place.

The seven different crime types available on the open data website include:

My aim was to build a customized, interactive map of this data using Google’s Fusion Tables (FT) and their Mapping API. I’d used Tableau and GeoCommons before, but I wanted more flexibility and didn’t want a Flash-based solution. FT therefore was my next choice given the wonderful maps I have seen folks show at NICAR-L. However, given FT’s limit of only being able to show 100,000 points on a map, I decided to only play with the last 5 years’ worth of data (2007-2011).


I compiled the data into one spreadsheet, and created a pivot table to do a quick analysis for any interesting trends over the five-year period. This is what I got:

Type Of Crime
2007 2008 2009 2010 2011
Commercial Break And Enter 2501 2284 911 1665 432 7793
Mischief Over $5000 79 68 49 68 152 416
Mischief Under $5000 4890 5439 2203 4447 2336 19315
Theft From Auto Over $5000 196 194 177 144 61 772
Theft From Auto Under $5000 12093 11217 9875 8477 3448 45110
Theft Of Auto Over $5000 941 710 465 321 143 2580
Theft Of Auto Under $5000 2389 1706 1421 1146 416 7078

My first instinct was to see what crime rates had done most recently i.e. between 2010 and 2011. In all but one case, crime has been halved. But then I realized the data for 2011 only includes up to the month of June. In most cases, if you speculate that the 2011 figure should be doubled for the remainder of the year, then that decrease is drastically reduced.

Given the incomplete and unreliable nature of the 2011 data, I then did a comparison of the 2009 and 201o columns. Here, you actually see not only an increase in total crime but a doubling in instances of mischief under $5,000 and commercial break and enters.

This is not to say that rates don’t appear to be falling – in many cases they seem to be – but these figures aren’t as binary as they first seem.

The next step was to clean up and geocode the data. The address data supplied by the City for each crime was approximate, I assume for privacy reasons. Rather than say 850 Granville St, 8XX Granville was recorded. So I had the dilemma of how to map these addresses. The only real option I had was to do a global replace of the XX with 00. This means the data is only accurate within say half a block.


But then I had to geocode the data as well. FT needs the latitude and longitude of each address to be able to map it accurately. Luckily you can submit batches of addresses to, but only in batches of 1,000 records. This meant babysitting 2 computers while they chugged away, and alot of copying and pasting.

Then the fun began. I uploaded the data to a FT, and created my basic map. Now at this point, I had a map with all the data on, that I could have then embedded in a web page. But frankly the map had so many red dots on it, you could barely see the streets, nor did you have any ability to filter the data by year or crime type. I also wanted to show people with a heat map, which was the most crime-ridden neighbourhood in the city.

The basic filters were done by using HTML, Javascript and Google’s Mapping API and some input from CBC’s David McKie! These filters can be turned on/off by a series of checkboxes that allow you to see how (a) crime has fluctuated over the years, and (b) which parts of the city are more susceptible to crime. Unfortunately, one other downside to FTs is the fact that you can only show 5 layers of data at a time. On my map, each crime type and each crime year was its own year – a total of 12 layers.


The heat map layer took me longer to figure out. I needed to be able to know how many crimes occurred in each of the city’s 22 neighbourhoods. Sounds easy right? Unfortunately, the crime data had no neighbourhood data, and the boundary data for the neighbourhoods (another data set – a KML file – from the Open Data Catalogue) obviously had no reference to crime data.

Enter QGIS, my saviour.

QGIS is a great piece of mapping software that not only works on Macs and PCs, but is free. Sorted. I was able, after a great deal of experimentation and harsh language, able to merge the 2 data sets into a .shp file that I could overlay on my map. This layer allows you to see how many crimes occurred in each neighbourhood over the 5 year period. Here’s the top 6 sketchy neighbourhoods in Vancouver:

Commercial Break and Enter

Downtown 2438
Fairview 742
Mount Pleasant 550
West End 550
Kitsilano 477
Strathcona 452

Mischief Over $5000

Downtown 173
Sunset 38
Strathcona 35
Kensington-Cedar Cottage 20
Grandview-Woodland 17
Mount Pleasant 16

Mischief Under $5000

Downtown 4737
Grandview-Woodland 1431
West End 1324
Renfrew-Collingwood 1266
Strathcona 1217
Kensington-Cedar Cottage 1172

Theft of Auto over $5000

Downtown 490
Mount Pleasant 201
Renfrew-Collingwood 185
Kensington-Cedar Cottage 176
Kitsilano 152
Sunset 151

Theft of Auto under $5000

Downtown 789
Mount Pleasant 603
Grandview-Woodland 592
Kensington-Cedar Cottage 581
Renfrew-Collingwood 566
Hastings-Sunrise 484

Theft from Auto over $5000

Downtown 289
Fairview 60
West End 56
Strathcona 39
Mount Pleasant 37
Kensington-Cedar Cottage 35

Theft from Auto under $5000

Downtown 13997
West End 4149
Fairview 3219
Mount Pleasant 2784
Kitsilano 2100
Kensington-Cedar Cottage 209


OK – so it was no surprise that the downtown core was the hub of crime in the city. But what was a surprise was to see neighbourhoods like Kitsilano and Fairview in a number of the lists.

But conversely the map also shows us which are the most crime-free parts of Vancouver.

So that means I could soon be moving to South Cambie…