Puzzle: Route Planner using a World Map in Excel

[dransfield]

Okay, this is what I want to do:

I'm going to make a world map in Excel, by making the cells square,
with blue for sea and green for land.

Then I want to be able to select 2 'ports' eg. NY and Sydney, press a
button, and a macro / optimiser work out the most direct route for a
ship to go between the 2 points.

I'm a professional Excel programmer, have used linear optimisers
before ('What's Best') but haven't yet worked out how to do this, and
was wondering if any of you guys had.

The reason I want to do this: I've got a list of a few hundred "From &
Tos" (some with the option to go via Suez & Panama canals), and I want
to work out eg. all the Journey times, distances, and how long the
ships spend in the middle of an Ocean, out at sea, or in the waters of
which continents. So to do this for each "From & To" I need the map of
the world to have a nice chain of red squares between the 2 ports.

I'm thinking in terms of a linear optimiser, where to start with NY
and Sydney are joined by a 'dumb' square route, and then the optimiser
'smooths' the route into being as short as possible.
Anyone know how I could do that?

TIA
Dz

 

[NickHK]

I don't think this will be a trivial solution, especially using cells to
colour land and sea.
Unless you have Excel2007, you only have 256 "pixels" wide, which will not
give much resolution.
Using a graphic in memory, possibly using the GID/GID+ API and some form of
collision detection (when you "hit" a land mass) would be better.
Unless you find something better, you could do an Alt+PrintScreen of the map
used by Windows for the Time Zones.
Changing the time zone re-centres the map (you could get say 24 maps), so
any journey does not fall off an edge of the map, unless you go around on of
the poles

Some database relating Location to Lat/Long would also be needed (or query a
website), then relate those numbers to your graphic.

According to:
http://www.sintef.no/upload/MARINTE...outer_Vessel schedule optimizing software.pdf
page 11

They first calculate the Great Circle route, then adjust to avoid any land
mass. However, as you are working with a flat projection of the globe, you
would have to allow for how far you stray from the equator in calculating
the East-West distance, and hence the time taken.

There are commercial products available (like the above). Possibly a
web-site that you could query, although I did not find one.
There are sites that you can get the Great Circle and Way point info from.
You would then have to adjust around land masses of your map.

It may prove easier using a vector format (e.g. emf) to test for
"collisions" rather than raster format (e.g. bmp), where you would have to
test pixel colour.
A little user input, to specify the way points, would greatly simplify the
matter. If you got that working first, then looked at an 'upgrade" to a
fully automated version, you would at least have something more quickly than
attempting the whole thing from the start.
If you only have relative small number of possible start/end points it would
be easier.
If you are basically for "any 2 points in the worlds" then a more complex
approach will be required.

<As an aside>
The time zone map that Windows uses stores this green/blue info in the
registry to draw the map you see.
<<<< Back up Registry before trying this >>>>
You can see this by playing with the numbers of the MapID key of say:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time
Zones\Afghanistan Standard Time
You can make countries sink below the water.
http://blogs.msdn.com/oldnewthing/archive/2006/10/27/880411.aspx
</As an aside>

NickHK