Geocodificar en Excel: Cómo obtener las coordenadas de una dirección

Definitivamente el trabajo es una fuente de inspiración única. Esta semana uno de los miembros de mi equipo me hizo una pregunta que despertó de inmediato mi curiosidad: ¿Es posible geocodificar en Excel direcciones físicas?. La idea es emplear una función o fórmula que se alimente con el texto de una dirección física y devuelva cuales son sus coordenadas en el mapa (latitud y longitud).

Por defecto no existe ninguna función que permita hacer esto… Por eso es momento de abrir esa maravillosa caja de herramientas que es Visual Basic para Aplicaciones para programar algo a nuestra medida.

Pensando en como resolver este reto recordé haber visto un artículo que describe en detalle cómo integrar Excel con la API de Google, algo que para nuestro objetivo resulta genial… Luego de pasar un rato más investigando y ensayando soluciones sobre la base de esta pista no tardamos en encontrar una solución simple.

Geocodificar-Excel

 

Conociendo la API de Google Maps Geocode.

Geocodificar es el proceso de convertir una dirección física (como “Paseo El Rosedal, Avenida Infanta Isabel, 900, Buenos Aires, Argentina”) en un par de coordenadas geográficas (como latitud -34.5723644 y longitud -58.4165524). Las coordenadas geográficas que se obtienen pueden luego ser usadas para localizar ese punto en un mapa dentro de un Sistema de Información Geográfica, como Google Maps.

Los usos pueden ser muy variados. Los más frecuentes se encuentran asociados a la necesidad de geolocalizar datos para elaborar análisis de datos sobre el mapa. Por ejemplo en nuestro caso la necesidad surgió por que trabajamos con información de un cliente que cuenta con más de 200 sucursales en toda la geografía nacional y necesitábamos geolocalizar distintos indicadores claves de desempeño sobre un mapa.

Google nos ofrece la posibilidad de realizar consultas a muchos de sus productos a través de un enorme conjunto de API. Es impresionante la variedad de información que podemos tener a nuestra disposición por esta vía. Si quieres darle una mirada no dejes de visitar la página de Google API Explorer. Justamente entre las API que Google pone a nuestra disposición existe una que nos permite consultar cuales son las coordenadas físicas de una dirección en el mapa, devolviendo entre otros parámetros su latitud y longitud, si bien con ciertas limitaciones para su uso. Se trata de la Google Maps Geocode API.

¿Qué hace la API de Google Maps Geocode?

En términos simples te permite visitar una URL que contiene la dirección física de un sitio y obtener como respuesta un archivo XML con la localización, expresada en términos de latitud y longitud.  Las consultas deben ser enviadas en el siguiente formato:

https://maps.googleapis.com/maps/api/geocode/output?parameters

Supongamos que quieres conocer las coordenadas de la dirección “Paseo El Rosedal, Avenida Infanta Isabel, 900, Buenos Aires, Argentina”, podrías poner la siguiente URL en tu navegador para obtener de regreso un archivo XML con el resultado:

http://maps.googleapis.com/maps/api/geocode/xml?address=Paseo%20El%20Rosedal%20Avenida%20Infanta%20Isabel%20900%20Buenos%20Aires%20Argentina&sensor=false

La respuesta XML en tu navegador tendrá este aspecto:

Consulta XML Google Geocode API

Esa localización es precisamente el parámetro que necesitamos, conteniendo la latitud y longitud determinada por los algoritmos de Google Maps. Conociendo que ya tenemos de donde capturar este dato ahora solo resta construir desde VBA un código que nos permita enviar la consulta a Google Maps e interpretar el XML de respuesta.

Empleando VBA para geocodificar en Excel

El código a emplear es muy simple. Para facilitar lo desarrollé como una función definida por usuario o UDF, por las siglas en inglés (User Defined Function).  Como las macros, las UDF emplean Visual Basic para Aplicaciones (VBA) como lenguaje de programación. Es importante que recuerdes que puedes emplear una UDF desde cualquier parte del proyecto, invocandola desde las hojas, los módulos e incluso formularios.

El código de la función para geocodificar en Excel consta de tres partes; una primera parte que se encarga del armado y envío de la consulta a la API de Google Maps Geocode; luego una segunda parte que se encarga de interpretar el XML de la respuesta de la API y extraer los datos de latitud y longitud y finalmente, en caso de error, una tercera parte que ayuda a arrojar un mensaje para identificar cuando falla la consulta.

Este código VBA fue desarrollado y probado para ser usado bajo ambiente Windows. Por ahora no se dispone de una versión funcional para ambiente Mac OSX. Recomiendo su uso previo en modo de prueba antes de implementar en un proyecto activo.

Public Function GEOCODIFICAR(Direccion As String)
Dim PrimerValor As String, SegundoValor As String, TercerValor As String, ValorTemporal As String
 'Primera parte: Armado de la URL con la consulta a la API de Google Maps Geocode
 PrimerValor = "http://maps.googleapis.com/maps/api/geocode/json?address="
 TercerValor = "&sensor=false"
 Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
 URL = PrimerValor & Replace(Direccion, " ", "+") & TercerValor
 'Parte dos: Interpretación del XML que se obtiene como respuesta de la API de Google Maps Geocode
 objHTTP.Open "GET", URL, False
 objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
 objHTTP.send ("")
 If InStr(objHTTP.responseText, """lat""") = 0 Then GoTo Error
 ValorTemporal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lat"" : ") - 7)
 lat = Split(ValorTemporal, ",")(0)
 ValorTemporal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lng"" : ") - 7)
 lng = Replace(Split(ValorTemporal, "}")(0), " ", "")
 GEOCODIFICAR = lat & " ; " & lng
 Exit Function
 'Parte tres: Manejo de errores y excepciones
Error:
 lat = lng = "No encontrado"
End Function

¿Cómo configurar la función para geocodificar en Excel ?

Presiona ALT + F11 para abrir el Editor de Visual Basic y haz clic en Insertar > Módulo. Ahora en el nuevo módulo copia y pega todo el código de arriba. Para regresar a la hoja de cálculo presiona nuevamente ALT + F11. Ahora puedes comenzar a emplear la nueva función para geocodificar en Excel de la misma forma que invocas otras funciones ya existentes como BUSCARV, SI, CONTAR.SI, CONCATENAR, etc.

Se llama GEOCODIFICAR y en la imagen anexa puedes ver cómo funciona con varias direcciones de prueba. En sus sintaxis solo debes cargar la dirección a consultar. Puedes hacerlo escribiendo entre comillas directamente la dirección a consultar (por ejemplo, “Champ de Mars, 5 Avenue Anatole France, 75007 París, Francia”) o haciendo referencia a la celda donde se encuentra la dirección  a consultar ( por ejemplo A4).

GEOCODIFICAR EN EXCEL - EJEMPLO

Puedes descargar aquí un libro de práctica, incluyendo el código utilizado: Geocodificar en Excel

Limitaciones

Debes tener en cuenta que existen algunas limitaciones al momento de emplear tu nueva función para geocodificar en Excel:

  • Si obtienes con mucha frecuencia el error “No encontrado”, es posible que estés enfrentando problemas con los límites de la API de Google Maps Geocode. De acuerdo a las especificaciones de Google puedes realizar hasta 2.500 consultas por día (cada vez que la fórmula se actualice, por cada celda en la que la emplees es una consulta) y 10 consultas por segundo.
  • En atención a esta limitación y para evitar errores debes tener mucho cuidado al realizar consultas o actualizaciones masivas de resultados de esta función. Consciente de lo límites evita superarlos para evitar errores.
  • Debes contar con una conexión a Internet directa, no a través de proxies. Este punto es muy importante porque el uso de proxies es muy común en las oficinas, y bajo esas condiciones el objeto XMLHttpRequest que empleamos para interpretar la respuesta XML no funciona.
  • Solo en el caso de que te estés planteando hacer un uso masivo de esta función, los límites de consulta pueden ser ampliados si accedes a plan de uso Premium: 100.000 consultas por día y hasta 50 consultas por segundo, con todas las consultas extras que se necesiten vía compra de créditos adicionales.

Si te resulta de utilidad no olvides compartirlo en tu red social de preferencia. Como siempre, espero que te sea de máximo provecho.

20 Comentarios

  1. Jesus 15/03/2016
  2. DAVID BALCAZAR 23/03/2016
  3. Guillermo Pech 29/03/2016
  4. Marto 20/04/2016
  5. joaquin 13/06/2016
  6. Jesús 05/07/2016
  7. German Cuellar 18/08/2016
  8. Marina Moreno 23/08/2016
  9. Tony SB 24/08/2016
  10. Vanessa Briceño 12/10/2016
  11. Gever Salazar 14/10/2016
  12. Candido Optimista 05/01/2017
  13. Nicolas Schmitow 19/01/2017
  14. CRISTIAN SANTE 08/02/2017

Deja un comentario:

¿Te vas sin unirte a nuestro Boletín Semanal?

Recibe gratis en tu correo cada semana nuevos recursos para hacer cosas asombrosas en Excel

Odiamos el SPAM tanto como tú, tus datos están seguros con nosotros.