Mashing up Virginia Beach Police Data with Google Docs

Last Sunday, 10/18/2009, the editor of the neighborhood section of my local paper, The Virginian-Pilot, said that he was looking for ways to improve how he reports crime data. At the same time, I know that neighborhood crime is a hot topic in our community league meetings, casual get-togethers, and email lists.

Witness: hyperlocal supply and demand in Gov 2.0.

In fact, there's plenty my local paper can do to improve the reporting of [crime] data in our city. In the printed edition of the neighborhood section they only print the police "blotter" from the previous week. Online they use Google maps to report crimes by location, but interfaces are constrained and inconsistent.

What my paper should do to improve crime reporting is go to the source.

The Virginia Beach Police Department publishes crime data on a city website. Here's the URL:

http://www.vbgov.com/vgn.aspx?vgnextoid=8f770d066c0dd010VgnVCM1000006310640aRCRD&vgnextchannel=

6a0a1c17717ec110VgnVCM100000190c640aRCRD&vgnextfmt=default

Gnarly.

You can query for all crimes going back to January 1, 2006 (why only that far back?) up to the present day. But you can look at exactly 15 results at a time.

So what my local paper should do is appeal to the city to make [crime] data more accessible to everyone; where everyone includes The Virginian-Pilot. Short of that, the Pilot should take the energy it invests in supporting its Web presence and use it to scrape and publish city data. If only there were a "Machine Friendly" link next to the "Printer Friendly" link. (Both are designed to help people.)

Shortcomings notwithstanding, here's what Pilot reporters can do right now for free:

Compile and publish Google spreadsheets with charts:

http://spreadsheets.google.com/ccc?key=0AtBw9czGJjSTdGZGVzh3MzdzWEQ1SzJ6NTJDbl9taHc

http://spreadsheets.google.com/ccc?key=0AtBw9czGJjSTdFdlaEEwT2Y1RGotdGNWU2w4WDh3Nnc

Wow, who knew that 72% of all crime in Virginia Beach> occurs at the Oceanfront?

Update: HUGE, HUGE ERROR in my reporting of this stat. The pie chart above does not show all crime in Virginia Beach. It shows only the top 5 neighborhoods.

This chart, below, is the correct chart. It shows that, while still a standout piece of the pie, 4% of all crime happens at the Oceanfront.

I'm feeling really dense right now, but in a way I'm glad this happened. This is a perfect illustration of a fundamental in data viz (and reporting).