Our latest Next Louisville story started with a question: how problematic is trash and litter in Louisville’s neighborhoods, and is it worse in areas with higher rates of poverty?
We answered that question by combining data already publicly available with open records requests -- and we discovered an interesting correlation. About 73 percent of all trash complaints reported to the city’s MetroCall 311 service are not within one block of a trash can -- and there are far more trash cans per mile in downtown or tourist-friendly spots than in those with a higher concentration of poverty.
In some spots west or south of downtown Louisville, a pedestrian could walk 12 blocks without any place to toss a soda can or food wrapper.
To reach these conclusions, we used spreadsheets and the open source mapping program QGIS. Below is an explanation of how we did this analysis -- and a step-by-step technical instructions for those familiar with these programs who might want to try something similar.
Anyone can download Louisville Division of Emergency Management’s 311 call data, which includes complaints about sidewalks, dead animal removal requests, reports of trash and so on.
We chose to isolate the 311 requests related to unwanted trash for this analysis. This included reports of garbage violations, illegal dumping, junk/trash near abandoned or vacant structures and lots, junk/trash near private property and reports of people littering.
We did not include 311 requests that had to do with damaged or stolen garbage carts, missed garbage collection or issues around problems with or damage to specific garbage bins.
Once we had these trash complaints, we removed any complaints without enough geographic information to place them on a map. We also filtered out repeat calls about the same reported trash. We wanted to map unique trash issues, not necessarily calls about trash issues.
We added our unique trash complaints, along with neighborhood and litter bin data from Metro Public Works, to QGIS. With this program and our added data, we were able to do the following:
- Count total, unique trash complaints in each neighborhood.
- Draw a one block radius, intersection to intersection, around each litter bin, and count the trash complaints that fell outside of that block radius. This gave us the count of trash complaints that did not fall within one block of a trash can which then allowed us to calculate the percentage of complaints that did not fall within one block of a trash can. (Fun Fact: The average block length in the city of Louisville is 411 ft.)
- Calculate the area of each neighborhood and count the number of litter bins in each neighborhood. These two data points allowed us to calculate trash can density per neighborhood.
- And finally, we were able to draw a more arbitrary geographic shape, something called a hex grid. Counting complaints per neighborhood is interesting, but the boundaries of a neighborhood are often arbitrary. That means a neighborhood-specific measure doesn't always adequately show where the biggest problem areas are. By drawing a hex grid with hexagons roughly one square block in area, we were able to count complaints per hexagon and better illustrate where the concentrations of trash complaints were coming from.
It is important to note that the data analysis was the origin of this story -- not the entirety of it. Once we were able to identify areas with an unusual density of trash complaints, we went to these areas and interviewed the people living there. We also surveyed the areas by car, bike and foot to confirm our data findings.
Here is a neighborhood trash summary spreadsheet we created during this analysis.
If you’re familiar with data analysis and mapping, here’s a super nerdy, step-by-step explanation of how we did this trash analysis:
- Download current year (2017) 311 data from Louisville Open Data Portal
- Filter out records where service_name is one of:
- GARBAGE VIOLATIO
- ILLEGAL DUMPING
- REPORT LITTERER
- TRASH PVT PROP
- VAP LOT TRASH
- VAP STRUC TRASH
Record Count: 9,721 (Note data were pulled end of December before total year complaints were recorded. Total count for 2017 is now 9,819)
- Remove records with blank latitude and longitude
Record Count: 9,676 - Deduplicate records using service_request_id in order to get unique instance of trash and to identify multi-call trash complaints
Record Count: 8,912 - Export unique trash records as csv → dedupped-trash-complaints.csv
- Add dedupped-trash-complaints.csv, Louisville KY Urban Neighborhoods shapefile, Jefferson County KY Street Centerlines shapefile and FOIA-ed LMPW Litter Bins shapefiles to QGIS
- Select only those unique trash complaints that fall within the Louisville KY Urban Neighborhoods shapefile because our LMPW Litter Bins shapefile includes only bins within these urban neighborhoods
Record Count: 5,847 - Count unique complaints in neighborhoods in order to get total unique complaints per neighborhood
- Determine median block length from Jefferson County KY Street Centerlines
Median block length: 411 ft - Create buffer with radius 411 ft around each litter bin point
- Select unique trash records that ARE NOT within 1 block trash buffers. Export these to separate file: complaints-nobin-1block
Record Count: 4,291 - Create buffer with radius 822 ft around each litter bin point
- Select unique trash records that ARE NOT within 2 block trash buffers. Export these to separate file: complaints-nobin-2block
Record Count: 2,399 - Count points in neighborhoods for each complaint group to get:
- Unique trash complaints with no litter bin w/i 1 block, per neighborhood
- Unique trash complaints with no litter bin w/i 2 blocks, per neighborhood
- Divide unique trash complaints with no litter bin w/i 1 block by total unique trash complaints for each neighborhood to get percent complaints not within 1 block of a litter bin
- Divide unique trash complaints with no litter bin w/i 2 blocks by total unique trash complaints for each neighborhood to get percent complaints not within 2 block of a litter bin
- Create 1 block hex grid to get an idea of density of unique complaints using a more arbitrary geographic measure than “neighborhood”
- Count litter bins per neighborhood
- Calculate neighborhood areas
- Divide bin count by neighborhood area to get bin density by neighborhood
The Next Louisville project is a collaboration between WFPL News and the Community Foundation of Louisville.
For more work from the project, click here.
