Network Analysis of Sand Transportation

- Introduction -

Mined silica sand will most likely need to travel by rail to its next destination. Some mines have direct or quick access to a railway, but others will undoubtedly need to transport the sand by truck to the nearest rail terminal. These trucks will be heavy and will continuously be driven back and forth on whichever road networks are available. If each county is responsible for maintaining their roads, what will be the cost of upkeep? In this analysis, a hypothetical value of 2.2 cents is used as the cost per mile that a truck drives and it was assumed that each mine will send one truck on 50 trips a year. Network analysis was performed on the results of python script #2 and a feature class of rail terminals to determine a route for each mine to the closest terminal. Model builder in ArcMap 10.2 was used to create the route and calculate a total length of trucking per county per year (Model 1).

- Methods -

Model 1: Workflow to determine the cost in dollars
per year per county from sand trucking

A street network dataset from ESRI street map USA was used as the active network dataset for the network analysis. A new 'closest facility' layer was created with mines as incidents and terminals as facilities. In model builder, mine and terminal feature classes were added with the 'add locations' tool. The 'solve' tool was then used to route from each mine to the closest terminal and create the 'closest facility' layer. The 'select data' and 'copy features' tools were then used to select just the routes from the 'closest facility' layer and make them a new feature class. This line feature class of routes was then projected and intersected with a polygon feature class of counties. The result of the intersect was then summarized by county giving the total length of routes, and therefore, an estimation of total distance of trucking, per county. The cost of maintaining the road networks was calculated by dividing the length field by 5280 (to convert to miles), multiplying by 100 (for the number of trips, to and from) and by 2.2 (cost per mile in cents) and finally dividing by 100 (to give a hypothetical dollar amount) in a new field. The equation reads: cost = (length*trips*cost_per_mile)/(528000). A map were then made to illustrate the impact of increased trucking per county.



- Results -

Table 1: The route length and cost per year for each of the affected counties.

 

Map 1: Map illustrating the cost per year per county to maintain road networks of
likely sand trucking activity, based on hypothetical data.
 

 - Discussion -

The total amount of money each county would  have to spend was lower than expected. The county with the most trucking and highest cost was Chippewa County (~$455) followed by Wood County (~$327). The least amount of trucking and lowest cost was Burnett County (~$1.50). Six of the twenty-four counties had costs below $10 dollars a year. The counties with low cost was often due to rail terminals being located just within their border resulting in only a small portion of different truck routes coming from other counties to actually be located in that specific county. Overall, the numbers may be underestimated because of the hypothetical cost of 2.2 cents a mile and the assumption that there would only be fifty truck trips per mine to unload sand at a rail terminal per year. Map 1 clearly illustrates that counties with more mines located a distance away from rail terminals tend to experience more trucking, which only makes sense. Chippewa and Wood Counties have more individual mines in need of trucking than all but one county, and therefore, incur the largest costs per year to maintain their road networks. Trempealeau County is a slight exception to this rule because it has the largest amount of mines and does not  have the highest cost, though it does have the 7th. This is due to the fact that the mines in Trempealeau County are all located in a cluster near a rail terminal making the routes short compared to other counties.

- Conclusions -

The two important factors in determining the impacts of increased trucking on road networks per county in western Wisconsin are the number of mines that need trucking and the distance these mines are located away from the closest rail terminal. Performing network analysis by creating a 'closest facility' layer in ArcMap allowed for the length of possible routes and costs associated with maintaining the road networks to be calculated. These calculated costs are hypothetical. Another important consideration in network analysis is having a quality network dataset. The network data set used in this analysis was produced by ESRI and allowed for accurate routing. Without a high quality network dataset, routing errors are introduced which limits the effectiveness of network analysis.


- Sources -

Environmental Systems Research Institute (ESRI). Street map USA, street network dataset.

Wisconsin Department of Natural Resources (WNDR). Mine data.

Wisconsin Department of Transportation (WisDOT). Rail terminal data.

Data Normalization, Geocoding, and Error Assessment

- Introduction -

A table of sand mines in Western Wisconsin was provided by the Wisconsin DNR and each student was given the task of geocoding 15 to 20 mines. Each mine was geocoded by multiple students to showcase the user error involved in the geocoding process. The table proved by the WI DNR has not completely normalized and for some mines contained only Public Land Survey System (PLSS) addresses. This complicated the process of geocoding by introducing error. The process and errors associated with geocoding will be discussed in this post.

- Methods -

My specific mines were located in the table provided by the WI DNR and copied into a separate excel spreadsheet to eliminate any formatting that may cause errors later in ArcMap. The address field in the table was divided into its constitute parts, or normalized, so the address could be understood by the World Geocode Service address locator provided by ArcGIS Online. The normalized table was used to geocode the mine addresses in ArcMap, with the appropriate fields used as Address Input Fields. Once the address were geocoded, the match addresses and spatial locations were inspected. The PLSS addresses could not be automatically geocoded and manual placement was needed by using a PLSS feature class and satellite imagery base map. The 'Pick Address from Map' option was used for many of the address to locate the closest address to the mine entrance from a road.

Once the mines were geocoded and inspected, each student imported a shapefile of their geocoded mines into a common geodatabase. All the shapefiles were merged and each mine that corresponded to the mines I geocoded were queried out. The distance between my mines and the same mines geocoded by my classmates was calculated with the 'point distance' tool.

- Results -

Table 1: Sample of the normalization performed on the mine addresses.
The red color in the normalized table indicate PLSS address.

Table 2: The error in distance (Miles) between the same mines
geocoded by my classmates and I.

Map 1: A map showcasing the geocoding result.

- Discussion -

Of the 22 mines I was assigned, every one automatically geocoded based on the address given in the normalized table. However, only 10 were actually correct. PLSS addresses do not follow a format that the address locator could understand and were therefore matched based on town or county. These mines needed to be found manually by querying a PLSS polygon feature class of WI and then analyzing a satellite imagery base map for the closest address location. All address that were not PLSS contained the street address information in one field of the table. Some address locators can parse this information but it is always better to be thorough and normalize the address field into its constituent parts. Parts of an address that need separate fields include house number, direction prefix, street name, street type, sub-address, city, state, and zip (seen in Table 1). If this information is in the same field an address locator could become confused and not match or erroneously match the addresses. Some addresses were incomplete or spelled wrong, examples of attribute data input error.

When geocoding the mines, I either accepted the automatic match or picked an address from the map that corresponded best with the given information and interpretation of a satellite imagery base map. Some mines geocoded by my classmates were not given a specific address and the mine was located in the middle of a city or county (like the PLSS addresses). This happens with the address was not available or understandable by the address locator and the match was based on what the address locator could find (city, county, or state). One address was even located in Missouri. Each student was given the same table to normalize and geocode yet when comparing my mines to my classmates, only 8 out of 55 addresses were the same (Table 2, 0.01inch represents the same address). This demonstrates the amount of subjectivity involved in geocoding unstandardized data.

- Conclusion -

The process of geocoding can be easy or difficult depending on the quality of the data used. When data automation and compilation is unstandardized, differences and errors in attribute entry can arise. This introduces error into the geocoding process as well as a level of subjectivity on the analyst's part, potentially introducing more error. Normalization and thorough assessments of data entry quality is vital to geocode addresses, especially for data that is unstandardized.

- Sources -

Wisconsin Department of Natural Resources (DNR). Mine table.

ESRI geodatabase (2013), USA Census Data. Accessed through UWEC Department of Geography and Anthropology.