Network Analysis: Cost of Sand Mining on Local Roads

Goals and Objectives

The goal of this lab was to become acquainted with the Network Analysis tool, and use it to perform analysis of the impact that sand mining has on the transportation system here in Wisconsin. The general workflow I followed is as follows.

1. Determine which rail terminal each mine will travel to in order to get the sand to a rail car.
2. Determine the most efficient route from the mine to the terminal.
3. Calculate the length of the route by county.
4. Estimate the costs each county will incur for road maintenance due to heavy sand truck traffic.

General Methods

The first step was to use a Python script to select all of the mines that were active, did not have a rail loading station included, and was not within 1.5km of any railroad. This script can be seen in my posting from November 11th. Using this new dataset of mine locations, I then had to add a streets network dataset, and all of the feature classes participating in it. I then added a rail terminals feature class to the map, and remove features that had an air terminal facility by selecting the desired features, and creating a layer from the selection. Then, I used the Network Analyst: closest facility tool to determine routes that trucks shipping sand would take to get to the nearest rail terminal. We then used model builder to visualize our workflow, and make it a reusable process. I essentially went through the same process I just explained, and exported the resulting feature class to a geodatabase. The model is shown below. The next step was to calculate the total road length covered per county. I did this by intersecting the trucking routes with the county boundaries, and summarizing (giving me the total length) based on the county field. From there, I created a new field called COST, and calculated its values based on the assumptions that each sand mine takes 50 truck trips per year to the rail terminal, and costs 2.2 cents per mile of wear and tear on the roads. The equation is as follows: [SUM_Shape_length] * 50 / 1609.344 * .022. Finally, I mapped my results. They are shown below.

Results

This image shows the process I went through in using network analysis to calculate routes
likely taken by trucks shipping frack sand.

This image shows the process taken to calculate the costs incurred by sand-moving trucks

This image shows my final results. Counties with higher impact by sand mining transportation
operations are shown in darker colors.

This image shows the table with total length of roads (in meters) affected by sand mine
transportation, and the cost associated with these values.

Discussion

After analyzing the results of this excersise, I am quite surprised at how low the annual costs on the roads are. It is important to note that the assumptions talked about in previous sections (being that each mine takes 50 truck trips per year, and that the cost per truck mile is 2.2cents) is hypothetical. More testing and analysis would be needed in order to make an accurate claim about the monetary implications that frac sand mining has on the transportation system here in WI.

Another important issue with the assumptions is that instead of calculating for 50 trips, I should have calculated for 100, accounting for the trip back from the rail terminal.

Conclusion

 

This lab was a good introduction to using network analysis, and a good way to familiarize myself with some its possible applications. I think that network analysis is one of the most practical tools that I've used so far in GIS, and I'm sure that it is a very popular area because of its use in routing. It will be a good tool to be familiar with in the future.

Python Script 2

For this script, the objective was to write a number of SQL statements to select mines that meet the following criterion:

• Mine must be active
• Mine must not also be a rail loading station
• Mine must not be within 1.5km of any railroad

The script narrowed down the number of mines to 41 that met the criteria. It is shown below.

Sand Mine Suitability Project: Data Normalization, Geocoding, and Error Assessment

Goals and Objectives

The goal of this exercise was to become familiar with the process of geocoding non-normalized data, and combining it with data from sources that have been geocoded differently. We were assigned to geocode addresses of sand mines, merge our results with results from other students, and compare.

General Methods

We were each assigned a number of sand mines from an excel table to geocode. The first issue was that the addresses we were given were not normalized. Some were in WI PLSS format, and others already had street addresses. To facilitate the geocoding process, I used a PLSS finder online to find a street address for each of the mines that I was assigned. (See below for pre-normalization, and post-normalization tables). Once I had all of my street addresses, I began geocoding, using ArcMap's geocoding tools. While the tool matched some of my addresses automatically, there were a number of them that I used the "Pick address from map" tool to locate. This involved zooming to their location using PLSS quarter quarter section data. Since this data was located on our University's database server, I had to connect in order to use it. After I was connected, I used the DNR's roads dataset, and PLSS data to locate my mines, and match them manually. Once they were all properly geocoded, I exported my results as a shapefile, and put it in a folder that included all of the students' geocoded mine shapefiles, some of which contained the same as mines that I had been assigned. The next step was to merge all of these shapefiles. However, some of the students' files were corrupted, or had been compiled differently, so merging the files was a challenge. I had to merge a few files at a time and repeat until I had a usable dataset. Next, I used the Project tool to project my data to a Wisconsin state system projection. I then queried for all geocoded mines that matched my own, and I used a point distance tool to generate a table of the distances between my points, and other students' points. The problem here was that this generated the distances between each of my points and each of theirs, while I was really only interested in the nearest points from other students to each of mine. I summarized my point distance table on my mine-id field, and included minimum distance to generate a table of my mines and how far the nearest mine mapped by other students was.

Results

This image shows some of my mine addresses. Note the ADDRESS_NORMALIZED
field that I added alongside the Address field after the normalization process

This image shows a table of the minimum distance (in ft) that my mines (INPUT_FID) were
from mines geocoded by other students. 

Discussion

As in any geospatial analysis, there were a number of sources of error in this project. Of course, there was inherent error in the map projection and scale etc, but more important was the operational error associated with data compilation and geocoding. We each had to geocode our mine addresses based on PLSS data. Since many of the mines didn't have an associated street address, we had to search for one and assign it one. This leads to various discrepancies between our datasets, resulting in some values in the table above being quite high. In the process of geocoding, many addresses were automatically matched to their normalized street addresses, but many required further analysis. That meant picking a point from the map for which geocode addresses tool would assign an address. This resulted in high operational error, as depending on where I or another student clicked, the address would be assigned differently. The points that are actually correct are likely ones that originally had a street address, and that the geocoding tool automatically assigned a point correctly to that address. These are points that likely have low distance between them in the above table.  

Conclusion

This lab was a good introduction to the process of data normalization and geocoding. In GIS data often comes from different sources, and there are often discrepancies between datasets that must be addressed before analysis. Having to normalize data that might be in a number of formats is a process that I should be familiar with. It is also good to be familiar with the geocoding process, so I am able to generate spatial data from tabular address data.