Post 5: Network Analysis

Goals and Objectives:

The purpose of this lab was to conduct a network analysis to determine the economic impact of trucking sand from mines to rail terminals on local Wisconsin roads. Trempealuea County was of particular interest in the analysis. The number of truck routes and cost per mile were estimated to determine hypothetical costs of mining per county in Wisconsin. A script was created to select mines suitable for analysis. Network analysis was then used to determine the closet route from each mine to the nearest rail terminal. Using network analysis is a valuable tool that can be used by project managers to predict the costs frac sand mines will have on Wisconsin roads.

Background: 

The Midwest is home to many deposits of sand well-suited for fracking. In order to use frac sand in hydraulic fracking, the sand has to be transported from the mining site to the user. It it quite typical for mines to use rail roads to transport sand to the user. The only problem is that sand mines are usually in rural locations, and are often not near rail roads. In order to get their sand to the rail roads, mine operators will use trucks on rural roads that are not built to withstand heavy loads over long periods of time (National Center for Freight & Infrastructure Research & Educatoin, 2013). Once frac sand mines are in full swing in Wisconsin, approximately 40 million tons of sand will be transported out of Wisconsin per year (Wisconsin Department of Transportation NW Region Planning Staff, interview, May 16, 2012). In order to upkeep the roads, Wisconsin has implemented road upgrade maintenance agreements (RUMA) with frac sand mine owners. The agreements ensure mine owners pay for any repairs needed for roads their trucks drive on (National Center for Freight & Infrastructure Research & Educatoin, 2013). The suitability model we create will determine the cost of trucking sand on Wisconsin roads. This will enable land planners to make better-informed decisions on where to allow frac sand mines.

Methods: 

Data Sources:

• Dr. Christina Hupy
• ESRI street map USA
• Wisconsin Department of Natural Resources

Part 1: Creating a Script

The goal for the script was to select frac sand mines suitable for network analysis in our class project, which is focused on creating a suitability model for frac sand mining in Wisconsin. These mines had to fit the following criteria:

• Must be active 
• Mine must not have a rail loading station. Mines with a rail loading station will use the rails to transport their sand, not the local roads. We are focused on mining's impact on local roads. 
• Must be 1.5 km away from any rail roads. Rail road data is not totally up to date, and mines closer than 1.5 km may have had a rail spur built to them within the past few years.

Wrtiting the script used the following procedure (Figure 1): 

• Set up the script in PyScripter
• Set the variables for the feature classes and feature layers to be used in the exercise
• Write 3 SQL statements to query the data based on the above criteria
• Use the SQL statements to select the mines that meet your criteria
• Use arcpy.MakeFeatureLayer_Management
• Use arcpy.SelectLayerByLocation_management to 
• Select only mines in Wisconsin
• Remove all mines within 1.5 km of a rail line
• Save the output layer resulting from the SQL statement using the CopyFeatures_management tool

Figure 1: The python script for this exercise used SQL statements to select frac sand mines suitable for network analysis. The SQL statement was based off three criteria listed above. 

Part 2: Network Analysis

The goal of network analysis was to find the quickest route from each frac sand mine to the nearest rail terminal. The following steps had to be taken to complete the network analysis:

• Connect to ESRI's originalrawdata2013\streetmap_na\data
• Add the "streets" network data set ArcMap
• Turn on Network Analyst and open the Network Analyst toolbar
• Make sure to turn on the Network Analyst extenstion in ArcMap
• Add the final rail selection from the scipting portion of this exercise
• Add the rail terminals feature class
• Select only the terminals that have RAIL in the MODE_TYPE field. Export the selection as a feature class and add it to the map. Use these rail terminals in the analysis.
• Use "closest facility" analysis in Network Analyst to determine which rail terminal each mine will transport its sand to and the most effecitent route between them
• Add a new closest facility layer form the Network Analyst menu
• Load the final mines and rail terminals into the closet facility layer
• Incidents: mines
• Facilities: rail terminals
• Solve the closet facility
• Use Model Builder to develop a data flow model that will determine the most efficient routes between each mine and the closest rail terminal
• Before creating the model, delete the address field in the Mines final feature class (the address field causes an error in model builder)
• Create a new tool box in your geodatabase to save the model to
• Add the Make Closest Facility Layer tool 
• use the Travel to facilty option in the tool
• Use the Add Locations tool to add both the Mines and the Rail Terminals feature classes 
• Add the Solve tool and run the model
• Export the route that was produced from the Closet Facility Layer tool as a feature class in your geodatabase
• Use the Select Data tool to select the route
• Use the Copy Features tool to export the Routes feature class to your geodatabase
• Use Model Builder to calculate the lenth of route by county and estimate the costs each county incurs in road matainanc due to trucking sand
• Project the “Routes” output from the model to Wisconsin Transverse Mercator 1983 (WTM83)
• Intersect the “Wisconsin Cont. Bond” feature class w/ the projected routes
• Add a field to calculate miles (from meters) for that field
• Summarize miles based on County name
• Calculate the cost per county based on results 
• Hypothetically, sand trucks will drive 50 times per year to the closet rail terminal, and will have to make the trip back to the mine as well. The hypothetical cost per truck mile is 2.2 cents. 
• Use the following equation to determine cost: 
• Cost in dollars per year = [Distance (miles)]*[2.2 cents/1 mile]*[$1/100 cents]*100
• Join table back to “Wisconsin Boundaries”

Figure 2: The data flow model was created in Model Builder to create a feature class that contained values for damages each county incurred from trucking sand.

Results:

Seventeen counties in the state had frac sand mines, and their roads were therefore affected by sand trucking. Please note the number of truck trips and costs are hypothetical. They were created for educational purposes, and are not completely accurate of the real world. Even though costs are hypothetical, the results are still meaningful because network analysis shows the frequency the roads are traveled by sand trucks and the resulting cost the trucks incur due to the frequency. 

Figure 3: The map shows the locations of mines, rail terminals, and quickest routes between mines and rail terminals. Seventeen counties had mines in them, and the darker-colored counties incurred larger damages due to trucking sand.

Figure 4: The table shows distance sand trucks travel per county and the cost incurred due to those trucks by county. The number of truck routes and costs are hypothetical, but still meaningful since they are based off real frac sand mine and rail terminal locations.

Figure 5: The bar graph shows which counties incurred the most damage due to trucking sand. Thirteen counties sustained less than $200 in damages per year, while only one county sustained more than $400 in damages per year.

Discussion:

The map in Figure 3, the table in Figures 4, and the bar chart in Figure 5 show the trucking impact is greatest in Chippewa, Barron, and Eau Claire Counties. This is because there are 12 mines in these 3 counties and only 2 rail terminals. Mines in these counties must then truck their sand long distances to the nearest rail terminal. The total mileage trucks hypothetically covered per year in each county were: Chippewa County 278.72 miles, Barron County 169.24 miles, and Eau Claire County 154.84 miles.

Trempealeau County had 10 frac sand mines, which was the greatest number of mines per county in Wisconsin (Figure 3). Surprisingly, Trempealeau County only incurred $180.82 in damages, which made it the fifth (out of 17) most expensive county in the state based on damages due to trucking sand. One reason to explain why the county incurred such low costs is because the average distance between frac sand mines and rail terminals was only 1.5 miles. The maximum distance traveled was only 11.5 miles in Trempealeau County. In comparison, Chippewa County had an average distance between mines and rail terminals of 4.6 miles and a maximum distance of 7.9 miles. This meant that sand trucks traveled three times more miles in Chippewa County than in Trempealeau County per year, which resulted in Chippewa County paying 3.4 times more money for road damages than Trempealeau County. These results demonstrate Trempealeau County would be a good county to have frac sand mines operating in.

Conclusion:

Overall, this lab helped me develop a lot of spatial and GIS skills. I further developed my scripting and model building skills. I also learned how to conduct network analysis in ArcMap, which was very helpful for the class's suitability and risk model for frac sand mining in Wisconsin. The lab gave me a chance to build upon my spatial reasoning skills, which helped me determine why Chippewa County had the highest costs for sand trucking out of all Wisconsin Counties. I made the important discovery that Trempealeau County is a very suitable place for operating frac sand mines because the county's mines are within 1.5 miles, on average, of rail terminals. Overall, the skills gained in this lab will be valuable to the rest of the class project and my career in the geospatial workforce.

References Listed:

Hart, M. V., Adam, T., Schwartz, A. (2013). Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study. National Center for Freight & Infrastructure Research & Educatoin, White Paper Series: 2013, Retrieved  November 19, 2015 from http://midamericafreight.org/wp-content/uploads/FracSandWhitePaperDRAFT.pdf

Post 4: Geocoding Frac Sand Mine Adresses

Goals and objectives:

The goal of this lab was to geocode the locations of all frac sand mines in Wisconsin and compare the positional accuracy of the geocoded results to my classmates mine locations and a shapefile of actual mine locations.

Methods:

Geocoding using regular addresses

A table containing the addresses of all frac sand mines in Wisconsin was obtained from the Wisconsin DNR (Figure 1). Addresses from the table were then normalized to ensure they worked in the Interactive Rematch Inspector in ArcMap (Figure 2). Normalized fields included address, city, state, zip code, and other fields. After the normalized table had been created in Excel, the table was added into ArcMap. The normalized table was turned into a shapefile using the "World Geocode Service". Once the shapefile was created, the Interactive Rematch Inspector was used to check the positional accuracy of each frac sand mine address. Addresses that had an automatic match and regular street address were usually correct. Addresses that had an automatic match but only had a PLSS address were incorrect. The geocoder placed incorrect addresses in the center of the city. Incorrect or unmatched addresses were placed in the correct location using the Interactive Rematch Inspector. Mines were placed as close to the road network or the driveway of the address for the best positional accuracy.

Addresses were geocoded with regular addresses because the Interactive Rematch Inspector only works with regular addresses.

Manual geocoding using PLSS addresses

In order to ensure the best data accuracy for mine locations, Public Land Survey System (PLSS) addresses were used to check the accuracy of the geocoded addresses. PLSS is system that divides land based on units called sections. Sections are further divided into "townships", which are each 36 square miles. Township numbers are assigned to each location to determine how far north the location is of the Wisconsin-Illinois border, and range numbers are assigned to determine how many townships (6 miles wide) east or west the location is of the principal meridian (Univeristy of Wisconsin-Extension, 2000).

A feature class containing PLSS sections was added to the map to help with referencing PLSS addresses of mines to locations on the map. After manually locating each address with the PLSS, inaccurate addresses were placed in the correct location using the Interactive Rematch Inspector.

Addresses were manually goocoded with PLSS addresses because these addresses were usually accurate, and using both regular and PLSS addresses was a good way to double check the positional accuracy of the mine locations.

Comparing data

After I had geocoded all frac sand mine addresses, I compared the positional accuracy of my mine locations to my classmates' mines. To do this, all classmates' shapefiles were merged together using the "Merge" data analysis tool. Then I had to select classmates' data that had the same mines as me, using "Select By Attribute" command and searching with the Mine Unique ID field. The merged classmates mines and my mines were then compared using the "Near (Analysis)" tool, which calculated the distance between my mines and my classmates' mines. The tool created a distance field in the feature class containing my mines. The same comparison process was used to compare the positional accuracy of my mines to the actual mine locations, which were provided as a shapefile from our professor, Christina Hupy.

Results:

Figure 1: The above table was obtained from the Wisconsin DNR, and contained information about all frac sand mines in Wisconsin. The data was not normalized.

Figure 2: A normalized Excel table was created for geocoding addresses for all frac sand mines in Wisconsin.

Figure 3: The map shows the frac sand mines I geocoded using the data from the WIDNR.

Figure 4: The map shows the locations of my mines in comparison to the same mines that were geocoded by other classmates.

Figure 5: The table shows the distances between my mines and my classmates' mines. Larger distances indicate larger discrepancies between the two data sets, which indicates error in one or both of the data sets.

Figure 6: The map shows the locations of my mines in comparison to the actual mine locations, provided by our professor Christina Hupy.

Figure 7: The table shows the distances between my mines and the actual mine locations. Larger distances indicate larger discrepancies between the two data sets, which indicates error in my data.

Discussion:

As shown by the distance tables (Figures 5 and 7), there were errors in my data and my classmates' data when compared to the correct mine locations. Half of my data agreed very well with my classmates' data because identical mines were within 30 meters of each other (Figure 5).  My data did not agree as well with the actual mine locations because they were usually off by hundreds of meters, which still isn't too bad (Figure 7). I do not really know how to explain why my mines were this far off. These errors were most likely gross errors caused by myself. Gross errors are simple mistakes made by a user. There were also inherent and operational errors in my classmates' and my data. Inherent errors are errors that already exist with the data. For example, classmates all had different projections for their data, which did not agree with my data. This projection problem was inherent with the classmate's data. Another example of an inherent error in the class data was the attributes for the mines were not input the same into each classmates normalized table. Some people had the mines specific ID in a field called Mine Unique ID, while others had the ID in a field called Mine ID. Operational errors that existed in the data resulted from user error. For example, some of my classmate's mine locations and my mine locations were just plain off. This was a result of error on both of our behalves, and created large distances between the actual mine location and our mine locations. In order to know which mine location points are correct, you must use only the points that match closely with the actual mine locations data. A close match will have a small distance number.

Conclusion:

Overall, this lab was eye-opening because it showed me the difficulties that come from working with other people's data. Working with data from the WIDNR was slightly tricky, since I had to separate the data into different columns. Geocoding with these addresses was also hard because some of the addresses were completely wrong. I had to place the mines in the correct location (to the best of my knowledge) many times. Working with classmates' data was another challenge. The data did not want to merge at first, but it worked after I deleted fields except for the x,y, and Unique Mine ID fields. Then I had to select classmates' data that had the same mines as me. Overall, the geocoding and comparison processes were very time consuming, but educational. Experience with geocoding and working with foriegn datasets will benefit my future career in the geospatial workforce.

Reference List: 

Univeristy of Wisconsin-Extension. (2000). Wisconsin Geological & Natural History Survey [pdf]. Retrieved from: http://wgnhs.uwex.edu/pubs/es0442002/