 |
|
 |
 |
Determing the Time Zone in MapPoint
Leon Schell shares a method for determining the time zone by accessing hidden POI information
A question that gets asked every once in a while is whether it's possible to display time zones in MapPoint. Using and
experimenting with articles from
John Washenberger
and Gilles Kohl I have
come up with the following solution.
One way to get to the time zone information is to loop through POI's in a way
described by John. Basically, you first determine the area you want to
examine and then loop through that area using a longitudinal (east to west)
loop nested within a latitudinal (south to north) loop. In the code example
below, I will try to get the time zone information for Portugal, Spain and
France, using latitudes ranging from 36 to 44 degrees N and longitudes
ranging from 10 degrees W to 4 degrees E. Since looping through all POI's can
be time consuming, I decided to include only major airports - I think it is
fair to presume that every country or state you want to map at least has one
major airport.
In my case I have used:
Windows XP Pro with MapPoint 2004 Europe and Excel 2003.
To try this yourself, open a new Excel workbook, add a command button to the
sheet and double click it. Set a reference to 'Microsoft MapPoint 11.0
Object Library' via Tools | References in the code window. Then, in the
CommandButton1_Click() sub, paste the following code together with the
CalcPos sub and Arccos function that you can find in Gilles' article
mentioned above.
Dim objApp As Object Set objApp = CreateObject("MapPoint.Application") objApp.Visible = True objApp.UserControl = True objApp.NewMap objApp.ActiveMap.MapStyle = geoMapStyleRoad objApp.PaneState = geoPaneNone
Dim objMap As MapPoint.Map Set objMap = objApp.ActiveMap Dim objSymbol As MapPoint.Symbol Dim objPlaceCat As MapPoint.PlaceCategory Dim objFindResults As MapPoint.FindResults Dim objLocLatLon As MapPoint.Location Dim objLocFindResults As MapPoint.Location Dim objPin As MapPoint.Pushpin Dim objPinOrLoc As Object Dim objResults As MapPoint.FindResults Dim objResult As Object
Dim lngCount As Long ' /// POI counter Dim lngSheetRow As Long ' /// Row in sheet Dim iLat As Integer ' /// To loop through Lats Dim iLon As Integer ' /// To loop through Lons Dim dblLat As Double ' /// Latitude of POI Dim dblLon As Double ' /// Longitude of POI Dim strCategory As String ' /// POI Category Dim strTimeZone As String ' /// Time zone of POI Dim strCountry As String ' /// Country of POI
' /// Select major airports only strCategory = "Airports - Major" For Each objPlaceCat In objMap.PlaceCategories If objPlaceCat.Name = strCategory Then objPlaceCat.Visible = True Else objPlaceCat.Visible = False End If Next
lngSheetRow = 1 For iLat = 36 To 44 Range("Sheet1!A1").Select ActiveCell.Value = iLat For iLon = -10 To 4 lngCount = 0 ' /// Move to current lat/lon Set objLocLatLon = objMap.GetLocation(iLat, iLon) ' /// Get POIs within 80 km radius Set objFindResults = objLocLatLon.FindNearby(80)
' /// Update sheet to show where we are Range("Sheet1!B1").Select ActiveCell.Value = iLon Range("Sheet1!C1").Select ActiveCell.Value = 0 Range("Sheet1!D1").Select ActiveCell.Value = objFindResults.Count
' /// Process all POIs For Each objPinOrLoc In objFindResults If TypeOf objPinOrLoc Is MapPoint.Location Then dblLat = 0 dblLon = 0 lngSheetRow = lngSheetRow + 1 ' /// Calculate the POIs Lat/Lon CalcPos objMap, objPinOrLoc, dblLat, dblLon ' /// Add a record to the sheet Range("Sheet1!A" & Trim(CStr(lngSheetRow))).Select ActiveCell.Value = dblLon Range("Sheet1!B" & Trim(CStr(lngSheetRow))).Select ActiveCell.Value = dblLat Range("Sheet1!C" & Trim(CStr(lngSheetRow))).Select ActiveCell.Value = objPinOrLoc.Name objPinOrLoc.Location.Goto objMap.Altitude = 12 Set objResults = objMap.ObjectsFromPoint( _ objMap.LocationToX( _ objMap.GetLocation(dblLat, dblLon)), _ objMap.LocationToY( _ objMap.GetLocation(dblLat, dblLon))) strTimeZone = vbNullString strCountry = vbNullString For Each objResult In objResults ' This error catching shouldn't be necessary in my view ' but not all objResult objects seem to have a ' .Type property. ' Let's just skip them On Error Resume Next If objResult.Type = geoShowByCountry Then If Err.Number = 0 Then strCountry = objResult.Name Range("Sheet1!D" & Trim(CStr(lngSheetRow))).Select ActiveCell.Value = strCountry Else Err.Clear End If End If If objResult.Type = -1 Then If Err.Number = 0 Then If Left(objResult.Name, 3) = "GMT" Then strTimeZone = objResult.Name Range("Sheet1!E" & Trim(CStr(lngSheetRow))).Select ActiveCell.Value = strTimeZone End If Else Err.Clear End If End If Next End If ' /// Update sheet to show where we are lngCount = lngCount + 1 Range("Sheet1!C1").Select ActiveCell.Value = lngCount DoEvents Next Next Next
objMap.DataSets.ZoomTo objMap.Saved = True
Set objFindResults = Nothing Set objLocLatLon = Nothing Set objLocFindResults = Nothing Set objPin = Nothing Set objResults = Nothing Set objSymbol = Nothing Set objMap = Nothing Set objApp = Nothing
After clicking the command button, the program will start MapPoint and fill
the Excel sheet with the airports found, their longitude and latitude, the
country and time zone they belong to. In the way the program works, many
airports will be found more than once but that doesn't matter much for our
purpose.
After the program has finished, in Excel, delete row 1 which contains the
user interface to show the progress - we won't need it any longer and it
will interfere with MapPoint wizard we are about to use. But first save the
sheet somewhere, for example on the desktop. Now switch to MapPoint and
start the Create Territories Wizard by selecting Data | Territories... from
the menu. Select 'Create from your own set of data' and click 'Next'. Select
'Import your source file', click 'Next'. Select the Excel sheet you just
saved and click 'Open'. Select 'Sheet1' when asked to 'Choose the sheet or
range you want to map'. Click 'Next'. Now, using the European version of
MapPoint, you have to select '<Multi/Other>' from the Country/Region drop
down box. Deselect 'First row contains column headings'. From the 'Data
type' drop down box select 'Country/Region' in the 4th column (F4) and
'Territory' under F5. Leave the first three columns to '<Skip Column>'.
Finally, click finish. MapPoint will now show the time zone information for
Portugal, Spain and France.
Gotchas: - As far as I know, the time zone
information will not take into account summer time changes. For example,
I know that in the U.S. some states change to daylight saving time while
others (or even parts of others) do not. I haven't tested it but I imagine
the info retrieved through the procedure above will be valid during winter
time only.
- As the code was used with the European version of MapPoint, U.S. users
should change the lines
' /// Get POIs within 80 km radius Set objFindResults = objLocLatLon.FindNearby(80)
to
' /// Get POIs within 50 miles radius Set objFindResults = objLocLatLon.FindNearby(50)
- For obvious reasons, this method only works for those countries and states
that are covered in MapPoint and at least have major airports POI
information.
Discuss this story in the forum.
Author: Leon Schell Email: lschell(AT)web.de Leon is an IT project manager for a Dutch airline company. His interests are programming, travelling (by plane and by car), Pocket PCs, GPS navigating, new technologies and finding new utilizations for existing (Microsoft) software.
|
 |
 |
 |
Recent Discussion
|
 |
|
 |
 |
|
 |
Resources
|
 |
|
 |
|
 |