Sand Mine Road Network Analysis

Introduction:

The road networks from sand mine to railway get used frequently by heavy trucks that are hauling sand from the mines to the railway loading stations to get shipped away.  This project uses GIS to calculate the amount of roads in each county and the cost of the trucks using the roads.  The data for the amount of trips the trucks are making is hypothetical and the figures are made up for the purpose of this lab.  The goal of this lab is to get introduced to network analysis and its features on ArcMap.

Methods:

To begin this lab a python script was made to filter out the mines and railway terminals to get railways with loading stations that are not located at a mine and to get mines that actually extract sand and do not have a railway terminal located at the facility.  This code can be found in the Python Scripts blog post.

In ArcMap the network analysis toolbar was used to complete steps in this lab. The first step was to make a layer in the network analyst tools. This was done by using the Make Closest Facility tool and called Closest Facility.   The next step was to find the closest railway loading station to the mines.  To do this the add locations tool was used to add the mines with no railway terminal at the facility and the railways terminals not at a mine.  When setting up the add location tools the mines were set as the incidents and the railways were set as the terminals.  Then the solve tool was used to create the best street routes from the mines to the railways.  The next step was to make this routes layer a feature class.  In the model builder, a model only tool of select data was used to select the routes.  Then the copy features tool was used to make those selected features into a feature class.  In order for the routes to be the appropriate units, the data from this point in the model had to be projected.  The Project tool was used to project the routes into NAD 83 Wisconsin TM feet.  This is important for later.  The next step in the process was to find the amount of road that is used for sand transportation in each county and to calculate the amount of money it costs each county.  This was done by using the intersect tool to find the routes in each county.  Then the summary tool was used to create a table with each county and the amount of road used for transportation in feet.  Next, the add field tool was used to add a cost field to that table. The calculate field tool was used to convert the routes distance in feet to miles and multiply each mile by 2.2 cents to get the amount of money each county was owed in road damage.  That number was then multiplied by 100 for a theoretical 50 truck trips to the railway and 50 back to the mine. The results from this equation were the cost in dollars for each county. The following image is the model showing all the tools and the results from the tools.

The following image shows the equation used to calculate the cost of transportation for each railway. 

Results:

The routes in the map above are the best routes from the mines to the railway terminals.  The table above shows how much road, in feet, is in each county and the corresponding cost of transportation in each county for the trucks moving sand from the mines to the railway and back to the mines.  This cost is from the county to the mine company for wear and tear on the roads.  Over a long period of time this cost can add up. For a theoretical 50 trips per year the cost is very manageable.  If a mine has to make 50 trips a day the cost would go up significantly.  Now that the model is made if a mine company wanted to know how much it would cost them for X amount of trips a year a simple modification of the cost equation could output the cost for each county the mine company has to drive through.

Conclusion:

This lab was a great way to get experience using the network analyst features of ArcMap and to get a little more experience using the model builder in ArcMap.  The model builder allowed for the whole process to be laid out and ran more then once if changes needed to be made.  It allowed for the network analyst tools to be utilized to make routes in a quick easy fashion and for costs to be calculated with those routes pain free.  The network analyst features in ArcMap are can be very important for future projects and is a great set of tools to be familiar with.  

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

Goals and Objectives
The purpose of this lab is to be introduced to geocoding and the challenges that come with it.  In this lab 19 sand mines in western Wisconsin were geocoded then compared to the actual location and classmates locations.  This lab was designed to make the user explore different tools that ArcMap has to offer, and to make the user creatively think how to solve a problem.

Methods
The mine location data file came from the Wisconsin DNR.  It contained a variety of information including facility name, contact address, address, PLSS location, facility type, etc. This information was not put together in a useful manner.  The address field contained the PLSS location as well as the actual location. The first step was to create a new table with just the mines that were to be geocoded and to normalize that table. The original table contained 129 mines, 19 were assigned to each student to geocode.  The 19 mines were put into a new Excel table and new categories were created to normalize the data.  PLSS location, street, city, and state categories were made and the actual facility address was put into these new categories. In some cases the mines did not have an address only a PLSS location. Once the table was normalized it was brought into arcmap and used to geocode the locations.  The address locator tools was used from the geocoding toolbar to find the location of the mines. The fields that were added were used as the fields to locate the mines by.

The initial matching report revealed that 100% of the mines were matched. This was not true.  A lot of the points were not accurate and needed to be found manually and matched by hand, this was done using the interactive rematch tool.  

This was done for all 19 mines.  For four of the mines no address was included.  Two of them had PLSS locations that were used to find them.  PLSS location quadrants were imported and used to locate these mines.  For the other two a google search was used.  One mine only had an intersection as its location.  For this one google maps was used to locate it. At the end all 19 mines were located and a  point feature class was created of the results. 

The next step was to compare my mines to the actual location, and the location of classmates who had the same mines.  The first step was to use a query to get the same mines from other classmates data and the actual location data set.  

This query selected only specific mines that were to be compared.  Once the mines were selected a new feature class was created for each of classmates and the actual locations data set.  The next step was to compare my points to the others.  To do this the near tool was used. This tool finds the nearest point to one of mine and gives the distance to that point in meters. To compare my points to classmates, the classmates data was the input feature and my points were the near features. This was done because they didn't have all 19 of the same points and the near tool would have just found a random point to fill in for that mine. For comparing my locations to the actual locations, the actual locations was the input features class and my locations was the near features class.

  This tool creates a new column in the input table with the distance to the nearest feature. The four tables were then exported as a text file and brought into Microsoft Excel.  The average distance to mines was calculated by using the average equation in Excel.

Results

The data from the DNR came in a format that was not ready to be used.  Four new fields were created to normalize the data.

Not Normalized

Normalized

Originally the address field was a mess.  The table was normalized to get the address in a more organized fashion.  This allowed the geocoding process to go quicker by finding the address more accurately.

After all the mine locations for the classmates were sorted out a map was created of all the different locations mines were geocoded at. 

The map below shows one mine and the four locations that were geocoded for that mine.  This map shows how some of the variance occurs.  One person put the location at the mine entrance on the road, down the road, in the mine, and behind the mine.  This variance results in the distance between locations being different for each person and for each mine. 

The table below shows the distance from the actual mine location that the DNR provided and where my mine locations were.  The average distance was 1027 meters.  This high number is the result of a few mines being very far off.  

The error that occurred was positional error .  This is the result of the points not being accurately placed at the mine location.  This has to do with different understandings of how to locate a place and how addresses work.  An address is read from the end of the driveway and not the actual spot of the house or building on a property.  The most accurate place to put a address location would be where the driveway meets the road.  This is not where the DNR placed the points and that is why there is fluctuation from the actual points and the authors point locations. 

Conclusion:

Geocoding can be very accurate and points can be placed where the user deems them most appropriate.  This freedom to place points where they please also leads to the resulting locations being different.  This must be kept in mind whenever geocoded locations are being used.  They might not be in the right spot for the application the user wishes to use them for.