Hacer Geomarketing lowcost #3 Geocodificacion con Excel

Las herramientas GIS son útiles y a menudo caras, con licencias o por curva de aprendizaje. Aprenderemos a pintar una serie de coordenadas en el mapa a partir de un listado de direcciones. Muy útil para transportes, logística, buzoneos, marketing directo o visitas presenciales.

Partimos de la necesidad de geocodificar un listado de direcciones en Excel. Antes, como vimos en el post “Hacer Geomarketing lowcost. Software libre #2 Mapping Sheets” , hay que hacer un proceso de limpieza (data cleansing) previo.

Partimos de nuestro Excel:

1

 

Indagando en internet, encuentro un buen post del 2012 que habla de cómo crear complementos en Excel (extensiones xlam). En este mismo post encuentro la query para montar nuestro complemento.

Modificación al post de febrero de 2017:

Parece que el código que nos ofrecía “Police Analyst” no funciona. Tal y como comenta la entrada http://grindgis.com/software/microsoft-excel/geocoding-excel-and-bing hay una herramienta de excel que nos realiza la geocodificacion: http://excelgeocodingtool.com/

Mantengo el contenido del post anterior aunque le introduzco una sangría por si el código pudieramos mejorarlo. El contenido del post continúa más abajo, ya sin sangría.

Gracias a “Police Analyst”: http://policeanalyst.com/using-the-google-geocoding-api-in-excel/

Primero tenemos que habilitar el módulo de “programador” en Excel para pegar la query:

Archivo-opciones-personalizar cinta de opciones-darle al check de programador

2

Cerramos el Excel e iniciamos una nueva sesión. Comprobamos que tenemos la pestaña “programador” en el menú. En éste, damos al botón de visual basic:

3

Entonces tendremos 2 posibilidades: insertar-módulo

4

O puede ser que tengas en Excel más proyectos VBA, por lo que tendrías que irte a tu proyecto VBAProject (Libro1), que es el nuevo Excel que yo tengo abierto, botón derecho del ratón, insertar-módulo:

5

En el módulo, en la ventana en blanco de la derecha, pegamos el siguiente script:

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

El código está hecho en una librería XML de  Microsoft, por lo que tendremos que habilitarlo: herramientas- referencias y en el menú darle al tic de Microsoft XML

6

Guardas, cierras el editor de visual y guardas el Excel formato xlam:

7

Habitualmente hay una carpeta en la ruta de Microsoft que es “complementos” y si lo guardas ahí, los tendrás todos ordenados. Yo he probado a guardarlo en otra ruta y también funciona.

Ahora tendremos que habilitar el complemento: archivo-opciones- complementos-administrar complementos de Excel-ir

8

Examinas donde has guardado el complemento y lo habilitas en el listado del la ventana de la izquierda.

9

Cierras Excel y vuelves a abrir una sesión nueva.

Te pones en la celda y escribes =get y compruebas que se han cargado las nuevas fórmulas de georreferenciación. Para que nos saque las coordenadas que son compatibles con Google Maps: =getcoordinates() y ya lo tenemos:

10

Lo comprobamos y ¡funciona!

11

Vayamos al siguiente paso: ¡PINTARLO!

Opto por pintarlo en Google Maps. Inicio sesión en Google y me voy al apartado de “mis mapas”:

12

Ahora tengo que añadir una capa donde importaré mi Excel con las direcciones geocodificadas: añadir capa- importar y arrastraremos a la ventana nuestro documento:

13

 

Nos pedirá cuál es el campo de las coordenadas, pero es que en mi Excel no tenía el nombre de las columnas: importante, pon nombre a las columnas y por si acaso, no pongas tildes a los nombres.

14

 

Volvemos a repetir  la importación, marcamos que nuestra columna de posición es “coordenadas” y de latitud/longitud, que es como las da Google:

15

Si lo hicieras al revés, poniendo longitud/latitud, te podrías encontrar este pequeño contratiempo: ¡AGUA!, nos ha dibujado nuestros puntos en el mar cerca de Kenia Si te pasara, ya sabes por qué es:

16

Si lo hacemos bien, el resultado será el esperado, ya sólo queda “tunear” el mapa: nombres a las capas, colores… como te guste:

17

Espero que haya sido de tu agrado: compártelo y agradecido por tus comentarios.
Te invito a visitarme más a menudo suscribiéndote
Anuncios

8 comentarios en “Hacer Geomarketing lowcost #3 Geocodificacion con Excel

  1. Pingback: Hacer Geomarketing lowcost #4 Google Fusion Tables | www.formacionenmarketing.com

Responder

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