Lab 7: Network Analysis

Introduction:

                This lab was created so that we can be exposed to network analysis. The scenario that we are using coincides with our semester project. One issue with Frac sand mining is that it has a very large impact upon roads. In order for sand to get from the mine to a rail terminal, sometimes it needs to be transported along public roads using a large truck. These trucks can get very heavy and when you have many of them leaving a mine every day, it can start to take a toll on the roads. Imagine the costs to the counties to repair these roads and think about the counties that have multiple mines that all take the same path to the nearest rail terminal. This lab will take an in depth analysis of the potential cost of repairing the roads used by these transport trucks. Keep in mind throughout this analysis that the costs to repair the roads and the number of trips per year are hypothetical.  

Methods:

                To start this network analysis we needed to obtain a road network so that we can use the network analysis toolbar. We got this data from ESRI street map USA. We also got the mines, rail terminals, and county outlines from the Wisconsin DNR. The mines that we used in this analysis are only those who do not have a rail terminals included with the mine or are further than 1.5 kilometers away from an existing rail line since we could infer that a spur would have been built. These mines were found from our python script that we created earlier.

                This lab required that we conducted our work through the use of a model builder. We activated the network analysis extension which allowed us to conduct a closest facility tool. We added the mines as incidents and the terminals as facilities. Since we are using model builder, we have to import every tool instead of just clicking on them from the toolbar, so we added the solve tool so that we can run the analysis. Then we had to select the data and copy the features so that from model builder we could save it to our geodatabase. Now that we have a feature class of our routes we have to calculate how many miles of those routes are in each county. We added a field so that we could calculate how many miles were in each of the routes. From here we added the summary statistics tool so that we could summarize by county how many miles there were and then using the field calculator we put in the expression:

Total Miles per County *50 routes a year (theoretical)* .022 (theoretically takes 2.2 cents per mile to repair the road)  

This gave us our final answer after we converted it into dollars of how much each county would theoretically spend on repairing their roads per year due to Frac sand mining. 

Data Flow Model created in ArcMap showing the different steps in network analysis.

Results and Discussion:

                We can see that these costs add up really quick.  Remember these results were calculated using theoretical values and in my opinion the results would be even higher since for many of these mines we are going to see substantially more than 50 trucks going in and out every year. Chippewa County according to my analysis would have the highest cost at $3657.46 and Winnebago the lowest at $17.01. Imagine that Chippewa County had a substantial more amount of trucks on the roads every year and how those costs would increase exponentially. 

Map created in ArcMap showing the mines used in the analysis across Wisconsin, their rail terminals and the routes taken to them.

Table showing the final statistics for each county affected by sand transportation.

Graph showing the relationship between the counties and the cost estimates per year on those counties.

Graph showing the relationship between the counties and the number of routes per county.

Conclusions:

                This lab was very beneficial to learning network analysis and also to understanding the impact that industry (not just sand mining) can have on an area. I am sure you can do many more in depth analyses into frac sand transportation and even look at the rail network and how sand mining has affected the rails and the relationship of commuter trains.

Lab 6: Geocoding

Goals and Objectives:

                The goal and assignment for Lab 6 is to geocode the locations of all of the sand mines in Wisconsin. We will do this by completing these objectives; normalizing the mines in Excel, Connecting to the ESRI Geocoding service, connecting to the department ArcGIS Server, Manually locating all the mines that only have a PLSS location, and comparing our results with the rest of the class.

Methods:

                We were given this data by the Wisconsin DNR and we quickly found out it was going to be a pain. The reason why it was going to be a pain is because it wasn’t normalized. What I mean by normalized is having the full address separated out into single fields for each feature. For example a street address needs to be separated into Address and Street, City, State, and Zip Code. You cannot have all of those in one single field because the geocoder won’t be able to pull apart the separate items. So it was up to us to go through the easy but time consuming process of normalizing the tables for the mines that we were assigned (18 mines each).

Once we completed that task we had to start the geocoding process by connecting to the ESRI geocoding service for one type of geocoding while also downloading the PLSS layers from our departmental server for the other type of geocoding. What are these two types of geocoding you ask? Well one is for those locations that have a normalized address associated with it that can be easily picked apart to find the exact address. The other type is for those locations that don’t have addresses associated with them but instead have PLSS locations which then have to be interpreted by ourselves to find the general location and then referencing other imagery to see where a mine may be currently.

This task was very time consuming as we had some problem locations that took many different referencing images to find the mine. When we had all the mines geocoded we exported them as a feature class so that we could merge them with the rest of the class. Here came a whole other can of worms as not everyone in the class had the same table structure so we had to go through every single persons attribute table and edit or delete data that was restricting us from using the merge tool to combine all of our geocoded mines. This took many problem soving skills and knowledge of Arc to figure out what was needed in order to merge two or more layers together. One common problem is that we couldn’t merge layers that had different types of fields. For instance some people for some reason had a string field that needed to be merged with a double field (doesn’t work).

LONG STORY short, the merge tool finally ran. With this new layer we put it into a spatial join with a layer that contained the actual mine locations (Thanks a lot Dr. Hupy….). Understanding that one of the reasons this lab is in place is to frustrate us so that we never give other people bad data is a hard lesson to learn but must be learned none the less. Anyways, the new layer created from the spatial join now had a field that gave us the distance between where we geocoded our mines to and where the actual mines were according to their lat/long. I compared my geocoding skills to the rest of the class and found that the class’s average distance that they were away from the actual mines was 1857 meters while my average was 306 meters closer at 1551 meters. I guess that means I did something right.

Results:

Figure 1: Comparison of a Table that is normalized versus a table that is not.

Figure 2: Map showing the correct actual locations of the mines versus the incorrectly geocoded ones.

Figure 3: Final table with a field showing the distance between the actual locations and the incorrect locations in meters.

Discussion:

                We can know which points are actually correct by referencing other images of the locations and by using the exact GPS coordinates. This way you don’t have any error resulting from incorrect PLSS interpretation.

Conclusion:

                The overlooking lesson from this lab was DATA INTEGRITY and why we should do our absolute best to never do this to anyone in our professional lives. Geocoding is an essential part of GIS especialt when you’re dealing with people and you want to make sure and get it right so you don’t but Joe in Bobs house. You can also see how even when you put a lot of time into trying to geocode all these addresses you can still be off as nothing is exact as the actual Lat/Long. However, this lab was instrumental as we move forward with our semester project. From here we can now take a look at network analysis and how we can route the sand from those mine locations to railways where they can be further shipped off.