ARTICLES

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)

  ' /// 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.

	MP2Kmag Internet