conviction-descriptions.md

mysql> select distinct(Conviction_Description) from convictions where Conviction_Description like '%SPEED%';
+----------------------------------------------------+
| Conviction_Description                             |
+----------------------------------------------------+
| SPEED IN ZONE                                      |
| SPEEDING                                           |
| SPEEDING +15                                       |
| SPEED NOT REASONABLE AND PRUDENT                   |
| SPEEDING IN A WORK ZONE OR ON A RESTRICTED HIGHWAY |
| DECREASED SPEED/STOPPING WITHOUT SIGNALING         |
| SPEEDING PAST SCHOOL                               |
| SPEED IN ZONE JUNIOR OPERATOR                      |
| DECREASED SPEED WITHOUT SIGNALING                  |
| SPEEDING 25 MPH OR MORE ABOVE LEGAL LIMIT          |
| SPEED CONTEST                                      |
| SPEEDING JUNIOR OPERATOR                           |
| IMPRUDENT SPEED WHILE APPROACHING A HORSE          |
| SPEEDING PAST SCHOOL JUNIOR OPERATOR               |
+----------------------------------------------------+
14 rows in set (0.20 sec)

conviction-rate.sql

DELIMITER $$
DROP PROCEDURE IF EXISTS `violations`.`GetConvictionRatesByYear`$$
CREATE PROCEDURE `violations`.`GetConvictionRatesByYear` (
	IN Year INT,
	IN Minimum INT
)
BEGIN
	SET @Year1 = Year;
	SET @Year2 = Year;
	SET @Minimum = Minimum;
	PREPARE STMT FROM
	'SELECT Tickets.Court AS \'Court\', Tickets.number AS \'Tickets issued\', Convictions.number AS \'Convictions\', (Convictions.number / Tickets.number) AS \'Conviction rate\'
	from
	(SELECT `Court`, COUNT(1) AS \'number\' FROM tickets WHERE `Violation_Year` = ? AND `Violation_Description` LIKE \'%SPEED%\' AND `Violation_Description` NOT IN (\'UNLAWFUL SPEEDOMETER\', \'VEH WITH PERFORMANCE SPEED LESS THAN 55 MPH REG-9\') GROUP BY `Court`) AS Tickets
	inner join
	(SELECT `Standard_Court_Name`, sum(`Count`) AS \'number\' FROM convictions WHERE `Year_of_Conviction` = ? AND `Conviction_Description` LIKE \'%SPEED%\' GROUP BY `Standard_Court_Name`) AS Convictions
	ON TRIM(Tickets.Court) = TRIM(Convictions.Standard_Court_Name)
	WHERE Tickets.number > ?
	ORDER BY `Conviction rate` ASC';
	EXECUTE STMT USING @Year1, @Year2, @Minimum;
END$$

DROP PROCEDURE IF EXISTS `violations`.`GetConvictionRates`$$
CREATE PROCEDURE `violations`.`GetConvictionRates` (
	IN Minimum INT
)
BEGIN
	SET @Minimum = Minimum;
	PREPARE STMT FROM
	'SELECT Tickets.Court AS \'Court\', Tickets.number AS \'Tickets issued\', Convictions.number AS \'Convictions\', (Convictions.number / Tickets.number) AS \'Conviction rate\'
	from
	(SELECT `Court`, COUNT(1) AS \'number\' FROM tickets WHERE `Violation_Description` LIKE \'%SPEED%\' AND `Violation_Description` NOT IN (\'UNLAWFUL SPEEDOMETER\', \'VEH WITH PERFORMANCE SPEED LESS THAN 55 MPH REG-9\') GROUP BY `Court`) AS Tickets
	inner join
	(SELECT `Standard_Court_Name`, sum(`Count`) AS \'number\' FROM convictions WHERE `Conviction_Description` LIKE \'%SPEED%\' GROUP BY `Standard_Court_Name`) AS Convictions
	ON TRIM(Tickets.Court) = TRIM(Convictions.Standard_Court_Name)
	WHERE Tickets.number > ?
	ORDER BY `Conviction rate` ASC';
	EXECUTE STMT USING @Minimum;
END$$
DELIMITER ;

data-load.sql

DROP DATABASE IF EXISTS violations;
CREATE DATABASE violations;
USE violations;

DROP TABLE IF EXISTS tickets;
CREATE TABLE tickets (
	`Violation_Charged_Code` VARCHAR(30) NOT NULL,
	`Violation_Description` VARCHAR(80) NOT NULL, 
	`Violation_Year` INTEGER NOT NULL, 
	`Violation_Month` INTEGER NOT NULL, 
	`Violation_Day_of_Week` VARCHAR(9) NOT NULL, 
	`Age_at_Violation` INTEGER, 
	`State_of_License` VARCHAR(25) NOT NULL, 
	`Gender` VARCHAR(1) NOT NULL, 
	`Police_Agency` VARCHAR(80) NOT NULL, 
	`Court` VARCHAR(80) NOT NULL,
	`Source` VARCHAR(30) NOT NULL,
	INDEX (Violation_Month, Violation_Year, Violation_Day_of_Week, Gender, Police_Agency, Court)
);

DROP TABLE IF EXISTS convictions;
CREATE TABLE convictions (
	`Year_of_Conviction` INTEGER NOT NULL, 
	`Court_Name` VARCHAR(60) NOT NULL, 
	`Standard_Court_Name` VARCHAR(60) NOT NULL, 
	`Conviction_Description` VARCHAR(150) NOT NULL, 
	`Count` INTEGER NOT NULL,
	INDEX (Year_of_Conviction, Standard_Court_Name, Conviction_Description)
);

-- Data from this data set -> https://data.ny.gov/Transportation/Traffic-Tickets-Issued-Four-Year-Window/q4hy-kbtf
LOAD DATA INFILE '/tmp/tickets.csv' INTO TABLE tickets
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(Violation_Charged_Code,Violation_Description,Violation_Year,Violation_Month,Violation_Day_of_Week,Age_at_Violation,State_of_License,Gender,Police_Agency,Court,Source);

-- Data from this data set (with slight mods) -> https://data.ny.gov/Transportation/DMV-Reportable-Traffic-Ticket-Conviction-Counts-by/gwz5-m9ja 
LOAD DATA INFILE '/tmp/convictions-standard-court-names.csv' INTO TABLE convictions
	FIELDS TERMINATED BY ','
	ENCLOSED BY '"'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES
	(Year_of_Conviction,Court_Name,Standard_Court_Name,Conviction_Description,Count);

violation-descriptions.md

mysql> select distinct(Violation_Description) from tickets where Violation_Description like '%SPEED%';
+---------------------------------------------------+
| Violation_Description                             |
+---------------------------------------------------+
| SPEED IN ZONE 11-30                               |
| SPEED IN ZONE                                     |
| SPEED NOT REASONABLE AND PRUDENT                  |
| SPEED OVER 55 ZONE                                |
| SPEED 11-30 OVR LMT                               |
| SPEEDING IN POSTED WORK ZONE                      |
| UNLAWFUL SPEEDOMETER                              |
| SPEEDING IN SCHOOL ZONE                           |
| SPEED WORK ZONE11-30                              |
| SPEED IN ZONE 31+                                 |
| SPEED IN ZONE 1-10                                |
| UNREASONABLE SPEED/SPECIAL HAZARDS                |
| SPEED-PSTD 11-30 NYC                              |
| DECREASED SPEED OR STOPPED W/OUT SIGNALING        |
| SPEED OVR LMT 31+                                 |
| JR SPEED IN ZONE                                  |
| SPEED PST SCH 1 - 10                              |
| SPEED WORK ZONE 31+                               |
| UNAUTHORIZED SPEED CONTEST                        |
| SPEED-PSTD 1-10 NYC                               |
| SPEED 1-10 OVR LIMIT                              |
| JUNIOR SPEED OVER 55 ZONE                         |
| SPEED PAST SCH 11-30                              |
| IMPRUDENT SPEED APPROACHING HORSE                 |
| SPEEDING ON RESTRICTED HIGHWAY                    |
| SPEED NOT REASONABLE AND PRUDENT ATV              |
| SPEED PAST SCH 31+                                |
| DECREASED SPEED W/OUT SIGNALING                   |
| VEH WITH PERFORMANCE SPEED LESS THAN 55 MPH REG-9 |
| SPEED-POSTED 31+ NYC                              |
| JR SPEED PAST SCHOOL                              |
| SPEED-JR 31+ OVER                                 |
| SPEED W/RADAR DETECTOR VEH OVER 18000             |
| SPEED W/RADAR DETECTOR VEH OVER 10000 LBS         |
| SPEED WORK ZONE 1-10                              |
| SPEED+RADAR11-30 OVR                              |
| SPEED+RADAR 1-10 OVR                              |
+---------------------------------------------------+
37 rows in set (16.03 sec)