Network Analysis

Background

With the increased activity currently being experienced in the frac sand mining industry in Wisconsin, "road damage over local roads is a concern for many communities" (Hart, Adams, Schwartz 5). To help deal with these concerns, county governments are implementing ways to help recover the costs of road damage. Chippewa County, for example, is "using road use and maintenance agreements (RUMA) to fund road repairs to frac sand haul routes" (Hart, Adams, Schwartz 19).


Goals and Objectives

The goal of this assignment is to execute a network analysis in order to calculate the impact of transporting sand on local roads from the mines to the rail terminals. Keep in mind, the number of trips and cost in each county per year is strictly hypothetical. 

The objectives for this exercise are as follows:

1. Using PyScripter, set up script and variables, write several SQL statements to select mines based on certain criteria, use the query statement to run queries, and finally select the mines within 1.5 km from a railroad and remove them from the mines
2. Practice with and use the closest facility solver
3. Build a model to calculate the closest facility route
4. Calculate the hypothetical cost of sand truck travel routes on roads by county

Data sets and sources include:

• Network Dataset - ESRI street map USA
• Mines dataset - Wisconsin DNR

Methods

In the first part of this exercise, I wrote python script to select the mines that would be used in the analysis later on (refer to my "Python Script" blog post for the script itself). The mines chosen had to meet the following criteria:

• The mine must be active
• The mine cannot have a rail loading station on-site as this would not impact local roads
• The mine must not be within 1.5 km of the rail since it is then likely that a spur has been built to route the rail line to the mine

After writing the script successfully and producing the mines_norail_final feature class, it was time to move on to figuring out which rail terminal each mine would travel to and which routes were most time efficient. Using model builder, I built a model that would find the most efficient routes between the desired mines and rail terminals, sum up the total route distance per county, and calculate the cost per county per year for the routes.

The steps I took in model builder to get my results are as follows (refer to figure 1 as I describe the steps; start from top left and follow the arrows):

1. In order to make the Closest Facility model, I had to first add the Make Closest Facility Layer tool and enter the streets layer which came from ESRI street map USA
2. The next step was to add the mines_norail_final and railterms_final layers for the locations
3. Once this was done, I was able to run the Solve tool which calculated the most time efficient routes between our inputted locations 
4. Next, I wanted to turn the routes into its own layer, so I added the Select Data tool to select the routes, then ran the Copy Features tool which produced the routes_copy layer
5. In order to figure out how much route distance ran through each county, I had to use the Intersect tool to intersect routes_copy and WI_County_Bnds (WI county boundaries)
6. I needed to get a table that would sum up the total routes and route distances per county, I had to run the Summary Statistics tool where I ran a SUM on Shape_length (route distances) and used the county names as the case field which summed up the number of routes and total distance for each county; this produced the Sum_Output_4 table
7. To convert the route distance into miles (since it was in meters) and display this in the table, I used the Add Field tool to add a field named Route_Miles then ran the Calculate Field tool where I multiplied the Shape_length by 1609.34 (the number of meters in a mile)
8. The final step involved adding another field which I titled Yearly_Cost and ran the Calculate Field tool to produce the final cost results in dollars per year; the equation I used for this was Route_Miles * 50 * 2 * 0.022 where '50' represents the number of trips which I then multiplied by '2' to account for the trip round way and the 0.022 represents the dollar amount each truck is charged per mile

Figure 1: Model for calculating closest facility route and cost per county

Results

Figure 2: Sand mine to rail transport routes

Figure 3: Final results chart of county sand mine routes, distances, and cost (per year)

Discussion

Referring to the map in figure 2, we see that many sand mines use the same routes and rail terminals. Some counties may not have very much truck traffic while others have a lot, relatively speaking. The graph in figure 3 helps show this a little better. The "Big 3" counties with high route miles and costs include (from most to least) Chippewa, Barron, and Eau Claire counties, and at the bottom of the list are St. Croix, Burnett, and Winnebago counties.

Looking at both the map and graph, counties (especially the ones greatly impacted) could determine how intensely they should focus on repairing and maintaining their roads, which roads require more attention, and how much they're charging the truck companies.


Conclusion

With the increased level of activity from sand mine trucks causing concerns over road damage to local roads, county governments are attempting to recover the cost of road damage and are implementing a variety of methods to tackle the issue. By analyzing the mine to rail terminal routes, we can calculate a hypothetical cost that counties could charge the truck companies. This could especially benefit the counties that experience high levels of truck activity.


Cited

Hart, Maria V., Teresa Adams, and Andrew Schwartz. Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study. Mid America Freight Coalition. 2013. Accessed April 10, 2016. http://midamericafreight.org/wp-content/uploads/FracSandWhitePaperDRAFT.pdf.