Analyzing the NYC Parking Violations Dataset

ON THIS PAGE

Understanding the schema
Loading data into Firebolt
Option 1: COPY FROM
Option 2: External table
Sample SQL queries for analysis
Count of violations by violation code
Violations per day
Top vehicle makes with the most violations
Violations by issuing agency

New York City's extensive parking-violation records provide rich analytical opportunities. This guide explains the dataset schema, demonstrates loading the data into Firebolt, and provides sample SQL queries for analysis.

## Understanding the schema

The dataset contains 44 columns capturing violation details:

Column Name	Data Type	Description
`summons`	Integer	Unique identifier for each violation
`plateid`	Text	License plate identifier
`registration`	Text	Vehicle registration identifier
`plate`	Text	License plate information
`issue_date`	Date	Date the violation was issued
`violation_code`	Integer	Code indicating the violation type
`vehicle_body`	Text	Vehicle body type
`vehicle_make`	Text	Vehicle manufacturer
`issuing_agency`	Text	Agency responsible for issuing
`street_code1`	Integer	Primary street code
`street_code2`	Integer	Secondary street code
`street_code3`	Integer	Another secondary street code
`vehicle_expiration`	Text	Vehicle registration expiration
`violation_location`	Integer	Violation location code
`violation_precinct`	Integer	Precinct where the violation occurred
`issuer_precinct`	Integer	Issuing officer precinct
`issuer_code`	Integer	Issuing officer code
`issuer_command`	Text	Issuing officer command
`issuer_squad`	Text	Issuing officer squad
`violation_time`	Text	Time of the violation
`time_first_observed`	Text	When the violation was first observed
`violation_county`	Text	County of the violation
`violation_infront_opposite`	Text	Position relative to the address
`house_integer`	Text	House number identifier
`street_name`	Text	Street name
`intersecting_street`	Text	Intersecting street name
`date_first_observed`	Text	Date first observed
`law_section`	Integer	Relevant law section
`sub_division`	Text	Subdivision information
`violation_legal_code`	Text	Legal code
`days_parking_in_effect`	Text	Days when parking restrictions apply
`from_hours_in_effect`	Text	Starting time of restrictions
`to_hours_in_effect`	Text	Ending time of restrictions
`vehicle_color`	Text	Vehicle color
`unregistered_vehicle`	Text	Unregistered vehicle indicator
`vehicle_year`	Text	Vehicle manufacture year
`meter_integer`	Text	Meter number
`feet_from_curb`	Integer	Distance from curb in feet
`violation_post_code`	Text	Postal code
`violation_description`	Text	Violation description
`no_standing_or_stopping_violation`	Text	Standing/stopping violation indicator
`hydrant_violation`	Text	Hydrant violation indicator
`double_parking_violation`	Text	Double parking indicator

## Loading data into Firebolt

There are two ways to load the data.

### Option 1: COPY FROM

Let Firebolt infer the schema and create the table automatically:

COPY INTO nyc_parkingviolations FROM 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nycparking/parquet/'WITH PATTERN="*.parquet" AUTO_CREATE=TRUE TYPE=PARQUET;

### Option 2: External table

First create an external table over the Parquet files in S3:

CREATE EXTERNAL TABLE ex_nyc_parkingviolations (  summons Integer,  plateid Text,  registration Text,  plate Text,  issue_date TEXT,  violation_code Integer,  vehicle_body Text,  vehicle_make Text,  issuing_agency Text,  street_code1 Integer,  street_code2 Integer,  street_code3 Integer,  vehicle_expiration Text,  violation_location Integer,  violation_precinct Integer,  issuer_precinct Integer,  issuer_code Integer,  issuer_command Text,  issuer_squad Text,  violation_time Text,  time_first_observed Text,  violation_county Text,  violation_infront_opposit Text,  house_integer Text,  street_name Text,  intersecting_street Text,  date_first_observed Integer,  law_section Integer,  sub_division Text,  violation_legal_code Text,  days_parking_in_effect Text,  from_hours_in_effect Text,  to_hours_in_effect Text,  vehicle_color Text,  unregistered_vehicle Integer,  vehicle_year Text,  meter_integer Text,  feet_from_curb Integer,  violation_post_code Text,  violation_description Text,  no_standing_or_stopping_violation Text,  hydrant_violation Text,  double_parking_violation Text)URL = 's3://firebolt-sample-datasets-public-us-east-1/nyc_sample_datasets/nycparking/parquet/'OBJECT_PATTERN = '*.parquet'TYPE = (PARQUET);

Then create the internal table:

CREATE TABLE nyc_parkingviolations (  summons Integer,  plateid Text,  registration Text,  plate Text,  issue_date Date,  violation_code Integer,  vehicle_body Text,  vehicle_make Text,  issuing_agency Text,  street_code1 Integer,  street_code2 Integer,  street_code3 Integer,  vehicle_expiration Integer,  violation_location Integer,  violation_precinct Integer,  issuer_precinct Integer,  issuer_code Integer,  issuer_command Text,  issuer_squad Text,  violation_time Text,  time_first_observed Text,  violation_county Text,  violation_infront_opposit Text,  house_integer Text,  street_name Text,  intersecting_street Text,  date_first_observed Integer,  law_section Integer,  sub_division Text,  violation_legal_code Text,  days_parking_in_effect Text,  from_hours_in_effect Text,  to_hours_in_effect Text,  vehicle_color Text,  unregistered_vehicle Integer,  vehicle_year Text,  meter_integer Text,  feet_from_curb Integer,  violation_post_code Text,  violation_description Text,  no_standing_or_stopping_violation Text,  hydrant_violation Text,  double_parking_violation Text);

Finally, insert from the external table, converting issue_date to a timestamp:

INSERT INTO nyc_parkingviolationsSELECT summons ,  plateid ,  registration ,  plate ,  TO_TIMESTAMP(issue_date,'YYYY-MM-DD') ,  violation_code ,  vehicle_body ,  vehicle_make ,  issuing_agency ,  street_code1 ,  street_code2 ,  street_code3 ,  vehicle_expiration ,  violation_location ,  violation_precinct ,  issuer_precinct ,  issuer_code ,  issuer_command ,  issuer_squad ,  violation_time ,  time_first_observed ,  violation_county ,  violation_infront_opposit ,  house_integer ,  street_name ,  intersecting_street ,  date_first_observed ,  law_section ,  sub_division ,  violation_legal_code ,  days_parking_in_effect ,  from_hours_in_effect ,  to_hours_in_effect ,  vehicle_color ,  unregistered_vehicle ,  vehicle_year ,  meter_integer ,  feet_from_curb ,  violation_post_code ,  violation_description ,  no_standing_or_stopping_violation ,  hydrant_violation ,  double_parking_violationFROM ex_nyc_parkingviolations;

## Sample SQL queries for analysis

### Count of violations by violation code

Identify the most frequently occurring parking violations.

SELECT violation_code, violation_description, COUNT(*) AS violation_countFROM nyc_parkingviolationsGROUP BY violation_code, violation_descriptionORDER BY violation_count DESC;

### Violations per day

Reveal temporal patterns in violation issuance.

SELECT issue_date, COUNT(*) AS daily_violationsFROM nyc_parkingviolationsGROUP BY issue_dateORDER BY issue_date;

### Top vehicle makes with the most violations

See which vehicle manufacturers are most frequently cited.

SELECT vehicle_make, COUNT(*) AS violation_countFROM nyc_parkingviolationsGROUP BY vehicle_makeORDER BY violation_count DESCLIMIT 10;

### Violations by issuing agency

Categorize violations by the responsible enforcement agency.

SELECT issuing_agency, COUNT(*) AS violation_countFROM nyc_parkingviolationsGROUP BY issuing_agencyORDER BY violation_count DESC;

The dataset invites further exploration to uncover insights about enforcement patterns and vehicle characteristics.