spacer2 1_1 1_2
 The MP2K Update!
Front Cover
What's New
Sample Data
MapPoint 2013
Press Releases
MapPoint Forums
Link to MP2Kmag
Wish List
MapPoint Trial
 Earlier Content
Past News Items
Past What's New Announcements

MapPoint 2013

Programming MapPoint in .NET

MapPoint Book

  Spatial Community
SVG Tutorials

Map Visitors


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.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
objPlaceCat.Visible = False
End If

lngSheetRow = 1
For iLat = 36 To 44
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
ActiveCell.Value = iLon
ActiveCell.Value = 0
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
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
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
End If
End If
End If
' /// Update sheet to show where we are
lngCount = lngCount + 1
ActiveCell.Value = lngCount

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.


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

Discuss this story in the forum.

Author: Leon Schell
Email: lschell(AT)
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.

MP2Kmag Internet

 Recent Discussion
Browse GIS books and periodicals
Find a MapPoint Partner or Consultant
Real Estate Columbia MO Homes for Sale

Want Your Site To Appear Here?

   © 1999-2012 MP2K. Questions and comments to:
  Microsoft and MapPoint 2002/2004/2006/2009/2010/2011/2013 are either trademarks or registered trademarks of Microsoft.