Friday, April 21, 2017

Lab 4: Network Analysis

Goal and Objectives There are three objectives in this lab:

This lab works to conduct a network analysis on the frac sand mining operations in the state of Wisconsin.  This lab is part of a multi part project to build a suitability/ risk model for sand mining in Western Wisconsin.  The focus of this lab was to use network analysis to better understand the impact heavy trucks moving sand from the sand mine location to the rail terminals has on the roads driven on.  In this lab first the route the sand takes from the mine to the closest railroad terminal was determined.  Next using simple calculations and estimates based on the number of truck trips and hypothetical estimates of costs of damage that occurs to the road per truck mile, the cost of damage that occurs to the roads per county was calculated.  The National Center for Freight and Infrastructure Research and Education has conducted research into similar methodology for determining road damage costs of frac sand trucks.  They have determined that road damage caused by these heavy trucks is a concern for many counties and local communities in Wisconsin.  The governments of numerous counties are looking into different methods for calculating the road damage caused by these trucks in hopes to recover the costs of damage from the mining operations.  This lab demonstrates one methods for calculating the costs accumulated by county from road damage.  The dataset that was used for the network analysis was the ESRI Street map USA.

There are three objectives in this lab:
  1.  Use Pyscriptor to select mines based on the criteria that:
      • The mine must be active
      • The mine must not have a rail loading station on-site
      • The mine must not be within 1.5 kilometers of a rail line
  2. In Arc Map use Closest Facility solver in the Network Analyst toolbar to determine the closest rail terminal for each mine and the most efficient route that could be taken between the two
  3. Build a model using Model Builder to automate the process of determining the closest facility route and the length of road being used by county. 
  4. Calculate the cost per county that sand truck travel causes to the roads also using Model Builder
Methods

The first step of this lab was to use Pyscriptor to select mines that are currently transporting their frac sand from the mine to a rail terminal by using trucks to drive on local roads.  These types of mines must be classified as active, must not have a rail loading station on site, because that would eliminate any trucks driving on roads, and must not be within a 1.5 kilometer radius of the rail terminal, because if it is most likely a spur has been built again eliminating the need for trucks to transport frac sand. Mines that follow these criteria were selected for and exported as a new feature class in Pyscriptor using python coding.  The python script for this section of the lab can be seen in the Python Scripting post.

Next, in Arc Map the Network Analyst toolbar was used to determine the closest route a truck could take from a sand mine to a rail terminal using roads.  This route would most likely be the route taken by truck drivers to transport frac sand.  This was done by first selecting the rail terminals that use railroads from the rail terminal feature class.  This feature class included air terminals and other non-rail modes of transportation, which were irrelevant if railroads were not in use.  Using a  simple query statement to only select the terminals of railroads did this and the selection was exported as a new feature class.  Next, the network analyst extension was activated and ESRI original raw geospatial data on the streets in the United States was added to the Arc Map viewer.  The Network Analyst toolbar was added to the data frame.  The mine selection that resulted from the python script and the feature class of only rail terminals was added to the viewer.  A closest facility layer was added from the Network Analyst menu and the sand mines were loaded as the Incidents, and the rail terminals were loaded as the Facilities. The closest route from each mine to a rail terminal was generated.  Before model builder could be used to automate this process the feature class of selected mines generated by the python script were modified.  The Delete Field Data Management Tool was used to delete the address field, which can cause difficulties if left in the table when using Model Builder.

Next, a model in Arc Map was created using Model Builder to automate the processes used in this lab.  A toolbox was created in the geodatabase and the model builder was created inside that toolbox.  The previously described steps using the Closest Facility solver and the steps used to calculate the length of road used to transport sand by county and the estimated costs of damage caused by the sand transport were included in the model.  Figure 1 is of the model created in model builder.  The first 4 tools (Make Closest Facility Layer, Add Locations (2), and Solve) function as the operation of the network analysis using the closest facility solver described above.  This section results in selecting the most efficient road route from each mine to the nearest rail terminal.  The next two tools (Select Data and Copy Features) work to export the routes as a feature class.

The next section of tools in the Model Builder work to determine the total routes road length by county.  The Intersect tool was first used to create a geometric intersection between two feature classes, the selected routes feature class and a Wisconsin counties feature class. The features that overlap are included in the output feature class.  This allowed the road route lengths to be classified by county.  Next, the Project tool was used to project the new feature class that did not use decimal degrees as the form of distance measurement, but rather feet so that the actual physical length of the routes could be determined. Next the Summary Statistics tool was used to sum up the road length of the routes by county.  Following this step it is now known the length of road in feet used by trucks to transport frac sand by county.

Finally, the costs each county can expect to pay for road damage caused by the trips from the heavy frac sand truck routes was determined.  First, the Add Field tool was used to add a new field to the output table from the summary statistics used.  This field was for the road length in miles to make calculations easier by working with smaller numbers in miles instead of larger numbers in feet.  The next tool used was Calculate Field, used to populate the new field that was just created.  The algorithm used to populate this field was: (sum shape length)/5280, used to convert the length of road by county in feet to miles.  Next, another field was added to the table by using the Add Field tool.  This field was for costs in dollars of road damage.  The last tool used was the Calculate Field tool, which was used to populate the new field.  To calculate the cost per year a county should be expect to pay for road maintenance the following algorithm was used:
((road length * 2.2 cents) 100 trips)/ 100 = U.S. dollar amount costs of road maintenance a county will incur due to heavy sand truck traffic. The estimates that were used were that the hypothetical cost per truck per mile was 2.2 cents and that every year a mine would have a frac sand mine truck make 50 trips to and from the sand mine resulting on the road being driven on 100 times per mine.  The resulting table after this final step included the calculated road length in miles used by frac sand trucks and the cost of the damage in U.S. dollars that was caused by the trucks for each county.

Figure 1.  The model builder tool set that was used to complete this lab.  This tool set results in a table that gives the calculated length of road covered by a frac sand truck moving sand from a mine to a the nearest railroad terminal by county and the estimated costs each county will need to spend on road maintenance due to the heavy sand trucks driving these routes.  

Results and Discussion

The results of this study determined that out of the counties in Wisconsin with mines requiring frac sand transport from the mine to the rail terminal by the way of trucks driving on roads, the county that will have to spend the most money on road maintenance is Chippewa County at $613.22 per year.  The county that will spend the least amount of money each year on road maintenance is Winnebago at $2.12.  The calculated costs of the road damage by county can be seen in Figure 2.

For this exercise only the transport to railroad terminals was conducted or mines that the nearest rail terminal was in Wisconsin.  There was one mine in Wisconsin located in Burnett County, which is located very close to the Wisconsin boarder with Minnesota.  For this mine the closest rail terminal was located in Minnesota, so the road damage calculations were only included for what the county will incur, not what any Minnesota counties will incur in the completion of moving the frac sand to the final destination rail terminal.  

Figure 2. Table of the road length that is traveled by heavy frac sand trucks and the cost of road maintenance by county.

In Figure 3. agraph can be seen which shows the extent of the cost and road length differences between frac sand truck transport impacted counties in Wisconsin.  It can be seen how Chippewa County is acquiring nearly double what most other counties are paying inroad damages.    

Figure 3. Graph for visualizing the comparison between the road length and the cost of road maintenance distributed through effected Wisconsin Counties. 

The three counties with the most damage costs due to truck frac sand transportation are Barron, Chippewa, and Eau Claire counties.  This is interesting given that the top three counties with the largest number of mines with sand being transported are Chippewa, Trempealeau, and Eau Claire.  Barron only has 4 mines that are having frac sand transported by trucks and Chippewa has 6 mines.  Trempealeau has 10 mines, the greatest number of mines transporting sand this way and it is not ranked as having the greatest cost of road damage.  This is interesting given Trempealeau County is not paying nearly as much as Chippewa County is and Trempealeau has 4 more mines than Chippewa.  It can also be seen that Barron County sends all of its trucks to Chippewa County for the nearest rail terminal, this is a possible explanation for Chippewa's increased damage costs.  Also Chippewa County is a larger county than Trempealeau leading to longer road distances to be traveled to transport frac sand.

Figure 4. Map of the roads used to travel between frac sand mines and railroad terminals including the costliness of the damage to the roads due to the transport.

There were a few estimates and hypothetical situations utilized in this lab.  These make the results of the lab not exact and should be modified if this model were to be used to generate actual data for a real analysis.  These estimated include that the cost of damage caused by a truck per mile was estimated at 2.2 cents and the number of truck trips 100 per year number was used as an estimate.  Also the routes that were determined to be the routes the frac sand trucks would take were only calculated as the nearest rail terminal to the sand mine and the roads used were just the most efficient routes.  The trucks might use different terminals given unknown circumstances like the size of the terminal and how many mines it can service.  The trucks might also use different road routes given unknown circumstances like road closures, changing the effected roads and changing the pricing per county.  Also the size of the mine was not taken into account, a larger mine would most likely require more truck trips, or the weight of the truck loads would be greater causing an increased amount of damage to the roads that those trucks are driving on.

Conclusions

Overall, the methodology used in this lab was sound for determining the routes potential frac sand tucks would take between a frac sand mine and the nearest rail terminal and the cost of road damage that a county would incur per year based on the length of the road traveled by the trucks within that county.  The network analysis tool is very versatile and easy to operate.  There are many operations it can conduct outside of just using road networks; water and utility networks can also be examined.  The Model Builder that was created in this lab can be used to calculate the inquires for a different state given a different mine dataset and if correct pricing and trip counts were used this model could be used to accurately calculate the expense of road damage per county, instead of just using estimates that were used in this lab.  

Sources

National Center for Freight and Infrastructure Research and Education. (2013). Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Cause Study. Retrieved from http://midamericafreight.org/wp-content/uploads/FracSandWhitePaperDRAFT.pdf.