Network Analysis

Goals and Objectives

The goal of this exercise was to learn how and perform network analysis in a real life scenario. The example scenario we are considering the transportation of frac sand. We routed the sand from the mines to the nearest railroad terminal. Once that is done we learned how to estimate the number of trips the trucks will take and the cost this traffic will incur on the local roads. We then figured how much each county in Western Wisconsin would have to pay to maintain these roads. This is a relevant example because in the real world frac sand trucking has a significant impact on the local roads. 

Methods 

     The first step in this part of the lab was to bring in the streets network data set for the whole U.S. The next step was to bring in the rail terminals data set. Since we are only interested in the mines that move their sand by truck and rail I did a select by MODE_TYPE and did truck and rail mines. I then took that selection and made a feature class that only has the truck and rail terminals.
     The next step was to do some network analysis. I opened the network analyst window and added a new closest facility layer. I loaded my rail_terminals as the facilities and my mines as the incidents. I then ran a solve this gave the routes from my mines to the closest rail terminal. It appears on the map that there is only one truck on each route but if you select the route there are many records of trucks on each route.
     Next I began to build my model in model builder. I started by recreating the above described routes through use of the make closest facility layer and add locations tools. Just like above I add the solve tool to my model and then run the model. I then used the select data tool to extract my routes from the closest facility solver and save them in my goedatabase. To save them I used the copy features tool.
      Once I had all the routes to the closest rail terminals could then begin to find how much the truck are going to cost the county in road damage. The first thing I did was to find the distance the trucks are traveling. I added a field and called it Miles then I did a calculate field and divided the shape_length field in the route table by 1600. This gives the distance in Miles. Next I added another field called Cost. I took my Miles field and took it times 100 because the trucks make 50 trips. I multiply that by 2.2 cents and then divide that whole equation by 100 because there are 100 cents in a dollar.(See calculations below.) I now had totals for how much each trip cost. The next step is to find out how much each county spent. In order to do this I did a field join between the Wisconsin counties class and my routes class. Once those are joined the final step was to run a statistics summary on the Cost field. I summarized it by county names so it took all the costs for each county name and summed them. The chart in the results section in what that table looks like. Directly below is the model I made and ran to get my desired results.



Model Builder

[SHAPE_LENGTH]/1600 = (x)mi   Then I did.  ((x)mi *100)2.2)/100= cost per trip

Results

After the model is run the map below is the result I got. It shows all the mines we are concerned with in Western Wisconsin as well as the rail terminals. The map also shows all of the routes the trucks are taking from a mine to the nearest rail terminal. 





Final Results Map

The chart below shows the hypothetical cost that sand trucking would cost by county, These number are all made up for a an example, however you can see that there is a cost associated with the transportation of the sand. I would venture to guess that the true numbers for the cost to each county is in the thousands of dollars every year for road maintenance. We only figured the cost for 50 truck trips from each mine in a year when in the real world that can be done in a day. Who should pay to fix the roads? Should the county have to pay for this cost because they are in charge of maintaining the roads or should the sand companies pay for it because they are the reason the road is deteriorating faster than with normal traffic. If the mines have to pay for it, is that fare because there are mines that put the sand directly onto rail and wouldn't have to pay this "road tax". This is a question that has been raised and discussed in depth. It is one of many possible draw backs that people see about frac sand mining. Air quality concerns along these roads is also a topic of discussion. 

Cost by County

Conclusion

The point of this lab was not to look at pros and cons of frac sand mining it was to apply network analysis to a real life situation and understand the results. This is a valuable skill that is widely used in many situations. At a city, county, state, and world scale for emergency response, package delivery, every day use of your GPS to get from point A to point B and countless other situations. I really enjoyed learning this technique. It  was more of a hands on technique and seeing how it can be applied in the real world makes it more interesting and realistic to me instead of just crunching data numbers like some of the other techniques. 

Sources: ESRI Street Map USA

Geocoding

Goals and Objectives

The goal of this lab was for us to connect to one of the databases on the departmental server and geocode some data that was in an Excel file. Once did that another goal of this lab was to learn how to gather, normalize and geocode data we obtained from the Wisconsin DNR. This is one of the first parts of a larger project we will be building on throughout the semester involving sand mining in Western Wisconsin. Our study area will consist of Trempealeau county and several others.

Methods

The first part of this lab was to connect to a geodatabase on the geography department’s server. This geodatabase included an Excel sheet with information about a couple of hundred Frac Sand Mines in Western Wisconsin. This data was given to us by the Wisconsin DNR. This Excel did not have all the data or was not organized in a way that we could geocode right away so we had to normalize the table. In other words I created 5 new columns to the Excel file, State, City, Street, Zip Code, and PLSS. By adding these fields you make the table readable by the geocode tool. So once we had the table normalized we could geocode the data or find the addresses and locations of these mines. We did this because not all the mines had an address with them. There are two ways of geocoding, manual and automatic. For the automatic way you put the normalized table into the geocoder and it will give you the street address for the mine if it can find it. The automatic was is obviously easier but most of my mines had to be manually geocoded. This is done using a wide variety of tools. I used Google Maps and Google Earth along with the PLSS finder. The Public Land Survey System is a grid system that is used to keep track of land parcel ownership. If I knew the PLSS address for a mine I would put it into the PLSS finder and it would take me to the grid square where that address is. This narrowed done my search area. I would then switch to Google Earth or Maps and look around that area for a sand mine with similar dimensions to what it is described as in my Excel sheet. On some occasions I could find the mine and get a street address off of Google Earth or Maps but a lot of the time the imagery is not up to date enough to show these mines because most of them are fairly new. Another method I used to find the mines was to just Google the name of the mine or the county it was in. I found a couple of my mines this way because some counties have all the mines in a document with addresses and owner information and the mine name so those made this easier. This was a challenging and pretty time consuming process. I was only responsible for 16 mines and of those I had to manually find 5. This took me a couple of hours.

Results

Below are a sample of the non-normalized table and the table after I cleaned it up. You can see in the non-normalized table that all the address information in just in one column. The goecoder cannot differentiate between street addresses and zip code and things like that in that format so I normalized the table. I took that address information and spread it out into separate columns which the goecoder could read.

Non-Normalized Table

Normalized Table

After we had all of our mines geocoded and placed into a shapefile in ArcMap we compared how much variation there was between the places each of us placed our mines with 4 other people who also placed the same mines. We did this by using the point distance tool which selects the mine closest to each of my mines and measures the distance between them. My table is measured in meters and as you can see below there was a lot of variation as to where the mine was placed by other people compared to where I put mine.

Distance Table in Meters

Discussion

During this lab there were a couple of different kinds of errors that could occur. During the geocoding process the major and most obvious error that occurred is an operational error. It is operational because it is a user error or caused by human mistake. When everyone had their mines geocoded and we brought them all together into one map the same mine was placed in multiple different places. In a perfect world each geocoded mine should have been in the same spot as the others with the same ID. This is caused by a mistake in image analysis or just by the opinion of the person where the mine should be.

Another type of error is called inherent error. This error occurred when we measured the distance between the mines and got different measurements. This is caused by the limitations of the computer or technology you are using to make the measurements. Technology is only so good. So operator accuracy is limited by machine technology and also by the resolution of the data. The lower the resolution the less accurate the measurements will be.

With these errors occurring the location of these mines is not very accurate in most cases. If we were in need of a very accurate location we could take the locations we got and compare them to a higher resolution data set that has more accurate locations of the mines and calculate the root mean square error of planimetry. 

Conclusion

Data that is not normalized is hard to work with. This lab showed the importance of normalized data and how if it isn't, preforming other tasks with it such as geocoding is nearly impossible. Knowing how to normalize data is a good skill to have because you will get data in all kinds of formats and from all kinds of places that can not be used until it is normalized. Geocoding is also a very valuable skill to know. It can be applied in many practical situations. The results of this process are what you see below. This is my map showing all the geocoded mines in an easy to understand way. It includes the location of my 16 mines as well as the locations of those same mines as located by other students in the class. The differences in the locations illustrate the errors that can occur during the geocoding process. 

Final Map of Mines

Data Source: Wisconsin DNR