nstory / wokewindows Goto Github PK

For each officer, calculate their rank among 2019 earners, and store it in a new field "earnings_rank" or similar. Display this field on the officer's page, something like "#1 highest earner in 2019". The algorithm to calculate this should be something like:

1. take all employees with at least some earnings in 2019
2. sort them by total earnings descending
3. assign the highest earner 1, the second-highest 2, etc.

A good place to implement this would be: app/lib/populater/officers.rb.

Add columns to the officers export (which will also appear in any export joined with the officers export) based on IA data.

Set the @layout_application_description variable on the following pages:

app/views/citations/show.html.erb
app/views/details/show.html.erb
app/views/incidents/show.html.erb
app/views/swat/show.html.erb

Look at app/views/officers/show.html.erb and app/views/field_contacts/show.html.erb for example descriptions. These descriptions are used in the meta description and og:description fields, which are displayed by search engines and twitter respectively.

Show at the top of each officer page a "scorecard" that surfaces the most important aggregate stats and biographical information.

• biographical information: name, rank, organization, etc.
• internal affairs data
  • IA Score
  • # of allegations, # of sustained findings
  • whether there is a sustained finding for each of: use of force, conformance to laws, and/or truthfulness
• earnings
  • total earnings in 2019
  • interesting facts e.g. "in the top 1% of earners for 2019" or "overtime earnings greater than regular salary"
• excessive # of detail shifts (maybe > 200 in a single year?)
• lives outside Boston

The card should be compact. If information isn't relevant e.g. if the officer has no use of force findings, don't display that information.

The priority for the card should be to surface red/yellow flags.

Finally, the card should be attractive and easily shared. I want users to e.g. take a screenshot of the card and tweet it, message it to a friend, etc. because it conveys relevant information clearly and compactly.

Officers that were present for the field contact are often listed in the narrative. See e.g. FC19001965 which is signed off B104F (BENT/LAYDEN). Bent is the officer who wrote the FIO, and Layden, I believe, is his partner (though there are several Laydens in the database, so, this reference is ambiguous).

We should:

1. create a new table field_contacts_officers to act as a join table between field_contacts and officers (TBD if the contact_officer and supervisor should be inserted in this table)
2. create a process that identifies possible references based on last name
3. create a UI for volunteers to confirm the references are correct and to disambiguate
4. link to the officers from the field_contact page
5. add the field_contacts to the table on the officer page

The UI component also needs to be built for crowdsourcing the linking of officers to articles. Articles are going to be higher impact, so, this ticket should be completed later.

For a particular incident, we may get a street address from the public journal and a lat-long from the crime incident reports file.

Currently, we mark the location on the map based on the lat-long, but sometimes the street address and the lat-long disagree, see this incident for an example:
https://www.wokewindows.org/incidents/202038832

For that incident, the lat-long shows a location on Harrison Ave next to D-4 station, while the street address is for a location near Symphony Hall. When the two disagree, the lat-long seems to cluster around the district stations, leading me to believe the street address is correct.

We need to do the following:

1. for incidents where we have a street address, run it through a geocoder to determine a latitude/longitude
2. store the geocoded lat/long in new fields: geocoded_latitude, geocoded_longitude
3. store the lat/long from the crime incident reports file in new fields: file_latitude, file_longitude (better names TBD!)
4. populate the latitude and longitude fields in the incidents table with 1. the geocoded location (if it exists) and 2. fallback on the lat-long provided in the crime incidents file

Note: we already have infrastructure for geocoding Boston addresses and caching results; see app/models/geocode.rb

Take IAD2019-0417 as an example. Each of the allegations listed seems to reference a rule from the BPD Rules and Procedures.

Respectful Treatment -> Rule_102_Amended.pdf -> Sec. 9 RESPECTFUL TREATMENT

Use of Force -> RULE 304 - USE OF NON-LETHAL FORCE (there isn't a single section that sums up the policy, unfortunately)

Conduct Unbecoming -> Rule_102_Amended.pdf -> Sec. 3 CONDUCT

It would be cool if the user could click on the allegation to see the text from the associated rule and be provided a citation/link to the full rule for further reading.

In the 2010 - 2020 spreadsheet, I think there are really five types of allegation:
Conduct Unbecoming
Neg.Duty/Unreasonable Judge
Respectful Treatment
Self Identification
Use of Force

Some definitions from the most recent CO-OP report:
https://www.boston.gov/sites/default/files/file/document_files/2018/07/annual_report_2017_2016.pdf#page=10

https://bpdfio.info/ shows the location of field contacts based on the zip code present in the record. We should do something similar. We already have a map component that is used on the app/views/incidents/show.html.erb and app/views/details/show.html.erb pages. We should:

1. expand app/javascript/controllers/map_controller.js to support a zip code parameter that highlights the zip as a region
2. add the map to app/views/field_contacts/show.html.erb

Just for interest, I tried navigating to pages for record IDs that I know don't exist in the database. I found that doings so leads to a server error for field contacts, incidents, and IA complaints.

See, for example:

• field contacts: https://www.wokewindows.org/field_contacts/1234567
• incidents: https://www.wokewindows.org/incidents/1234567
• IA complaints: https://www.wokewindows.org/complaints/1234567

It would be better to return "404 Not Found" in these cases, as we do for other record types.

Update either the controller or the show.html.erb view for each of the above record types so that navigating to a non-existent record leads to a 404 error, not a 500 error.

We should provide data exports, specifically joined datasets of cleaned and normalized data ready for analysis.

1. create a task (one for each export?) that produces the CSV file; invoke the task from scripts/daily.sh and copies the result to S3

2. create a page "Data Exports" that lists the available exports, displays the last modified time from S3 (cache this value), and thoroughly documents the files

Ideas for exports (probably do one to start; and then create tickets for the others):

• officers.csv the officers table
• complaints_officers.csv the complaint_officers table (which, yeah, I named wrong) joined with the associated complaint and officer
• field_contact_names.csv the field_contact_names table joined with the field_contacts table joined with the officers table through both contact_officer_id and supervisor_id
• citations joined with officers
• compensations joined with officers
• details joined with officers
• incidents

The boston_nibrs_foia.csv.gz file has the incident date and time, but we aren't currently ingesting it. We should import the data, but only for incidents that don't already have date/time data; the NIBRS data has the date and hour, but not the minute, which is present in other data sources.

These are the exports I did not implement in #33

• field_contact_names.csv the field_contact_names table joined with the field_contacts table joined with the officers table through both contact_officer_id and supervisor_id
• citations joined with officers
• compensations joined with officers
• details joined with officers
• incidents

On the page for an officer there is a chart that shows the officer's earnings each year and a table that shows the same. In the table, there is a percentage next to each type of earnings that shows e.g. 19% of the officer's total earnings were overtime pay. When you mouse-over a bar in the earnings chart, you can see the amount of earnings, but the percent is not shown (see attached screenshot).

Add the percent of total to the mouseover text so it will read e.g. "Overtime: $68,964.13 (19%)"

The page app/views/complaints/show.html.erb already sets the @layout_application_description variable, but it doesn't list the officers in the complaint. Implement logic along these lines:

1. if there is one officer listed in the complaint, list their name in the description e.g. "Citizen Complaint received Aug 1, 2020 against John Smith"

2. if there are multiple officers listed in the complaint, say something like "against multiple officers"

3. if there are no officers listed in the complaint (this sometimes happens!) probably drop the "against xxx" part altogether

A given traffic citation e.g. T1943062 contains multiple offenses, each of which references a particular law in the M.G.L. Link the offense to the relevant chapter and section based on the offense code.

For example:
909D0 | WINDOW OBSTRUCTED/NONTRANSPARENT * c90 9D

Should link to:
https://malegislature.gov/Laws/GeneralLaws/PartI/TitleXIV/Chapter90/Section9D

The documentation is a little sparse and it doesn't currently explain the differences between Mark43 and "New RMS" records:

https://www.wokewindows.org/pages/help#field_contact
https://www.wokewindows.org/pages/help#field_contact_name
https://www.wokewindows.org/pages/help#fio_records_2011_2015

Here is a list of BPD personnel accurate as of 7/15/2020:
https://www.muckrock.com/foi/boston-3/boston-police-department-personnel-roster-98136/

The ALPHa_LISTING_BPD_FRONT_DESK_Badges_included.xlsx file has the most interesting data, especially the "ALPHa_LISTING_BPD_FRONT_DESK_Ba" and "ALPHa_LISTING_BPD_FRONT_DESK_B1" worksheets in that spreadsheet.

We should use it to do the following:

1. Create officer records. currently, for officers hired after ~2015 (when the last alpha listing spreadsheet was posted), I had to play a lot of games to create officer records based on mentions in the incident log and fuzzy matching to earnings reports. Using this report will be much simpler and it should add some employees we are missing.

2. Populate a new field with the organization the officer is part of.

3. Determine which officers are no longer active. If the ID doesn't appear in this spreadsheet, we should mark the officer as "retired"

4. Populate the badge field on the officer. I think it would be reasonable to display this now too given the data is recent enough to be useful.

5. Populate title field on officer (but only if it isn't already populated!) this appears to be the same as the title field in the earnings report, but it's truncated

6. Maybe create a rank field on officer. We already mostly get this from the title field, so not a must-have

In #24 I imported opinions from CourtListener. That site also contains the RECAP archive, which contains "millions of PACER documents and dockets." We should look into importing those documents also!

Create a flag on officers that indicates whether the officer was employed by BPD as of 7/15/2020. Any employee who appears in 2020 Alpha Listing is active.

Also! Officers are automatically created by Populater::OfficersFromIncidents if they appear in the district journal; these should be created with active=true.

Use the lat/long we have for each incident to determine which block group it lies in using the Census Shapefiles. Heroku supports PostGIS, so we could probably use that and activerecord-postgis-adapter to do the lookup.

The "block group" is the smallest geographic area for which the census provides data as part of the American Community Survey (ACS). I belive a data scientist or GIS specialist could then join incident data with demographic data from American Community Survey 1-Year Data (2011-2018).

https://www.muckrock.com/foi/massachusetts-1/boston-police-nibrs-files-98203
^ Boston_NIBRS_FOIA.xlsx

This file covers October - December 2019.

This file contains incident numbers, which we can connect to incidents currently in the database, and a few interesting fields we don't currently get from other sources:

1. Attempted or Completed (is this referring to whether the crime was actually completed?)
2. Location Type
3. Method of Entry
4. Incident Clearance (e.g. "Cleared by Arrest")
5. Exceptional Clearance Date

It also has some (approximately 100) incidents that were not present in other sources.

For FieldContact and FieldContactName records, switch to using the cleaned data prepared by @jacoblurye
https://github.com/jacoblurye/bpd-fio-data

• migrate the field_contacts and field_contact_names tables to mirror the fields in bpd-fio-data e.g. drop vehicle_style, deceased columns
• that data does not include 2015; we can create a separate ticket to re-add that year later

Very cool project. Thanks for your work.

Have you considered adding an API? I'm curious for my own use and also to see if there are reasons you've explicitly decided not to do so.

I'm thinking that an API where I could, for example, retrieve the number of internal affairs complaints per month... or see the number of officers per neighborhood ...would be useful.

See attached image taken on this page https://www.wokewindows.org/incidents/202038832

Looks like we need to adjust the z-index of either the map zoom control or the menu.

Contributions to federal candidates:
https://www.fec.gov/data/receipts/individual-contributions/?contributor_employer=boston+police
^ contributions by people who wrote in their employer "boston police" (also catches "boston police dept")

https://www.fec.gov/data/receipts/individual-contributions/?contributor_occupation=POLICE&contributor_employer=boston
^ contributions by people who wrote "boston" in their employer (e.g. "City of Boston") and "police" in their occupation

Contributions to Massachusetts state candidates:
https://www.ocpf.us/Reports/SearchItems
^ I can't make a link to the search results. a search for "boston" + "police" returns 3,423 results from 01/01/2000 to present

It would be cool if we could:

1. provide the usual data table showing the records
2. break down contributions by political affiliation: Democrat, Republican, Other; show the total $ for each and the % share

CourtListener has opinions from federal courts and from MA appellate courts. A search for opinions containing the phrase "boston police" and filed after 1980-01-01 gives ~1.1k results. I believe this includes civil rights claims under section 1983 (I'm not 100% certain what that means, but I think it's important).

CourtListener also has documents from PACER in their RECAP Archive. A search for cases containing the phrase "boston police" and filed after 1980-01-01 returns ~1k results.

These cases could be imported as articles, including applying officer-name-matching logic.

Rule 101 contains descriptions of the various organizations within the BPD. I believe the section numbers indicate where in the hierarchy each organization falls.

Create a page that shows all the organizations, arranged according to reporting structure.

Create a page for each organization that shows:

1. the description of the organization taken from Rule 101
2. the list of officers in that organization

This could have SEO benefits. It will channel "link juice" from the home page -> organizations index -> organization page -> officers pages.

In #27 I implemented the import process for campaign contributions and created a data export for them.

1. create the data table where users can search, sort, etc. the records
2. add the table to each officer's page, so you can see their contributions
3. calculate for each officer the amount contributed to Democrats vs Republicans. display this in the officer's profile, and add it as a column in the officers export file