Vivek B: Long term crime tracking by area and beat

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 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.

7 thoughts on “Vivek B: Long term crime tracking by area and beat

  1. Matt B

    One thing you may want to add to your calculations is the rate of crimes. That way you aren’t comparing the number of crimes in one beat of, say, 10,000 people, to another beat of 20,000 people. For that you need the yearly population of the various areas. Hard to get, but Urban Strategies Council has done some estimates. For the city, you can access the CA Department of Finance website.

  2. Max Allstadt

    I have a little theory that some cross referenced data might prove/disprove.

    Income and robbery/burglary on the same map. I hypothesize you’ll find the highest robbery and burglary rates where rich people are closest to poor people. Inequality of money is like inequality of solute on opposite sides of a membrane.

  3. Californio

    Not sure about your conclusion that “the OPD did something right in June,” etc. It might be the OPD’s doing, but then again it might not. Fluctuations in crime rate have numerous causes, as we all know. Did the OPD do something specific in the neighborhood that would reduce crime? Do you correlate the officers’ attendance at the NCPC’s with the latest-month reduction in crime? Or? Causation is notoriously difficult to demonstrate in criminology (especially when dealing with crimes of opportunity), and it’s important to know when not to draw a conclusion from data. I realize your statement was made as a passing comment and not intended to be formal, but, hey, let’s be careful!

  4. VivekB

    Californio: The reason I made that comment is that it was nearly unilaterally green across the board. Certainly at a micro-level causation could be problematic due to multiple factors, but at a macro-level I can’t think of any reason why we’re below the 2008 median truly across the board. Of course, I’m no pro here, so if folks have a good reason tell me.

    Just as we’re all quick to condemn the OPD when average crime rates go up year over year, so should we be ready to congratulate them when rates go down. It’s hypocritical for us (not saying you specifically, the general We) for us to say that crime rate increase is OPD’s fault, but crime rate decrease isn’t due to OPD. We need to pick one mentality – are deviations in crime rates at OPD’s feet or not, and stick with that story in good times and bad.

  5. VivekB

    Stats through Aug ’08 are now complete. I tried my hand at creating an “executive summary” in English this time as the tables aren’t the easiest to read if you’re not a numbers wonk. is the link, registered & logged in users only.

    It’s got YTD and Medians per month for the following crime categories:
    - Violent (crimes against a person)
    - Property
    - Violent + Property + Drug (aka, the stuff we should worry about)
    - All Crimes

    There’s also now a page with the actual penal codes for each of those buckets, and a page with “unallocated” crimes that will only show up in the “all crimes” bucket, so y’all can check my work.

    It’s got the following geographies:
    - Oakland Wide
    - Area 1, 2, & 3
    - Beats 4X, 7X, 9X, 10X, 11X, 12X, 12Y/13X, 13Y.