Vehicle routing involves travel
distances and the corresponding travel times. The add-in stores
the locations of selected points within a region on the Distance worksheet.
These are the map locations. Points may be specified with either Cartesian or
Geographic coordinate systems. Distances between map
locations are specified in miles or kilometers. Distances
are computed with either the straight-line measure (Euclidian)
or by the length of the line that follows the curvature of
the earth (the Great
Options regarding locations on the map are determined by filling
the dialog below. A button on the Start worksheet
calls this dialog. The example is the 446 point representation
of the Austin area as pictured on the summary page.
The Name and Map Points fields
are filled with the name of the distance worksheet and the
number of map locations. The add-in creates a second worksheet,
the Customer worksheet, to store customer locations.
The number of locations is entered
in the Customer Points field. Both distance and customer
worksheets use four columns to represent the index, name and
the two coordinates for each point. The Extra
Columns field adds blank columns to hold additional data
such as address or phone number. The Same Map and Customer button
makes the number of customer points equal to the number of
map points and links the customer data to the map data with
formulas. This is illustrated on the summary page.
The three decision pairs in the center of the
dialog set the type of coordinates, distance measure and distance
calculation. With Cartesian Coordinates the Euclidean measure
is always used. It is the straight line distance between two
The Great Circle measure computes the distance between
two points considering the curvature of the earth. The equations
assume the earth is spherical. This measure is always used
when Geometric Coordinates specify the locations.
When the Provide Matrix for Distances button
is checked, the point-to-point
distances between all pairs of map
locations are calculated and stored in a square matrix on the
distance worksheet. Otherwise only the coordinates are stored
and the distances are computed as needed. Of course, when a
large number of map locations is specified, the matrix defining
the distances is correspondingly large. Although the matrix
is initially filled with distances computed with one the measures,
the purpose of the matrix is to hold more accurate distance
The concept used for this analysis tool is that
most travel is between major travel destinations in a local
region. Map locations identify major intersections,
apartment complexes, shopping malls and business parks. Over
time the distances or travel times between these fixed locations
can be measured carefully or obtained from some database of
travel times such as Google Maps or GPS systems. Individual
customer locations can be located relative to these fixed locations
and the extra travel time estimated using the Euclidean measure
and average travel speeds.
The Random Coordinates checkbox causes
the add-in to generate
random coordinates for the locations. This is handy when
learning about the add-in and building large models without much
data. The random number seed controls the sequence of random
numbers. Change the seed to obtain a different set of map points.
The base values determine the origin of the distance map and
the range determines the upper and
lower bounds of randomly generated map points.
On clicking the OK button on the dialog, the distance worksheet
created by the add-in is illustrated below. The names are in
column E and geographic location
coordinates are in columns F and G. Column H is an extra column
intended for some additional data item regarding map points.
Although it is customary to specify geographic coordinates
in degrees, minutes and seconds together with N,
S, E, and W, designations, we use the more concise decimal
(latitude, longitude). The origin for latitude
is the earth's equator and measures distances north and south
of the equator. Latitude varies from -90, the latitude of
the south pole to +90, the latitude of the north pole. Points
south of the equator have negative latitude and points north
of the equator. have positive latitude. The origin of longitude
is the Royal Observatory, Greenwich England, with
points east of Greenwich
having negative values and points east having positive longitude.
Longitude varies from -180 to +180. The international date
line is at both maximum and minimum longitude.
The numbers in column K are used to measure the distance of
each map location from a fixed location on the map. To illustrate,
K7 hold the coordinates of the first customer location. Numbers
starting in row 15 and continuing compute the distance to all
446 map points. K8 computes the minimum value in this list
and K9 is the map location closest to the first customer.
K10 holds the map location name. This list is used to assign
their closest map points.