If you fill out the "Forgot Password" form but don't get an email to reset your password within 5-10 minutes, please email logistics@ire.org for assistance.
By Gavin Off
Tulsa World
I drive by an elementary school on my way to work every day. More than once there’s been a police cruiser idling in the school’s parking lot with lights flashing and the officer standing nearby.
Although those incidents never involved a major crime, on several occasions this year the Tulsa World has chronicled arrests at schools.
In January, police arrested an 18-year-old man found with a stun gun, two samurai swords and six knives in his car in a high school parking lot.
In February, police arrested a 59-year-old man after he pointed a gun at students standing outside a Tulsa high school.
And later that month, police arrested a Tulsa County music teacher for allegedly fondling a 17-year-old male student.
There were others incidents, most of which were minor. Some included fights, vandalism and drug use.
Not long after the teacher’s arrest, I called my contact at the Tulsa Police Department’s information technology department and requested a database of all calls from area schools to police since 2005.
I wanted to see what types of crimes happened on campus. What schools were particularly violent? Has crime increased in recent years? How did elementary schools, such as the one I pass every day, stack up against their high school counterparts?
At the suggestion of the World’s education reporter, I also requested similar data from the police department in Broken Arrow, a city of about 91,000 just outside Tulsa.
It could be interesting to compare crime within each city’s schools, she said. Broken Arrow, unlike Tulsa, does not carry a big-city reputation and the negative connotations that typically follow. Despite its growing population, Broken Arrow is still known as a quiet, peaceful suburb.
Taming the call list
Data from both departments were free and came surprisingly fast. Within a week I received a Microsoft Excel spreadsheet from Tulsa and Broken Arrow police, but I immediately noticed a problem with my request.
Since 2005, Tulsa and Broken Arrow schools combined logged more than 18,500 calls to police. That was a red flag.
Although the calls from schools to police would be helpful, the data I really wanted to analyze were calls that resulted in a charge or an arrest.
That data would provide a more accurate description of crime in area schools. It would eliminate most calls for traffic stops, stray animals, abandoned vehicles and the like. It would also eliminate calls where police found no evidence of wrongdoing.
So I re-requested the information, and in a few days I received all calls that resulted in a police incident report. That, police said, was the closest they could get to the data I was looking for. Both departments provided all data for free.
Combined, the data now totaled 3,700 records, a more sensible number considering Tulsa and Broken Arrow are two of the biggest cities in Oklahoma.
The data for both departments came in Excel spreadsheets. They were remarkably simple and required no joins or advanced computer-assisted reporting techniques.
Fields included location, date, time, call priority, call description and officer’s remarks, which were usually left blank. As in previous experiences with police department data, this data required a lot of basic cleaning.
The location field, for example, often included the school’s name and address, but there was rarely uniformity among records.
Sometimes the school name was abbreviated and followed directly by an abbreviated address. Sometimes the school name and address were written out. Sometimes the two were separated by a comma, dash or semicolon, and sometimes the field failed to include a school name or address at all.
I imported the data into Microsoft Access database manager and created two new fields, one titled "School" and a second titled "Address." Through a combination of update queries, I populated the new "School" field and used that data to fill in the "Address" field.
It was easy but time-consuming. I was still interested in analyzing the 18,500 calls and not just the ones that resulted in a police report.
Similarly, I had to replace about 140 description codes with a reason for the call. For instance, I replaced "INDIP" with "Indecent exposure" and "CAB" with "Child abuse." The police departments provided that information with my original request.
Once I had two cleaned databases—one of all calls from schools to police and a second of only the calls that resulted in a police report—I began running basic queries.
Finding the story
I was still without a specific story idea, so I began counting crimes by year or description, comparing crimes among Tulsa and Broken Arrow schools and among elementary, middle and high schools in both cities.
I created a new field to differentiate crimes against people, such as assault, versus crimes involving property, such as burglary. And to help put the numbers into perspective, I compared the total number of crimes per school with the school’s population. I received that data from the Oklahoma Department of Education.
The police data could have produced any number of solid stories:
My editors recommended a two-part series. The first story would investigate the different crimes at elementary, middle and high schools.
Data showed that crimes at Tulsa elementary schools had increased 44 percent since 2005. And although crimes had decreased at Tulsa middle and high schools, the majority of violent crimes still occurred there.
The second part of the series would specifically look at school-reported child abuse.
From 2005 through 2008, Tulsa schools reported to police an average of 10.4 cases of possible child abuse each month, the World found. Police found about 75 percent of those calls—374 over the four years—credible enough to file an incident report, records show.
According to police, the majority of school-reported child abuse cases happened off campus. Teachers and counselors merely saw the effects, usually bruises on the child’s arms, neck or face, and notified police.
'Alarm Bells' online package
Because of the sensitive topic violence at schools and because of the amount of data we had, the World decided to create a special Web page for the series.
Besides simply posting the stories, a World Web designer placed the copy between larger photos, breakout boxes and an interactive map.
Using ESRI ArcView geographic information system software, I mapped each school and assigned different color dots for elementary, middle or high schools.
Then, the designer created an interactive feature in which users could scroll over a school to find out how many times school personnel called police and how many of those calls resulted in a police report.
We also uploaded a searchable database that let users search calls from each school. They could search by school, call description or year. The World uploaded a second database that specifically allowed users to search calls that resulted in a police incident report.
Ideas for future stories
Given more time, the World could have produced any number of interesting stories using the calls from schools data.
A few ideas I would have liked to explore include matching the call data with school dropout data from the Oklahoma Department of Education. Do schools with the highest crime rates have the highest dropout rates?
It also would have been interesting to overlay a map of the schools with Census household income data. Are schools with the highest crime rates in areas with the lowest average household income?
A final idea would be to investigate how much time and money Tulsa police spend answering calls from schools. On average, Tulsa schools called police nearly seven times a week, data show.
Gavin Off is the data editor at the Tulsa World in Oklahoma and a former analyst for the IRE and NICAR Database Library.
Looks like you haven't made a choice yet.
