Originally posted by Tiana Antul:

Does your Agency do any proactive policing? If so, step up and play a role by using your data analysis skills to glean some new insights and give your department some data-driven direction. Need a place to start? Try using a small series of simple select queries to identify some specific locations in your jurisdiction with quality of life issues that your department can focus on. You’ll likely turn up locations that are well-known amongst the officers in your department, but they will also likely be surprised by some of the locations you identify. It’s not uncommon for problem locations to go unnoticed when multiple incidents at a location take place on different shifts, are responded to by different officers, or when a location is only problematic for a short duration of time.

Start by creating a table with your most recent data. While it isn’t entirely necessary to create a new table, it will help to speed up your queries if you work for an agency that deals with a high volume of incidents. I would suggest starting with a month’s worth of data, and adjusting accordingly at the end based on the results you get.

Brainstorm which types of incidents you want to include in your definition of “quality of life” issues. Incident types will vary from department to department, but I suggest considering some of the “lesser” incident types that don’t necessarily rise to the level of a crime, such as loud parties, loud music, animal complaints, disorderly persons, disturbances, aggressive panhandling, and the like. Using a select query, pull out just those records involving the types of incidents that you’ve defined as constituting quality of life issues.

Now create a new query that runs off the previous one. With the “Totals” button (summation symbol) activated, count the number of incidents grouped by the address at which they took place. Sort your count of incidents in a descending fashion. This will sort your results so that the addresses with the most quality of life-related incidents appear at the top. You can also set a threshold in the criteria row of your incident count to limit your results. For example, “>3” will only return those addresses that have had 4 or more quality of life-related incidents in the previous month.

Depending on how many locations your query results turn up, you may find that you need to scale back to 3 or 2 weeks worth of data if the volume is too large. Conversely, if your jurisdiction is a relatively quiet one, or if your jurisdiction consists of a small geographic area such as a college campus, you may need to expand your original data selection to include a larger date range. You can also adjust the threshold (if you set one) to see more or fewer addresses.

Voila! Within minutes you’re armed with a list of locations that have experienced recent quality of life issues, and it’s all backed up with data.

Feeling adventurous? Try repeating the process for other categories of incidents, such as violent crime, property crime, etc. to identify other types of problem properties.