After an extensive break for Christmas and New Years Lilly and I are back to working on our data journalism project. We met with Doug last week and talked to him about the upcoming semester. He told us that he had mostly been cleaning up the data so that Access can properly analyze it. He is emailing us the files that still need cleaning up so that we can help with this side of the project. This week we met with Chris Krug again and Lilly and I decided on a name for our website: policepatternsproject.com. We also talked through what we want the layout of the homepage to look like and what all should be on the navigation. We are going to go ahead and start building the basics of our web page since we already own a one-year hosting package. We will be buying that domain name very soon. We will be going to the municipal court soon to try and obtain the names of the Norman police officers and their correlating badge ID numbers. We still haven’t decided if this information will a part of our final website or not. But, we still want to go ahead and obtain the information that way we have it if we decide it should e a part of our website in the future. We also will be going to register our business name with the Secretary of the State. We want this to be a long-term project and have big plans for the future of it.

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.