How KyCIR analyzed eastern Kentucky flood debris data
From the story: “As of December 22, when [debris cleanup] was declared complete, only 59% of the debris estimated to be in waterways was actually picked up.”
When we reported on cleanup after the eastern Kentucky flood, we heard over and over from people saying that their streams and creeks still had debris – especially large piles of dirt, rock and general sediment that washed down the mountain and was clogging infrastructure. Locals and contractors claimed this was clearly debris and should have been taken.
We wanted to determine just how thorough a job was done as of Dec. 22, the day when the Kentucky Transportation Cabinet considered the mission to be “completed.” After some work, we determined that only about 59% of the debris that was estimated to be in eastern Kentucky was actually picked up.
Here is how we did it.
What data did we use?
Through an open records request, KYTC provided us with a large Microsoft Excel spreadsheet that compared an initial debris estimate to the amount contractors had reported picking up each day.
KYTC also provided us with a slew of estimate files that grew every single day. The files included a new daily estimate and then tacked that amount onto a total rolling estimate.
“These are daily numbers from the State’s contractor report,” wrote a spokesperson for the U.S. Army Corps who was familiar with the data. “It is possible and common that the USACE spreadsheet may not exactly match the final numbers. It is reasonable to assume that an individual day’s quantity may change, for many different and legitimate reasons. Generally these spreadsheets could be used to track progress, upward reporting, [and] possibly trend analysis.”
From several interviews with state officials and the U.S. Army Corps of engineers, we discovered that this contained the only estimate of the total debris created by the eastern Kentucky floods.
But there is a problem: while the estimate was in cubic yards, the actual work was measured in tons.
“The US Army Corps of Engineers prepared the debris estimate document you referenced,” a KYTC spokesperson wrote in an email. “The Kentucky Transportation Cabinet’s contract with AshBritt establishes tonnage as the measurement unit to track debris collection.”
We also received a .csv (comma separated value) file that contained data on every single load of debris hauled by contractors. It contained information on tens of thousands of loads of debris. For each load, it included information on the date of pickup, date of disposal, the subcontractor, the GPS coordinates, type of debris, the weight and much more.
How did we process the data?
Step 1: Filtering
Let’s start with that second file we reference, the one that has information about ALL the debris loads related to this disaster.
It contained a plethora of information and included debris loads that weren’t taken from streams, but loads of material being transported after it was processed and removed. For instance: Trees felled in disasters are commonly ground into mulch and then taken to a final location. The data also included some redundant tickets that were called “mobile tickets”, used for tracking purposes.
To just get the information on loads of debris being removed from streams, we needed to set up some filters.
Using these filters, we narrowed the data to about 25,185 tickets.
Step 2: Categorizing debris by the county where it originated.
According to USACE, one problem in comparing the actual tickets to the initial estimates was that the state categorized debris by the county where it was dumped – not the county where the debris was found. In some cases, it may have been more convenient to take debris located in one county to a dump site in another county if one site was full or a weighing scale broke down.
“It was common during this mission for contractors to utilize the nearest or most convenient debris management site, regardless of the county of origin,” a USACE spokesperson wrote in an email. “For example, debris picked up in Perry County could be transported to the Breathitt County debris management site, therefore it would be reported on this spreadsheet as Breathitt County not Perry County. USACE did not maintain a spreadsheet or running log of tons of debris by the county of origin”
Even though USACE didn’t maintain a log that categorized debris by the county of origin, it’s not difficult to do that with GIS software. We used a free program called QGIS to map the origin location of 25,000 debris loads and created a new field (DebrisOriginCounty) to group them by.
While mapping, we found 62 instances in the more than 25,000 rows where points were geocoded incorrectly as being in places like Turkey, China, and the middle of the Atlantic Ocean. We kept those rows in order to count them towards the grand total of debris picked up, but left the new field we created blank so they wouldn’t get unduly counted to a county’s total.
We then simply totaled up all the tons of debris picked up in each county.
Converting our debris pickup estimates from tons to cubic yards
Although KYTC told us they do not have a debris conversion factor specific to this disaster, FEMA does have standard conversion rates outlined in policy documents.
“We also use the conversion factors in the FEMA 329 guidance (Debris Estimating Field Guide),” a Corps spokesperson wrote in an email. “We did not, nor do we have knowledge of KYTC doing any tests to verify which conversion factor to use.”
Construction and Demolition Debris Multiplier (ton x 2 = cubic yard)
This was very straightforward. Our data showed “CD” material, so we just multiply that weight by two to estimate cubic yards per FEMA's guidelines.
Vegetative Debris Multiplier (ton x 4 = cubic yard)
We decided on using the FEMA multiplier for hardwoods because hardwood tree species are far more prevalent in eastern Kentucky than softwoods.
“It’s a hardwood forest - oak [and] hickory dominant,” Jake Hall, the Hazard branch manager for the Kentucky Division of Forestry, said.
“In terms of both estimated population and estimated standing volume, 18 of the top 20 species are hardwood species. Only eastern redcedar and Virginia pine [softwoods] are found on each list. Kentucky has long been considered a State where hardwoods are the predominant forest; these results indicate that is still the case,” a 2012 USDA report says.
“I would agree with…the USDA that ‘hardwoods’ (Angiosperms) are the dominant species in the impacted counties,” Bill Fountain, professor at the University of Kentucky and chair of the Kentucky chapter of the International Society of Arboriculture, wrote in an email. “Though the forests are second and third growth timber; oaks, maple, hickory, tulip poplar (Liriodendron tulipifera), magnolia (Magnolia spp.), and beech are still the dominant species. The floodplains of streams would be different species (true poplar [Populus spp.], willow, sycamore) but still hardwoods. White pine (Pinus strobus) and some other species are mainly planted with eastern redcedar (Juniperus virginiana) and hemlock (Tsuga canadensis) are going to be the ‘softwoods’ (Gymnosperms) that would be present but all will be out of the normal floodplain.”
You can also play around with this really neat analysis tool from the U.S. Forest Service and see that pretty anecdotally as well.
Mud, Silt, Rock Multiplier (ton x 1 = cubic yard)
There is no FEMA guidance on how to convert this type of debris, however there are lots of websites that have estimates, including the EPA. Generally speaking, this type of material is very heavy and very compact. Therefore, most of the conversion rates would say that the multiplier should actually be less than 1, usually somewhere around .75. However, we want to be as conservative as possible and set this multiplier at a 1:1 ratio.
Now we just refer back to the Corps initial estimate in the first file and compare cubic yards we estimated to be picked up to the Corps cubic yard estimate.