Uno de los post que tiene más visitas en Excel Signum es API MATRIZ DE DISTANCIAS, donde obtenemos el tiempo y la distancia a partir de un origen y un destino, realizando la petición de información a Google Maps.
Hace unos días, un lector me solicitó ayuda para conseguir los datos de geolocalización de una serie de direcciones (latitud y longitud) y me pareció interesante para realizar una nueva entrada basada en esta consulta.
Antes de nada, comentar que existen varias alternativas para realizar este trabajo, todas ellas pasando por el API de Google Maps de geolocalización. Para este ejemplo usaré el API con formato de datos de salida jSON (en breve os mostraré otra versión con formato XML). Si queréis leer acerca del API y sus especificaciones, podáis hacerlo aquí: Google Maps Geocoding API
Utilizaremos un caso práctico para ilustrar el ejercicio, imaginad que hemos previsto realizar un pequeño viaje y queremos visitar: La Plaza Mayor (Chinchón), El Palacio Real (Aranjuez) y el Alcázar de Toledo. El punto de partida en el que hemos quedado es la Plaza de Santa Ana (Madrid), y queremos obtener las coordenadas de dichos lugares a partir de sus direcciones. Lo indicamos de la siguiente forma en nuestra hoja Excel:
La idea es crear una función que capture el elemento de la columna B (Dirección) y nos devuelva en la columna C las coordenadas de latitud y longitud. Para ello vamos a utilizar 3 funciones. Una será la principal y las otras dos serán secundas pero muy importantes.
Antes de montar la función principal vamos a ver las otras, la primera es Carácter_e:
Private Function Caracter_e(ByVal Destino) As String
Dim i As Integer, fin As Integer
With Sheets(2)
fin = Application.CountA(.Range("A:A"))
'Aplicamos un bucle para sustituir caracteres especiales
For i = 1 To fin
Destino = Replace(Destino, .Cells(i, 1), .Cells(i, 2))
Next
Caracter_e = Destino
End With
End Function
Con esta función, a la hora de pasar los parámetros a Maps, convertimos aquellos caracteres especiales (ejem: tildes, eñes, etc) que normalmente crear errores a caracteres válidos. Los parámetros los he puesto en la segunda hoja, (en caso de que necesitéis añadir alguno más solo tenéis que indicarlo como el resto):
La otra función que vamos a utilizar es aquella que extraerá de la cadena de datos solo los números, las comas, los puntos y los guiones.
Public Function Numeros(LatLong As String)
Dim i As Double, j As Double
Dim num As Variant
'extraemos números, comas, guiones y puntos de la cadena alfanumérica
With Sheets(1)
For i = Len(LatLong) To 1 Step -1
If IsNumeric(Mid(LatLong, i, 1)) Or Mid(LatLong, i, 1) = "," Or Mid(LatLong, i, 1) = "-" Or Mid(LatLong, i, 1) = "." Then
j = j + 1
num = Mid(LatLong, i, 1) & num
End If
If j = 1 Then num = (Mid(num, 1, 1))
Next i
Numeros = num
End With
End Function
Esta función (Numeros) es necesaria dado que cuando extraemos los datos mediante la función principal, en formato jSON, tendremos que extraer la información de Latitud y Longitud. Este es el formato de salida:
Ahora que ya hemos visto estas dos funciones os dejo la función principal Geocoding()
Function Geocoding(Destino As String) As String
Dim Consulta As String
Dim ObjetoXML As Object
Dim Peticion As String
Dim Marcador As Long
Dim sCadena As String
Dim LatLong As String
'Pasamos parámetros de la consulta y aplicamos función caracter_e
'por su tenemos caracteres especiales
Consulta = "http://maps.googleapis.com/maps/api/geocode/json?" _
& "&address=" & Caracter_e(Destino) & "&sensor=false"
'Enviamos la consulta y recibimos respuesta
Set ObjetoXML = CreateObject("Microsoft.XMLHTTP")
ObjetoXML.Open "GET", Consulta, False
ObjetoXML.send
Peticion = ObjetoXML.responseText
'extraemos los datos de latitud y longitud que necesitamos
'de la cadena que hemos obtenido
Marcador = InStr(1, Peticion, "location")
sCadena = Application.WorksheetFunction.Trim(Mid(Peticion, Marcador, 120))
'Para extraer las coordenadas usamos la función Números para que extraiga numeros, puntos, comas y guiones
LatLong = Numeros(Mid(sCadena, InStr(1, sCadena, "{"), (InStr(1, sCadena, "}") - InStr(1, sCadena, "{") + 1)))
'Obtenemos el resultado
Geocoding = LatLong
'Liberamos variable ObjetoXML
Set ObjetoXML = Nothing
End Function
Una vez que tenemos la cadena de texto, solo tendremos que realizar algunos ajustes para extraer los datos que necesitamos y que realizamos con diferentes funciones y recogeremos en las variables Marcador, sCadena y LatLong
El resultado es el siguiente:
El botón que he colocado en la página es para visualizar mediante una sencilla macro la ruta que queremos realizar tomando como datos los códigos, está en el módulo2 de la hoja:
Sub Mapa()
Dim URL As String
With Sheets(1)
'Componemos la url para mostrar el mapa con la ruta.
URL = "https://www.google.com/maps/dir/" & .Range("C2") & "/" & .Range("C3") & "/" & .Range("C4") & "/" & .Range("C5")
ActiveWorkbook.FollowHyperlink URL, NewWindow:=False
End With
End Sub
Y la información que pasamos a maps nos muestra el siguiente mapa:
Como habéis podido comprobar, la información es correcta. He querido utilizar esta forma de extraer información de un archivo jSON para utilizar algunas de las funciones que hemos visto en el blog y demostrar así su utilidad (Números o Carácter_e).
Por último me gustaría recordaros que esta versión del API tiene una limitación diaria a 2.500 consultas. Lo podéis ver aquí: Límites de uso
En los próximos días os mostraré otra función pero trabajando con datos XML y utilizando otras variables y funciones para extraer la misma información.
Pues esto ha sido todo, como siempre os dejo el archivo:
Descarga el archivo de ejemplo pulsando en: API GEOLOCALIZACIÓN EN VBA
IMPORTANTE:
Tal y como le comento a lectores anteriores, google ha dejado de ofrecer consultas al API libres,. ahora solicita una KEY para poder realizar las peticiones. No obstante, también comenta que los resultados sin API pueden ofrecer informaciones “degradadas”, o lo que es lo mismo, que en ocasiones no ofrezca resultado (mostrando el error que comentas).
En concreto, esto es lo que google ha comunicado:
“A partir del 11 de junio de 2018, tendrás que habilitar la facturación con cargo a una tarjeta de crédito y contar con una clave de API válida para todos los proyectos. Esto te permitirá escalar el servicio fácilmente con períodos inactivos más cortos y menos problemas de rendimiento. Además, hemos reducido las 18 API individuales a solo tres productos: Maps, Routes y Places.
En junio de 2016 anunciamos que las solicitudes sin clave de API o ID de cliente dejarían de admitirse. Este cambio se hará efectivo el 11 de junio. A partir de esa fecha, no se podrá acceder sin clave y las llamadas sin clave a las API de Street View y JavaScript de Maps devolverán mapas de baja resolución con la marca de agua “for development purposes only.” (solo con fines de desarrollo). Las llamadas sin clave a cualquiera de las API siguientes devolverá un error: Maps Static API (incluida Static Street View), Directions API, Distance Matrix API, Geocoding API, Geolocation API, Places API, Roads API y Time Zone API.”
Aquí podréis leer sobre el tema: ENLACE NOTICIA
A pesar de ello, no voy a eliminar el post, dado que la programación puede ser interesante para otros proyectos o puede que deseéis modificarla para introducir la KEY (para lo que recomiendo que os pongáis en contacto con Google para proceder con total seguridad a introducir datos de tarjeta de crédito y habilitar la facturación).
Pingback: API GEOLOCALIZACIÓN EN VBA CON FORMATO XML | EXCEL SIGNUM
La función Deocoding localiza perfectamente algunas direcciones, pero otras no consigue encontrarlas y pone #¡VALOR!. Por poner un ejemplo: Calle DON RAMON DE LA CRUZ 80 28006 MADRID, esta dirección no la localiza. Cuál puede ser el fallo? no consigo averiguarlo, he probado a escribir la dirección de todas las formas posibles
Me gustaMe gusta
Hola Luis:
Acabo de hacer la prueba y me funciona perfectamente: estas son las coordenadas que muestra 40.4284959,-3.6741043
Intenta no utilizar tildes y verifica que no has borrado la hoja 2 que contiene caracteres especiales.
Por otra parte, no se cuanto registros has utilizado, el límite diario es de 2.500 consultas (para ampliar es necesario hacerse con el API de pago).
Prueba pasado un día con esa dirección en concreto y debería aparecerte
Saludos.
Me gustaMe gusta
Hola, como podrías hacerlo pero a la inversa, me gustaría saber el país en base a coordenadas.
Me gustaMe gusta
Hola Boris:
Siento no haber podido responderte antes, pero he tenido muchas consultas esta semana y ha sido imposible. Sobre lo que preguntas, visita este post: https://excelsignum.com/2016/11/13/api-geolocalizacion-en-vba-con-formato-xml/
hace lo mismo que el actual pero exportamos los datos en XML lo que hace que sea más sencilla la tarea de extracción de datos.
En ese post, para averiguar el pais, colocando unas coordenadas, solo has de sustituir en la macro el tagname, es de decir, en lugar de “location” escribes formatted_address y tendrás el nombre del país:
Set iNodos = Respuesta.getElementsByTagName("formatted_address")Saludos
Me gustaMe gusta
Hola, muy bueno el post, pero me aparece error de acceso denegado, será que hace falta validar un key de uso de la API?
Me gustaMe gusta
Hola Andrés:
La aplicación funciona sin problema hasta el límite que indica en las especificaciones del API en Google. Una vez que llegas a ese límite, se corta el acceso. Para poder utilizar o ampliar el servicio de pago debes solicitar una KEY y contratar los servicios de pago.
Saludos.
Me gustaMe gusta
Hola buen día no e consegido que la macro funcione con las cordenadas y arroje el pais ojala me pueda ayudar
Me gustaMe gusta
Hola Rick:
La macro funciona perfectamente en versiones 2010, 2013 y 2016. Si no obtienes información es posible que las direcciones que estás indicando contengan un parámetro erróneo, verifica las comas bien situadas y la dirección completa.
Por otra parte, si estás bajando gran cantidad de coordenadas, debes saber que existe un límite de consultas diario, en la página web podrás de Maps podrás ver estas limitaciones.
Si aún así sigue sin funcionarte, puedes enviarme ese archivo que no te funciona para examinarlo detenidamente.
Saludos.
Me gustaMe gusta
Hola Buen día , la macro no me funciona ..podrías enviarme el archivo excel con la macro correcta para probarlo?
Me gustaMe gusta
Hola Sergio, el archivo es el que está disponible para descargar, y el código es el mismo. Lo he probado y no generar ningún tipo de problema.
Saludos
Me gustaMe gusta
Excelente trabajo, me has salvado la vida, ya podré terminar mi proyecto de geoestadística. Gracias
Me gustaLe gusta a 1 persona
Hola
¿Como puedo modificar la macro para que me aparezcan 280 direcciones en el mapa?
Sub Mapa()
Dim URL As String
With Sheets(1)
‘Componemos la url para mostrar el mapa con la ruta.
URL = “https://www.google.com/maps/dir/” & .Range(“C2”) & “/” & .Range(“C3”) & “/” & .Range(“C4”) & “/” & .Range(“C5”)
ActiveWorkbook.FollowHyperlink URL, NewWindow:=False
End With
End Sub
En lugar de c2, c3, c4, c5, tengo valores de la celda c2 hasta la c280?
Gracias
Me gustaMe gusta
Hola Paulina:
Esto lo puedes solucionar con una macro que publiqué en la actualización del API de distancias:
https://excelsignum.com/2017/10/27/actualizacion-api-matriz-de-distancias/
La macro es sub Mapa() funciona mediante la selección de las celdas directamente y pulsando el botón. Descarga el archivo y lo podrás comprobar.
Saludos.
Me gustaMe gusta
Estimado,
Muy útil el modelo..
Tengo una consulta.
Es posible efectuar exactamente el mismo modelo, pero en vez de que el input sea con una dirección o ciudad, este sea ingresando la latitud y longitud de mi punto de origen y mi destino..
Como podría conseguir eso?
Gracias de antemano…
Me gustaMe gusta
Hola,
De antemano muchísimas gracias por tus aportes, me han ayudado mucho, solo tengo dos preguntas
1- ¿Se puede cambiar la api para hacer mas peticiones por día?
2- ¿ se puede indicar que la ruta no lleve peajes ?
saludos y de nuevo muchas gracias.
Me gustaMe gusta
Hola Raúl
Para obtener más consultas tendrías que solicitar una key a google y pagar por el servicio. Tendrías que ponerte en contacto con ellos.
Sobre los peajes. Eso sólo funcionaría con el API de distancias no el de geolocalizacion. Ahora estoy de vacaciones y no puedo enviarte un ejemplo, pero deberías especificar en la consulta avoid=tolls
Cuando regrese de vacaciones intentó enviarte un ejemplo.
Saludos
Me gustaMe gusta
muchísimas gracias, ya puse el código en la consulta y funciona perfectamente.
Me gustaMe gusta
perdón el comentario iba en el de medir distancias, pero tenia este post abierto y no me di cuenta
Me gustaMe gusta
Perfecto. Saludos!!
Me gustaMe gusta
la API aun funciona? por que la acabo de integrar y me queda en blanco. ¿Alguien sabe por que?
Me gustaMe gusta
hola buena tarde espero que te encuentres bien.
mi pregunta es como actualiza la consulta especial
por su tenemos caracteres especiales
Consulta = “http://maps.googleapis.com/maps/api/geocode/json?” _
& “&address=” & Caracter_e(Destino) & “&sensor=false”
ya que como mencionas el api de google maps cambio.
Me gustaMe gusta
Hoy Raymundo, no sé muy bien qué es lo que necesitas, pero deseas modificar la función Caracter_e, la tienes en el código de la macro y ahí podrás modificarla.
Si el problema es que la macro no te funciona, se debe a lo que comento en el post, es necesario obtener una key y colocarla en la cadena de conexión.
Saludos.
Me gustaMe gusta
Hola Segu
Felicidades por tu articulo, llevaba tiempo buscando una solución como esta, a mi no me funciona la función geocoding mi imagino que será por no tener la apikey de google, me gustaría saber que una vez que la tenga como la debo de introducir en tu código vba
el mapa no me lo genera por que no me funciona la función geocoding pero si en lugar de tomar los datos de la columna c(latitud) los tomo de la columna b( dirección postal) me funciona perfectamente.
Muchas gracia por tu tiempo
Me gustaMe gusta
Buenas tardes Segu:
tengo 2 sugerencias que no se si son posibles de ejecutar
1.- si es posible especificar la hora y fecha de la ruta mara que muestre la mejor ruta según la previsión de trafico para esa hora
2.- Si por ejemplo tenemos 10 destinos que calcule cual es la mejor orden para recorrer menos distancia, y que no lo calcule en función del orden que tengamos en la lista de Excel.
Enhorabuena por tu trabajo tan interesante y bien explicado eres un crac
Me gustaMe gusta
Hola Luis:
Efectivamente, lo que planteas es interesante. El inconveniente es que es necesario utilizar la API para descargar esa información (y no sé hasta que punto se pueden obtener todos los datos necesarios).
El tema de la programación de la API, lo he dejado a modo de consulta, y sin especificar como añadir la key y otros elementos, el motivo es sencillo, desde el momento que la API es de pago y es necesario incluir una tarjeta de crédito para obtener la Key, ahí finaliza mi colaboración con este asunto, dado que no sé qué uso se hará del código.
Sobre las dos consultas, se debe realizar con programación y con los datos de la API, (esto es lógico dado que es su negocio).
Muchas gracias por tus palabras y me alegro que te resulten de utilidad los post.
Me gustaMe gusta