Instantly share code, notes, and snippets.

Embed
What would you like to do?
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)
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 ;
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);
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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment