Exercise 7: Network Analysis

Goals and objectives:

      The objectives of this lab were to map and analyze the transportation routes of the frac sand mines to rail station terminals in their close vicinity in order to asses the possible damage to the roads the trucks would be driving on and then how much it would cost each county. To do so we would be using programs such as pyscripter, ArcGIS, and excel. Inside ArcGIS we would be using the Network analysis tool to estimate the hypothetical distance of trips taken by trucks transporting frac sand within a given year.

Methods: 

      First I wrote the python script we were to use for the network analysis of this lab which was meant to select the mines that are active, not have a rail station for the sand to be loaded on, and not be within 1.5 km of a rail station. The script then exported a new copy of the file sifted through the SQL statements. 

Figure 1

      Next I loaded the final mines, rail terminals, and streets feature classes into the network analyst closest facility function with the mines being the incidents and the terminals being the facilities, and after clicking the solve button exported the routes to a new feature class, as seen in figure two along with the mines and rail terminals. 

Figure 2

      Before going on to the model building step I deleted the address field from the final mines feature class in order to avoid future errors it might cause. Then by dragging and dropping in the needed tools from the Arc Toolbox I created a model layout that would result in the automation of making the closest facility layer with the added locations, the solve function to get the route, create a copy of the feature class, project the data into the Wisconsin state plane system (meters), add fields for distance traveled and cost, the field calculations to create the end values (distance_travelled_inmiles = shape_length * 0.00062713 * 50 * 2) and (cost = distance_travelled_inmiles * 2.2/100) , and lastly a join to combine it all with the Wisconsin Counties feature class for the process of map making.


Figure 3

Results and discussion:

      The computed table of values for the effected counties as shown in figure 4 helps convey the cost associated with the road maintenance of each counties due to trucking of frac sand. Counties such as Chippewa, Dunn, and Wood all incur heavy costs from the needed road maintenance in the range of 329-753$ a year. Nearly all counties with suitable mines in them would experience some level  of moderate to high road damage and increased trucking traffic save for counties like Burnett and Winnebago which will be affected very little. Located below are maps (figures 5-6) and graphical representations (figure 7-8) of the road maintenance and distance in miles traveled by frac sand trucks. They directly reflect and create a visualization of the data in Figure 4.

Figure 4

Figure 5

Figure 6

Figure 7

Figure 8

Conclusions:

     

      The impact of frac sand mining on roads used for transportation is a serious issue to the counties involved and can create numerous problems such as increased road traffic, pollution (ground, noise, and air) and the costs of road maintenance and repair have to be covered by either the county or the mining company (else the roads fall in to disrepair and become unusable). The costs to each county vary on how the road networks connect mines to the nearest rail terminals which has been calculated using the ArcGIS network analyst, and in the end several select counties such as Chippewa, Wood, and Dunn simply have more connections to mines within their borders which leads to more traffic costs.  Based off the information I have gathered, depending on who has access to it (be it the county officials or the mine executives) they could use the information to plan the most effective routes that will incur the least costs to themselves or the county, and also figure out where the busiest areas of operation are in order to know where to avoid installing more mining locations.