Google Geocoding API in Excel (II), problems found.

01 Jul 2013

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

El mes pasado hablaba sobre la API de Google para la geolocalización de coordenadas en mi anterior post, dónde comentaba que había tenido una serie de problemas en la implementación. Pues bien, voy a detallarlos y sobre todo, a explicar qué hice para solucionarlos. Empezaré explicando aquellos pequeños inconvenientes que son fáciles de superar.

Cuando realizamos la búsqueda de las coordenadas para aquellos nombres de países, comunidades autónomas, provincias, municipios, etc… debemos de prestar especial atención a su escritura, pues si éstos llevan la letra ‘ñ’ o bien van tildados seguramente causarán conflictos.

Hay diferentes opciones de salvar este escollo, la más rápida, bajo mi punto de vista fue crearme una función que transformase la ‘ñ’ o ‘Ñ’ en ‘n’ o ‘N’ y las vocales acentuadas por vocales sin acentuar.

Esta función se da de alta de la misma manera que la anterior y el código puede ser éste:

Function txtNoAcc(texto) As String
Dim largoTexto As Long, iX As Long
Dim Lett As Long

txtNoAcc = ""

largoTexto = Len(texto)

For iX = 1 To largoTexto
Lett = Asc(Mid(texto, iX, 1))
Select Case Lett
Case Is = 225
txtNoAcc = txtNoAcc & Chr(97)
Case Is = 193
txtNoAcc = txtNoAcc & Chr(65)
Case Is = 233
txtNoAcc = txtNoAcc & Chr(101)
Case Is = 201
txtNoAcc = txtNoAcc & Chr(69)
Case Is = 237
txtNoAcc = txtNoAcc & Chr(105)
Case Is = 205
txtNoAcc = txtNoAcc & Chr(73)
Case Is = 243
txtNoAcc = txtNoAcc & Chr(111)
Case Is = 211
txtNoAcc = txtNoAcc & Chr(79)
Case Is = 250
txtNoAcc = txtNoAcc & Chr(117)
Case Is = 218
txtNoAcc = txtNoAcc & Chr(85)
Case Is = 241
txtNoAcc = txtNoAcc & Chr(110)
Case Is = 209
txtNoAcc = txtNoAcc & Chr(78)
Case Else
txtNoAcc = txtNoAcc & Mid(texto, iX, 1)
End Select
Next iX
End Function

Una vez más decir que en la red seguramente encontremos funciones que nos parezcan mejores, de resolución más elegante,…

Como cualquier otra función en Excel para llamarlas en cada celda bastaría con escribir:

= GoogleGeocode(txtNoAcc(nombreCelda))

El segundo de los problemas, con el que nos podemos encontrar es que a veces, el proceso nos devuelve un mensaje del tipo “Not found (try again, you may have done too many too fast)”

A veces el error simplemente consiste en que el nombre de la localización existe en más de una provincia, estado… o lo que es lo mismo que existen diferentes coordenadas para ese nombre.

Esto suele pasar con nombres de municipios, sugerimos que en estos casos se le añada en la búsqueda la Comunidad Autónoma o cualquier otro dato que la pueda diferenciar.

Pero en otros casos el error es imposible de resolver ya que se trata de una restricción de Google.

Server-side geocoding through the Geocoding Web Service has a quota of 2,500 requests per IP per day, so all requests in one day count against the quota. In addition, the Web Service is rate-limited, so that requests that come in too quickly result in blocking. Client-side geocoding through the browser is rate limited per map session, so the geocoding is distributed across all your users and scales with your userbase. Geocoding quotas and rate limits drive the strategies outlined in this article.

In Google Maps API for Business, quotas are tied to client IDs, which provide much higher quotas. To learn more about Maps API for Business quotas and error handling, we recommend reviewing our article, Usage Limits for Google Maps API Web Services. If you’re still running into quota limits using the Google Maps API for Business, file a support request here: http://www.google.com/enterprise/portal/.

Se puede consultar más al respecto aquí:

https://developers.google.com/maps/articles/geocodestrat

En estos casos sólo queda esperar a que nos quiten la restricción para poder seguir consultando.

Precisamente para eludir este problema ideamos una forma más refinada de hacer las operaciones de geolocalización.

Si en lugar de realizar 4000 consultas, una por registro, obtenemos los países diferentes, o las provincias únicas que aparecen, etc…bajaríamos mucho las posibilidades de saturar el máximo de peticiones.

Explico a continuación qué se hizo y cómo:

Lo primero es usar la opción de la pestaña Datos, Filtro Avanzado, pulsando sobre Avanzadas. Una vez se nos abre la ventana correspondiente indicamos que queremos copiar a otra celda y lo fundamental que queremos quedarnos únicamente con aquellos valores diferentes.

De esta forma por cada páis, comunidad, municipio sólo tendremos una instancia por la que preguntar y eliminaremos muchas consultas innecesarias a la API de Google para la geolocalización.

Otro pequeño detalle, aunque en este caso pensado para una mejor visualización sería el colocar las coordenadas en dos columnas en lugar de sólo una como sería tras resolverse la función que creamos anteriormente.

En una de las columnas tendríamos la Latitud y en la otra la Longitud, para conseguir esto y dado que tenemos ambos valores en la Columna generada, a la que por ejemplo se le llamó Coordenadas, aplicaremos dos funciones una para cada columna.

  • Para la Latitud aplicaremos ésta: =IZQUIERDA(B1;HALLAR(“,”;B1)-1) donde B es la columna Coordenadas.
  • Para la Longitud usaremos ésta otra: =DERECHA(B1;LARGO(B1)-HALLAR(“,”;B1)) donde B es la columna Coordenadas.

Una vez que tenemos todos los valores para x instancias únicas podríamos pasar estos valores de las coordenadas a la hoja donde se encuentra la información al completo usando funciones de búsqueda y/o consulta.

Post relacionados

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

10 Respuestas to “Google Geocoding API in Excel (II), problems found.”

  1. Francis 2 de Julio de 2013 at 11:21 #

    Hola
    Estoy interesandome en usar google maps con Excel y veo que dominas
    el tema.
    Tengo una duda, inserto el activeX de excel (Webbrowser) en una hoja, pero soy incapaz de imprimirlo como parte de una ficha que estoy creando. Me gustaria poder usar las APIS directamente pero no se como se hace. Me prodrias Ayudar?
    Si no te molesta, he modificado un poco la funcion de separar las coordenadas para que se pueda arrastrar y rellenar las celdas consecutivas.
    Gracias por compartir
    Un saludo
    Ppri

  2. Francis 2 de Julio de 2013 at 11:23 #

    Se me olvido poner la modificacion:

    =IZQUIERDA(GoogleGeocode(A10);HALLAR(“,”;GoogleGeocode(A10))-1)

    =DERECHA(GoogleGeocode(A10);LARGO(GoogleGeocode(A10))-HALLAR(“,”;GoogleGeocode(A10)))

    Lo dicho, Gracias
    Ppri

  3. Maria Jose Corbera 3 de Julio de 2013 at 8:32 #

    Buenos días Francis, a ver si te he entendido.
    ¿Lo que necesitas es incrustar en una hoja de Excel un mapa sobre el que pintar las direcciones?
    Si es así, vas bien.
    Lo primero es irte a la pestaña Programador. Seleccionas Insertar/Más controles.
    En la ventana que te muestra con los distintos controles selecciona Microsoft Web Browser.
    Pulsas Aceptar y a continuación seleccionas con el ratón el área que va a ocupar ese control, cuanto más amplia mejor para manejarte luego en el mapa.
    Hasta ahí entiendo que ya lo has hecho.

    Luego hablas de una ficha, así que interpreto que vas rellenando datos en una columna del tipo Municipio, Provincia, País,…
    Lo más importante es la URL que vas a usar para pedir la localización en GoogleMaps.

    En mi caso, la URL es insertada en una de las celdas como una función de este modo:
    =”https://maps.google.com/maps?q=”&B2
    A medida que quieras pasarle datos tendrás que concatenarlos en la petición.

    En la columna A tengo los títulos de provincia, etc.. y en la columna B los valores.
    Ahora es necesario pasarle el código a ese control que añadimos.
    Para ello en modo Diseño, selecciono el control, botón derecho y selecciono Ver código.
    Al hacer esto se abre una ventana de VB donde escribiré el código.
    Por defecto viene esto:
    Private Sub WebBrowser1_StatusTextChange(ByVal Text As String)

    End Sub

    A lo que yo le añado lo siguiente

    Sub abreGoogle()
    WebBrowser1.Navigate2 Range(“C8”).Value
    End Sub

    C8 es la celda que corresponde a la función que comentaba antes (=”https://maps.google.com/maps?q=”&B2).

    Hasta ahora el funcionamiento es hacer una petición a Google maps con los datos que hemos rellenado en las columnas (B en mi caso) y lanzar la petición que tenemos almacenada en este caso en la celda C8.
    Con este código el control se debe mostrar con cada punto de geolocalización que le indiquemos.
    Probamos primero desde la propia ventana de VB a Ejecutar.
    Si todo va según lo previsto el siguiente paso sería añadir un botón que nos agilice el trámite.
    Eso se hace del mismo modo que se añadió el Web Browser. Se asocia al método del WebBrowser y se ejecuta.
    Con esto deberías ver por cada conjunto de datos que le pases al mapa los puntos correspondientes en Google Maps.
    Espero haber contestado tu pregunta. 🙂

  4. Maria Jose Corbera 3 de Julio de 2013 at 9:02 #

    Dejo una imagen de lo que se obtendría:
    http://img825.imageshack.us/img825/6522/7g5i.png

  5. Francis 3 de Julio de 2013 at 12:32 #

    Hola
    He visto tu imagen y hasta ahí ya lo consigo, quizás me explique mal,
    El siguiente paso es establecer un área de impresión abarcando el mapa y mirando en la vista previa, aparecen todas las celdas y gráficos que tenga ese área de impresión, pero el mapa no se muestra.
    He visto que el WebBrowser tiene un método print, pero si lo utilizo sólo me imprime el objeto y no todo el área de impresión.
    En resumen, quiero imprimir una ficha con datos de excel en un área definida de impresión y que esta, incluya un mapa.
    Quizás incrustandolo en una shape sirva (lo probare) al fin de cuentas es para imprimir y no es necesario que siga siendo interactivo.¿Que te parece?
    Gracias por tu tiempo
    Un saludo
    Ppri

  6. Maria Jose Corbera 4 de Julio de 2013 at 13:37 #

    Ahhhhhhhhh vale sí sí, perdón. Te entendí mal entonces… es lo que tiene pensar en las vacaciones ya!! 🙂
    Sí, efectivamente. Si colocas el control en una shape, en mi caso he insertado una forma rectangular, sí que podrás imprimir el conjunto. Es decir el control WebBrowser que necesitas para tu geolocalización y la ficha del sitio.

    He buscado un código apropiado en las webs de Microsoft y éste me ha funcionado a la primera:

    Option Explicit

    Private Type DOCINFO
    cbSize As Long
    lpszName As String
    lpszOutput As Long
    End Type

    Private Declare Function FindWindow Lib "user32.dll" Alias _
    "FindWindowA" (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

    Private Declare Function FindWindowEx Lib "user32" _
    Alias "FindWindowExA" _
    (ByVal hWnd1 As Long, _
    ByVal hWnd2 As Long, _
    ByVal lpsz1 As String, _
    ByVal lpsz2 As String) As Long

    Private Declare Function CreateCompatibleDC Lib "gdi32" _
    (ByVal hDc As Long) As Long

    Private Declare Function CreateCompatibleBitmap Lib "gdi32" _
    (ByVal hDc As Long, _
    ByVal nWidth As Long, _
    ByVal nHeight As Long) As Long

    Private Declare Function SelectObject Lib "gdi32" _
    (ByVal hDc As Long, _
    ByVal hObject As Long) As Long

    Private Declare Function DeleteObject Lib "gdi32" _
    (ByVal hObject As Long) As Long

    Private Declare Function BitBlt Lib "gdi32" _
    (ByVal hDestDC As Long, _
    ByVal x As Long, _
    ByVal y As Long, _
    ByVal nWidth As Long, _
    ByVal nHeight As Long, _
    ByVal hSrcDC As Long, _
    ByVal xSrc As Long, _
    ByVal ySrc As Long, _
    ByVal dwRop As Long) As Long

    Private Declare Function CreateDC Lib "gdi32" _
    Alias "CreateDCA" _
    (ByVal lpDriverName As String, _
    ByVal lpDeviceName As String, _
    ByVal lpOutput As String, _
    ByVal lpInitData As Any) As Long

    Private Declare Function GetDC Lib "user32" _
    (ByVal hwnd As Long) As Long

    Private Declare Function ReleaseDC Lib "user32" ( _
    ByVal hwnd As Long, _
    ByVal hDc As Long) As Long

    Private Declare Function GetDeviceCaps Lib "gdi32" ( _
    ByVal hDc As Long, ByVal nIndex As Long) As Long

    Private Declare Function DeleteDC Lib "gdi32" (ByVal hDc As Long) As Long

    Private Declare Function StartDoc Lib "gdi32" _
    Alias "StartDocA" (ByVal hcs As Long, lpDI As DOCINFO) As Long

    Private Declare Function EndDoc Lib "gdi32" (ByVal hcs As Long) As Long

    Private Declare Function GetDefaultPrinter Lib "winspool.drv" _
    Alias "GetDefaultPrinterA" _
    (ByVal sPrinterName As String, _
    lPrinterNameBufferSize As Long) As Long

    Private Const LOGPIXELSX As Long = 88
    Private Const LOGPIXELSY As Long = 90
    Private Const PointsPerInch = 72
    Private Const SRCCOPY As Long = &HCC0020

    '---------------'
    'Public routine.'
    '---------------'
    Public Sub PrintVisibleRange()

    Const ZOOM_FACTOR = 3 '< --- Change this value as needed. Dim MyDoc As DOCINFO Dim hwnd As Long Dim lBmp As Long Dim lMemoryDC As Long Dim lwbDC As Long Dim lDC As Long Dim wd As Single Dim hg As Single Dim bHeadings As Boolean 'Store the workbook hwnd. hwnd = FindWindow("XLMAIN", Application.Caption) hwnd = FindWindowEx(Application.hwnd, 0&, "XLDESK", vbNullString) hwnd = FindWindowEx(hwnd, 0&, "EXCEL7", vbNullString) 'Store the Visible Range height & width. wd = Application.ActiveWindow.VisibleRange.Width hg = Application.ActiveWindow.VisibleRange.Height 'store the row & column headers visible state. bHeadings = Application.ActiveWindow.DisplayHeadings 'hide the row & column headers. If bHeadings Then Application.ActiveWindow.DisplayHeadings = False 'Store the workbook window dc. lwbDC = GetDC(hwnd) 'create a memory dc. lMemoryDC = CreateCompatibleDC(lwbDC) 'create a compatible bmp. lBmp = CreateCompatibleBitmap _ (lwbDC, PTtoPX(wd, False), PTtoPX(hg, True)) 'select the bmp to the memory dc. DeleteObject SelectObject(lMemoryDC, lBmp) 'copy the visible range onto the memory dc. BitBlt lMemoryDC, 0, 0, PTtoPX(wd, False), PTtoPX(hg, True), _ lwbDC, 0, 0, SRCCOPY 'restore the row & column headers. Application.ActiveWindow.DisplayHeadings = bHeadings 'retrieve the default printer dc. lDC = GetPrinterDC If lDC <> 0 Then

    'Initialize the DOCINFO srtucture.
    MyDoc.lpszName = "VisibleRange_PrintOut"
    MyDoc.lpszOutput = 0
    MyDoc.cbSize = Len(MyDoc)

    'Start a new print job.
    Call StartDoc(lDC, MyDoc)

    'Print the visible range.( May need to adjust the zoom factor ! )
    BitBlt lDC, PTtoPX(0, False), PTtoPX(0, True), _
    PTtoPX(wd, False) * ZOOM_FACTOR, PTtoPX(hg, True) * ZOOM_FACTOR, _
    lMemoryDC, 0, 0, SRCCOPY

    'End the printing.
    Call EndDoc(lDC)

    'CleanUp.
    Call DeleteDC(lDC)
    Call ReleaseDC(0, lwbDC)
    Call ReleaseDC(lMemoryDC, 0)

    End If

    End Sub

    '------------------'
    'Private routines .'
    '------------------'

    Private Function GetPrinterDC() As Long

    Dim sBuffer As String
    Dim sPrinterName As String
    Dim hPrinter As Long

    sBuffer = Space(128)
    If GetDefaultPrinter(sBuffer, 128) Then
    sPrinterName = Left(sBuffer, 128 - 1)
    GetPrinterDC = CreateDC("WINSPOOL", sPrinterName, vbNullString, 0&)
    End If

    End Function

    Private Function ScreenDPI(bVert As Boolean) As Long

    Static lDPI(1), lDC

    If lDPI(0) = 0 Then
    lDC = GetDC(0)
    lDPI(0) = GetDeviceCaps(lDC, LOGPIXELSX)
    lDPI(1) = GetDeviceCaps(lDC, LOGPIXELSY)
    lDC = ReleaseDC(0, lDC)
    End If
    ScreenDPI = lDPI(Abs(bVert))

    End Function

    Private Function PTtoPX _
    (Points As Single, bVert As Boolean) As Long

    PTtoPX = Points * ScreenDPI(bVert) / PointsPerInch

    End Function

    Ese código se coloca en un módulo de VB y en mi caso en el botón Imprimir (CommandButton2) coloco la llamada al método

    Private Sub CommandButton2_Click()
    Call PrintVisibleRange
    End Sub

    Tendrás que modificar el tamaño de la impresión, zoom porque con el código original se ve bastante pequeña la ficha.

  7. Francis 9 de Julio de 2013 at 6:28 #

    Hola
    Perdona por tardar en contestar.
    Voy a probar el código. Pero primero tengo que recuperar todo el trabajo que hice hasta ahora porque se me ha roto el ordenador y estoy intentando recuperar los datos.
    Gracias por todo
    Un saludo
    Ppri

  8. Francis 9 de Julio de 2013 at 6:53 #

    Hola otra vez
    Se me olvidaba, si estas pensando en vacaciones es que las tienes pronto….
    Espero que las disfrutes.
    Otra cosita, he encontrado una forma mas sencilla de imprimir el mapa que se genera en google maps y es tan sencillo como esto :”Worksheets(“BBDD”).Shape(“tu_Shape”).Fill.UserPicture strURL” donde strURL es la direccion que tu creas en “c8”.
    Lo dicho que disfrutes tus vacaciones, yo esperare hasta setiembre.
    Un saludo
    Ppri

  9. Francis 9 de Julio de 2013 at 6:58 #

    Hola de nuevo
    Te pego esta dirección que también parece interesante
    https://developers.google.com/maps/documentation/staticmaps/

    Un saludo
    Ppri

  10. juan pablo pulido rueda 12 de Junio de 2014 at 12:35 #

    Tengo una pregunta, estoy implementando un sistema de coordenadas, en donde tengo, la coordenada de un lugar A y la coordenada de un lugar B, y me gustaria que en otra columna se importara de google maps la distancia y el tiempo , pero no se por donde comenzar, jamas habia trabajado con google maps

Dejar un comentario

*