Network Analysis

Goals and Objectives

The goal of this lab was to learn and practice network analysis skills while continuing our research of frac sand mining in Western Wisconsin. For this exercise, we were tasked with finding the shortest route from each mine/processing plant to the closest railroad terminal. We then had to calculate the cost of road maintenance due to frac sand trucks using these routes, broken down by county.

Data

Street Network Dataset - ESRI street map USA
Western Wisconsin frac sand mines - Point feature class geocoded by our class during previous lab
Railroad Terminals - Point feature class provided by instructor

Methods

Some of our geocoded mines are also a rail loading location. The first step was to exclude these sites from our analysis, as they would not require and road travel to bring their product to a rail terminal. Once I did this, I created separate shapefiles for mines that would require road travel and for mines that have rail loading locations. Then I merged the mines with rail loading with the rail terminals shapefile (mines could transport their product to a mine with rail loading, even if owned by different companies) so that I had one shapefile containing all locations where product can be loaded onto a train.

Next I created a network analysis. I used the closest facility function to find the closest rail terminal to each mine. The points in the mines shapefile were set as the incidents and the points in the terminal shapefile were set as the facilities. Flipping those around would create a network analysis that creates routes from the terminals to the closest mine, which would be unhelpful for our purposes.

Figure 1 - Model Builder work flow

Using model builder, I exported my route data as a shapefile and found a way to calculate total miles driven on roads, as well as the cost the county incurs each year for road maintenance (Figure 1). The first part of the model (up to the "Solve Succeeded" bubble) is for creating and exporting the closest route data. All steps after that were to calculate miles driven on road and cost incurred to each county. To do this, I intersected the route data with my selected counties. This not only figured in only the mines and terminals in Western Wisconsin, but also merged the tables so that I had a county field with my route data. This was needed so that I could calculate distance and cost for each county separately. Next, I summarized the Shape Length field by county.

Equations:
Yearly Cost Per County = [Sum_Shape Length] (This is the total length of all routes in the county in meters) * .0006271 (This converts distance from meters to miles) * .022 ($.022 is the maintenance cost per mile) * 100 (This is the amount of trips every year on each road: 50 trips, both ways)
(The maintenance cost per mile and the amount of trips made every year are hypothetical figures)

Results


Figure 2 - Map of mines, rail terminals, and the truck routes to the nearest terminal



Figure 3 - Table showing results from calculations

Figure 4 - Miles driven every year by trucks transporting frac
sand to rail terminals, by county

I created a map showing the results of the network analysis (Figure 2) as well as graphs showing the results of distance and cost calculations for each county (Figures 4 and 5 respectively). Figure 3 is the table used to create figures 4 and 5. As you can see, Jackson county will have to spend the most money on road upkeep at $744 per year, followed by Trempealeau county at $650 per year.



Figure 5 - Cost of road maintenance incurred each year by
transporting frac sand to rail terminals, by county

Trempealeau county has a very large number of frac sand mines, meaning more trucks on the road. Jackson has fewer mines than other counties, but longer distances to travel to get to the nearest rail terminal. the mines located in the southeastern part of the county are routed to the opposite corner of the county. The highway used even runs parallel to a rail line for a portion of the trip, and so the damage to roads could be greatly reduced by constructing another rail terminal in this section of the county.

Conclusion

Before this exercise, I had never used Model Builder or Network Analysis. Both are extremely useful tools that I hope to utilize even more in the future. The actual results are hypothetical, as the cost and mileage data were hypothetical. Also, I assume that in the real world specific routes are altered and updated as necessary. But I think this is an accurate look and the counties relative to each other. Jackson and Trempealeau counties probably do have higher road construction costs due to the high number of frac sand trucks that use those roads everyday. The skills I learned in this lab are therefore very useful in Wisconsin.