¿Cómo crear un mapa de calor a partir de direcciones con la API de Bing Maps y macros de Excel (VBA)?

 En este artículo te mostraré paso a paso como se puede crear un mapa de calor (Heatmap) solo introduciendo direcciones de lugares de manera totalmente gratis y rápido. Para eso utilizaremos una hoja de cálculo de Excel la cual mediante VBA consigue las coordenadas del sitio desde la API de Microsoft Bing Maps. Todos los archivos los tendrás al final de este post.

1. Creamos una cuenta de desarrollador en Bing Maps Dev Center

Para crear una cuenta de desarrollador en Bing Map Dev Center entramos en el siguiente enlace y damos click en Sign in:

Bing Maps Dev Center - Bing Maps Dev Center (bingmapsportal.com)

Una vez registrados, vamos a My account > My keys.

En la sección My keys, creamos una nueva key, la cual es gratuita, introduciendo los datos solicitados, en Application type seleccionamos Website, después damos al botón Create.

Maps API Key | Create a Map App | Bing Maps for Enterprise (microsoft.com)

Finalmente, presionamos en Copy key, para copiar la key que nos dará acceso a la información que responde la API. Esta key la podemos pegar en un blog de notas para tenerla guardada, ya que la usaremos más tarde.

2. Creamos nuestra hoja de cálculo de Excel habilitada para macros

Creamos un nuevo documento de Excel en el cual iremos introduciendo las direcciones de lugares de los cuales queremos generar el mapa de calor. En mi caso crearé una tabla con los campos:

• Dirección: direcciones buscadas.
• Query: será el término de busqueda que se introduce en Bing Maps.
• LatJSON: latitud extraída de la función creada.
• LongJSON: longitud extraída de la función creada.
• HTML locations: columna de coordenadas que se copiará al código HTML que crearemos más adelante.

3. Creamos la Macro en VBA que extraiga los datos de Bing.

Lo primero que debemos saber sobre la API de Bing Maps es que la respuesta a una solicitud que le hagamos viene en un archivo JSON por defecto, podremos cambiarlo por uno XML si es que así nos sentimos más cómodos, pero en mi caso lo trabajaré desde JSON ya que con este me resultó más fácil.

Puedes ver la respuesta de la API utilizando el siguiente enlace, reemplazando ADDRESS por la dirección que quieres buscar, y YourKey por la key que generamos en el Paso 1.

http://dev.virtualearth.net/REST/v1/Locations/ADDRESS?&key=YourKey

Adicionalmente, en la web Json Parser Online puedes ver el JSON de una manera organizada y más fácil de entender su contenido, solo tienes que pegar la respuesta que te da el link anterior en la página.

En el artículo a continuación Microsoft nos enseña como podemos obtener coordenadas a partir de una dirección (Query es el término de consulta) con su API:

Find a Location by Query - Bing Maps | Microsoft Learn

Otro detalle a tener en cuenta es que una vez estemos en VBA debemos entrar a la sección Herramientas > Referencias...

y allí activar la opción Microsoft Scripting Runtime:

Además de esto, importamos el archivo JsonConverter.bas (no es escrito por mí) el cual será necesario para que VBA se pueda comunicar exitosamente con la información contenida en el JSON.

Una vez hecho lo anterior, creamos una función en VBA que extraiga los datos del nuestro archivo JSON, de la siguiente manera:

Function LatJSON(address)
    Set MyRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    MyRequest.Open "GET", "http://dev.virtualearth.net/REST/v1/Locations/" + address + "?&key=YourKey"
    MyRequest.send
    
    Dim Json As Object
    Set Json = JsonConverter.ParseJson(MyRequest.responseText)
    
    out = Json("resourceSets")(1)("resources")(1)("geocodePoints")(2)("coordinates")(1)
    
    LatJSON = out
    
End Function

En YourKey introduciremos la Key que guardamos en el paso 1.

Esta función nos devuelve la latitud de la dirección introducida, para obtener la longitud solo tenemos que crear una función igual cambiando Lat por Long, tanto en el título como en la salida.

4. Usamos las funciones en la tabla que creamos

Ahora usaremos las funciones en la tabla que creamos anteriormente, de la siguiente manera:

El argumento de la función simplemente será el término de busqueda, como si estuviesemos realizando una búsqueda en Bing Maps. Esto automáticamente nos arrojará la latitud y longitud de la dirección buscada:

Si buscamos estas coordenadas en Bing Maps vemos que nos lleva al mismo sitio de la dirección, lo único que debemos tener en cuenta es que debemos cambiar la coma (,) por punto (.):

Adicionalmente, llenaremos la última columna de la tabla con lo siguiente: 

=CONCATENAR("new Microsoft.Maps.Location(";SUSTITUIR([@LatJSON];",";".");", ";SUSTITUIR([@LongJSON];",";".");"),")

Esto lo que hace es dejarnos la columna lista para copiar y pegar en el siguiente paso, quedando de esta manera:

5. Creamos el archivo HTML del mapa

Ahora, tomaremos de la web de Microsoft la plantilla para crear un Heatmap que ellos mismos nos proporcionan:

Add default heatmap (bing.com)

El código que nos proporcionan ha sido modificado para introducir coordenadas personalizadas, ya que por defecto el mapa genera ubicaciones aleatorias, quedando de la siguiente manera:

En la línea que he seleccionado irán las coordenadas que copiamos de la última columna de la tabla de Excel, además, en la tercera línea de código de abajo hacia arriba (línea 50 en el archivo de ejemplo) hay un enlace donde aparece YourBingMapsKey, lo cual reemplazaremos por nuestra key propia.

Una modificación adicional se realiza en las líneas 17 y 18, lo cual es una ubicación predeterminada para que se abra nuestro mapa (centrado en ese punto) y el nivel de zoom que queremos, los cuales los podremos cambiar a nuestro gusto.

Finalmente, cuando abramos nuestro archivo HTML desde el navegador de internet de nuestra preferencia, se verá así:

La mancha roja que vemos a la derecha de la Estación de ferrocarril de Buga es nuestro punto que hemos generado usando la dirección del lugar. Así se ve un poco más ampliado:

Si le añadimos más direcciones a nuestra tabla de Excel la función que creamos nos irá mostrando las coordenadas, y podemos añadir más al HTML en la sección que les indiqué para que nuestro mapa se vea más y más lleno de pequeñas manchas que irán generando nuestro mapa de calor, hasta conseguir algo como esto, o incluso mejor:

Los archivos creados durante este blog no contienen la key, ya que es propia de cada persona, para usarlos y que te funcionen solo debes poner tu key en los campos que te indiqué en el paso a paso.

Prueba Heatmap.zip