Network Analysis

Introduction:

An important component of the logistical side of mining is how and where to bring the sand once it is mined in order to ship it to the appropriate receivers. There are a few different ways to transport sand to the market, two of which are by railroad and via truck. Given that, we wanted to figure out which routes would be best for trucks to pick up the sand and deposit them to the nearest rail terminal which would ultimately bring the sand to the market.

In addition, trucks transporting frac sand to rail terminals take quite a toll on the public road system. This lab will help determine just how far trucks taking our routes travel as well as the hypothetical cost that is inhibited on each county due to frac sand truck transportation.

Methods:

In order to eliminate the mines for this project that already had a rail system in place, a Python script was run in order to determine which mines were greater than 1.5 kilometers away from a rail line. The script can be viewed here under 'Script #2'. This result ultimately leaves mines that do not use their own rail system to transport their mined sand. 

Once the mines without rail systems were selected, routes were developed using network analysis.Network analysis an extension of ESRI's ArcMap which serves as a GPS of sorts; it allows for individualized inputs and outputs to create a very specific routing plan. ArcMap has its own routing layer that contains all of the streets at the time the layer was created. For this project a specific routing tool called 'Closest Facility' was required. This allows the user to display the best routes between incidents and facilities. The 'facilities' for this lab were the rail terminals and the 'incidents' were the mines.

The routing was created in Model Builder. In addition other tools were used including the 'Project' tool to change the Geographic Coordinate System into a Projected Coordinate System. This gave us the ability to calculate the distance that it took to get from one side of a route to the other (because a GCS does not allow you to calculate surface distances). 

To gain an understanding of the cost that would be required for the trucks to transport the sand from mines to market, a calculation was done using a hypothetical scenario. The scenario was said that every route was traveled on 50 times per year (from the mine to the terminal and back) and the cost per truck mile was 2.2 cents. Be aware that the number calculated for this lab were hypothetical and by no means reflect the actual cost of trucking sand to and from mine sites. Below is the data flow (figure 1) that was used to find the closest facility routes, change the projection, and add/calculate fields in order to determine the distance of each counties routes. To break the distance down to the county level, the summary tool was used. The distance that was determined for these counties was then applied to the cost calculation and the table seen below is the output of the data flow model (figure 2).

Figure 1. Data flow of the entire process from determining facilities to calculating both the miles and cost fields. The output resulted in routes from the mines to the rail terminals, and two additional field calculations.  

Figure 2. The output table after Model Builder was successfully 'run'. The 'calc_miles'  field reflects the length of the trucking routes in each county by mile. The 'calc_cost' field reflects the result of the cost that trucks have on the roads systems at the county level. 

Results:

The results of the routing and cost analysis that were conducted from the data flow are shown below (figures 3 and 4).

Figure 3. The final map showing the routes that truck should take from the mines to the terminals for the sand to be shipped.

Figure 4. Cost Analysis map which depicts the cost that is inhibited from trucking sand to market by each county. The counties in grey were not calculated for their maintenance cost because no routes were created in those counties.

Conclusion:

The process of figuring out the routes for trucks as well as their mileage calculation is no easy task. In addition to that, the shear cost of mining on a county is not just the potential environmental hazards, but also the monetary cost of using the public roads system for the benefits of a private industry. Below is the cost to each county based on the amount and distance that trucks carrying sand inhibit largely on taxpayer dollars (figure 5).

Figure 5. Graph depicting the cost of trucking sand from the mines to the terminals and back to the mines. The number that was used for this calculation was the hypothetical number of 50 truck trips per year per route with the cost of trucking the sanding being .022 cents/mile.

I had never considered weight to be a factor in the cost of frac sand mining impacts. This brings the question: who pays for the cost of the roads system? Is it a tax specific to frac sand mining companies? This issue has implications on the ethics of using public road systems in order to bring goods to the market. This issue is extensively studied in a case study of Chippewa County's frac sand mining transportation impacts which is found here.

Discussion:

We discussed in class that some people have businesses just to serve as a routing developers. I now understand why, because the amount of precision and detail that is required to accurately measure roads and addresses is incomprehensible. Even the ESRI street routing layer that we used was out of date, and that is not a cheap ESRI extension.

I noticed in my cost map that there was no route created in the very northwestern counties. The first map has the route because it was calculated outside of Model Builder. A parameter set in Model Builder that was different than the original route that I calculated was that I intersected the state of Wisconsin with the routes, which eliminated the routes outside of the state. Had the route been executed correctly, the cost in those counties would have been much higher because of the long distance that the sand needed to travel in order to get to market (see figure 3).

Sources: 

Wisconsin Department of Natural Resources. (n.d.). Retrieved November 8, 2015, from http://dnr.wi.gov

Network Dataset analysis was calculated based on ESRI Street Map USA.

Transportation Impacts of Frac Sand Mining in the MAFC Region: Chippewa County Case Study. (2013). Retrieved November 20, 2015, from http://midamericafreight.org/wp-content/uploads/FracSandWhitePaperDRAFT.pdf 

Geocoding Frac Sand Mines in Wisconsin

 Goals and objectives

This lab will serve as a continuation of the previous frac sand mining lab. In the previous lab, spatial data was collated for Trempealeau County, Wisconsin. This lab will take raw mine address information via an Excel Spreadsheet and transform it into a map so that the mines can be spatially analyzed for sand mining suitability in subsequent labs, which will be done through the process of geocoding. After geocoding, we will compare our results to our peers and the precise locations (provided by our professor).

The objectives are listed below (figure 1).

Figure 1. Geocoding lab objectives.

Methods

Many steps have to be completed in order to properly geocode. The data that we received in Excel from the DNR was quite messy and the data was not normalized (figure 2). In order to normalize the data we needed to separate the location information into different cells containing: address, city, state, zip code and unique mine ID (figure 3).

Figure 2. Non normalized data in Excel. Note that the address column contains PLSS information as well as the zip code, city, and other information. 

Figure 3. The normalized output table, which was then exported to ArcMap for geocoding.

The data provided for the lab was distributed by the Wisconsin Department of Natural Resources (DNR). Much of the data that they receive from the mines is sporadic and we received that data directly. Some of the locations included the address, city, and zip code and we could easily find the location of the mine; however, other contained only the PLSS. PLSS is the Public Land Survey System (figure 4), which is the grid system that Wisconsin, and much of the United States, has based their land descriptions on. This is why there appears to be a grid-like pattern on aerial imagery (figure 5).

Figure 4. PLSS grid. If the mine sites were given without an address, they instead had a PLSS. The image depicts how  to read a PLSS.(http://geology.isu.edu/geostac/Field_Exercise/topomaps/plss.htm)

Figure 5. Aerial imagery of farmland. Note the grid-like patches that are derived from the PLSS.
http://oklahomafarmreport.com/wire/news/2012/11/media/05086_FarmAerialView06282012.jpg

Once the Excel table was normalized, it was brought in to ArcMap and the Geocoding Extension was turned on. The ESRI address database could then match all potential addresses. 14 of my 19 addresses were found and 5 were unable to be found. Of the ones that were found, the locations were verified by locating each address in ArcMap. If the address point was 'off', then a new point could be created to make the location accurate.

The reason why some could not be found was due to ESRI's inability to find locations based on PLSS data when address information could not provided. Without a hint of where the addresses were located, we needed to turn to the PLSS shapefiles containing all of the information required to locate the PLSS mines. Some PLSS descriptions were straightforward, however some of them gave multiple PLSS descriptions, which made it more difficult to find the exact location of the mine. To attempt to combat this, I looked up the names of the mines to see if their relative locations could be determined using Google Earth, I could then hopefully narrow down the mine location.

In the end of my geocoding, I kept only 3 of the address markers that ESRI found in the initial stages of geocoding. Although many of them were near the mine locations, they were often far away from the road, which is less accurate for the information required in the future.

Once all of the geocoding was complete, I compared my results to my classmates and my professor's exact mine addresses. Organizing the mines to do this was tricky, as there were four other peers that had my mines. To combine their data into one shape file, I went into the attribute table and selected the mines that I was given to geocode and subsequently created a new layer from the selected features. The reasoning for doing such was to be able to sift through less mine locations than if I had performed a merge of all of my peer's mine sites, which would have included mines that I had not geocoded. Another element of inconvenience was that we all named our 'unique mine ID' field a different alias name. Without the same naming scheme, it makes it initially impossible to merge the data. This required creating new fields in each of my peer's attribute tables. In addition, all of the shape files needed to be projected into a projected coordinate system instead of a geographic coordinate system in order to be able to calculate the distance in measureable lengths, not degrees.

The 'merge' tool was then used to create one shape file with my peer's mine information. To determine how accurate my geocoding was comparatively to my peers and the exact locations provided by my professor, I used the 'Point Distance' Tool.

 Results

As expected, my mine locations were 'off' comparatively to the actual mine locations. Below figures 6 and 7 show the output tables after running the 'Point Distance' tool. The distance calculated was in meters.

Figure 6. This was the output table from the point distance tool. The distance shown is in meters shows how far the actual mine location was in comparison to the mines I geocoded. The average error distance was 3,756 meters from my mine sites to the actual mine sites.

Figure 7. The output table from the point distance tool. This comparison was between me and my peer's mine locations. Due to missing mines from some of my peers, I could only show the distance between 13 of the 19 mines.

Below figures 8-10 are the maps for the locations of the mines that I found as well as the actual mines and my peer's mines.

Figure 8. The location of the mines I geocoded and the actual locations of the mines. Overall, my locations were fairly close to the actual mine locations.

Figure 9. My mines in comparison to my peer's. They were mostly spot on, which is a relief to know that the locations that I found were very similar to my peers.

Figure 10. All of the mines that were compared in the process of geocoding.

Discussion

In the end I had a larger mean error with the actual data from the DNR at a distance of 3,756 meters whereas my error mean distance with my peers was 1,030 meters. One of this issues with the number with my peers is that I only had 13 of the 19 mines to compare the distance to because some of my peers did not post their mines to ArcMap. The errors largely in part came from issues in the data automation and compilation areas which included digitizing (or geocoding). This is because geocoding (if you choose to use the points found from the geocoder) finds its addresses generally by estimation, not a precise location. In addition, anyone adding points manually will not have the same points as someone else, so even the 'correct' points could be wrong technically.

Additionally, attribute data input, an error type, could have been incorrect by the DNR or the people that they received the information from. This is very likely considering that the data provided was sporadic and sometimes difficult to understand.

We can know which points are correct by having the lat/long data from the DNR. The majority of the time lat/long data is a fool-proof way of figuring out a particular address location. However, another error type, field survey measurements, could have initially calculated the lat/long data incorrectly, thus giving an incorrect location.

Conclusion

The process of  geocoding is extremely helpful in a spatial analysis, and without it, one would lose accuracy Using geocoding is never precise, especially when one has to manually add the points. This shows that there has to be some consideration when looking at a map for accuracy, which is exactly why it is so important to include the data source as well as the metadata. In addition, some of the data points that were given from the 'correct' mine locations was actually centralized on the mine itself without consideration of the closeness to the road, which is what we were suggested to do for this lab. Basically, you have to take geocoding as a relative form of locating addresses.

Sources

Wisconsin Department of Natural Resources. (n.d.). Retrieved November 8, 2015, from http://dnr.wi.gov

PLSS - Legal Descriptions | PLSS. (n.d.). Retrieved November 8, 2015, from http://www.sco.wisc.edu/plss/legal-descriptions.html