search

openjusticebaltimore / gttf

Investigation of the prevalence of Baltimore Police Department's Gun Trace Task Force testimony in State of Maryland judicial cases
4 stars 2 forks source link

Network analysis of officers #5

Open kbmorales opened 4 years ago

kbmorales commented 4 years ago

We've toyed a bit with case connections between "regular" officers and indicted officers--we should revisit this and talk about how this could be used to investigate further officers?

pmatchette commented 4 years ago

Came up with a query to link cops that have worked together on cases that shows charges and disposition of charges. Used our good friend HERSL as the sample.

SELECT DISTINCT cases1.*, RP.name, RP.connection, CHG.description, CHG.disposition FROM (SELECT cases.case_number, dsk8_related_persons.name FROM cases INNER JOIN dsk8_related_persons ON cases.case_number = dsk8_related_persons.case_number WHERE dsk8_related_persons.name LIKE '%HERSL%' AND dsk8_related_persons.connection LIKE '%POLICE%') AS cases1 INNER JOIN dsk8_related_persons RP ON cases1.case_number = RP.case_number INNER JOIN dsk8_charges CHG ON CHG.case_number = cases1.case_number WHERE RP.connection LIKE '%POLICE%';

kbmorales commented 4 years ago

Definitely would be interesting to look at more officers who are connected with already indicted ones