Google Geocoding API en Excel (I)

Leyendo un post sobre cómo la geolocalización puede utilizarse como evidencia electrónica se me vino a la mente una tarea que hace poco tuve la suerte de iniciar, quizás poco relevante, pero bastante atractiva, ya que, aunque en una segunda fase fuese algo automática, siempre se agradece hacer trabajos donde al final acabas con nuevas ideas y/o conocimientos.

El objetivo de la tarea era obtener a partir de las direcciones de un documento Excel (direcciones completas, nombres de países, comunidades, provincias o municipios…) sus coordenadas en el mapa. A esta transformación se le conoce con el nombre de Geolocalización y a pesar de que en un principio pudiese pensar que sería algo complicado o costoso de obtener, lo cierto es que es bastante rápido y por los resultados efectivos.

Todo esto gracias a la API de Google para Geolocalización, con la cual conseguimos que, realizando una consulta en la que enviamos una URL con los datos de la dirección que queremos, obtenemos un XML con toda la información de esa localización, entre ellas las que nosotros necesitábamos: latitud y longitud.

Si pulsamos sobre el siguiente enlace veremos que obtenemos la geolocalización de la oficina dónde me encuentro 🙂

http://maps.googleapis.com/maps/api/geocode/xml?address=Glorieta%20Fernando%20Qui%C3%B1ones&sensor=false

A continuación, voy a explicar los pasos que debemos dar para obtener nuestro documento con todas las coordenadas necesarias.

Para ello, primero indicar que usaremos la versión 2010 de Excel.

Necesitaremos tener visible la pestaña «Programador» puesto que es aquí donde vamos a iniciar el proceso de geolocalización.

Para habilitar esta pestaña, nos dirigimos al menú Archivo, Opciones.

Pulsamos sobre “Personalizar cinta de opciones”, en el menú desplegable bajo el epígrafe del mismo nombre seleccionamos “Fichas principales”, seleccionamos el checkbox de “Programador” y pulsamos sobre el botón “Aceptar”.

Una vez realizados estos pasos ya podemos ver como una pestaña más la opción Programador.

Necesitamos ahora ejecutar el comando VisualBasic de Excel para crear nuestra función.

Por tanto, pulsamos sobre el icono que nos da esta opción:

Se nos abre una nueva pantalla.

Seleccionamos “Insertar” y posteriormente “Módulo”.

Y copiamos el siguiente código en el módulo que se haya creado.

En internet existen muchas variantes de código para obtener lo que queremos. Yo usé este y puedo decir que me funcionó bastante bien.

Function GoogleGeocode(address As String) As String
  Dim strAddress As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String

  strAddress = URLEncode(address)

  'Assemble the query string
  strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
  strQuery = strQuery & "address=" & strAddress
  strQuery = strQuery & "&sensor=false"

  'define XML and HTTP components
  Dim googleResult As New MSXML2.DOMDocument
  Dim googleService As New MSXML2.XMLHTTP
  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode

  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation

  googleService.Open "GET", strQuery, False
  googleService.send
  googleResult.LoadXML (googleService.responseText)

  Set oNodes = googleResult.getElementsByTagName("geometry")

  If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
      GoogleGeocode = strLatitude & "," & strLongitude
    Next oNode
  Else
    GoogleGeocode = "Not Found (try again, you may have done too many too fast)"
  End If
End Function

Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
  Dim StringLen As Long: StringLen = Len(StringVal)

  If StringLen>0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String

    If SpaceAsPlus Then Space = "+" Else Space = "%20"

    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)

      Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
        result(i) = Char
      Case 32
        result(i) = Space
      Case 0 To 15
        result(i) = "%0" & Hex(CharCode)
      Case Else
        result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

<img class="alignnone size-full wp-image-4963" title="Module_1_Code" src="https://www.viafirma.com/wp-content/uploads/2013/05/Module_1_Code.png" alt="" width="1346" height="1033" />

El código hace uso de la biblioteca de XML de Microsoft por lo que tendremos que agregar una referencia a la misma. Para ello hacemos clic en la opción del menú «Herramientas» y seleccionamos «Referencias».

Una lista de casillas de verificación aparecerán. Necesitamos encontrar «Microsoft XML v6.0» en la lista y comprobar que funciona. Pulsamos “Aceptar”.

Guardamos el documento como «Libro de excel habilitado para macros«.

Si hemos realizado todos los pasos correctamente deberemos ser capaces de usar las nuevas funciones creadas tal y como usaríamos las funciones cargadas por defecto de Excel.

Es decir, colocándonos en una celda cualquiera y tecleando lo siguiente:

=GoogleGeocode(A646)

Sirva el código de arriba como ejemplo.

Por su puesto, en este proceso de geolocalización me encontré con algunos problemas, unos perfectamente evitables y otros con los que no podremos hacer absolutamente nada, al menos que me conste. Pero eso será cosa de otro día, ya que el post me está quedando un poco largo ya :).