Vivek B: Long term crime tracking by area and beat

by Vivek B. | August 6, 2008 | 7 comments

Hi there. My name is Vivek B, and as I’m writing this guest post since VS is taking the week off but we still need our fix. First, a quick few sentences about who I am. I’m just a regular citizen who works in a totally unrelated field, and has lived in Rockridge for about 7 years. I got heavily involved in home security a few years ago when we had a big crime wave in 2006. We had a ton of armed robberies within 1-2 blocks of my house, so I taught myself how to replace & beef up my security system, and put in a security camera network. That helped a little, but I wanted to know how I should feel about whether crime was getting better or worse. I started monitoring the weekly OPD stats that are published. A year ago, I realized that although I knew what the weekly numbers were, I didn’t know whether to feel good or bad. Is 10 stolen cars in a month really bad? Is it good? How does this compare to how it was a few years ago?

And so began my descent into crime tracking madness. I started tracking & plotting everything in sight, weekly trends, monthly trends, across a variety of categories. Last month I realized that although now I knew how crime was trending in Rockridge vs Area 1 vs all of Oakland, if it moved a few blocks south, I wouldn’t know. I just added both neighboring beats and a few miscellaneous beats to see if there’s any knowledge I can glean there. Here’s what I’m finding so far. Let me know if you see any obvious logic or process errors, this is an ever-evolving process.

Enough intro, let’s go. This is the data through June, I haven’t yet gotten the final week of July but I should have that within a few days after which I’ll recut all these #s with that info.

For a larger version of this table, click here.

What you see here is:

1. Crime is broken up into varying geographies. And, as my reporting is focused on the 12Y/13X NCPC meetings, Area 1 is further detailed out on the beats that surround it.

2. Crime broken up into 4 tables:

• Violent is crimes against a person such as robbery or worse. We *really* don’t like these crimes.
• Property is crimes not against a person, but still worrisome, such as burglary. Petty theft isn’t included because it’s not the “worrisome” level.
• Violent & Property Crimes is just that – the above two summed together. Or, in other words, the total crime that occurs that we should be worried about. However, drug trafficking isn’t technically a “violent” crime, but neither is it a “property” crime. Rather than create another table for just drugs, I’ve left it out of here, although I’m still torn about the wisdom of that, and wonder if I should have a “Violent & Crimes & Drug trafficking” column. Slippery slope though, do I put simple drug posession in? Best I stop meandering my thoughts here.
• All Crimes. This is every crime, but it all counts the same. Shoplifting is treated the same as Attempted Murder. On the one hand, that’s not good. But on the other hand, it’s good to have at least 1 table with a global view. Drugs are in here.

I know that the above categories are rough at best, and that within any given category a spike in one area could be hidden by a lull in another area, but my theory is that macro-level crime tracking is where I can help. OPD needs to track micro-spikes, such as an explosion in the number car thefts along a given block, but if it’s really just the same gang moving from one part of the beat to another it’s less relevant for me to know that.

3. This is measured in “median per month”, not “average per month”. I did that intentionally as I didn’t want any given month skewing the results. I plotted the 2008 median against both 2005 & 2007, to get a view on how OPD has done lately and comparing to how it was a few years back. Anything earlier than 2005 is hard for me to wrap my mind around as life was different then, I’m really focusing on the near-term moves.

If you check out the stats, they’re pretty revealing.

• If you look at either All Crimes or Violent & Property, life doesn’t seem so bad at the aggregate level. Indeed, it even shows how recent months show a good dip below the median 2008 levels, which themselves are within 10% of 2007 levels. Comparing it to 2005 is a different story, and that story is different based on what beat you live in. I’m not too happy about how 12Y/13X (Rockridge) is compared to 2005 as we’re one of the few red spots in growth, although in absolute terms it looks like we just caught up with everyone else. My theory is that water flows to the lowest point, and so did crime. Think about it – would you rather rob A) a bunch of folks in downtown where there’s already tons of crime and folks are on edge, or B) La-La-land (Rockridge) where folks still like to believe crime doesn’t exist, leave their doors unlocked/open, don’t listen to their gut about avoiding sketchy people because they’re afraid of offending them, and think the City Council is doing right to focus on smoking and trees as important topics. I know I’d go for the easy marks.
• I also found it interesting that within Area 1, comparing beats now to 2007 showed much less variance across beats than I expected. Compared to 2005 it’s very different, so my layman’s guess is that from 2005->2007, crime got more evenly spread throughout the Area.
• Area 3 has seriously borne the brunt of Violent & Property Crime growth since 2005.
• Could there be *any* more red on the Violent Crimes when compared to 2005? And those #s aren’t small either.
• But to end here on a positive note, there is a whole lotta green in the “most recent month” column. That tells me that OPD did something right in June, because other than a few spots here & there, crime across the board was relatively decent.

So there it is. I don’t do this for a living, only work on it from 9pm -> midnight a few nights per week (after the family goes to bed), so I may have not done it fully correctly. Let me know what you think, and what you’d suggest improving. The MS-Access database that I’ve loaded, and the excel worksheet that I use as a “staging” area for all the tables & charts is open to anyone who wants it, if you want to double-check my stuff.

BTW, if you want regular updates or more detail, there’s a monthly 20 page PDF I put out about this with more graphs & charts than you can stomach. Stuff like weekly graphs on how crime is going so we can tie it to current events. Did the big Acorn bust a few weeks back really make any difference? The next one in 2 weeks also has the penal code breakdown for the above categories, so you can see exactly what’s in where. There’s 6 different yahoogroups, a few blogs, and 2 newspaper columnists who are interested in it, so rather than email it out to everyone and deal with questions on multiple venues, I created a website for both publication and comments. The site is www.RockridgeResidents.org. The stats are only for registered users, but registration is free; I just do that so I can tell who’s looking at it, and via the traffic, see whether any given report resonated more with folks.

If you made it this far, thanks for reading.

On a related note, i’d like to say how impressed i’ve been with the dedication of OPD. From our PSO (Officer Millar) to Sgt Ortiz, Lt. Hamilton, and Captain Toribio. Heck, even the 12X PSO (Officer Gerrans) comes to our NCPC meetings as crime spills over beat lines. Every person i’ve interacted with clearly demonstrates passion & commitment to their job, and I’m confident that together, we can figure out how to lick this problem.