We contacted Captain Tom Easley last week about wanting the names of the officers to math their badge ID number. He didn't see why we needed that information so we decided to make an appointment to go talk to him about this request. Once we were at the appointment he asked us why we felt that the names of the officers corresponding to the badge numbers was necessary. We explained to him that we wanted to provide the users of our database as complete of information as possible. We also felt that if we could show the officer's names then our database users would think of the cops as people instead of numbers. We also reassured him that our intent with this project was to neither benefit the police force or the journalists but to present citizens with the facts and let them do with them what they may.
After listening to out answer Easley responded with that he felt that there needed to be a bigger reason if he was going to release that information to us.He gave us several hypothetical examples about what would happen if he gave us the information. One of them was that he was concerned that people would be able to see which cops give the largest amount of tickets. He feared that these cops would get harassed more once they pulled someone over because their name was now associated with giving out large amounts of tickets.
Easley counter-offered our request by giving us information on which beats cops are assigned (traffic etc). and we accepted. He told us that we could try the municipal court if we still wanted to match the badge ID numbers with the officer names. Easley made it very clear that he did not want his name associated with the release of this department and that he wanted to protect the men and women within his department.
Lilly and I understand where Easley is coming from and that the release of this information could cause a rise in stress within the Norman Police Department but we also feel that this is a major part of our project and that this information would be beneficial to our users. We have decided to add in the information about what beats the police officers are on and pursue Municipal Court to get the information we still want. We have decided that we will go ahead and obtain the information and decide later whether or not we will use it. We will be away for the Christmas holidays and intend to contact the Municipal Court during the second week of January.

Before we start laying out the structure of our website, we want to make sure all of our data is complete.  When we initially requested data from the Norman Police Department, we asked for police officer's ID numbers but not their names, so that we wouldn't overstep our boundaries at first.  Now that we have most of the data, we feel like having the names of the officers is necessary.  However, the NPD is reluctant to let us have that information.  When I talked with one of the captains about it over the phone, he said that NPD generally doesn't give out the names of the officers.  He also mentioned that often, when someone is pulled over, it leads to other larger charges (drug/firearms possession, etc.), and he doesn't want to endanger that by providing the names of the officers.
We don't really see how these things correlate, but we do understand that the officers' names could be considered sensitive information (especially if they include undercover officers).  However, we aren't trying to "blow the cover" of any of these officers or whatever operations they may be taking part in.  We don't have any alternative agenda in compiling this data.  We're doing it because it is the public's right to see how their local law enforcement works.  We aren't working for or against the police department or criminals.
Hopefully we can speak face to face to the captain and agree that it's okay to release the officers' names.  Either way, we can obtain this information through open record requests, but we want to work on preserving and nurturing the relationship we have with the NPD.  They've been very helpful so far, and we don't want to ruin that by not having an honest conversation about this and sidestepping the captain to get the information we feel we need.

We've had lots of exciting things happening with our project in the past few weeks.  It feels like our plans are growing and changing every week, and we're just trying to keep up with it all!  Firstly, I bought a one-year hosting package, so nothing is stopping us from creating a domain for our project except deciding on a name for the website.  Hopefully we'll be able to figure out something soon.
Also, if you've read the last couple posts Ashley has made, you know that we've been having a lot of trouble figuring out how to map out the locations of where people were pulled over.  We decided to reach out to Doug Stiehler, a geographic information systems technician here at OU, to see if he could give us any tips for mapping out the data, and he proved to be a huge help.
We sent him the raw data we had received from the NPD, and when we met with him Thursday he had already mapped out about a third of the data on ArcView, a GIS mapping software.  It was nice to finally be able to visualize some of the data.  We talked about how we could manipulate the data with ArcView, and he showed us a really cool feature where he could select any variable (color of the car, gender of the driver, time of day, etc.) and plot just where those were pulled over.  This adds a whole new aspect to how we can display and manipulate the data on our website.
Although we initially spoke to Mr. Stiehler for tips on how to map the data more efficiently, he turned out to be a very helpful source and has graciously agreed to continue to help us with mapping the locations.  We still have to work through a lot of the data because ArcView is unable to map it due to problems with the data, but we are definitely a lot further along than we were two weeks ago.
When we first started this project, I figured we'd be doing all of the work and figuring everything out on our own.  Everything has been a lot more complicated and time-consuming than we thought it'd be, but we're learning to ask others for help in order to work more efficiently.  Hopefully we can continue to connect with people who can help us make this project be even more versatile than we ever thought it could be.

As we have mentioned before, we have one table in particular that has been giving us headaches, the locations. We have over 2,400 locations that have been given as an intersection instead of one street with a block number. The first set of instructions that our professor, Chris Krug gave us was to use Google maps to try and find a block number for the largest street of the two in the intersection.

After realizing that this would be an extremely long process we were advised to contact the City Planner's Office in Norman. I talked to Joyce Green, Manager GIS Services Division, I was hoping that the department would have some sort of list of the intersections within the city's limits and what block it falls into on both streets. Joyce informed me that no such list exists. The office actually uses a software program called Geographic Information Systems. I was also told that there is a detailed map that we could print for $200. Since neither Lilly nor I have large amounts of money lying around this isn't a possibility.

So, we have now been advised to try to find the longitude and latitude of the intersections that have been impossible to find block numbers for. The ones that have been the most problematic are the ones that involve Highway 9 and the Interstate 35. We hope to be able to use another facet of Google maps to figure out this information on the more difficult intersections. We are hoping that by building our table around Google maps and latitude and longitude the future viewers of our database and website will be able to see the locations of these tickets in a more visual way on a map.

The reason that we decided to go ahead and process the simpler tables was because many of our tables were extremely complex. The most complex one by far is the location of where the ticket took place. Some of the entries are block numbers with the accompanying street name. For example: 800 Classen Blvd. Others are entered as intersections. For example: Robinson St. and 24th St. This has caused major delays in assembling the table. Our professor, Chris Krug has suggested that we organize all the locations by block number. This has required Lilly and I to go through each piece of data and choose the most prominent street of the two and figure out what block of the street the intersection is on. Since there are over 2,400 unique locations that tickets were given we are attempting to work with the city planner's office to see if they have records that already hold this information in order to speed up the process. The picture above is to show how inconsistent the data is. Lindsey St. and 12th St. are entered into the police database in four different ways.

We have started to sort through our thousands of entries of data and split them into categories of similarity. Some of the tables are easier to sort through than others. For example, the gender of the driver is rather simple but others like the location of where the ticket happened is much more complex because sometimes block numbers are given and other times intersections. This requires us to give each piece of data individual attention. Because of the complex categories we have decided to go ahead and begin building the tables in our database that contain the simpler categories. These categories are: Citations, Genders, Ticket Types, Officers, and License Plate States. We will come back and add the more complex categories after they have been properly sorted through.

After a few semesters of building websites, reporting on in-depth trend stories in our communities and classes full of discussions on what the future of journalism might look like, Ashley and I decided to take all of this preparation and actually do something with it.  We've been doing class projects for years--some that could definitely be considered good, solid journalism--but they were always graded and filed away, never actually doing their job of informing citizens.  So, during our last year of school (or last semester, in Ashley's case), we're building the real deal.  Although this project is technically an independent study under Chris Krug, our professor, it is a real journalistic endeavor for us.

We have been working with Cpt. Tom Easley of the Norman Police Department to gather all the information of every traffic ticket or warning issued in the past year in Norman, Okla.  We have a massive spreadsheet with the date, time, location, issuing officer and violation of the ticket/warning, the make, model, color and state of the car pulled over, and the driver's gender, race and age.  We are in the middle of processing all the information--building spreadsheets for each individual variable and fleshing out the information.  Once we get all this in order, we are going to build a website with a detailed search function so that users may compare any of the variables to find trends.  The website will also eventually contain summaries and infographics of the main trends we've found, and maybe a forum or ability for users to comment on the findings.

Right now, we're excited and overwhelmed with this project.  We initially wanted to look at tickets and warnings for the past five years, but when we found out that just one year of data consists of over 38,000 entries, we agreed to start with that.  Already, we are finding that a lot of the records are incomplete, ambiguous, and that there's no set system for how the information is entered.  Going through and trying to decipher the abbreviations for violations, car models, etc. is taking longer than we thought it would, but getting all of the data cleaned up and organized will make things easier in the long run.

This is my first foray into data journalism, and I'm really excited about it.  The data is straightforward and objective, and we'll definitely see a lot of patterns emerge without having to rely on human opinion.  It's especially thrilling to apply it to the mysterious process of traffic violations--there are so many questions and rumors about how this works, and these can only really be answered through data.  If you asked an officer if they pull over more red cars than others, if they give out more tickets at the end of the month, or other similar questions, you may get an ambiguous answer.  By answering these questions through crunching data, you get undeniable facts.  That's what appeals to me most about data journalism.

Although we don't even have a name for our website yet, we are really optimistic about this project.  Depending on how things go, we will probably continue to work on this long after the semester has ended.  If it's successful, we may expand to other towns and compare their data to Norman's.  Hopefully this will be something widely utilized by citizens and journalists alike, and will promote the endless possibilities that data journalism has to offer.
-Lilly