world map showing routes

[capefear]

I want to show routes from site to site on a global map. For example there are 30 manufacturing sites selling 200 products to 35 different customer sites. I want to show on a map visually the location of where the product is produced and to what customer site the product is shipped. Basically, it would look like a airline flight map. Ideally, I want to be able to pick a product or manufacturing site and the map would visually show the routes. For example, if a product is made in Paris, France and it is sold to a client in Atlanta, Georgia, London, England, and Shanghai, China the map would show lines originating from Paris, France to the cities mentioned.

Any suggestion that create a map like this would be appreciated.

 

[phxsportz]

You might want to take a look at Map Point.

http://www.microsoft.com/mappoint/en-us/features.aspx

 

[Harlech]

I’ve used MapPoint to plot out airline style maps and it does work. An advantage is it’s easy to automate with excel vba. A big problem with MapPoint is that it uses a closed geographic data set so for example you can’t switch off the labels that appear at whatever scale. So I’ll bet that when you get a scale that’s right for your work there are pesky labels that spoil the presentation value. There’s a free 60-Day (??) trial of MapPoint 2009 so you can always give it a try.
A possible alternative is to try one of the open source GIS tools eg uDig is straightforward to use and it’s easy to source a free countries shapefile. Advantage is that the lines and labels can be entered as separate layers so can be turned on and off. Disadvantage is that I don’t think automation and presentation of maps are as easy as with MapPoint.

 

[capefear]

Thanks for your reply. I downloaded MapPoint 2009 trial version. How do I use it to plot out airline style maps? Could you get me started..........

 

[Harlech]

The first thing to do would be to locate the 65 sites on the map. First time round do this manually as address-finding will only work for a small part of the globe (Europe or NA depending on which MapPoint you’ve got). So for each manufacturing site place a pushpin on the map, add in the site identifier or name, and right click on the pushpin, choose Properties and assign it to a data set name called Manufacturers. Then go through the same process with customers, creating a data set called Customers. Now if you point at any pushpin, in the bottom right hand corner of the screen you’ll see the latitude and longitude. These need to be recorded for each pushpin. You could do this manually but it’s fairly easy to use vba from excel and the following is some barebones code...

[FONT=Calibri][SIZE=3]Private Sub LocateSites_Click()[/SIZE][/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[FONT=Calibri][SIZE=3]Dim objMap As MapPoint.Map[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim objDataSets As MapPoint.DataSets[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim objRecords As MapPoint.Recordset[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim objPin As MapPoint.Pushpin[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim objLoc As MapPoint.Location[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Ws1 As Excel.Worksheet, Ws2 As Excel.Worksheet[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim intRow As Integer[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Lat As Double, Lon As Double[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]'Assumes mappoint map with locations plotted already open. Only one map open.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set objMap = GetObject(, "MapPoint.Application.EU.16").ActiveMap[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]Set Ws1 = Sheets("Manufacturers") 'Output geocodes for manufacturing sites here[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set Ws2 = Sheets("Customers") 'Output geocodes for customer sites here[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]Set objDataSets = objMap.DataSets[/SIZE][/FONT]
 
[SIZE=3][FONT=Calibri]      For Each objDataSet In objDataSets[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          If objDataSet.Name = "Manufacturers" Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Set objRecords = objDataSet.QueryAllRecords[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Ws1.Cells(1, 1).Value = "Manufacturer Name"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Ws1.Cells(1, 2).Value = "Latitude"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Ws1.Cells(1, 3).Value = "Longitude"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              intRow = 2[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              objRecords.MoveFirst[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                  Do While Not objRecords.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Set objPin = objRecords.Pushpin[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Set objLoc = objPin.Location[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Lat = objLoc.Latitude 'Get latitude of this location[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Lon = objLoc.Longitude 'Get longitude of this location[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Ws1.Cells(intRow, 1) = objPin.Name[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Ws1.Cells(intRow, 2).Value = Round(Lat, 6)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Ws1.Cells(intRow, 3).Value = Round(Lon, 6)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      intRow = intRow + 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      objRecords.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                  Loop[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]          ElseIf objDataSet.Name = "Customers" Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Set objRecords = objDataSet.QueryAllRecords[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Ws2.Cells(1, 1).Value = "Customer Name"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Ws2.Cells(1, 2).Value = "Latitude"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Ws2.Cells(1, 3).Value = "Longitude"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              intRow = 2[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              objRecords.MoveFirst[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                  Do While Not objRecords.EOF[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Set objPin = objRecords.Pushpin[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Set objLoc = objPin.Location[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Lat = objLoc.Latitude 'Get latitude of this location[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Lon = objLoc.Longitude 'Get longitude of this location[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Ws2.Cells(intRow, 1) = objPin.Name[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Ws2.Cells(intRow, 2).Value = Round(Lat, 6)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      Ws2.Cells(intRow, 3).Value = Round(Lon, 6)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      intRow = intRow + 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                      objRecords.MoveNext[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]                  Loop[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]          Else[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Next[/FONT][/SIZE]
<o:p></o:p>
[FONT=Calibri][SIZE=3]End Sub [/SIZE][/FONT]

So now the sites are geocoded you can work with them more easily in MapPoint and load them using Data Import Wizard or vba code. I’m assuming that you’ll want to be able to plot the airline maps for any of your 200 products at the push of a button so how you organise this data is crucial. (Access might offer a neater solution than Excel.) For illustration I’ve assumed just one product where the first column in worksheet “Product Network” lists a manufacturing site, the second column then lists the customer sites for that manufacturing site and when those are all listed then the next manufacturing site appears in the first column and so on. (So basically the first column will have a lot of blanks). So the following barebones code will draw lines between manufacturers and customers . (I’m sure there’s much neater ways of lining up the manufacturer and customer sites but I’m not particularly adept with vba!!)

[FONT=Calibri][SIZE=3]Private Sub DrawLines_Click()[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim objMap As MapPoint.Map[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim objLoc1 As MapPoint.Location, objLoc2 As MapPoint.Location[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Arrow As MapPoint.Shape[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Ws3 As Excel.Worksheet[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim intRow As Integer[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim strManLocation As String, strCustLocation As String[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Dim Lat1 As Double, Lon1 As Double, Lat2 As Double, Lon2 As Double[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' Assumes mappoint map already open. Only one map open.[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set objMap = GetObject(, "MapPoint.Application.EU.16").ActiveMap[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]Set Ws3 = Sheets("Product Network")[/SIZE][/FONT]
<o:p></o:p>
[FONT=Calibri][SIZE=3]intRow = 2[/SIZE][/FONT]
<o:p></o:p>
[SIZE=3][FONT=Calibri]  Do[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      If Ws3.Cells(intRow, 1).Value <> "" Then[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          strManLocation = Ws3.Cells(intRow, 1).Value[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Else[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          strManLocation = strManLocation[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      End If[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      strCustLocation = Ws3.Cells(intRow, 2).Value[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      MsgBox strManLocation & "   " & strCustLocation[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]      Select Case strManLocation[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Case "Cologne"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lat1 = 50.970505[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lon1 = 7.006841[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Case "Riga"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lat1 = 56.964707[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lon1 = 24.096759[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      End Select[/FONT][/SIZE]
 
[SIZE=3][FONT=Calibri]      Select Case strCustLocation[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Case "Paris"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lat2 = 48.878855[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lon2 = 2.319235[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Case "Meaux"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lat2 = 48.961897[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lon2 = 2.885167[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Case "Sydney"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lat2 = -33.866571[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lon2 = 151.209569[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]          Case "Phoenix"[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lat2 = 33.4494[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]              Lon2 = -112.074204[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      End Select[/FONT][/SIZE]
[FONT=Calibri][SIZE=3]'-----------------------------------------------------------------[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]' Draw in the line[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]'-----------------------------------------------------------------[/SIZE][/FONT]
[SIZE=3][FONT=Calibri]      Set objLoc1 = objMap.GetLocation(Lat1, Lon1)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Set objLoc2 = objMap.GetLocation(Lat2, Lon2)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Set Arrow = objMap.Shapes.AddLine(objLoc1, objLoc2)[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Arrow.Line.EndArrowhead = True[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Arrow.Line.ForeColor = vbRed[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Arrow.Line.Weight = 1[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      Arrow.SizeVisible = False[/FONT][/SIZE]
[SIZE=3][FONT=Calibri]      intRow = intRow + 1[/FONT][/SIZE]
<o:p></o:p>
[SIZE=3][FONT=Calibri] Loop While Ws3.Cells(intRow, 2).Value <> ""[/FONT][/SIZE]
<o:p></o:p>
[FONT=Calibri][SIZE=3]End Sub[/SIZE][/FONT]

<o></o>
I guess the final step would be having a form in excel with a listbox allowing you to chose which of the products you want plotted.

Hope this helps, at least to point you in the right direction (no pun intended).

 

[DaveR]

You can do this in excel as long as you have the co-ordinates for the places you want to map to. As you have the X,Y co-ords, all you need to do is use a map as a background to an x,y graph (you will need to adjust the x,y offset to suit).

I can send you a demonstration excel file that evidences how to do this with a UK map and postcodes (It also calculates the distance between two points). Changing it to a world map shouldn't be too difficult.

Also saves having to shell out for Map Point....

PM if you'd like a copy