Network Analysis

Nathan Sylte
4/21/2017

Sand Mine Road Cost Analysis

Background and Objectives:

The primary objective of this assignment is to become familiar with network analysis. This involved performing a transportation cost analysis on the hauling of frac sand on roadways as part of the ongoing sand mine suitability project. The intention was to route sand from the sand mines themselves to the nearest railroad terminal. It should be pointed out that some of the specifics as far as cost are just hypothetical, and were used for the purpose of learning network analysis. Therefore, the number of trips the haul trucks took was estimated as well as the overall cost.

An additional portion of the assignment that took take place before the network analysis involved generating a python script. This was done to prepare some of the data that was used for the network analysis portion of the project. The utilization of python allowed the initial data to be processed in a fast and efficient manner.

Methods:

The steps for the project can be laid out in the following order.

• Generate a python script that will select active mines, mines that do not have a rail loading station on site, and mines that are not located within 1.5 km of a rail line.
• Use model builder to calculate the closest facility route. This will be done by using the data generated from the python script and a ESRI Street Map USA dataset.  
• Calculate the transportation cost of sand truck travel on the roads by county.
• Generate a map showing the sand transportation routes and costs by county.

To begin the project a python script was created using PyScripter (Python Script ). This model selected active mines, mines that did not have a rail loading station on-site, and mines that were located further than 1.5 km away from a rail line (Figure 1).

Figure 1. Python script for network analysis assignment.

Next, with the use of model builder the closest route from the sand mine to the rail facility was calculated. Also, the transportation costs to haul the sand were calculated per county. The model is shown in Figure 2.

Figure 2. Data flow model calculating the closest route from the sand mine to the fail facility. Also, shows the calculated cost per county.

First, the closest facility layer was added and the ESRI Streets layer was used as the input. Next, the add location tool was used to specify the mines feature class as the incidents. The same tool was used again to make the rail terminal feature class the facilities. After setting the incidences and facilities, the solve tool was used to create the closest facility routes. The select data tool was then used to select the closest facility routes, and the copy features tool allowed the facility routes to be made into a new feature class. After the routes feature class was created, the routes feature class was projected into the correct coordinate system so it could then be intersected with the county boundaries feature class.

A new field was then added to the county boundaries / routes intersect. This field was named distance. The calculate field tool was then used to determine the total distance traveled on each of the routes on a per county basis. The following equation was used [Shape_length] * 0.00621371. This equation also converted meters into miles. Following this another filed was added. This field was called the annual cost field. Again, calculate field was used to calculate the annual cost per county. The following equation was used ( [distance_miles] *100 *.022). The number 100 represents the total amount of trips going to and from the each mine while the cost per mile is estimated at 2.2 cents per mile. After the annual cost was calculated summary statistics was used to determine the total annual cost per county. The COUNTY_NAM  field was selected as the case field and the statistics type that was used was SUM.

Result and Discussion:

Figure 3. Road cost analysis per county. Counties with the greatest cost are shown in dark orange/brown while counties with no sand transportation costs are shown in tan. Annual cost is in dollars and is only hypothetical.

Chippewa County represented the county with the highest annual sand transportation cost (Figure 3). There are many sand mines in Chippewa County and there is also a rail terminal. This contributes to high road usage by sand trucks. Trempealeau County also had a high annual transportation cost. There are many sand mines located in Trempealeau County, however, when compared to the mines in Chippewa County they are located closer to rail terminals. Overall, sand mine activity is primarily located in the Northwest/Central part of the state. Refer to the first blog post for information as to why this is Sand Mining in Western Wisconsin . Therefore, many counties do not have any costs related to sand mining.

Total cost per county is also shown below (Figure 4, Figure 5).

Figure 4. Total cost per county shown in excel.

Figure 5. Bar graph generated in excel showing total transportation cost per county.

Conclusion:

Overall, network analysis proved to be a very applicable and useful tool. This tool could also be applied to a number of different projects other than the sand mine road cost analysis. Furthermore, model builder was very useful on this project. It allowed the user to keep track of every tool that was used, and kept the data analysis portion of the project organized. This was important because on several occasions the model had to be changed. A future project could use network analysis to determine the quickest driving routes around sand mine rail terminals since the trains can be quite long.

Geocoding and Error Assessment

Nathan Sylte
4/7/17

Geocoding 

Background and Objectives:  

As a section of the multi-part sand mine suitability/risk model project, this part of the project will continue to focus on sand mining in Wisconsin. Trempealeau County will continue to be a focal area of the project. However, in this section of the project the study area was expanded to other areas in Western Wisconsin. The primary objective of this section is to geocode sand mine addresses with the use of a normalized data table. Sand mine location data was provided by the Wisconsin DNR, however, the data had to be normalized before they could be used for geocoding. 

The overall objectives for the lab are as follows:

•  Normalize the mines MS Excel table.
•  Connect to the geocoding service from ESRI and geocode the assigned mines. 
•  Connect to the departmnt ArcGIS server and add the Public Land Survey System (PLSS) feature class. 
•  Manually locate the assigned sand mine locations using either their address or PLSS location. 
•  Compare results with the actual sand mine locations as well as with other colleagues results. 

Methods:

To begin the sand mine location data table that was provided had to be normalized. The original data table contained several errors that had to be corrected before geocoding could take place (Figure 1). Spaces in the titles had to be removed and certain information such as addresses as well as PLSS locations had to be separated so they were alone (Figure 2). 

Figure 1. Original data table (not normalized). 

 Figure 2. Normalized data table with address and PLSS information separated. 

Next, the sand mine locations were geocoded. This was performed by logging into the enterprise ArcGIS Online Account in ArcMap. The normalized data table that was added into ArcMap was used to geocode, and was selected in the geocode addresses module. After the geocoding tool was ran the address inspector was viewed. Each candidate was viewed individually to see if the mine was indeed in the position that the geocoding results said it was in. Mines that were in the wrong location were manually re positioned using the pick address from map function. Some maps did not have an address provided. For these situations the PLSS location was used to determine were the actual mine location was. In other instances the address that was provided was not were the actual sand mine was. The mine location was set were the drive way met the road way for analysis purposes.

After the sand mines were georeferenced the results were compared to the actual locations of the sand mines. The actual locations were provided by the DNR. Results were also compared with other people in the class that were assigned the same mines. To acquire the information about the distance between the geocoded mine locations, the actual mine locations, and class mine locations, two selections were first performed. The selections were done to select for the mines that were assigned so that a direct comparison could take place (Figure 3). Mines were selected by using the Unique_ID field.

Figure 3. Selection tool.

After the correct mines were selected two spatial joins were performed to acquire the distance information (Figure 4). One join was performed between the geocoded mine locations and the actual mine locations. The other spatial join was performed between the geocoded mine locations and the class mine locations. To allow for the correct distance units to be used, the sand mine location data had to be projected into the NAD_1983_Wisconsin_TM (meters) coordinate system. 

Figure 4. Example of one of the spatial joins. 

Following the spatial joins the difference in distance between the mine locations was viewed and then transferred to an Excel spreadsheet for analysis. A map of the mine locations was also generated. 

Results/Discussion:

The primary objective of the lab was to accurately geocode the sand mine locations. Below is a table showing distance between the mine locations (Figure 5). The average distance between my mine locations and the actual locations was around 3000 meters. This is much smaller compared to the distance between my mine locations and the locations of the class mines. The indication of this result is that I was more accurate in placing mine locations than the others in the class who shared the same mines. 

Figure 6 displays were I put the sand mine locations compared to the actual locations. A great majority of the locations were correct. However, two locations in Barron County and one location in Jackson County were incorrect. The reason for this is that there was no mine located were the PLSS data said the mine was supposed to be. The DNR website was used to place these three mine locations which turned out not to be the actual locations. A likely possibility is that these mines are no longer active. The locations on the DNR website may have just been active mines owned by the same company. 

A reason for the large distance (3000 meters) between were I placed the mine locations and the actual mine locations has to do with were I placed the address. The address was placed were the driveway met the main road, whereas, the DNR placed the mine location directly on top of the mine itself. This would account for a good portion of the error. 

 Figure 5. Table showing the distances between the locations of my mines and the actual locations, as well as the distances between my mines and locations were the class put the mines. The right side is the distances between my mines locations and the class mines locations. 

Figure 6. Map displaying geocoded mine locations compared to the actual mine locations as provided by the WIDNR. 

Conclusion:

Overall, the lab was very effective in teaching how to geocode. Geocoding is an important skill to posses and should prove useful in the future. In the future, making sure every location is exactly were it should be will be the primary focus. The lab was also very helpful because required the data to be normalized. Normalizing data is often overlooked even though it is extremely important. If ones data is not normalized then it cannot be used in ArcMap.