Network Analysis

Goals and Objectives

The goal of this assignment was to assess the damage on roads contributed from trucks, driving from the sand mine to the rail terminals. Case studies have been done to analyze the damage done by these trucks as mentioned in the white paper "Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study." In order to do this there were many steps:

• Set up a python script to query out the correct mines that met the following criteria
• Only active mines
• Mine must have rail loading station on site
• Mine must not be within 1.5 kilometers of a rail line
• Build a Model to calculate the closest facility route
• Calculate the cost of sand truck travel on the roads by county

Methods

Below is a model of all the steps and tools I used to reach my results.

Figure 1: Model builder of process I went through to answer the question at hand

In this model you can see my work flow through this situation. I first started by using the "Make closest facility layer." In order for that tool to function I had to use the tool "Add Locations" to add the Mines as the incidents and the rail terminals as the facilities. From there the closest facility was solved using the solve tool, where you see "solve succeed" is where the routes were calculated using the road system layer that Esri provided us with.

Next I used two tools to export the resulting route that had been calculated. To do this I first used the "Select Data" tool and then the "Copy Features" tool. This saved the routes as a layer in my geodatabase. Next was to project the data, I chose to project it in a Wisconsin state plane coordinate system that used US feet as its measurement. This would play an important role later on in the process.

After this is when I had to figure out how I would use the information I had to find the distance these trucks were traveling and then the cost  of damage the trucks were causing to each county. I first started with the "Intersect" tool. I intersected the feature classes county boundaries, and routes. This way the feature classes were combined together so I could use the next tool of "summary statistics." The summary that was reported in the attribute table gave the distance of the routes for each county by US feet. I then added a field so US feet could be converted to miles, this field was called "Length_mi." Next I calculated the field by using the simple equation "SUM_Shape_length * 0.000189" This gave the length of the routes in miles. Then I added a field called cost where the cost of damage would be calculated, this was calculated by the equation "Length_mi * 100 * 0.022." It was assumed that each truck would take 50 trips per year, but that only includes one way, the round trip had not been factored which is why the mile field was multiplied by 100 (50 round trips = 100 trips total). The number 0.022 is the hypothetical cost per truck mile is 2.2 cents. Below is a table showing the results of the calculations and a graph displaying the counties with the highest cost from sand trucks and the damage they cause.

Figure 2: Attributes containing the length in miles field called "Length_mi" and the cost field indicated by "cost"

Results

Here you will see the chart I have created that shows the cost of sand transportation of each county and a few maps that illustrate the routes taken by the trucks and what counties they need to go through in order to get the sand from the mine to the rail terminal.

Figure 3: Graph displaying the hypothetical expenses for each county in Wisconsin due to trucks driving sand from the mine to the rail terminal.

These results show that the counties with the highest expenses are Chippewa county, Barron county, Eau Claire and Wood county. The results make sense because the amount of miles driven in a county directly correlates with the cost that each county would incur. In order for these counties to lower the damage on the roads they need to drive less. Maybe they should think about creating their own rail terminal near by as to limit the amount of driving and therefor damage on the roads.

Figure 4: Map of Wisconsin with railroads, railroad facilities and the mines.

Just by looking at this map you can crate a ball game estimate of what is the nearest rail terminal that the sand will be delivered to from the sand mine. Below is a more specific route of the routes taken by this trucks as calculated by the "Make closest Facility Layer" and "Solve."

Figure 5: Map of Wisconsin showing the shortest routes taken by sand mine trucks to the closest rail terminal.

You can see that many routes go through Chippewa and Eau Claire counties.

Conclusion

It was very interesting to find the actual cost that these truck cause on each counties roads. The cost seemed relatively lower than what I thought it would be and makes me worried a calculation may have been done wrong. Overall you can see the top 5 counties with the most driving done in them and subsequently the most expenses for their roads due to the damage by the trucks.

A few problems with this model are that the network analysis tool only calculates the shortest route from sand mine to rail terminal. This does not mean it is the fastest or best way to take. In reality the trucks may be taking a slightly different way than that mapped. Also the trucks are heavier as they take the sand to the rail terminal, likely causing more damage than when the truck is empty coming back from the rail terminal.

Sources

• Street map of USA: From Esri
• White paper "Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study"