Google Geocoding API in Excel (I) (Spanish version)

04 Jun 2013

Compartelo:Share on Facebook0Share on Google+1Tweet about this on TwitterShare on LinkedIn0

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

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 dirijimos 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é éste 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

<a rel="attachment wp-att-4963" href="http://www.xnoccio.com//?attachment_id=4963"><img class="alignnone size-full wp-image-4963" title="Module_1_Code" src="http://www.xnoccio.com//wp-content/uploads/2013/05/Module_1_Code.png" alt="" width="1346" height="1033" /></a>

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

Post relacionados

Compartelo:Share on Facebook0Share on Google+1Tweet about this on TwitterShare on LinkedIn0

22 Respuestas to “Google Geocoding API in Excel (I) (Spanish version)”

  1. Macarena 4 de Julio de 2013 at 11:29 #

    Hola! Estoy encantada con tu macro pero una vez que he hecho todos los pasos no sé como acceder a ella. Es decir, tengo una lista de direcciones y quiero convertirla en las coordenadas, como accedo a la macro para ello? Lo de =GoogleGeocode(A646) no me queda claro.

    Gracias!!!

    Un saludo.

  2. Maria Jose Corbera 4 de Julio de 2013 at 13:52 #

    Buenas Macarena, una vez que tienes todos los pasos anteriores finalizados deberías poder acceder a la función GoogleGeocode.
    Para ello en tu hoja Excel ve a una celda vacía, comienza a teclear la entrada a una función =GoogleGeocode o como la hayas llamado. Normalmente si no ha habido problemas Excel te autocompleta la llamada.
    A esa función tienes que pasarle el valor de la celda donde tienes las direcciones.
    Por ejemplo si tienes una columna A donde en la fila 1 tienes Sevilla, en la fila 2 Huelva, en la 3 Cádiz,…
    Podrías poner a continuación en la columna B, en la fila 1 =GoogleGeocode(A1), en la fila 2 =GoogleGeocode(A2), en la fila 3 =GoogleGeocode(A3),…

    De esa forma inmediatamente vayas introduciendo las funciones en cada celda irás recibiendo el par de coordenadas.
    Si tienes las direcciones en distintas celdas de distintos filas y/o columnas sólo tienes que jugar con otras funciones para concatenar la dirección completa y pasarle un valor.

    Por ejemplo, tienes en la fila 1 en A Andalucía, en B Sevilla, en C Tomares.
    Y quieres que las coordenadas te aparezcan en esa misma fila 1 pero en la columna D
    Te aconsejo que primero con la función CONCAT, concatenes los valores de la dirección y después en D1 introduzcas la función =GoogleGeocode (celda donde esté la concatenación de la dirección).

  3. Hola 17 de Julio de 2013 at 9:25 #

    En primer lugar gracias por facilitar el código ya que me viene de perlas. Pero no consiguo hacerlo funcionar. el código me da error… aparemente en todas las líneas que tienen “;”. No entiendo de Visual Basic por lo que no puede encontrar otra lógica que esta.

  4. Hola 17 de Julio de 2013 at 9:57 #

    Y también coindide que todas las líneas de error (aparecen en rojo) tienen el parámetro &…

    Gracias de nuevo

  5. María José Corbera 17 de Julio de 2013 at 19:33 #

    Buenas, el error se debe a que al copiar el código al blog se han colado caracteres que no debían.

    El código original lleva sólo el & y no el & espero que ahora se vea bien. Se lo comento a los administradores para que lo corrijan.

    Saludos.

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

  6. María José Corbera 17 de Julio de 2013 at 19:36 #

    Me ha vuelto a hacer lo mismo… decía que el “&amp” seguido del punto y coma se sustituye directamente por &

    Este símbolo, &, concatena las distintas partes de la cadena de consulta.

  7. Hola 18 de Julio de 2013 at 16:49 #

    Ahora perfecto… Mil gracias… El problema era con el

    & que debe ser substituido por &
    > que debe ser substituido también por &

    Estaría bien advertirlo para que los torpes como yo no nos leimos. Pero lo dicho.. parece que el codigo funciona perfectio

  8. Hola 18 de Julio de 2013 at 16:52 #

    Vaya… ahora mi comentario anterio más que aclarar lo lía de nuevo. Intenta que lo borre el administrador

    Lo que quería decir que es hay que corregir todos los & ya sea por que le sigue el texto amp y el punto y coma, o o por que le sigue el texto gt seguido del punto y coma.

    Lo dicho. Gracias

  9. Jorge Castillo 19 de Julio de 2013 at 8:28 #

    De borrarlo nada, lo aclaras perfectamente en el siguiente comentario 😉

  10. Maria Jose Corbera 23 de Julio de 2013 at 6:29 #

    Cambios realizados en el código para subsanar todos los & que añadían incorrectamente el “amp;” y en la condición que ponía if StringLen>0 modificado por StringLen>0

  11. Vega 13 de Agosto de 2013 at 11:02 #

    Hola María José.

    Antes que nada, gracias por tu explicación. He llegado hasta este blog porque buscaba por la red la manera de convertir una lista de direcciones en coordenadas x e y de manera massiva, ya que son muchas las direcciones que tengo que convertir. He probado tu macro y no me funciona. He pensado que tal vez podría ser por como tengo yo la información sobre las direcciones en el excel. Yo lo tengo de esta manera: en la columna A tengo el país, en la B la Comunidad Autónoma, en la C la comarca, en la D el municipio, en la E el tipo de vía (calle, avenida…), en la F el nombre de la calle y en la G el número de la calle. Quisiera que en la columna H me pusiera la coordenada “x” y en la columna I me pusiera la coordenada “y”. Cada fila es una dirección diferente.

    Debería funcionar tu macro con un excel con la información dispuesta de esta manera?

    A mí al principio de la macro me da error; me marca la primera “Dim” y me sale una ventana emergente donde dice “Error de compilación: Se esperaba: fin de la instrucción”.

    Me puedes ayudar de alguna manera? Muchas gracias de antemano!

  12. Maria Jose Corbera 19 de Agosto de 2013 at 12:54 #

    Buenas tardes Vega.
    Lo primero es disculparme por el retraso en la contestación.
    He leído tu mensaje y he intentado plasmar sobre mi excel con la macro que explico lo que me comentas.
    Para empezar, antes de hablar de columnas, de concatenar campos, etc..una prueba rápida para ver que todo anda en orden.

    Vas a una celda vacía y escribes la dirección que quieras con el siguiente formato:
    =GoogleGeocode(“[PAIS],[CCAA],[PROVINCIA],[MUNICIPIO],[TIPO CALLE] [NOMBRE CALLE] [NÚMERO CALLE]”)

    En mi caso he probado con esta dirección que sé de antemano que ha de devolverme.

    =GoogleGeocode(“Espanya,Andalucia,SEVILLA,Castilleja,Calle Real 1”)

    Si en la celda te aparece un valor, como pudiera ser éste 37.3875165,-6.0462367 , es que la macro está funcionando correctamente luego el error vendría en la forma de pasar esos mismos parámetros como columnas de Excel.
    Ya que tienes tantas columnas útiles, yo intentaría concatenar todos los valores en una, por ejemplo G si es que no la usas y luego en H añadiría la función de esta forma: =GoogleGeocode($G1)
    donde en $G1 tienes la concatenación de $A1,$B1,… teniendo en cuenta los espacios y comas que has usado en la primera prueba.

    De todas formas indicas un error de compilación, es raro puesto que yo estoy haciendo uso de la macro y no me da ningún error.
    ¿Es posible que al copiarla hayas añadido espacios en blanco, caracteres extraños, algo que la haya podido modificar?
    Intenta ir viendo línea por línea qué diferencias hay con respecto a la que incluyo en el post teniendo en cuenta los mensajes previos en los que se hablabla de un problema con los &

    Dime si hay algún avance y sino pégame el error tal cual que te aparece a ver si arroja algo más de luz.

  13. Vega 22 de Agosto de 2013 at 13:05 #

    Hola María José,

    Acabo de leer tu respuesta. Hoy ya no tengo tiempo de probar lo que me comentas, pero el próximo lunes lo probaré y te diré cómo me ha ido.

    Muchísimas gracias por tu ayuda. Soy totalmente nueva en esto del VBA, pero tengo muchas ganas de aprender. Me he sacado un libro de la biblioteca y estoy empezando a entender un poco de qué va. (Imagínate mi nivel…;))

    Buen fin de semana! 🙂

  14. Vega 27 de Agosto de 2013 at 13:02 #

    Hola!

    Disculpa por no haber contestado antes…

    He vuelto a copiar toda la macro (esta vez dejando los saltos de línia y todo como se ve en la captura de imagen de tu macro) y no parece que ahora esté dando ningún fallo. Antes se veía toda en letras rojas y ahora aparecen en colores como la tuya.(¿Te he comentado ya que soy muy muy novata en el tema de macros..? ;)). Pero cuando pongo en una celda cualquiera: “=GoogleGeocode(“Espanya,Andalucia,SEVILLA,Castilleja,Calle Real 1″)”, como me comentas, me dice que hay un error en mi fórmula (no dice cuál) y las palabras “Espanya, Andalucia, SEVILLA, Castilleja y Calle” aparecen sombreadas en negro en la barra de la fórmula. ¿Tienes idea de qué puede estar pasando?

    Lo del problema con los & pensaba que ya estaba solucionado… ¿O no? :/

    ¡Muchísimas gracias de nuevo por tu ayuda! 🙂

  15. María José Corbera 27 de Agosto de 2013 at 17:34 #

    En la fórmula =GoogleGeocode(“Espanya,Andalucia,SEVILLA,Castilleja,Calle Real 1″)

    Veo que añades ” antes del = (signo igual) esto es incorrecto porque si colocas la ” (signo doble comilla) Excel no entenderá que se trata de una función.
    En el momento en que comiences a teclear en la barra de fórmulas (Fx) el signo = te aparecerán diferentes funciones.

    El tema de los & debe estar ya arreglado en el post principal, de no ser así te daría un error en el código.
    No te preocupes por las veces que necesites preguntar, ni te imaginas la de veces que me toca hacerlo a mí.
    🙂

  16. Vega 30 de Agosto de 2013 at 10:00 #

    Ah, no! :/

    He puesto la fórmula entre comillas sólo para mencionarla en el post. Cuando la escribo en una celda del Excel lo hago sin comillas…

    Disculpa la confusión… :/

  17. Maria Jose Corbera 2 de Septiembre de 2013 at 10:31 #

    Vaaaaaaaaaaaaaaaale, creo que ya sé cuál es el problema.
    ¿Estás copiando y pegando lo que está escrito en el post? Me ocurre, e intuyo que eso es lo que te sucede a ti, que cuando copio la función del post y la pego en la barra de fórmulas de Excel me da un mensaje de error en la fórmula con la opción de aceptar y/o cancelar.
    Acepto y me subraya el contenido de la fórmula, en negro hasta la palabra Calle.

    Sin embargo, si tecleo directamente esa fórmula en la barra me funciona a la primera.
    Pruébalo

  18. Vega 9 de Septiembre de 2013 at 8:32 #

    Merci!

    Hoy probaré lo que me comentas y te digo algo. 🙂

  19. Vega 9 de Septiembre de 2013 at 10:48 #

    Pues no me funciona aunque lo copie a mano… 🙁
    Estoy intentando averiguar cuál puede ser el problema, pero tengo tan poca idea de marcros (nada, vaya), que es frustrante… Me estoy imaginando que a lo mejor es algo básico en lo que no he caído, como en el chiste ese en que una persona se queja de que la pantalla de su ordenador está toda negra y el problema es que el ordenador ni siquiera está enchufado a la corriente… Me siento inútil… 🙁
    Seguiré probando…

  20. Vega 9 de Septiembre de 2013 at 11:12 #

    Hola de nuevo.

    Haga lo que haga con la fórmula siempre me sale el mismo error: “uno de los valores utilizados en la fórmula es del tipo de datos incorrecto”. No sé cuál es el motivo.

    Lo que sí que es un gran paso es que en la celda en la que empiezo a escribir =GoogleGeocode…” me aparece como opción de función, así que la función la reconoce.

  21. Carlos Delgado 9 de Septiembre de 2013 at 16:47 #

    Hola, muy interesante este post. Me ha funcionado perfectamente sobre una Excel 2003, teniendo la precaución en la base de datos de eliminar todas las vocales acentuadas para que la dirección la reconozca Google. Muy útil para aplicar con las aplicaciones OpenStreet Map.
    ¡enhorabuena por la web!

  22. Maria Jose Corbera 10 de Septiembre de 2013 at 12:18 #

    Hacemos una cosa Vega, si quieres sube a la red el documento que estás realizando, sólo con información de prueba, no es necesario que esté entero para ver el documento en sí y por qué falla.

    Pon aquí el link y en cuanto pueda le echo un vistazo

Dejar un comentario

*