Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Puzzle: Route Planner using a World Map in Excel
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Puzzle: Route Planner using a World Map in Excel
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/MARINTEK...softw are.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/ar...27/880411.aspx </As an aside NickHK "dransfield" wrote in message ups.com... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Puzzle | Excel Discussion (Misc queries) | |||
How do I map the route of curser to specific input cells excel | Excel Discussion (Misc queries) | |||
How do I route an excel workbook to a series of e-mail recipients | Excel Discussion (Misc queries) | |||
how to delete duplicate file example route 2_2:1 or route 2_2:2 | Excel Discussion (Misc queries) | |||
When set to route, route with on open worksheet...not the hidden o | Excel Programming |