Sometimes a database story starts with an idea. Other times it arises from the data itself. In this case, it was both. Reporter Jill Leovy, who was covering homicide, showed up at my desk with a database of every homicide investigated by the LAPD since 1988. I quickly looked through the table and had to inform Leovy that it wouldn't answer her question about detective deployment because there was no information on the detectives' identities.
She was going to each of the 18 police divisions to look through old detective rosters. I started keypunching. When she was done, I did a simple calculation that showed the caseload for every division for every year. It was obvious that detectives working South Los Angeles had consistently been burdened with more cases. Our concept began to focus on the perception and fear of violence. We shifted our focus to unsolved homicides, and I turned to mapping to find patterns.
Using ESRI ArcView 8.2, I geocoded the homicides and made a subset of the unsolved ones. Geocoding turns street addresses in data tables into point maps. The first pass yielded startling results. By laying the dots over a street map, I saw that a large area had one or more unsolved homicides on almost every block. My next step was to produce theme maps to show unsolved homicides as a ratio of population and area of Census tracts.
It finally hit me that the ideal base was the city block. By shading individual blocks with the number of unsolved homicides that occurred on them, I produced the dramatic pattern that was ultimately published. No rate was necessary because everyone knows what a block is. I also added an elementary school shapefile and made a table showing the number of unsolved homicides within a quarter mile of each elementary school.
For the Web presentation, I wanted readers to be able to see the unsolved homicides in their own neighborhoods. Over a couple of days, I made 25 Adobe Acrobat Portable Document Files with the street grid showing lightly in the background. The Web staff turned this into an interactive display. The stories prompted the police chief to beef up detective strength in overworked bureaus.