Skip to content

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
You can’t perform that action at this time.