SHOT: PHP-MySQL System

Building a National Database for Police Shootings in the United States

 

Gun violence and in particular police shootings have become prominent in the news media in the United. States.  However, there is currently no national database of police incidents. This is a project to develop a web-enabled database to collect, edit, query and analyze police shootings. Once developed the database will be used for research, informing policy and training.   The SHOT Project (Statistics Help Officer Tactics) has designed a Microsoft Access prototype and has a pretty complete schema for the database.  This project will validate the database design, build a MySQL database with PHP web interface.

The data collected to date is from open sources (news feeds, newspapers, etc.) and placed in a spreadsheet.  There are 4 categories of data: Source, Incident, Suspect/Victim, and police officer with a total of more than 50 fields or variables:

Category

Attributes

Source

Title, date, author, publication, URL, full-text.

Incident

Date (day, month, season, year), approximate time (early morning, morning, noon, afternoon, evening, night and midnight), number of officers on the scene, number of officers, who fired their guns, number of rounds hit the target, part of target being hit (head/neck, torso, limbs) and geographical information (region, state, city, address, location type).

Suspect

Age, race, gender, fatality, mental status, weapon possession, type of aggression, use of vehicle, occurrence of any foot or car chase, gang affiliation, nationality, fatality/injury and whether a lawsuit was filed.

Officer

Race, gender, experience, affiliation, department type, assignment, status (on/off duty) and type of police call.

 

The data flow is to create an incident, for which there can be multiple suspects and officers.  Multiple sources can add to the data. See Data Entry Flow [1].

The current database schema uses 4 main tables for each component of an incident [1].

The interface can be modified, and note that forms are select by using tabs [1]. 

The project components include:

1.       Building the schema and database in for MySQL

2.       Populating the Database with data from the existing spreadsheet

3.       Build data entry, data query, data editing and reporting functions

4.       Creating the PHP front-interface

References

[1] 2015 Conference Paper.