Wisconsin Sand Mines: Network Analysis

Introduction:

Network analysis in general is the process of creating paths to determine flow in order to efficiently make strategic decisions about the most effective path to follow. For this assignment the GIS 2 class at the UW - Eau Claire was asked to look at network paths from sand mines to railroad terminals where trucks go to deposit there product (quartz sand). Sand mining trucks, which can weigh upwards of 30,000 lbs (15 tons) and when filled to capacity, near 80,000 lbs (40 tons), naturally creates quite a stress on the road ways. The purpose developing a network analysis then, was to look at road way impact (in the form of cost) from the trucks that use them.

Methods:

Since the assignment in question was divided into two parts, for the purpose of organization, this section will also be divided into two parts.

Part One:

Within part one of this assignment, the main purpose was to set up the provided data to begin network analysis. This process was done through PyScripter by creating, debugging and running tools in script form. Figure 1.1 is a copy of the script created and ran.

Figure 1.1: Python script for the first part of network analysis.

The python script was used to create the sand mine criteria listed below, in order to start part two of this assignment.

• The mine must be active.
• Each mine must not include an on-site rail loading station.
• Each mine must be farther then 1.5 kilometers away from a rail road terminal.  

The first step in this process was to set up field delimiters for the fields of interest. In this case, field1 equaled "Site_Statu" and field2 was equivalent to "Facility_T". Then variables were established. (A full list of variables can be found in figure 1.1.) From this point, three SQL statements were created to find all active mines(SQL 1), that have the word mine in the facility type field (SQL 2), but do not include the word rail (SQL 3) in that same field. For each new SQL statement made, a corresponding feature layer was also created. Then, two separate lines of script were created to use the select by location tool. The first was to select only mines that were completely within Wisconsin's boundaries, and the second was to remove any mine within 1.5 kilometers from a rail road terminal from the final selection. The final step in this python script sequence was to save select features. In this case two feature classes were saved; "mines_norail" and "mines_norail_final".

To finalize part one of this assignment, the final script was then debugged, and once deemed good, ran through ArcMap.

Part Two:

This portion of the assignment was based on the actual network analysis of the roadways from mine to terminal. To better view this process figure 1.2 shows the data flow model for this portion of the assignment. A list of the tools used is provided below:

• Make Closest Facility Layer
• Add Locations
• Solve
• Select Data
• Copy Features
• Project
• Intersect
• Summary Statistics
• Add Field
• Calculate Field

Figure 1.2: Data flow model depicting work path for sand mining network analysis

The first step in this analysis was to add the North America street map data that was provided by ESRI so that the Network Analyst toolbar would preform correctly. From this data the layer "streets" was added to the map of Wisconsin. From here, the first step in analyzing roadway impact was to create best distance routes from mine to terminal. Thus the first tool, "Make Closest Facility Layer" was used. Then the "Add locations tool was used twice, the first was to establish facility (rail road terminals) and incidents (mine location). The solve tool was then added to create the best route from incident to facility based on distance. From here, "Copy Features" took the field entitled "Routes" and saved it as the feature class "final_routes". This feature class then became the basis for the analysis of road impacts.

From this point, the data flow model shows the work path for finding the cost spent each year, per county, to maintain roadways. To establish the total distance of routes per county, the first step was to re-project the original feature class "final_routes" to fit the rest of the data, which was in NAD 1983 HARN Transverse Mercator. Then, "final_routes" had to be intersected with "WI_County_Bands" to create an attribute table that showed both of the features respective data. From here the "Summary Statistics" tool was used to create a count on the number counties with routes in them, and then to create a sum of the total length of said routes. After this was done, two additional field were added to the new summarized table, the first for total distance in miles (as data was projected in meters), and the second for impact cost. To calculate total distance in miles, the field calculator tool was used and the equation [SUM_Shape_Length] *0.000621371 was used. (0.000621371 is the number of miles in one meter.) The final calculation for cost was then determined by using the equation [Distance] *50 *2 *0.022. where 50 is the number of truck trips for each mine per year, 2 is the round trip taken by each truck, and 0.022 the the cost for roads (in dollars) per mile traveled. (This data was all predetermined in the parameters for this assignment, outlined by the course instructor.) Figure 1.3 is the final data table produced from the data flow model.

Figure 1.3: Final attribute table after data flow model was completed.

Results:

From the first part of this assignment a total of 44 mines were selected using the criteria listed in that section. Then based on part two, 48 facilities or terminals were found within Wisconsin's Boundaries. Thus 44 separate routes were calculated using 8 of the rail way terminals (*route paths often overlapped). From the data table above (figure 1.3), the bar graph down below was created in Excel.

Figure 1.4: A bar graph that depicts the impact or cost to roads by Wisconsin County from sand mine trucks

From this graph it can be seen that the three counties that spend the most on roadways due to mine truck impact were:

1. Chippewa County (6)*
2. Trempealaeau County (10)*
3. Barron County (6)*

*The number listed behind each county is the total number of sand mines, that meet the previously listed criteria, per county.

Figure 1.5 is the final map created from the data collected, and depicts the best route, based on distance, for each of the 44 mines listed.

Figure 1.5: Depiction of best route from sand mine to rail road terminal.

By looking at this data, along with the data table from figure 1.3, a couple of things can be noted. First, there was a large range of costs per county (range of 598). Secondly, because of this, the average cost ($132.44) was rather skewed.

Discussion:

It should also be noted that because only 8 terminals were used for 44 mines, some of the paths from mine to terminal were traveled more heavily than others. This means that repair expenses would be higher for these roads. Also, for further analysis of the effects of heavy mining trucks on surrounding road ways, it would be beneficial to look at, and compare actual pathways taken and the terminals actually used. This data could then be used as a reference layer to compare the data that was collected from this project. Another valuable piece of information for future studies in this area would be to look at road types, whether that would be state, county or rural, or the materials the roads are made of (i.e. cement, gravel, dirt). For both of these data types would vary in the amount of impact occurred and thus the cost to maintain them.

Conclusion:

In conclusion, counties with higher numbers of mines are bound to have a higher level of impact to the surrounding road ways and thus cost more for counties to repair them. To lower the level of impact, understanding the shortest or most effective route then becomes imperative to the stakeholders who manage them. By using network analysis, a better grasp of the road ways at hand and the best way to navigate them becomes possible.