Network Analysis

Goal

The overall goal of this assignment was for us to perform a network analysis, in this case we are trying to figure out what the total cost and total mileage of certain truck routes that currently exist in Wisconsin. This will allow us to see what impact trucking sand from mines to rail terminals has on the local roads. This entire scenario including the trips and cost of the trips is entirely hypothetical and for practice purposes only. 

Objectives

There were two parts in performing the network analysis, the first part was to create a python script so than we could select all of the mines that are within 1.5 km from a rail road and remove them from the map. Listed below are the objectives for the entire Network Analysis:

1. Set up your script

2. Set up the variables

3. Write Several SQL statements to select the mines based on the above criteria

4. Use the query statement to run the queries

5. Select all mines that are within 1.5 km from a rail road and remove them from your mine

6. Practice with the closest facility solver

7. Build a model to calculate the closest facility route

8. Calculate the cost of sand truck travel on roads by county

Methods

The first step in this process was to set up the script in python, we did this so than we get all of the mines that we are trying to find the routes for below is figure 1 which is a screenshot of the python script that was created for this network analysis. 

Figure 1: Python Script for Mines

Once we had all of the correct mines set up and available for use, we now have to create a model in ArcMap so than we would not only get the routes, but the distance of the routes and the total cost that goes along with them. Below is figure 2 which is an image of the model that was created in model builder which includes all of the tools that was used in this analysis. 

Figure 2: Model for Routes and Cost of Routes

To describe this model we need to start at the beginning, we added three features to start with, the streets, rail only terminals, and the mines that are further than 1.5 km away from the railroads. This is the original data that we will be using, the rail only terminals are the facilities and the mines with no rails near them are the incidents. The next tool was 'solve' this will calculate all of the data that was recently mentioned and create a route for each mine to the closes facility or in this case the rail only terminals. The next tool is 'select data' this allows us to select the data that we want to use or in this case the routes that were just created. Then we go to the 'copy features' and 'project' tool, this will allow us to put the routes in the correct projection so than there aren't any unexpected errors later on in the model. The next tool is the 'tabulate intersection' this is where we added in the counties of Wisconsin and this will clip the routes, facilities, and incidents to the counties. The final few steps is where we start adding fields, in this case we are adding 'Length' and 'Total Cost'. The first calculation field is where we change the distance from meters to miles by dividing the length that we currently have which is in meters by 1609.344 because that is how many meters are in a mile. Then we would multiply that value by 100 in the next calculation field because they make 50 truck trips per year, that's only including one way trip but they do round trips so than 50 turns into 100. The final calculate field tool is where we multiply the distance that is now in miles by the cost per mile which in this case is 2.2 cents per mile. The equation that was used to calculate the total cost for the routes is [Length]*0.22. 

Results

Figure 3: Truck Routes

Figure 4: Total Cost of Routes

As one can see by looking at the figures above some of the mines are using the same routes, this is because they are all going to the same facility because it is the closest facility to that particular mine. Below are figure 5 which represents the table from ArcMap that represents the results from the entire analysis, including the cost and length. 

Figure 5: Final Results Table

The field 'LENGTH' is showing what the distance is for a one way trip, and the field 'Length1' is representing the total distance of a round trip for a year. As you can see Chippewa county has the most expensive route by far compared to the other counties. Below is a figure 6 which is a graph showing the cost of the routes for each county in a different perspective. 

Figure 6: Routes Total Cost per County

Conclusion

After looking at all of the data that was collected we can see that the three most expensive routes are the ones in Eau Claire, Barron, and Chippewa County. This means that they have the longest routes and are driving the most which will lead to higher costs. This assignment was a very good learning experience, not only did we get some experience working with network analyst but we also got to progress our skills with python and using model builder. Once again this assignment is entirely hypothetical both the number of trips and the cost, this assignment was used for practice purposes only.