Pedro Wave Blog Spot

By showing, in the previous article How to dynamically generate GUI, a world map with time zones illuminated by the sun in real time in which cities are created by geocoding.

This technique is used in Geographic Information Systems - GIS to include cities in a layer within a spatial database in which the points are the geographic coordinates of terrestrial latitude and longitude measurements.

Not treated to compete with the many applications of GIS software on the market, but if you can say that use Excel as a tool for geotagging is cheaper and easy and allows you to add geographic information in the metadata of images that are used for georeferencing.

Contribution of this World Time Zones Clocks Map can be categorized within the Volunteered Geographic Information - VGI movement that disseminate geographic information provided voluntarily by people like this map of clocks that can be seen in this video:

Geolocation of cities

Here's how geolocate cities on the world map.

The first is a list of cities and this is created connecting to this Wordlclock website:
http://www.timeanddate.com/worldclock/full.html?sort=0

Sheet Cities

On the Cities sheet are obtained these columns:
A - The name of the countries, provinces and cities separated by hyphens.
B - The local time in each city.
C - Time Zones.

Using deconcatenate or split techniques, from data in column A are obtained data in columns:
D - Countries.
E - Provinces.
F - The name of the cities.

With the Name Manager, list of cities is defined:
ciudades=Ciudades!$F$3:$F$700

Sheet Coord

This sheet temporarily stores the coordinates of the cities:
Latitude: 38° 30' North
Longitude: 28° 00' West

Next cells contain:
B1 - Latitude in degrees and minutes.
C1 - North or South.
B2 - Longitude in degrees and minutes.
C2 - East or West.

Sheet Clocks

On Clocks sheet two dropdown lists are displayed with data validation into the columns:
R - Select a city.
S - Select a country.

The change event, in any cell in this sheet, executes this routine:
Private Sub Worksheet_Change(ByVal Target As Range)

If a city or country changes in columns R and S, this routine is called:

If ActiveSheet.Name = "Relojes" Then
    If Not Application.Intersect(Target, Range("R:S")) Is Nothing Then
        coordCiudad (Target.Row)
        LocalizaCiudad Target.Row - 2
        MuestraCiudad Target.Row - 2
    End If
End If

The routine coordCiudad calls CoordWebQuery that connects to the website of the city chosen by reading hyperlink in column A of the Cities sheet and save the coordinates of the city on the Coord sheet.

If the city is not in the list of cities www.timeanddate.com page, call the GetLatLonCiudad (city, country) function that refers to:
http://api.wunderground.com/auto/wui/geo/GeoLookupXML/index.xml?query=" & city & "," &country

For example, to obtain the coordinates of my city Zaragoza in Spain:
http://api.wunderground.com/auto/wui/geo/GeoLookupXML/index.xml?query=zaragoza,spain

The result was stripped from special signs with ConvertAccent(result), obtaining the latitude and longitude from an XML document type "MSXML2.DOMDocument"

The treatment of the coordinates is done in columns:
AB - Latitude in degrees and minutes.
AC - Longitude in degrees and minutes.
AD - Latitude decimal = decLat.
AE - Longitude decimal = decLong.
P - X location on the map = (decLong + 180) * 760 / 360-8
Q - Y location on the map = 240 - (decLat - 8) * 360 / 170

These latter formulas are approximate and depend on the size of the map.

And just need to locate and display the city on the map with MuestraCiudad and LocalizaCiudad functions, respectively, but I won't explain that because I like to make you wonder.