ACTUALIZACIÓN API MATRIZ DE DISTANCIAS

Hola a todos!

Hace tiempo que tenía pendiente realizar una actualización del post: API MATRIZ DE DISTANCIAS EN VBA.

Y los motivos de realizar dichos cambios se deben por una parte a la necesidad de actualizar su programación y por otra a las limitaciones actuales de trabajar con matrices para poder obtener los datos de distancia y tiempo.

No obstante, el post anterior funciona correctamente y se puede utilizar sin problema.

Dado que no solo quería limitarme crear dos funciones independientes, una para obtener el tiempo y otra para obtener la distancia entre dos puntos, también he mejorado la programación. Podemos comenzar con la primera, la función DURACION_RUTA:

Function DURACION_RUTA(Origen, Destino)
'Declaramos las variables a utilizar
Dim Url As String
Dim Consulta As String
Dim Peticion As Object
Dim Respuesta As Object
Dim iNodos As Object
'Aplicamos conversion de caracteres especiales (con la función Caracter_e) al Origen y Destino
Origen = Caracter_e(LCase(Origen))
Destino = Caracter_e(LCase(Destino))
'Componemos la url necesaria para realizar la petición de datos como una matriz
Url = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & Origen & "&destinations=" & Destino _
& "&mode=" ' por defecto la ruta es en coche, el resto de opciones serían:
'"mode=bicycling"- en bicicleta
'"mode=walking"- andando
Set Peticion = CreateObject("Microsoft.XMLHTTP")
Set Respuesta = CreateObject("Msxml2.DOMDocument.6.0")
Set iNodos = Respuesta.createNode(1, "xml", "")
'Realizamos la consulta
Peticion.Open "GET", Url, False
Peticion.send
Respuesta.LoadXML (Peticion.responseText)
Set iNodos = Respuesta.getElementsByTagName("duration")
'Extraemos la duración del viaje y le damos formato
DURACION_RUTA = Format(Trim(Mid(iNodos(0).Text, 1, InStr(iNodos(0).Text, " "))) / 86400, "h:mm ""h""")
'Liberamos memoria
Set Peticion = Nothing
Set Respuesta = Nothing
Set iNodos = Nothing
End Function

Y ahora con la función DISTANCIA_RUTA:

Function DISTANCIA_RUTA(Origen, Destino)
'Declaramos las variables a utilizar
Dim Url As String
Dim Consulta As String
Dim Peticion As Object
Dim Respuesta As Object
Dim iNodos As Object
'Aplicamos conversion de caracteres especiales (con la función Caracter_e) al Origen y Destino
Origen = Caracter_e(LCase(Origen))
Destino = Caracter_e(LCase(Destino))
'Componemos la url necesaria para realizar la petición de datos como una matriz
Url = "https://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & Origen & "&destinations=" & Destino _
& "&mode=" ' por defecto la ruta es en coche, el resto de opciones serían:
'"mode=bicycling"- en bicicleta
'"mode=walking"- andando
Set Peticion = CreateObject("Microsoft.XMLHTTP")
Set Respuesta = CreateObject("Msxml2.DOMDocument.6.0")
Set iNodos = Respuesta.createNode(1, "xml", "")
'Realizamos la consulta
Peticion.Open "GET", Url, False
Peticion.send
Respuesta.LoadXML (Peticion.responseText)
Set iNodos = Respuesta.getElementsByTagName("distance")
'Extraemos la distancia y le damos formato
DISTANCIA_RUTA = Format(Trim(Mid(iNodos(0).Text, 1, InStr(iNodos(0).Text, " "))) / 1000, "#,##0.00 ""Km""")
'Liberamos memoria
Set Peticion = Nothing
Set Respuesta = Nothing
Set iNodos = Nothing
End Function

Como podéis observar, con estas nuevas funciones podemos obtener los datos de manera independiente, no es necesario introducir el resultado en una matriz ni mostrar los dos datos en conjunto. Además con la nueva programación trabajamos sobre la salida de datos en XML y también damos formato a la información sin necesidad de hacerlo en la hoja.

Por otra parte, en el post anterior, había escrito una función para tratar los caracteres especiales, como la ñ o las tildes. El inconveniente era que esos caracteres se tenían que escribir en varias celdas para ser consultadas por la función, lo que supone un problema si esas celdas se eliminan o la información se pasa a otra parte de la hoja (dejaría de funcionar todo). Pues bien, también he mejorado esta función:

Function Caracter_e(ByVal Cadena As String) As String
'Declaramos variables
Dim sDato As String
Dim sCadena As Long
Dim Contador As Long
Dim nItem As String
'Si encontramos un caracter especial, lo sustituimos
sCadena = Len(Cadena)
If sCadena > 0 Then
For Contador = 1 To sCadena
nItem = Mid$(Cadena, Contador, 1)
Select Case nItem
Case "Ñ"
nItem = "N"
Case "ñ"
nItem = "n"
Case "á"
nItem = "a"
Case "é"
nItem = "e"
Case "í"
nItem = "i"
Case "ó"
nItem = "o"
Case "ú"
nItem = "u"
Case "ç"
nItem = "c"
End Select
sDato = sDato & nItem
Next Contador
End If
Caracter_e = sDato
End Function

Efectivamente, lo solucionamos con un sencillo “Select Case”. Ahora la función está incorporada dentro del código sin necesidad de escribir información en la hoja.

El resultado es el siguiente:

ACTUALIZACION API MATRIZ DE DISTANCIAS

Las dos funciones generan y muestran la información correctamente. Podemos colocarlas en el lugar que queramos y sin necesidad de dar formato, el código lo hace directamente.

Y por último, en el post anterior, también había incluido una sencilla macro para generar el mapa de los datos que habíamos indicado en la hoja. Esta macro componía una url que se ejecutaba y mostraba la ruta indicada en las celdas. El inconveniente que tenía era que la referencia a las celdas estaba escrita en el código, de forma que si era necesario aumentar o reducir el número de celdas, era necesario modificar la macro.

Pues bien, finalmente la he programado para que solo sea necesario seleccionar las celdas con los datos de origen y destino, sea un rango o celdas independientes y pulsar el botón de comando que existe en la hoja y se mostrará el mapa con la ruta.

Esta es la función:

Sub MAPA()
'Declaramos las variables
Dim Matriz As Object, Palabra As Variant, Area As Object
Dim alfaDato As Variant, iPalabra As String
'Creamos colección arraylist para ir agregando los elementos de la matriz
Set Matriz = CreateObject("System.Collections.ArrayList")
'Trabajamos con la hoja activa
With ActiveSheet
Set Area = Application.Intersect(Selection, .UsedRange)
'Controlamos que existan datos seleccionados
On Error GoTo Control
'Por cada objeto/palabra del rango seleccionado
For Each Palabra In Area
'Añadimos cada palabra a la matriz con un loop
Matriz.Add Palabra
Next Palabra
'Pasamos los datos a una cadena de texto
For Each alfaDato In Matriz
iPalabra = iPalabra & "/" & alfaDato
Next alfaDato
OrdenarAlfa = Trim(iPalabra)
'Abrimos explorador y mostramos la ruta
Url = "https://www.google.com/maps/dir" & OrdenarAlfa
ActiveWorkbook.FollowHyperlink Url, NewWindow:=False
'mostramos mensaje de error
Control: If Err.Number = "424" Then
MsgBox ("EL RANGO O LAS CELDAS SELECCIONADAS NO CONTIENEN DATOS"), vbExclamation, "SIN DATOS SELECCIONADOS"
End If
'Limpiamos variable de objeto
Set Matriz = Nothing
Set Area = Nothing
End With
End Sub

Y este es el resultado después de seleccionar y pulsar el botón:

ACTUALIZACION API MATRIZ DE DISTANCIAS1

Y con esto finalizamos el post de hoy, espero que este post os sea de utilidad.

Descarga el archivo de ejemplo pulsando en: ACTUALIZACIÓN API MATRIZ DE DISTANCIAS

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

 

¿Te ha resultado de interés?, puedes apoyar a Excel Signum con una pequeña donación.

Donate Button with Credit Cards

¡¡Muchas gracias!!

Anuncios

39 pensamientos en “ACTUALIZACIÓN API MATRIZ DE DISTANCIAS

  1. Pingback: API MATRIZ DE DISTANCIAS EN VBA | EXCEL SIGNUM

    • Gracias Salvador! Me alegro que te guste el post!.

      Sobre lo del tiempo total entre todas las rutas, sería necesario reprogramar el API, pasando un rango por los parámetros de origen y destino (algo parecido a lo que hago en la última función). Me parece interesante tu comentario, así que investigaré esa petición y cuando tenga un resultado te lo informaré.

      Muchas gracias 🙂

      Me gusta

    • Hola María:

      Para insertar el navegador es necesario hacerlo con un webbrowser en un formulario. Pero es problemático, en primer lugar por los errores de script y por otro la versión del navegador embebida en ese webbrowser normalmente no se corresponde con la última versión del navegador instalado en tu equipo, lo que origina problemas de compatibilidad e impide mostrar correctamente el mapa. La solución pasa por modificar el registro de windows (y eso implica riesgos en el equipo), por eso he optado por mostrar directamente el mapa en el navegador invocándolo desde Excel.

      Sobre esta problemática tienes bastantes ejemplos de usuarios quejándose en foros y webs.

      En resumen, dado los riesgos que puede generar el realizar cambios en el registro de windows, es muy recomendable utilizar el navegar que tengas instalado y usar internet.

      Un saludo.

      Me gusta

  2. Hola Segu, tengo una pregunta con respecto a la parte del código que calcula el tiempo y las distancias, como lo puedo modificar para que funcione en las hojas de calculo de google drive?, gracias por compartir es genial el trabajo!

    Me gusta

    • Hola María, en google drive las macros se denominan Google Apps Scripts. Y aunque tiene parecido, no es igual que VBA. Se tendría que programar la función en ese lenguaje para que te funcionase. Pero eso ya no es materia de esta web, donde los ejemplos se basan en Excel con microsoft.

      Saludos!!

      Me gusta

  3. Buenas tardes:

    Increible post. Tengo una duda: ¿Cómo puedo hacer para mostrar la duración de las rutas en total de minutos? Es decir, si por ejemplo una ruta es de 2:20 horas, en lugar de este resultado, que aparezca 140 minutos

    Muchísimas Gracias!

    Me gusta

  4. Buenos dias, grandioso aporte, un agradecimiento especial y con con una merecida retribuccion economica, te cuento, estudio ingenieria industrial y diseño un modelo de transporte para el sistema de recoleccion de residuos en una ciudad de mi pais, la matriz de distancias inimaginable al rededor de 200 mil datos, venia sacandolos uno a uno por meses, sin pensar que existia una macro, el unico inconveniente es que las distancias cercanas digamos 100 – 300 metros lo arroja como tiempo 0, ya lo coloque en minutos como indicas arriba, te agradeceria de sobre manera como puedo modificar el codigo para evitar dicho error, ya que tengo muchos puntos cercanos y a mano pues claramente da 1 minuto de movilizacion en carro pero en el programa arroja 0.
    Muchas gracias

    Me gusta

    • Hola Juan Camilo:

      Estoy probando la macro en dos puntos prácticamente en la misma posición y no me muestra 0, sino 0,02 (por ejemplo. De todas formas si lo quieres ver en metros solo tienes que eliminar el 1.000 dentro de la función de las distancias. Saludos.

      Me gusta

    • Hola Miguel:

      La macro debería funcionar perfectamente, independientemente del lugar en el que te encuentres. es decir, no debes realizar ninguna modificación.

      Saludos.

      Me gusta

  5. Hola, gracias por tu valioso aporte.
    Estoy tratando de utilizar los códigos, pero me arroja error al tratar de utilizarlos. ¿Puede ser que hayan modificado la página de donde busca la información?.
    Quedo atento a tu comentario.

    Saludos.

    Me gusta

    • Hola Francisco:

      Pues no han modificado en maps nada, a mi me funciona correctamente la macro. Verifica que te funciona la que dejo como descarga y si has modificado el código es posible que el error provenga de esa modificación.

      Saludos

      Me gusta

  6. Buenas amigo, estoy tratando de hacer lo mismo que haces en excel pero en Visual Basic 6, pero me e perdido donde imprimes el resultado

    DURACION_RUTA = Int(Trim(Mid(iNodos(0).Text, 1, InStr(iNodos(0).Text, ” “))) / 86400 * 1440) & ” minutos”

    me sale el siguiente Error : “variable de Tipo Object o la variable de bloque With no esta establecida”, estoy empezando en esto de la programación espero puedas ayudarme…
    y por si te sirve e simplificado tu funcion de caracteres especial, te la paso x si te sirve ….

    Private Function Caracter_e(ByVal especial As String) As String
    Dim Filtro, Lista As String
    Dim i As Integer
    Filtro = “áéíóúñÑÇ”
    Lista = “aeiounNc”
    ‘Aplicamos un bucle para buscar celdas especiales en el momento de ejecutar la función “Ruta”
    For i = 1 To Len(Filtro)
    especial = Replace(especial, Mid(Filtro, i, 1), Mid(Lista, i, 1))
    Next
    Caracter_e = especial
    End Function

    Espero puedas ayudarme
    Saludos y gracias

    Me gusta

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

      • Excelente, muchas gracias.

        Tengo otra consulta, la matriz de tiempos distancia funciona para 6 puntos, cuando arrastro las fórmulas para agregar más locaciones (100 puntos) el cálculo sale incorrecto, me salen distancias que no son reales si las comparo con google maps.
        Me parece que es porque la matriz esta definida en el código para 6 puntos, como puedo cambiar eso para que me funcione para los 100 puntos que me interesa saber?

        Gracias de antemano

        Me gusta

        • Hola Xavier:

          No entiendo cuando dices que cuando arrastras las fórmulas el cálculo sale incorrecto. La función debe conectarse a google maps y espera la descarga, no debería tardar demasiado en actualizar 100 puntos, pero debes esperar a que calcule.

          Otro tema es qué coordenadas introduces, ten en cuenta que google tiene su propio formato de coordenadas y si se cambia el formato el dato no es fiable.

          Verifica bien las coordenadas de lat y long son las correctas y que las has extraído de la macro que te pasé. Si ves que no funciona envíame un ejemplo donde se vea esa diferencia para verificar cuál es el problema.

          Saludos.

          Me gusta

  8. Estoy intentado trabajar en la herramienta pero me saca error, ya he intentado todo y no he podido configurar. Algun error?, alguna actualización que ya no permite funcionar?.

    Me gusta

    • Hola Antonio:

      No he podido actualizar la información, pero te lo comento igualmente. Parece ser que Google Maps quiere forzar a que todos los usuarios del API utilicen una Key para disfrutar del servicio. Esta key se puede obtener gratuitamente, y ofrecen un credito mensual de 200$, (pero si te pasas con las descargas debes pagar …).

      https://cloud.google.com/maps-platform/pricing/#details

      En breve actualizaré las entradas relacionadas con API y estos nuevos condicionantes.

      Saludos.

      Me gusta

  9. Hola, he usado las funciones dentro de un libro propio en el que realizo otros cálculos y en el que necesitaba los kms entre ciudades, me funciona, pero no se pq la función de distancia_ruta entra en bucle haciendo peticiones y si dejo el archivo abierto un rato me gasta el límite y tengo que esperar un día para volver a usarlo.
    He puesto un msgbox para que me muestre la ruta que compone de la petición y es como he visto que está constantemente lanzando peticiones.
    Alguna idea de pq me hace esto?
    Muchas gracias por tu aporte.

    Me gusta

    • Hola zurcueal:

      Dependiendo de la cantidad de consultas que realices el loop se puede ralentizar. Por otra parte, tal y como le comento al lector anterior, Google ahora está solicitando una KEY para que el usuario pueda realizar consultas en MAP, y la macro que tengo programada en ocasiones puede mostrar errores o experimentar problemas.

      De todas formas, si obtienes respuestas, realiza la prueba con algunos registros, 10 o 20 y verifica si te sigue tardando tanto, verifica que el código lo has copiado correctamente en el libro nuevo que indicas y sobre todo, verifica que no te estás pasando del límite de peticiones.

      A mi no me muestra los errores que comentas ni se queda enviando peticiones.

      Saludos

      Me gusta

      • Hola Segu me gustaría saber como puedo actualizar la macro con una llave para no tener la limitante esto debido a que quizá por la cantidad de usuarios que utilizamos tu api este en el limite de uso, por ello me gustaría saber como es el proceso de poder adquirir la KEY sin tener que reestructurar tu macro inicial o de que manera puedo solicitar el api en google.

        Me gusta

        • Hola Antonio:

          Le acabo de contestar a Antonio sobre este asunto.

          Ojo, que para que la API funcione es necesario una KEY y activar la facturación de la tarjeta de crédito. Por ello, deberías ponerte en contacto con google para informarte sobre gastos y otras dudas que puedas tener.

          Saludos.

          Me gusta

    • Hola Hector:

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

      https://cloud.google.com/maps-platform/user-guide/?__utma=102347093.1826101992.1523644539.1537993068.1537993068.1&__utmb=102347093.0.10.1537993068&__utmc=102347093&__utmx=-&__utmz=102347093.1537993068.1.1.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)&__utmv=-&__utmk=196657324&_ga=2.1279345.1985638506.1537992958-1826101992.1523644539

      Me gusta

  10. Hola Segu Ya tengo tengo una KEY ahora me gustaría saber como la integro a la macro que tenemos en el archivo que nos has compartido?, ojala, me pudieses compartir tus comentarios a la brevedad, saludos.

    Me gusta

¿Te ha gustado?, Realiza un comentario.

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.