Category Archives: cartography

Aggregating points: JSON on SQL and loops on infowindows

NOTE: I’ll use CARTO but you can apply all this to any webmapping technology backed by a modern database.

Get all the data

So we start with the typical use case where we have a one to many relationship like this:

    select e.cartodb_id,
           l.cartodb_id as locaction_id,
      from locations l
inner join employees e
        on e.location = l.location
  order by location

Easy peasy, we have a map with many stacked points. From here you can jump to this excellent post by James Milner about dense point maps. My example is not about having thousands of scattered points that at certain zoom levels overlap. Mine is a small set of locations but many points “stacking” on them. In this case you can do two things: aggregate or not. When you aggregate you pay a prize for readability: reducing all your data to those locations and maybe using visual variables to show counts or averages or any other aggregated value and finally try to use the interactivity of your map to complete the picture.

So at this point we have something like this map, no aggregation yet, but using transparency we can see where CARTO has many employees. We could also use a composite operation instead of transparency to modify the color of the stacked points.

Stacking points using transparency

Stacking points using transparency

Aggregate and count

OK, let’s do a GROUP BY the geometry and an aggregation like counting. At least now we know how many people are there but that’s all, we loose the rest of the details.

    select l.the_geom_webmercator,
           min(e.cartodb_id) as cartodb_id,
           count(1) as counts
      from locations l
inner join employees e
        on e.location = l.location
  group by l.the_geom_webmercator
Grouping by location and counting

Grouping by location and counting

Aggregate one field

But in my case, with CARTO we have PostgreSQL at hand so we can do way more than that. PostgreSQL has many many cool features, handling JSON types is one of them. Mix that with the fact that almost all template systems for front-end applications allow you to iterate over JavaScript Objects and you have a winner here.

So we can combine the json_agg function with MustacheJS iteration over objects to allow rendering the names of our employees.

    select l.the_geom_webmercator,
           min(e.cartodb_id) as cartodb_id,
           json_agg(e.firstname) as names, -- JSON aggregation
           count(1) as counts
      from locations l
inner join employees e
        on e.location = l.location
  group by l.the_geom_webmercator,l.location

And this bit of HTML and Mustache template to create a list of employees we can add to the infowindow template:

<ul style="margin:1em;list-style-type: disc;max-height:10em;">
{{#names}}<li class="CDB-infowindow-title">{{.}}</li>{{/names}}

List of employees on the infowindow

We could do this without JSON types, composing all the markup in the SQL statement but that’s generating quite a lot of content to move to the frontend and of course making the whole thing way harder to maintain.

Aggregate several fields

At this point we can repeat the same function for the rest of the fields but we need to iterate them separatedly. It’d be way better if we could create JSON objects with all the content we want to maintain in a single output field we could iterate on our infowindow. With PostgreSQL we can do this with the row_to_json function and nesting an inner double SELECT to give the properties names. We can use directly row_to_json(row(field1,field2,..)) but then our output fields would have generic names.

    select l.the_geom_webmercator,
           min(e.cartodb_id) as cartodb_id,
           count(1) as counts,
             SELECT r
               FROM (
                 SELECT photourl as photo,
                        coalesce(preferredname,firstname,'') as name
             ) r
           ),true)) as data
      from solutions.bamboo_locations l
inner join solutions.bamboo_employees e
        on e.location = l.location
  group by l.the_geom_webmercator,l.location
  order by counts asc

With this query now we have a data field with an array of objects with the display name and web address for the employee picture. Easy now to compose this in a simple infowindow where you can see the faces and names of my colleagues.

<div style="column-count:3;">
<span style="display:inline-block;margin-bottom:5px;">
  <img style="height:35px;" src="{{photo}}"/> 
  <span style="font-size:0.55em;">{{name}}</span>


Adding pictures and names

That’s it. You can do even more if you retrieve all the data directly from your database and render on the frontend, for example if you use D3 you probably can do fancy symbolizations and interactions.

One final note is that if you use UTF grids (like in these maps with CARTO) you need to be conservative with the amount of content you put on your interactivity because with medium and big datasets this can make your maps slow and too heavy for the front-end. On those cases you may want to change to an interactivity that works like WMS GetFeatureInfo workflow, where you retrieve the information directly from the backend when the user clicks on the map, instead of retrieving everything when loading your tiles.

Check the map below and how the interactions show the aggregated contents. What do you think of this technique? Any other procedure to display aggregated data that you think is more effective?

How a daily digest of geospatial links is distributed

TL;DR If you are interested on getting a daily digest of geospatial links subscribe to this mailing list or this atom feed. Take «daily» with a grain of salt.

Over the last six years Raf Roset, one of my favourite geonerds out there, has been sending all the cool stuff he founds about our geospatial world to Barcelona mailing list at OSGeo mailman server. He started circa 2011 sending one link per mail, but in 2013-04-03 he started to make a daily digest. A gun burst in Spanish is called Ráfaga so the joke was really at hand when someone proposed to call those digests that way.

Time passes, September 2014 and I ask Raf to send them also to Valencia mailing list, since most people there understand Catalan and the content was too good to be enjoyed only by our loved neighbours. Finally in January 2015 I decide to start translating them into Spanish and send them also to Spanish and Seville mailing lists.

Then in May I join CARTO and @jatorre thinks is a good idea if I can send them to the whole company mailing list so after some weeks I stop translating them into Spanish. Since that day I only do it English, trying to follow Raf lead everyday translating his mails and forwarding them to CARTO internal mailing list and the rest of the OSGeo ones.

Also at June I decided to put those mails in a simple website so the Ráfagas would also be accessible on GitHub and a static jekyll website so anyone could use the Atom feed to reach them.

Final chapter, in July I also decide to create a dedicated mailing list just for those people who are only interested in receiving those digest mails, obviously thinking in a broader audience, not just my fellow friends from Spain. I think at some point I will stop sending them to the Spanish lists because normally Ráfagas don’t fire any discussion and I’m sending the same message to three lists. To be fair they sometimes provoke discussions at CARTO mailing list. By the way I’m almost certain the full team has a filter to move them to their archives and they think I’m just an annoying spammer (a couple of times I’ve changed the subject just to troll them xDDD).

To conclude I want to post here my daily Ráfagas experience:

  • Raf is an early bird and sends the digest in the morning, I copy the contents into a shared Google Doc where a group of collaborators help me on translating the content. It may seem not a lot of effort, but doing this every single day needs a team. Really.
  • I go to my favorite text editor, put the translated content into a new file and start a local server to check the website renders properly.
  • If everything is OK I copy the rendered content and send it to CARTO and OSGeo mailing lists
  • I commit and Push to the GitHub repo so the website is updated along with the feed.
  • I archive Raf’s mail from my inbox.

Creating a Ráfaga

That’s it. Raf you are a formidable example of perseverance and I hope you’ll have the energy to keep giving us all those contents for many years. Thanks mate!

About Antipodes Map

We’ve been pretty quiet over the last year but that doesn’t mean we’ve been unoccupied. Last summer we (Pedro and me) participated with some friends on a hackathon with a project to give to teachers from our region a tool to help them to relocate, precalculating travelling times with OSRM and some open datasets, one of them a database of schools that our government published as a spreadsheet. That gave us the chance to work and improve our knowledge on the CartoDB Platform, we used their JavaScript API to place a Leaflet map with a parametrized map where the SQL that defined the layer changed depending on user selections. The project is online with some slides with further information, all in Spanish.

De Casa al Cole map

De Casa al Cole Map

After that experience, and thanks to Pedro’s friendship with Carlos Galcerán, a Cuban GIS consultant working in New Zealand, we had the opportunity to put our brains working again for another pet project. The idea is easy, have you ever wondered who is on your antipodes? Yes, three quarters of our planet are oceans so the chance to have an inhabited antipodes is not high but here in Spain, it happens that half of the Iberian Peninsula is antipodal to New Zealand. Join that with the possibility to have data about schools on both countries and well, that’s reason enough for us to start playing. Imagine a geography class on primary school, say in the north coast of Galicia, where kids can contact their antipodal school in Christchurch and practice their English, or kiwis practicing their Spanish, both learning about our cultures, favorite sports, our differences.

We started with a dataset only for Galician schools and end up digging a national registry of schools to create a full dataset of schools for the country. That and the help of Carlos and some help from the Spanish Embassy in New Zealand, gave us all the data needed to set up two tables on CartoDB, so the last piece was just a web interface to develop. With the recent release of OpenLayers 3, and having played with it a bit before, I wanted to do something more complex. We’ll leave the technical details about data and software for another post or two. The application is available at

Antipodes Map

Antipodes Map

If you like the idea and know someone in New Zealand or Spain that could be interested, please spread the word. And of course, the data is available for reuse on CartoDB and the code is also on GitHub, ready to be reused on other lucky antipodal combinations, we’d love to see both data and software reused and improved!!

Cartografiando para Filipinas en OSM

Tras una semana frenética de actividad en OpenStreetMap para ayudar en todo lo posible en la crisis por el tifón Haiyan/Yolanda, acabar haciendo una sesión de formación y sobre todo construcción de comunidad en Valencia para mí ha sido toda una experiencia.

Por un lado la increíble respuesta de la comunidad OSM en general a esta crisis (ya casi llegamos a los mil colaboradores), con noticias cada día más esperanzadoras de la cantidad de información que se ha generado en tan poco tiempo, así como la actividad en la lista de correo del equipo HOT. Por otro lado artículos en medios de comunicación como The Atlantic y espectaculares visualizaciones como la del NY Times. Y finalmente la guinda con la sorpresa de obtener una buena aceptación por parte de los estudiantes de cartografía de Valencia para participar en la sesión.

De hecho se nos desbordó un poco el asunto y tuvimos que prometer que repetiremos la actividad la semana que viene en la universidad con ellos para que no vinieran todos hoy. Y menos mal porque hemos casi llenado las instalaciones que tan amablemente nos han cedido la gente de beCode. Creo que vamos a seguir contando con ellos para hacer cosas juntos, ya que casi sin conocernos nos han abierto sus puertas y nos ofrecido de forma desinteresada su local para que hagamos allí lo que se nos ocurra. ¿Mola no?

Al final hemos sido algo más de veinte personas, prácticamente todas noveles en OSM. Tras una charla ultra rápida sobre qué es OSM y qué íbamos a hacer hoy, la gente se ha puesto primero a pillarle en tranquillo a JOSM, y después ya a trabajar. Y la tarea no era para nada sencilla, ya que había que comparar datos anteriores al tifón (la imagen proporcionada por Bing) con la imagen del satélite Pleiades que Esri ha servido para que la comunidad pueda identificar los daños del tifón. Imágenes desplazadas, usuarios que no habían sido muy cuidadosos con la creación de cartografía y algunas nubes más o menos densas han sido los mayores problemas que ha tenido la gente para poder dar de alta nuevos edificios y carreteras y marcar aquellos que han quedado destruidos por el paso de Haiyan.

Como suele ser habitual en estas sesiones, han habido muchas dudas, algún problema técnico y unos cuantos despistes por mi parte pero en general la gente creo que ha entendido tanto la mecánica del trabajo en OSM, como la importancia del trabajo en estas situaciones de desastres medioambientales. Realmente sesiones como hoy hacen comunidad y me alegra ver que las nuevas generaciones de cartógrafos se interesan por estos temas, espero que cale el mensaje y desde Geoinquietos Valencia hayamos contribuido a aumentar la comunidad de OSM en nuestra ciudad.

A ver qué tal nos sale la semana que viene.


Herramientas Libres para trabajar con datos LiDAR

En los últimos años ha proliferado el uso del LiDAR como técnica topográfica. Básicamente, consiste en un telémetro láser que mide el tiempo que tarda una pulso láser en ir y volver después de haber rebotado en un objeto. De este modo consigue hallar la distancia entre el instrumento y el objeto. Es decir, es sencillamente un distanciómetro, pero con la particularidad de que puede llegar a medir unos 100000 puntos por segundo (100 MHz). Si, además, incorporamos a los equipos de medición un GPS que nos dé la posición y un sistema inercial que nos de la orientación si estamos en movimiento, podemos dar coordenadas globales, normalmente en el sistema WGS84, a todos los puntos medidos. Por tanto, tendremos lo que se denominan nubes de puntos de los cuales conoceremos su posición en un sistema de referencia global, además de otras características relativas al objeto, como la intensidad o diferentes ecos de retornos, o referentes a la medición como ángulo de emisión del pulso, tiempo o distancia relativa al sensor.

Las compañías que desarrollan instrumentación LiDAR, ya sean telémetros aerotransportados, dando lugar a lo que se denomina en inglés Airborne Laser Scanning (ALS), o telémetros terrestes, Terrestrial Laser Scanning (TLS), también desarrollan su propio software destinado a:

  1. Extraer los datos del equipo de medida y ofrecer los datos en algún
    formato propio o, al menos, conocido.
  2. En algunos casos, hacer post-proceso de dichos datos y obtener productos
    cartográficos finales.

Sin embargo, como en el ecosistema de los GIS, existe un software privativo que monopoliza casi todo el mercado del software para realizar los trabajos de post-proceso. No voy a dar el nombre de ninguno de estos paquetes porque el objetivo de esta entrada es justamente la contraria, exponer las posibilidades existentes para utilizar software libre a la hora de trabajar con datos LiDAR.

Lamentablemente, para extraer los datos de la mayoría de los equipos actuales no hay alternativas y necesitamos utilizar obligatoriamente software privativo. Comprar la licencia no es el verdadero problema, porque si tenemos dinero para comprar un equipo que cuesta varias decenas de miles de euros es que también podemos comprar al menos una licencia por un par de miles. Lo peor es lo que verdaderamente implica el software privativo, es decir, que no eres libre de hacer lo que quieras con producto adquirido. Sin embargo, si alguien quiere hacerse un telémetro láser, puede utilizar el módulo para la captura de datos de las librerías PCL, ya que soporta algunos dispositivos conocidos. Pero sobre la librería PCL y otras más hablaremos más datelladamente en otra entrada. Además, en la mayoría de los casos, cuando podamos acceder a datos LiDAR, estos estarán ya en un fomato conocido. Lo lógico es que obtengamos los datos en el formato LAS que es el formato estándar que define la ASPRS (American Society for Photogrammetry and Remote Sensing).

Hay varias librerías libres para la lectura y escritura de archivos en este formato LAS. La decana de ellas es LASlib. Está desarrollada y mantenida por Martin Isenburg y está escrita en C++. Está licenciada bajo LGPL, por lo que se puede utilizar en otros paquetes, aunque sean privativos. Al descargar estas librerías y compilarlas genera unas herramientas llamadas LAStools que sirven para la gestión de archivos LAS (las2las, lasmerge), para creación de LAS a partir de archivos de texto (txt2las) o archivos de texto a partir de LAS (las2txt), para dar información sobre archivos (lasinfo, lasprecision, lasdiff) o para crear un índice espacial de los puntos dentro de los archivos (lasindex). En las últimas versiones, también se crea la herramienta laszip que sirve para comprimir archivo LAS. El formato de salida es LAZ y el archivo de comprimido ocupa SOLO entre el 7% y 20% del tamaño del archivo original. A todo esto hay que añadir que con la librería LASlib también se distribuye otra librería para leer y escribir formatos LAZ, también en C++ y también con licencia LGPL. Para los usuarios de windows, además, están disponibles otras herramientas precompiladas, de las cuales no voy a dar detalles porque no son libres, sino que son shareware.

De la librería LASlib se hizo un fork y nació la librería libLAS, que está bajo el auspicio de OSGeo. También escrita en C++ pero incorporan bindings para una gran cantidad de lenguajes de programación. También incorpora herramientas para la gestión de archivos en formato LAS y texto, que se llaman de igual manera que las LAStools, aunque la utilización de los comandos pueda variar. Las diferencias que existen actualmente entre ambas librerías se pueden encontrar aquí.

La librería SPDLib es bastante reciente. Tanto es así que empezó a desarrollarse en el verano de 2011. El formato estándar que utiliza para trabajar con datos LiDAR se denomina precisamente SPD (Sorted Pulse Data) y está basado en el formato HDF. Es un formato de datos ordenados e indexado que está optimizado para el acceso rápido a los datos y en el que es posible trabajar con toda la señal del pulso de retorno, en inglés full waveform, no sólo con ecos discretos, como hace las librerías anteriores. Y precisamente ésta es una de sus grandes virtudes. Por lo demás, está en consonancia con las librerías ya vistas. Está desarrollada en C++, con bindings para python e IDL, y con licencia GPL. Al compilar se crean una serie de herramientas para la gestión, manipulación e información de archivos SPD, así como una herramienta imprescindible para transformar entre formatos LAS y SPD. Otra de las ventajas que presenta es que incorpora utilidades para generar modelos digitales en cualquier formato soportado por gdal, y para aplicaciones forestales. ¡Todas ellas libres! Sin embargo, al ser desarrollada principalmente por una sola persona y ser tan reciente, uno de los problemas que presenta es la escasa y, en algunos casos, inexacta documentación.

Sin duda, existen más librerías capaces de trabajar con datos LiDAR pero con otros propósitos distintos a los que hemos cubierto aquí. Las intentaremos tratar en otras entradas. Manteneos atentos.

PD: Mientras escribo estas líneas me llega un tuit en el que hablan de otra libreria
en python para leer y escribir datos LiDAR en formatos LAS. Se llaman laspy 🙂

¡Hazme un mapa de España! … ¡y bien rápido!

Ostras como se ponen los jefes de vez en cuando y claro, cuando vas y estiras del hilo te enteras que sobre el supuesto mapa de España van a pintar con chorricientos colores y necesitan que se vea bien a cualquier escala y que tenga el entramado de las calles, pero también salgan las provincias y puedan hacer zoom y pintar sobre las provincias… ¿no os suena?, jopé que suerte, a mi me pasa a menudo…

Así que en esta entrada vamos a intentar dar una solución planteando un mapa desaturado para poder pintar en colorines sobre él, el mapa tendrá orígenes de datos fáciles de conseguir – y libres – y utilizará un stack de software libre para construirlo… así que tendréis un mapa rápido [ish], fácil y bonito [ish]…

Continue reading

Extreme cartography

People are coming home after a great Spanish FOSS4G meeting. There will be time to write about the event, here or maybe on my Prodevelop blog, but before that, I really want to share with you this nice piece of art by RealIvanSanchez and Vehrka. Planet readers follow this link as Planet software eats videos for breakfast.

If you understand Spanish, you should wait for the publishing of meeting videos and see the complete presentation of Iván explaining what Extreme Cartography means to him.

What will GEOHULK think about this?