#ExcelPedroWave

🔝To translate this blog post to your language, select it in the top left Google box.

Tal día como hoy de hace 12 años escribí el primer artículo de este blog, en el que sigo dando "guerra", y que seguiré publicando mientras tenga lectores que me sigan, a los que tengo que agradecer que visiten este blog a menudo para llegar algún día a los dos millones de páginas vistas (a ver si no hacen falta otros 10 años para alcanzar el segundo millón de visitas).

Mapamundi con relojes

En un artículo anterior publiqué un Mapa con Relojes Mundiales que puedes descargar desde aquí:

Excelente Mapa con Relojes Mundiales | #ExcelPedroWave

En otro artículo expliqué cómo cambiar, en tiempo real, la representación plana del planeta Tierra, con zonas de día y de noche:

Mapamundi de día y de noche | #ExcelPedroWave

En este artículo explicaré cómo se añade una nueva ciudad al mapamundi llamando a una Web API.

Si en el Mapa con Relojes Mundiales se hace clic en el icono con el símbolo ➕, y luego se introduce un nombre de ciudad en la celda AA2, esa ciudad se busca con la Nominatim API y, las primeras 20 ciudades encontradas, se cargan en el rango de celdas: AA3:AA22

Al seleccionar una de esas 20 ciudades encontradas, la geolocaliza en el mapamundi y pregunta si se quiere añadir esa nueva ciudad al mapa, como se puede ver en esta captura de pantalla:

API que busca por nombre

La abreviación API se refiere a los Interfaces de Programación de Aplicaciones, que permiten consultar grandes bases de datos desde una Web API, mediante un servidor que atiende mensajes de solicitud-respuesta definidas, normalmente expresados en formato JSON o XML, por medio de un mensaje basado en HTTP a través de la web.

En este caso llamaremos a la API de la web Nominatim (Open-source geocoding with OpenStreetMap (OSM) data), que nos devolverá la latitud y longitud de cualquier ciudad del mundo en cuestión de milisegundos.

Para poder encontrar hasta 20 ciudades o pueblos al introducir un nombre en la celda AA2, se ejecuta la macro GetLatLongCity, del módulo modCiudadNueva que, mediante un objeto XML, hace una llamada "GET" para obtener datos desde la página web que llama al servidor de Nominatim API:

https://nominatim.openstreetmap.org/search?format=xml&limit=20&accept-language=en&q=" & sCity

Siendo: sCity el nombre de la ciudad buscada, por ejemplo: Zaragoza, en esta página web:

https://nominatim.openstreetmap.org/search?format=xml&limit=20&accept-language=en&q=Zaragoza

En la llamada a la API se incluye:

• format=xml, con el formato del archivo de salida.
• limit=20, con el número máximo de resultados.
• accept-language=en, con el lenguaje de búsqueda, en este caso en inglés (=en). Para español habría que cambiar a: =es.

Código de la macro que llama a Nominatim API, y que devuelve xml.responsetext con la respuesta, que es cargada en la celda A1 de la hoja 'Ciudades':

Nominatim API

Nominatim indexa entidades con nombre (o numeradas) dentro del conjunto de datos de OpenStreetMap (OSM), que es un mapa del mundo creado por gente como tú o como yo, y que es de uso libre bajo una licencia abierta.

La Nominatim API consulta los datos buscando objetos OSM por nombre o tipo, y esta documentada en esta página en inglés:

/search - Nominatim Documentation

La API de búsqueda (/search) permite buscar una ubicación a partir de una descripción o dirección textual. Nominatim API Search admite consultas de búsqueda estructuradas y de formato libre.

En la siguiente página se detallan las políticas de uso de esta API.

Nominatim Usage Policy (osmfoundation.org)

Me quedo con esta frase:

"En principio, estamos contentos de que la API pública sea utilizada por usuarios externos para usos creativos e inesperados."

El resultado de la búsqueda es un archivo con formato XML, del que solamente interesa obtener los valores de estas 3 etiquetas para cada uno de los 20 resultados de la búsqueda:

• display_name: con el nombre de la ciudad o pueblo encontrado.
• lat: con la latitud geográfica en formato decimal.
• lon: con la longitud geográfica en formato decimal.

Cómo extraer la latitud y longitud de una ciudad

Para extraer esos 3 valores se puede hacer importando los datos XML de un servicio web en la hoja de cálculo de Excel, como se explica aquí:

Información general sobre XML en Excel (microsoft.com)

¡O mediante fórmulas! ¡que es como a mí me gusta!, pues tengo todo el control sobre las etiquetas que quiero extraer.

En la celda A1 de la hoja 'Ciudades' está el resultado de la búsqueda con Nominatim API.

En la columna B he escrito los 3 nombres de las etiquetas, repetidos 20 veces, de las que quiero obtener su valor: lat, lon y display_name, con el signo igual (=).

En la celda A2 he escrito esta fórmula arrastrada hacia abajo hasta la celda A61:

=SI.ERROR(EXTRAE(A1;C1+LARGO(B1)+1;LARGO(A1)-C1-LARGO(B1));"")

En la columna C está la fórmula que busca la posición de las etiquetas de la columna B en la columna A:

=SI.ERROR(ENCONTRAR(B1;A1);"")

La fórmula de la columna D extrae los valores de cada una de las etiquetas XML:

=SI.ERROR(IZQUIERDA(A2;ENCONTRAR("'";A2)-1);"")

En las columna E a K he creado una tabla de ciudades y fórmulas auxiliares para conseguir la lista de hasta 20 ciudades encontradas, lo que no voy a explicar por falta de espacio...

En la fila 24 se encuentra la ciudad seleccionada, con su latitud y longitud, su región y país.

La nueva ciudad sustituirá a una ciudad anteriormente programada en la hoja 'Relojes' y obtendrá su geolocalización en el mapamundi mediante la función LocalizarCiudad, que devuelve la posición en el mapa del punto que representa a la ciudad:

Esta función se llama en la macro PreguntarCiudad para obtener la longitud y latitud de la nueva ciudad, tanto en la hoja 'Relojes' como en la hoja 'Mundo':

Y así he podido geolocalizar cualquier ciudad del planeta Tierra en el mapamundi con relojes.

¿Qué te parece? ¿Cómo te quedas?