The goal of this assignment is to
perform a network analysis on the transportation of sand from the
mines to the nearest railroad depot via trucks. The trucks will have
a substantial impact on the condition of local roads, which are
maintained by the county.
This assignment is divided into the
following six objectives:
- Load features into the Network Analysis Window
- Calculate a route
- Calculate a closest facility and route
- Build a model to calculate the closest facility route
- Calculate the cost of sand truck travel on roads by county
- Write a report based on the results (this blog is the report)
Data sets and Sources of Data
All data used in this analysis was
obtained from ESRI, with the exception of mine locations. The mines
were obtained from Wisconsinwatch.org, and the addresses were
geocoded for this network analysis. A complete data set of railroad
terminals could not be obtained due to many of them being private.
Methods
Objective 1: Load
Features into Network Analysis Window
The first objective of this assignment
was very straightforward. All that was required was to connect to a
folder and add a streets network data set. Once that was completed
we enabled the Network Analyst Extension of ArcGIS and imported the
rail_terminals feature class that the professor provided for us. The
final part of this objective was to add a combined mines feature
class of all of the mines our group geocoded in the previous
assignment. This proved to be more difficult than expected for me
due to one group member dropping the class the first week, and then
while waiting for the only other group member to import his feature
class, I found out that he was dropping the class as well. As a
result, I was unable to obtain the necessary mines, and so the
professor provided me with another groups compiled feature class of
mines (Figure 1).
![]() |
Figure 1: Screenshot of the compiled mines feature class. |
Objective 2: Calculate
a Route
The second objective was quite simple
as well. All I needed to do was open the Network Analysis window and
right click on stops to choose my mines feature class as the load
location, accepting all other settings as default. Then I clicked
the solve button and a route was generated. From here I could have
exported the result as a feature class, but this is not useful to
goal of the assignment. All this process did was calculate a route
that would travel to all of the mines. What is needed is to find the
most efficient route from each sand mine to its nearest rail
terminal. Once we have that, we can calculate the length of the
route by each county and estimate the cost to each county using a
formula I create.
Figure 2: Merely a route to all of the mines. This is not to be used for the actual analysis, but rather was intended for us to see the result and learn how to use the tool. |
Objective 3: Calculate
a Closest Facility and Route
To begin Objective three I removed the
route that was generated in the second objective, as it had no value
and was cluttering the map. The purpose of this objective is the
calculate a closest facility and route, which will allow us to find
the closest railroad depot to each sand mine, as well as find the
shortest route in mileage. To begin this process, I added a new
closest facility layer from the Network Analysis toolbar. I loaded
the mines as the facilities and the rail terminals as the incidents,
and then solved. Upon examining the results, I noticed that this was
the opposite route we wanted. This method found the closest sand
mine to each rail depot, resulting in mostly completely unnecessary
routes and very few connections with most sand mines (Figure 3a).
To get the results I wanted, I had to
switch the mines and rail depot, making rail depots the facilities
and mines the incidents. Upon solving, I immediately saw this was
the correct method, as there were routes going from each sand mine to
the nearest rail depot (Figure 3b). To keep the map and table of
contents clean, I removed the first calculation.
(Figure 3b: Correct
Closest Facility)
Objective 4: Build a
Model to Calculate the Closest Facility Route
This objective begins the creation of a
model using the Model Builder present in ArcMap. Through this
objective, the model will perform the same actions (and have the same
results) as Objective 2. In Objective 5, we will expand this model
to include calculating the costs incurred on counties from sand
trucks traveling to and from the rail terminals.
The following process was used to
create the model shown in Figure 4:
- Add tool: Make Closest Facility Layer (Network Analyst)
- Input Streets feature class, leaving all other settings on default
- Add tool: Add Locations (Network Analyst)
- Input Locations: Mines feature class; Set sub layer to incidents; all other settings on default
- Connect output of Make Closest Facility Layer to Add Locations tool of Step 2, and input network analysis layer
- Add tool: Add Locations (Network Analyst)
- Input Locations: rail_terminals feature class; Set sub layer to facilities; all other settings on default
- Connect output of Add Locations in Step 2 to Add Locations in Step 4 and input network analysis layer
- Add tool: Solve (Network Analyst)
- Connect output of Add Locations in Step 4 to Solve and input network analysis layer
I then ran this process to ensure it
worked, however, the process said it could only partially complete,
and it would not display on the map. I initially thought it was only
partially completing due to there being 11 mines that were not
completely geocoded and did not have matched addresses. To remedy
this I started an edit session and looked in the attribute table of
the mines. I found the 11 mines that had several fields with (null)
values and no matched addresses. I deleted these from the attribute
table, saved the edit, and stopped the edit session. This seemed to
fix the partial completion, as this time it was fully successful, but
it still did not display the results. Being confused by this, I
decided to consult the ArcGIS Resource Center. I searched for “model
builder not displaying,” and the first entry lead me to a tutorial
on how to create tools with ModelBuilder (Tutorial
can be found here). From there I found out
that the output of a model will not be added to ArcMap unless you
have both “Add To Display” and “Model Parameter” checked.
This is because when a model is run from its tool dialog box, the
“Add To Display” setting is ignored. When I ran the model with
both of these checked on the Solve output, it successfully completed
the model and added it to the display. The results were the same as
found in Objective 3. Next I needed to export this result by adding
processes onto the model I created.
The following process was used to
export the result in ModelBuilder:
- Add tool: Select Data (Model only tool)
- Go to Insert → Model Only Tools → Select Data
- Connect Solve output to Select Data, and Input Data Element
- Open select data tool, choose Routes for child data element
- Add tool: Copy Features (Data Management)
- Connect output of Select Data to Copy Features, and Input Features
- Open Copy Features, changed output workspace to my geodatabase
The model ran successfully. It created
the feature class, but I forgot to add the “Add To Display” and
“Model Parameter” to the Copy Features output, so I ran it again
with these changes (Figure 4b). On this second run, I realized that
it is necessary to have this as a Model Parameter because it allows
you to change the output location and file name, which may be
necessary if you run the model more than once. This completes
Objective four.
Fig 4b: Model with added tools to export the Routes as a feature class. With the Add To Display and Model Parameter selected on the Routes_final result, it will be added to the map on completion. |
Objective 5: Calculate
the Cost of Sand Truck Travel on Roads by County
In order to calculate the cost of sand
truck travel on the roads in each county, I needed to add some
processes to my model. In my first attempt I merely intersected the
routes feature class with a counties map, which resulted in an
attribute table containing a Shape_Length field with really small
values. After consulting a fellow classmate, I realized this was
likely the sum of decimal degrees, as I had not projected both
features into the same coordinate system.
My second attempt was successful. I
projected both the routes and counties feature classes into
NAD_1983_Wisconsin_TM_US_Ft. This is a Wisconsin State System that
uses feet. I chose this coordinate system because it provided the
smallest area that could be used effectively. A state plane system
would be less accurate because it splits the state in half. UTM Zone
15N could have been used, but this is encompasses a much larger area,
and so it does not provide as much accuracy as the state system.
Wisconsin has two options for the state system, one in meters and the
other in feet. I chose the one in feet since it would be the easiest
and most accurate to convert feet into miles.
With the projections complete, I then
intersected the projected routes (roads) with the projected counties.
The intersect tool will provide me with an attribute table that
links the truck routes to the county it is in. If a truck route goes
into multiple counties, the intersect tool cuts the route at the
county border. For example, if a truck route goes through three
counties, at each county border the route will be cut, resulting in
three line segments that each belong to a specific county. This will
allow me to calculate the total length of roads in each county.
The final step to the model involves
summarizing the Shape_Length field (which is the route/road length)
based on the county. To do this I added the Summary Statistics tool
and connected it to the output of the intersect. The output of
Summary Statistics is a table, which can be seen in Figure 5a.
The model is now complete and can be
seen in Figure 5b. However, I have still not completed
objective five. In order to do so I will need to edit the attribute
table generated by the Summary Statistics tool. First, I removed the
frequency field, as that was just a count of the number of routes in
the county. I also renamed the Name and SUM_Shape_Length to County
and Road_Length, respectively. I added a Miles field to the table,
and used the field calculator to convert the road length from feet to
miles, using the formula depicted in Figure 5c. The final
step was adding the Cost field, where I used the field calculator
again to determine the cost to each county. The formula can be seen
in Figure 5d. The formula used is hypothetical. It assumes
that each mine takes 50 truck trips per year to the rail terminal,
and then return to the mine. This results in 100 truck trips per
year for each mine. The hypothetical cost is 2.2 cents per truck
mile. The completed table and the cost to each county in dollars can
be seen in Figure 5e.
Figure 5b: This is the completed model, showing the full process from start to the finish. I reorganized the model in order to make it more compact, allowing it to be more easily read and edited. |
Figure 5c: I used the Field Calculator on a new field to convert the total length of routes in each county from feet to miles. The formula can be seen in the bottom box. |
Figure 5d: Again, I used the Field Calculator. This time the formula used will calculate the cost to each county per year. |
Results and Discussion
As can be seen on the map, the vast majority of railroad terminals are located in the southwest portion of the state, where there are very few sand mines. This results in most sand mines travelling to one of the three railroad terminals in west central Wisconsin. When looking at La Crosse county, the southern most county in red, it only has one sand mine, yet it has the fourth highest annual cost due to most of the mines in the surrounding counties travelling to its railroad terminal.
Our hypothesis assumes that there will only be 50 truckloads per mine each year. This seems like an extremely conservative estimate, as that is saying each mine will only send about one truck per week. Even so, it still demonstrates the proportion to which each county will be affected. This means Chippewa, Eau Claire, Trempealeau, and La Crosse counties will have a significant impact on the deterioration of the roads, requiring more maintenance and potentially earlier and more frequent replacement. This is certainly an issue to taxpayers within each county, as it is their tax dollars that will be spent in order to maintain and repair the roads.
Conclusions
While this analysis provides a general idea of the potential proportional costs to local communities due to the sand mines, it is neither definitive nor accurate enough to provide the answers people will want. There are many potential options to offset this issue, such as sand mines paying a fair share to each county they will affect, or creating more rail terminals in western Wisconsin to either reduce the number of trucks required, or at least reduce the distance they must travel. This analysis does not provide the answers, but rather, points out the fact that more research is required in order to determine what the possible solutions are and which is the best course of action.
Cool you inscribe, the info is really salubrious further fascinating, I'll give you a connect to my scene. frac plug setting tools
ReplyDelete