Avoiding Parking Tickets

Visiting the city of Atlanta is great. There are many things to do and interesting people. But in car-centric cities finding parking can be a hassle. And paying for parking, much less dealing with parking ticket is even worse.

This project was done to build basic skills in

– Database Management

-Data Cleaning

-Visualizations in Tableau

Table of Contents
Data and a Brief Look

Posted by DOT in Montgomery, Maryland on Data.gov years 2020-2021

We split the master table into two-appropriate sub-tables

A Location Date table and a Violations table

We are dealing with 100,000+ rows so SQL is best suited

TicketLocations Table
TicketViolations Table

Cleaning Strategy

  1. Remove Null Rows
  2. Fix Date/Time datatype and year errors
  3. Clean Ticket Locations by splitting into Address and Lot#
  4. Create Time Of Day Categories
  5. Condense Parking Violation Types (From 33 similar types, down to 7)
  6. Finalize Tables by dropping columns and adding Primary/Foreign Keys
1) Remove Null Rows

For best practices, we inserted into two new tables: Locations and Violations. Leaving TicketLocations and TicketViolations as our original backup tables.

After removing null rows, our rows reduced from 103,000 to 102,000.

Select *
INTO ParkingTickets..Locations
From ParkingTickets..TicketLocations
WHERE [Ticket ID] is not null
and [Date/Time] is not null
and [Ticket Location] is not null
Select *
INTO ParkingTickets..Violations
From ParkingTickets..TicketViolations
WHERE [Ticket ID] is not null
and [Violation Code] is not null
and [Violation Description] is not null
and [Ticket ID] IN –Helps us later on, when adding a foreign key on [Ticket ID]
(
Select [Ticket ID] From ParkingTickets..Locations
)
view raw TicketQuery.sql hosted with ❤ by GitHub
2) Fix Date/Time errors

The original Date/Time data type was a ‘string’ and had errors in the typed in years. This was corrected with the below update

ALTER TABLE ParkingTickets..Locations
Add date_time DATETIME
UPDATE ParkingTickets..Locations
SET [Date/Time] = Replace([Date/Time],'0020','2020')
UPDATE ParkingTickets..Locations
SET [Date/Time] = Replace([Date/Time],'0021','2021')
UPDATE ParkingTickets..Locations
SET date_time = convert(DATETIME,[Date/Time])
Select FORMAT(date_time,'MM/dd/yyyy hh:mm tt')
From ParkingTickets..Locations
view raw TicketQuery.sql hosted with ❤ by GitHub
3) Clean Locations by Splitting out Lot # and Address

Ticket Locations Column had an entry error where locations were of the format “lot#-Address”.

A secondary error was encountered of the form “lot#-lot#-Address” , hence a secondary split query was performed, and the correct Clean address was confirmed

ALTER TABLE ParkingTickets..Locations
ADD Lot varchar(10),
[Address] varchar(100)
–Check Query, Notice additional error: double entry of lot number
SELECT DISTINCT
[Ticket Location]
,TRIM('-' FROM LEFT([Ticket Location], CHARINDEX('-',[Ticket Location])))
as LotNumber
,RIGHT([Ticket Location], LEN([Ticket Location]) – CHARINDEX('-',[Ticket Location]))
as [Address]
FROM ParkingTickets..Locations
Where TRIM('-' FROM LEFT([Ticket Location], CHARINDEX('-',[Ticket Location]))) != ' '
Order by 3 desc
–Perform another trim, to correctly split address and lot number
UPDATE ParkingTickets..Locations
Set [Address] = RIGHT(RIGHT([Ticket Location], LEN([Ticket Location]) – CHARINDEX('-',[Ticket Location])), LEN(RIGHT([Ticket Location], LEN([Ticket Location]) – CHARINDEX('-',[Ticket Location]))) – CHARINDEX('-',RIGHT([Ticket Location], LEN([Ticket Location]) – CHARINDEX('-',[Ticket Location]))))
UPDATE ParkingTickets..Locations
Set [Lot] = CASE
WHEN TRIM('-' FROM LEFT([Ticket Location], CHARINDEX('-',[Ticket Location]))) = ' '
THEN 'Street'
ELSE TRIM('-' FROM LEFT([Ticket Location], CHARINDEX('-',[Ticket Location])))
END
view raw TicketQuery.sql hosted with ❤ by GitHub
4) Create Time Of Day Categories

Time of day was partitioned by

ALTER TABLE ParkingTickets..Locations
ADD time_of_day varchar(20)
UPDATE ParkingTickets..Locations
Set time_of_day = CASE
When CONVERT(varchar(255),CONVERT(Time(0),date_time)) between '06:00:00' and '12:00:00' Then 'Morning'
When CONVERT(varchar(255),CONVERT(Time(0),date_time)) between '12:00:00' and '18:00:00' Then 'Afternoon'
When CONVERT(varchar(255),CONVERT(Time(0),date_time)) between'18:00:00' and '24:00:00' Then 'Evening'
Else 'Late Night'
End
view raw TicketQuery.sql hosted with ❤ by GitHub
5) Condense Parking Violations to 7 types

Originally there were 33 classifications of violations. But many types were self-similar, like off and on street of the same overtime parking violation. Violations were grouped sensibly

ALTER TABLE ParkingTickets..Violations
Add violation_type varchar(50)
UPDATE ParkingTickets..Violations
Set violation_type = 'No Standing Parking'
Where [Violation Code] = 10 or [Violation Code] = 17 or [Violation Code] = 22 or [Violation Code] = 22 or [Violation Code] = 23 or [Violation Code] = 25 or [Violation Code] = 31 or [Violation Code] = 32 or [Violation Code] = 57
UPDATE ParkingTickets..Violations
Set violation_type = 'No Parking Anytime'
Where [Violation Code] = 3 or [Violation Code] = 5 or [Violation Code] = 34 or [Violation Code] = 37
UPDATE ParkingTickets..Violations
Set violation_type = 'Expired/Overtime'
Where [Violation Code] = 7 or [Violation Code] = 41 or [Violation Code] = 50
UPDATE ParkingTickets..Violations
Set violation_type = 'Sign Violation or Impeding Traffic'
Where [Violation Code] = 9 or [Violation Code] = 35 or [Violation Code] = 36 or [Violation Code] = 43 or [Violation Code] = 54
UPDATE ParkingTickets..Violations
Set violation_type = 'Prohibited Vehicle'
Where [Violation Code] = 2 or [Violation Code] = 38 or [Violation Code] = 42 or [Violation Code] = 48
UPDATE ParkingTickets..Violations
Set violation_type = 'Orientation Violation'
Where [Violation Code] = 8 or [Violation Code] = 19 or [Violation Code] = 20 or [Violation Code] = 24 or [Violation Code] = 27 or [Violation Code] = 28 or [Violation Code] = 59
view raw TicketQuery.sql hosted with ❤ by GitHub
6) Adding Primary and Foreign Keys

For proper referencing, keys were added onto the [Ticket ID] Column as a unique identifier

ALTER TABLE ParkingTickets..Locations
DROP COLUMN [Date/Time],[Ticket Location]
ALTER TABLE ParkingTickets..Locations
ALTER COLUMN [Ticket ID] int NOT NULL
ALTER TABLE ParkingTickets..Locations
ADD Primary Key ([Ticket ID])
view raw TicketQuery.sql hosted with ❤ by GitHub
ALTER TABLE ParkingTickets..Violations
DROP COLUMN [Violation Code],[Violation Description]
ALTER TABLE ParkingTickets..Violations
ALTER COLUMN [Ticket ID] int NOT NULL
ALTER TABLE ParkingTickets..Violations
ADD Foreign Key ([Ticket ID])
REFERENCES ParkingTickets..Locations([Ticket ID])
view raw TicketyQuery.sql hosted with ❤ by GitHub

Metrics

Appropriate metrics were created to answer our initial question: How can I avoid a parking ticket?

Our sub questions included:

–Most Common Violation
Select violation_type
,Count(violation_type) as violation_count
From ParkingTickets..Violations
Group by violation_type
Order by 2 desc
–Frequency of Top Violation by months
Select DATENAME(Month,l.date_time) as [Month]
,year(l.date_time) as [year]
,Count(DATENAME(Month,l.date_time)) as count
From ParkingTickets..Violations v
INNER JOIN ParkingTickets..Locations l
On v.[Ticket ID] = l.[Ticket ID]
Where v.violation_type = 'Expired/Overtime'
Group by year(l.date_time),DATENAME(Month,l.date_time)
Order by 2,3 desc
–Most Ticketed Location
Select [Address]
,Count([Address]) as address_count
From ParkingTickets..Locations
Group by [Address]
Order by 2 desc
–Most Ticketed Time of Day, Seperated by Year
Select time_of_day
,year(date_time) as [year]
,Count(time_of_day) as count
From ParkingTickets..Locations
Group by time_of_day, year(date_time)
Order by 2,3 desc
–Least Ticketed Day of the week, Seperated by Year
Select DATENAME(Weekday,date_time) as [day]
,year(date_time) as [year]
,Count(DATENAME(Weekday,date_time)) as count
From ParkingTickets..Locations
Group by DATENAME(Weekday,date_time), year(date_time)
Order by 2,3 desc
view raw TicketQuery.sql hosted with ❤ by GitHub

Results

For Tableau, a new table was created by joining Locations and Violations. This full table was used to create the below Dashboard

See my github repo to view the entire code

Common Violation and Locations
How should we strategize to avoid a ticket?
Parking Ticket Dashboard 
            <script type='text/javascript'>                    var divElement = document.getElementById('viz1659200784654');                    var vizElement = divElement.getElementsByTagName('object')[0];                    if ( divElement.offsetWidth > 800 ) { vizElement.style.minWidth='1000px';vizElement.style.maxWidth='1920px';vizElement.style.width='100%';vizElement.style.minHeight='827px';vizElement.style.maxHeight='1107px';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else if ( divElement.offsetWidth > 500 ) { vizElement.style.minWidth='1000px';vizElement.style.maxWidth='1920px';vizElement.style.width='100%';vizElement.style.minHeight='827px';vizElement.style.maxHeight='1107px';vizElement.style.height=(divElement.offsetWidth*0.75)+'px';} else { vizElement.style.width='100%';vizElement.style.height='1577px';}                     var scriptElement = document.createElement('script');                    scriptElement.src = 'https://public.tableau.com/javascripts/api/viz_v1.js';                    vizElement.parentNode.insertBefore(scriptElement, vizElement);                </script>