Vancouver Police Department

Project Background

In 2003, the Vancouver Police Department decided to evaluate its current police patrol staffing needs.

In particular, the organization needed to verify whether there was an adequate match between the number of 911 calls received in the various police districts of Vancouver, and the number of police patrol members deployed in each district.

The VPD asked Cépage Data Development to develop a methodology to compare police deployment with 911 calls, and to implement this methodology by developing a custom data analysis program.

Project Objectives

• Define a methodology allowing VPD to answer the following question: are there enough police patrol members deployed in the city to attend 911 calls, at any given date and time and in any given police district?



• Collect, transform, and analyze two data sets for a full year. One dataset should contain information about 911 calls by date, time, and police district. The other dataset should provide information about police patrol member allocation, also by date, time, and police district.



• Produce a set of summary tables allowing for a comparative analysis of the two data sets.



• Assist a VPD research analyst in interpreting the results.

Requirements and Challenges

• 911 calls are automatically logged in a database system called CAD. But the information is not usable as is. CAD is designed to log individual calls for dispatch purposes, not to provide data for statistical analysis.



• Police patrol deployment information is recorded in paper documents called "duty sheets". Those sheets were never intended to provide statistical information, only to keep a dispatch record for payroll and staff management purposes.



• The challenge, therefore, was to capture, transform, and compare huge quantities of non-statistical data in order to make them meaningful for statistical analysis.

Solution and Outcome

• The following steps were taken:
  
  
  • Build a custom data-entry software to capture patrol staffing information for a full year, based on the paper duty sheets. Train a data-entry clerk to read and accurately interpret the paper duty sheets before entering the data.
  • Check the consistency of the data entered by the clerk and correct entry errors, using a set of SQL statements.
  • Extract raw data from the CAD database, containing log entries for every 911 call received during a full year.
  • Write automated routines to transform the CAD raw data and consolidate the 911 call logs by date, hour, patrol unit, and district. Export the resulting data in a SQL Server database.
  • Compare the two resulting data sets.
  • Assist a VPD research analyst in interpreting the results.


• The project was completed in September, 2003.



• This is the first time that such a methodology was used at VPD to analyze the relation between 911 calls and police patrol staffing.



• VPD senior management has recognized the ground-breaking nature of this project. They have presented the results to the City of Vancouver to make the case for hiring additional police members.