openpolicedata
Review California Stops Data
California has a state law (RIPA) that requires departments to report traffic stop data. Originally, the state appeared to be hosting data from all the departments so when individual departments hosted this data, was not included in our source table as it was viewed as redundant. In some cases, we include pre-RIPA data in our source table instead.
At some point, California to the RIPA data down. It is now back up but only for a limited number of departments and it's in a form factor (zip file contain Excel files for each department) that is less user-friendly than a lot of the data being shared by individual departments. It would be better to now include data in our source table for individual departments. Need to re-survey California departments for RIPA data and identify departments where we are sharing pre-RIPA data instead of RIPA. We currently have no ability to share multiple datasets for the same type of data where both datasets span multiple years (Will be addressed in #131)
FYI, here is the data in the current RIPA files published by the state, with the count of stops for each bucket. First, is the list grouped by agency, showing all years for each agency. Second is the list grouped by years, showing all agencies for each year.
Are the cities putting up data for different date ranges? Is there a catalog of sources? (Sorry, thinking with my mouth, I can probably find that last.)
+-------------------------------------+----------+
| agency_yrs | count(0) |
+-------------------------------------+----------+
| ALAMEDA CO SO - 2021 | 15505 |
| ANAHEIM PD - 2021 | 26568 |
| ARCATA PD - 2021 | 1428 |
| BAKERSFIELD PD - 2020 | 12170 |
| BAKERSFIELD PD - 2021 | 11948 |
| BELMONT PD - 2021 | 1475 |
| BERKELEY PD - 2021 | 5469 |
| CAPITOLA PD - 2021 | 606 |
| CARLSBAD PD - 2021 | 5321 |
| CHP-HQ A/C, STAFF - 2018 | 1036484 |
| CHP-HQ A/C, STAFF - 2019 | 2175618 |
| CHP-HQ A/C, STAFF - 2020 | 1696390 |
| CHP-HQ A/C, STAFF - 2021 | 1749421 |
| CONTRA COSTA CO SO - 2021 | 3131 |
| COTATI PD - 2021 | 1728 |
| CSU CHICO - 2021 | 303 |
| CULVER CITY PD - 2021 | 9454 |
| DAVIS PD - 2020 | 2626 |
| DAVIS PD - 2021 | 4545 |
| EMERYVILLE PD - 2021 | 1665 |
| EUREKA PD - 2021 | 2906 |
| FRESNO CO SO - 2021 | 19282 |
| FRESNO PD - 2019 | 51849 |
| FRESNO PD - 2020 | 14738 |
| FRESNO PD - 2021 | 10816 |
| HILLSBOROUGH PD - 2021 | 646 |
| KERN CO SO - 2021 | 12277 |
| LA DEPT OF AIRPORT - 2021 | 4672 |
| LAPD - 2018 | 336681 |
| LAPD - 2019 | 712807 |
| LAPD - 2020 | 521290 |
| LAPD - 2021 | 429242 |
| LAUSD PD - 2020 | 1150 |
| LAUSD PD-GRAND - 2021 | 100 |
| LIVERMORE PD - 2021 | 4552 |
| LONG BEACH PD - 2019 | 40524 |
| LONG BEACH PD - 2020 | 17197 |
| LONG BEACH PD - 2021 | 11986 |
| LOS ALTOS PD - 2021 | 987 |
| LOS ANGELES CO SD - 2018 | 136635 |
| LOS ANGELES CO SD - 2019 | 196850 |
| LOS ANGELES CO SD - 2020 | 104238 |
| LOS ANGELES CO SD - 2021 | 179888 |
| MILL VALLEY PD - 2021 | 808 |
| OAKLAND PD - 2019 | 24395 |
| OAKLAND PD - 2020 | 21076 |
| OAKLAND PD - 2021 | 13782 |
| ORANGE CO SO - 2019 | 50396 |
| ORANGE CO SO - 2020 | 39794 |
| ORANGE CO SO - 2021 | 46283 |
| PETALUMA PD - 2021 | 3882 |
| PIEDMONT PD - 2021 | 639 |
| POMONA PD - 2021 | 4594 |
| RIVERSIDE CO SO - 2018 | 44505 |
| RIVERSIDE CO SO - 2019 | 58379 |
| RIVERSIDE CO SO - 2020 | 56257 |
| RIVERSIDE CO SO - 2021 | 75779 |
| RIVERSIDE PD - 2021 | 19262 |
| ROHNERT PARK PD - 2021 | 2360 |
| SACRAMENTO CO SHERIFF'S DEPT - 2019 | 60944 |
| SACRAMENTO CO SHERIFF'S DEPT - 2020 | 43852 |
| SACRAMENTO CO SHERIFF'S DEPT - 2021 | 33018 |
| SACRAMENTO PD - 2019 | 68012 |
| SACRAMENTO PD - 2020 | 51391 |
| SACRAMENTO PD - 2021 | 46671 |
| SAN BERNARDINO CO SO - 2018 | 62433 |
| SAN BERNARDINO CO SO - 2019 | 157715 |
| SAN BERNARDINO CO SO - 2020 | 108979 |
| SAN BERNARDINO CO SO - 2021 | 98576 |
| SAN DIEGO CO SO - 2018 | 40515 |
| SAN DIEGO CO SO - 2019 | 65029 |
| SAN DIEGO CO SO - 2020 | 38800 |
| SAN DIEGO CO SO - 2021 | 21925 |
| SAN DIEGO PD - 2018 | 89455 |
| SAN DIEGO PD - 2019 | 187231 |
| SAN DIEGO PD - 2020 | 150575 |
| SAN DIEGO PD - 2021 | 130112 |
| SAN FRANCISCO CO SO - 2021 | 628 |
| SAN FRANCISCO PD - 2018 | 56409 |
| SAN FRANCISCO PD - 2019 | 101614 |
| SAN FRANCISCO PD - 2020 | 38582 |
| SAN FRANCISCO PD - DOC - 2021 | 27420 |
| SAN JOSE PD - 2019 | 44306 |
| SAN JOSE PD - 2020 | 17978 |
| SAN JOSE PD - 2021 | 17167 |
| SANTA ANA PD - 2021 | 21910 |
| SANTA BARBARA PD - 2021 | 4343 |
| SANTA CLARA CO SO - 2021 | 14469 |
| SANTA ROSA PD - 2021 | 6697 |
| SONOMA CO JR CLG DIST PD - 2021 | 549 |
| SONOMA PD - 2021 | 248 |
| SONOMA SHERIFF - 2021 | 2569 |
| SONOMA STATE UNIVERSITY PD - 2021 | 272 |
| ST UNIV PD-STANISLAUS - 2021 | 202 |
| STOCKTON PD - 2021 | 23937 |
| UC-IRVINE PD - 2021 | 785 |
| UC-SAN FRANCISCO PD - 2021 | 535 |
| VENTURA CO SO - 2021 | 47268 |
| WINDSOR POLICE DEPARTMENT - 2021 | 582 |
+-------------------------------------+----------+
+-------------------------------------+----------+
| agency_yrs | count(0) |
+-------------------------------------+----------+
| 2018 - CHP-HQ A/C, STAFF | 1036484 |
| 2018 - LAPD | 336681 |
| 2018 - LOS ANGELES CO SD | 136635 |
| 2018 - RIVERSIDE CO SO | 44505 |
| 2018 - SAN BERNARDINO CO SO | 62433 |
| 2018 - SAN DIEGO CO SO | 40515 |
| 2018 - SAN DIEGO PD | 89455 |
| 2018 - SAN FRANCISCO PD | 56409 |
| 2019 - CHP-HQ A/C, STAFF | 2175618 |
| 2019 - FRESNO PD | 51849 |
| 2019 - LAPD | 712807 |
| 2019 - LONG BEACH PD | 40524 |
| 2019 - LOS ANGELES CO SD | 196850 |
| 2019 - OAKLAND PD | 24395 |
| 2019 - ORANGE CO SO | 50396 |
| 2019 - RIVERSIDE CO SO | 58379 |
| 2019 - SACRAMENTO CO SHERIFF'S DEPT | 60944 |
| 2019 - SACRAMENTO PD | 68012 |
| 2019 - SAN BERNARDINO CO SO | 157715 |
| 2019 - SAN DIEGO CO SO | 65029 |
| 2019 - SAN DIEGO PD | 187231 |
| 2019 - SAN FRANCISCO PD | 101614 |
| 2019 - SAN JOSE PD | 44306 |
| 2020 - BAKERSFIELD PD | 12170 |
| 2020 - CHP-HQ A/C, STAFF | 1696390 |
| 2020 - DAVIS PD | 2626 |
| 2020 - FRESNO PD | 14738 |
| 2020 - LAPD | 521290 |
| 2020 - LAUSD PD | 1150 |
| 2020 - LONG BEACH PD | 17197 |
| 2020 - LOS ANGELES CO SD | 104238 |
| 2020 - OAKLAND PD | 21076 |
| 2020 - ORANGE CO SO | 39794 |
| 2020 - RIVERSIDE CO SO | 56257 |
| 2020 - SACRAMENTO CO SHERIFF'S DEPT | 43852 |
| 2020 - SACRAMENTO PD | 51391 |
| 2020 - SAN BERNARDINO CO SO | 108979 |
| 2020 - SAN DIEGO CO SO | 38800 |
| 2020 - SAN DIEGO PD | 150575 |
| 2020 - SAN FRANCISCO PD | 38582 |
| 2020 - SAN JOSE PD | 17978 |
| 2021 - ALAMEDA CO SO | 15505 |
| 2021 - ANAHEIM PD | 26568 |
| 2021 - ARCATA PD | 1428 |
| 2021 - BAKERSFIELD PD | 11948 |
| 2021 - BELMONT PD | 1475 |
| 2021 - BERKELEY PD | 5469 |
| 2021 - CAPITOLA PD | 606 |
| 2021 - CARLSBAD PD | 5321 |
| 2021 - CHP-HQ A/C, STAFF | 1749421 |
| 2021 - CONTRA COSTA CO SO | 3131 |
| 2021 - COTATI PD | 1728 |
| 2021 - CSU CHICO | 303 |
| 2021 - CULVER CITY PD | 9454 |
| 2021 - DAVIS PD | 4545 |
| 2021 - EMERYVILLE PD | 1665 |
| 2021 - EUREKA PD | 2906 |
| 2021 - FRESNO CO SO | 19282 |
| 2021 - FRESNO PD | 10816 |
| 2021 - HILLSBOROUGH PD | 646 |
| 2021 - KERN CO SO | 12277 |
| 2021 - LA DEPT OF AIRPORT | 4672 |
| 2021 - LAPD | 429242 |
| 2021 - LAUSD PD-GRAND | 100 |
| 2021 - LIVERMORE PD | 4552 |
| 2021 - LONG BEACH PD | 11986 |
| 2021 - LOS ALTOS PD | 987 |
| 2021 - LOS ANGELES CO SD | 179888 |
| 2021 - MILL VALLEY PD | 808 |
| 2021 - OAKLAND PD | 13782 |
| 2021 - ORANGE CO SO | 46283 |
| 2021 - PETALUMA PD | 3882 |
| 2021 - PIEDMONT PD | 639 |
| 2021 - POMONA PD | 4594 |
| 2021 - RIVERSIDE CO SO | 75779 |
| 2021 - RIVERSIDE PD | 19262 |
| 2021 - ROHNERT PARK PD | 2360 |
| 2021 - SACRAMENTO CO SHERIFF'S DEPT | 33018 |
| 2021 - SACRAMENTO PD | 46671 |
| 2021 - SAN BERNARDINO CO SO | 98576 |
| 2021 - SAN DIEGO CO SO | 21925 |
| 2021 - SAN DIEGO PD | 130112 |
| 2021 - SAN FRANCISCO CO SO | 628 |
| 2021 - SAN FRANCISCO PD - DOC | 27420 |
| 2021 - SAN JOSE PD | 17167 |
| 2021 - SANTA ANA PD | 21910 |
| 2021 - SANTA BARBARA PD | 4343 |
| 2021 - SANTA CLARA CO SO | 14469 |
| 2021 - SANTA ROSA PD | 6697 |
| 2021 - SONOMA CO JR CLG DIST PD | 549 |
| 2021 - SONOMA PD | 248 |
| 2021 - SONOMA SHERIFF | 2569 |
| 2021 - SONOMA STATE UNIVERSITY PD | 272 |
| 2021 - ST UNIV PD-STANISLAUS | 202 |
| 2021 - STOCKTON PD | 23937 |
| 2021 - UC-IRVINE PD | 785 |
| 2021 - UC-SAN FRANCISCO PD | 535 |
| 2021 - VENTURA CO SO | 47268 |
| 2021 - WINDSOR POLICE DEPARTMENT | 582 |
+-------------------------------------+----------+
@rkiddy Thanks, this is a big help on where to look for additional California stops data.
The difference in date ranges is because the RIPA law was phased in. The largest agencies had to start reported first and more agencies report each year.
Are you asking about our catalog of sources? That is here.
If you'd like to provide further help on this issue, there are a few other items (which help on any of them would be valuable):
- We currently are not able to load the state RIPA data into our tool. The data is stored as multiple Excel or CSV files inside a zip file. We need a new data loader that can extract data from this case so that we can add the RIPA data to our source list.
- Some police departments post data on their own sites in addition to it being posted by the state. These datasets tend to be better because (A) they don't require downloading the entire state's data to access and (B) sometimes they add additional fields to the data. We haven't looked through all the locations that you have listed to see if their data can be accessed from their own website. You can see the locations that we have already checked here.
By the way, there was an error regarding a small detail in the original issue statement. I have corrected that.
I knew about the RIPA law phase-in.
As far as a source for the CA RIPA data, I have put the data into mysql tables. Can that be used as a data source? I have an app sitting right next to the data which is not doing much. It can, though, provide REST services for, for example, an agency, or an agency-year.
The code to read the spreadsheet and put it into mysql was not very much. It was just a bother to set up and it takes time to run. See https://github.com/rkiddy/ca_ripa for the code.
I would be interested to see what LEOs in CA are publishing themselves. I suspect that, yes, the data gets put up quicker and may be in an easier-to-use format, but for the long run, it will be better to use the RIPA data. RIPA data includes all data, even the data that the LEO does not want to put up. And it is a standard that will represent the data of all 58 counties in California.
FYI, here are the places in CA that we have data from more from more than one source. All of the others (above) are RIPA-only.
ANAHEIM PD - STOPS - 2021-01-01 - 2021-12-31 - CA RIPA
Anaheim PD - TRAFFIC STOPS - 01/01/2012 - 03/14/2017 - Stanford
BAKERSFIELD PD - STOPS - 2020-01-01 - 2021-12-31 - CA RIPA
Bakersfield PD - TRAFFIC STOPS - 03/09/2008 - 03/09/2018 - Stanford
CHP-HQ A/C, STAFF - STOPS - 2018-07-01 - 2021-12-31 - CA RIPA
California HP - TRAFFIC STOPS - 07/01/2009 - 06/30/2016 - Stanford
LONG BEACH PD - STOPS - 2019-01-01 - 2021-12-31 - CA RIPA
Long Beach PD - STOPS - 01/01/2008 - 12/31/2017 - Stanford
OAKLAND PD - STOPS - 2019-01-01 - 2021-12-31 - CA RIPA
Oakland PD - STOPS - 04/01/2013 - 12/31/2017 - Stanford
SAN DIEGO PD - STOPS - 2018-07-01 - 2021-12-31 - CA RIPA
San Diego PD - TRAFFIC STOPS - 01/01/2014 - 03/31/2017 - Stanford
SAN FRANCISCO PD - STOPS - 2018-07-01 - 2020-12-31 - CA RIPA
San Francisco PD - TRAFFIC STOPS - 01/01/2007 - 06/30/2016 - Stanford
SAN JOSE PD - STOPS - 2019-01-01 - 2021-12-31 - CA RIPA
San Jose PD - STOPS - 09/01/2013 - 03/31/2018 - Stanford
SANTA ANA PD - STOPS - 2021-01-01 - 2021-12-31 - CA RIPA
Santa Ana PD - STOPS - 06/11/2014 - 04/13/2018 - Stanford
STOCKTON PD - STOPS - 2021-01-01 - 2021-12-31 - CA RIPA
Stockton PD - TRAFFIC STOPS - 01/01/2012 - 12/31/2016 - Stanford
Your points about the RIPA data being a better data source make sense. Perhaps if we are putting up additional stops data for CA agencies, there should be a good reason:
- It's released more quickly
- It provides additional time coverage (like those Stanford datasets)
- It provides more information (data not part of RIPA...unlikely because RIPA has a lot of requirements)
As far as a source for the CA RIPA data, I have put the data into mysql tables. Can that be used as a data source? I have an app sitting right next to the data which is not doing much. It can, though, provide REST services for, for example, an agency, or an agency-year.
We do really need to have the RIPA data available. We have discussed alternative ways for hosting data before for some cases where PDs took data down. This could be a good trial run since the data on Open Justice isn't very user-friendly.
I would say that we can make most data that is available online in a machine-readable format into a data source. We would just need to write a new class to handle that case, which is not a big issue. How to put data online is really outside my area of expertise. When you say that you have an app right now that can provide REST services, does that mean that someone could already make API requests from it or that you have a tool that would provide REST services but would need to be hosted somewhere (or something else, I really don't know a lot about this part)?
Adding @potto216 to see if he has any thoughts/comments.
@sowdm Thanks. I run several flask applications at opencalaccess.org but I am having problems at the moment. My existing apps are working but new deployments are not. I am putting some TLC into my server this weekend.
I have a bog simple REST app for the RIPA data. It has parameters of {year} and {agency} and it returns a big bunch of JSON data and that is it. I have a few other ideas for searchers but will do those after I have the basic things up.
@rkiddy Sounds good. Currently, we would only really need to filter by year and agency anyway so that is perfect. Maybe once you have everything back up and running, you can either:
- provide some example queries to help us play around with it OR
- we could schedule a Zoom call to chat in more detail.
How does that sound?
@rkiddy Hey, just following up to see if you ever got your flask application for the CA stops data back up and running