Author Archives: Jorge

About Jorge

I'm a geogeek living near Valencia, Spain. I work on geomatics and I try to have fun with technology and free software.

FOSS4G UK Online

Yesterday’s FOSS4G UK Online was the first fully virtual geospatial event I ever attended. Over the previous years I’ve joined remotely some FOSS4G and SoTM events, like last year excellent Bucharest 2019 FOSS4G. Having everyone joining remotely makes things quite different, since you don’t feel like a second class attendant. The OSGeo UK Local Chapter organized this fantastic conference in just a few weeks, gathering speakers around three different tracks of talks. There was even a fourth track simulating a coffee corner where you could join and have a chat maybe in the frequent breaks in between talks, what a brilliant idea!

I attended along with Jennifer Allen, the Kibana Maps team lead. We were the whole day grabbing notes around the same document, capturing links, resources, screenshots, and what not. This was way easier from the comfort of my workplace at home compared to the quick notes you can take when running from one session to the next. Surprisingly, I felt also I could get more focused during talks compared with being in a real conference venue; maybe because I didn’t had to worry about going from one room to another to find a good spot to see and listen well, or because I could simply disconnect and get a cup of tea anytime.

No doubt I missed the practical workshops, the hallway conversations, and the after event beers; but as for the way to consume the content, my awkward reflection, at least at this moment, is that I think I prefer a remote conference over in person one. Is it worth to make folks to travel all around the world for a quite exclusive exchange of knowledge? Aren’t we already used to collaborate and learn together at a global scale without the need to make such a big expense? What about of those that don’t feel welcomed at this kind of event for many different, and all legit, reasons? What about those that can’t afford them? I still find value on real gatherings, but maybe I find more bearable to do this once per year or even less, and by the way, totally separated from any holidays plan.

Regarding the content itself, as always there were quite a few great talks. National FOSS4G events tend to attract more use cases and consultancy projects than “core” product development. In this conference I liked the opportunity to learn about the last updates from Geoserver, and all the great work behind the Input App for data collection on the field that can be synchronized with QGIS projects using a Software as a Service called Mergin. Thinking strategically was a good talk with some reflections about the geo industry, and how small it is in the ocean of the data analytics world; the Q&A session afterwards was also quite interesting. The Women in Geospatial+ talk showed all the good work this global community is doing to promote diversity in our industry. Finally I wanted to mention the talk that showcased the OSM regions project, since it shares quite a lot of objectives, tools, and workflows with the stuff we are doing for the Elastic Maps Service. I’d love to put some time to go through the project details and eventually try to find collaboration opportunities.

And that’s it, I think I could have tried to have a talk on this event, and I feel a bit bad for not stepping forward back when the request for talks was launched but I’m sure I will have plenty of opportunities to present Elastic Geo features to the FOSS4G community in the future. As a closing call for action, take a look to the Twitter #FOSS4GUKOnline hashtag to find more opinions and resources, and more importantly, check the recordings when they get up in a couple weeks, you won’t regret it.

Happy Summer!!

Github Pages

Charla: introducción a Github Pages

Hace tiempo que tenía ganas de tratar este tema, llevo usando Jekyll desde hace ya muchos años, y desde que Github incluyó en su portfolio la publicación de sitios web no he vuelto a pensar en usar un CMS. Curiosamente este blog sigue en por razones casi históricas, pero es la excepción a los diferentes proyectos web en los que he participado (todos muy humildes) como son la web de geoinquietos,, o la web de la Geocamp de 2019.

El caso es que Jekyll y Github Pages forman un combo muy potente e increíblemente asequible si se sabe cómo empezar, y ahí es donde en esta charla queríamos entrar. La documentación ya está en español, y con unos pequeñas adiciones, se puede montar una web con minima, uno de los temas oficiales, en apenas unos clics.

Así que cuando Manel posteó en Twitter que tenía ganas de montar un blog, aproveché para liarle y montar una sesión de menos de una hora en la que hablamos sobre qué es Github Pages, cómo funciona, y cómo se puede usar directamente desde la web para publicar un blog muy sencillo.

La idea de la charla/taller, que dura menos de una hora, es apuntar a aquellos que tienen un cierto nivel técnico (¡no mucho!), y que no quieren depender de un CMS como WordPress o Medium para montar un sitio web con cero mantenimiento, que sea sencillo y que puede cubrir casos de uso típicos como montar una página web personal, o un blog.

Si la charla tiene buena acogida, podemos hacer una sesión ya en directo para resolver dudas o comentar temas adicionales como ir un poco más allá en el tema de editar contenido, activar comentarios, instalar un tema nuevo, etc. Quedamos atentos 😀.


PostGIS Day 2019

This year I was happy to attend the PostGIS Unconference organized by the good folks from Geodan and Webmapper. The idea for the event was to cover the middle ground between typical introductory activities and developer-oriented ones, targeting then mainly PostGIS power users.

The event was held at Science Park Amsterdam, hosted around 30 people and was organized in a very open way, meaning there’s an open Google Document for anyone to jump in, propose talks, express interests, etc. Super simple. The rest of the logistics of the event involved having coffee and lunch at the venue provided by Geodan, and some beers afterward sponsored by DAT.mobility.

The list of ideas exposed at the event was large, but at the end, only three “classic” talks were delivered and after lunch, the group split into different discussions/Birds of a feather to cover the following themes:

  • Serving vector tiles with PostGIS
  • Postal codes (focused in Netherlands case)
  • Cloud discussions
  • Large data storage and partitioning

Then we grouped back to provide some outcomes/conclusions and we head towards the pub (and myself to the airport back to Valencia).

The talks were related to the BoF sessions and covered the following topics:

  • Tom van Tilburg from Geodan introduced us to Clickhouse database, and how it can complement Postgres using its Foreign Data Wrapper to rely on this highly performant columnar storage for analyzing big volumes of data, basically to perform filtering and aggregation to send back to Postgres the results to be joined with master geometries and other indicators. The approach needs to craft carefully your queries to ensure you do all the processing in Clickhouse and return only the results. Anyway, I see more and more this database being mentioned as a good technological option to pay attention to.
  • Marco Slot from Microsoft exposed how Citus, an extension for enabling sharding for Postgres, can help scale the database to manage large datasets. It’s not focused on anything geospatial in particular, but he was looking for use cases where Citus can help to achieve good results. In fact at this moment apparently Citus does not support PostGIS aggregates but you can track this issue for future developments.
  • I presented the work that my colleague Yuri and I are doing to evolve the standard OpenMapTiles workflow from generating a static MBTiles asset to serve vector tiles into serving them directly from the database, leveraging the rather recent support for the MVT format just straight from PostGIS. You can find more details about the procedure on this document I used as a script for my talk and demos.

As a conclusion, it was nice to attend this unconference, to catch up with some friends from the area, and learn how relevant it is becoming for the Postgres community to respond to big data use cases.

Banner GEOCAMP 2019

Se abre la inscripción para GEOCAMP 2019 en Valencia

Se abre la inscripción a la GEOCAMP 2019 que se celebrará el próximo sábado 15 de junio en el Museu Comarcal de L’Horta Sud, Torrent (Valencia).  El aforo está limitado a 60 personas por lo que recomendamos la inscripción a la actividad lo antes posible.

Para los que no conozcáis este evento, se trata de una desconferencia (tipo Barcamp) donde todo el mundo está invitado a participar. No hay agenda, no hay oradores confirmados, no hay charlas patrocinadas, cualquier tema relacionado con las ciencias de la Tierra es bienvenido. Ven y cuéntanos lo que te apasiona y aprende de otras experiencias y proyectos.

Al comienzo de la jornada nos reuniremos y se recogerán propuestas de charlas o cualquier otra actividad de no más de 20 o 30 minutos. Una vez recopiladas las ordenamos de manera que tengan la mayor consistencia y procedemos con la jornada. Así de simple.


Además, este año se ha hecho coincidir este evento el mismo fin de semana que otro evento conocido como Geopaella, que se viene realizando desde Geoinquietos Valencia desde hace ya algunos años. Se trata de una reunión el mismo domingo 16 de junio para charlar de forma distendida sobre temas geoespaciales en buena compañía, con “auténtica” paella valenciana y en un espacio privado integrado en la huerta valenciana en la pedanía de Borbotó en l’Horta Nord (Descubre L´Horta). Los nuevos asistentes y los geoinquietos que repiten suelen valorar la clase magistral que da Fernando para saber preparar una buena paella con la materia prima recogida directamente de sus campos y en un entorno natural como la huerta, así como una gran sobremesa que se suele alargar hasta el atardecer. Hay más información y detalles para la inscripción en la página para la Geopaella 2019.

¡¡Esperamos vuestra asistencia y vuestras contribuciones al evento!!

Twitter: @geocampes ( #geocampes )

Mini speech #TopoGala

Ayer sin avisar me hicieron salir a decir unas palabras en la cena anual de la Escuela Técnica Superior de Ingeniería Cartográfica, Geodésica y Topográfica de la Universidad Politécnica de Valencia. Es una cena que organizan con ilusión los alumnos, a la que también acuden algunos profesores y personal no docente, y se invita a externos de la empresa privada. Esta fue mi tercera cena y se pueden ver fotos y burradas en #TopoGala.

El caso es que estaba cansado, y había bebido un poco, así que lo cierto es no dije nada especialmente interesante, y eso que ya en la cena del año pasado me quedé con un regusto raro, me explico.

El sector de la geomática y la ingeniería civil no está en su mejor momento; aunque parece que lo peor ya ha pasado sigue siendo difícil para los nuevos profesionales hacerse un hueco. En tiempos de guerra todo agujero es trinchera, así que andar soltando por el mundo a personas espabiladas, con idiomas, y con cierta capacidad para la responsabilidad no iba a ser ignorado por empresas de consultoría a las que, en fin, aparentemente no les es fácil fichar ingenieros en informática noveles. Es el mercado amigo.

Así que con ese contexto, estas serían las frases que les hubiera dicho a esos 100 o 120 estudiantes de grado y máster si me volvieran a dar la oportunidad.

Continue reading

12 years with you

This blog makes today 12 years old. Yes, we haven’t posted that much lately, indeed we are still around, busy with our projects, families, and lives. We keep sharing some content in our twitter and LinkedIn social networks and will maintain this place for sure. 12 years is already a lot, but our passion for geospatial content and sharing it is not less than when we started this space.

Leave us a comment if you remember a content we created that you liked, or whatever comes to your head!

We love you!!

Photo by Audrey Fretz on Unsplash

Curso GIS Cloud en UNIGIS

Por segundo año tutorizo el curso de GIS Cloud en el máster a distancia UNIGS. El curso se ofrece tanto como módulo para los alumnos del máster profesional en SIG como curso de especialización abierto a cualquiera que quiera profundizar en esta temática.

El curso se desarrolla entre el 14 de mayo y el 25 de junio y el período de inscripción acaba el 7 de mayo con un precio de 375 euros, bonificables a través de la Fundación Estatal para la Formación en el Empleo (anteriormente conocida como Fundación Tripartita).

El año pasado fue una experienca muy interesante. Como trabajador en remoto y además miembro de un equipo de soporte, la metodología didáctica de los cursos de UNIGS me resulta totalmente familiar. Desde sus casas o centros de trabajo los alumnos siguen los materiales a su ritmo y disponen de varios foros (en función del tipo de duda) para preguntar. Es muy interesante ver cómo poco a poco la gente se suelta y cómo acaban por ayudarse entre ellos. Para mí son unas semanas ajetreadas, de intentar resolver las dudas lo más rápido posible y a la vez dar la mejor información complementaria para que puedan aprender el máximo. A la vez algunos hasta descubren que se pueden incrustar gifs animados casi en cualquier lado 😄.

Personalmente creo que el curso me enriquece a mí tanto como a los alumnos, porque nada te da mejor perspectiva que el feedback que te devuelven aquellos que llegan por primera vez, con pocos prejuicios y diferenets backgrounds.

El temario es básicamente el mismo del año pasado con las inevitables actualizaciones de pantallas y pequeños detalles pero en general la estructura me parece muy balanceada para el tiempo del que disponen:

  • Una introducción genérica a qué son los servicios en la nube en general
  • Primera aproximación con Google Fusion Tables, un servicio ya veterano pero que sigue siendo muy válido para trabajar con datos sencillos
  • Luego seguimos con Mapbox, el mejor servicio (tanto entre los productos libres como privativos) para diseñar mapas vistosos y potencialmente complejos. Mapbox Studio es una herramienta que no te la acabas.
  • Acabamos con CARTO Builder, un completo SIG en la nube con el que hacer análisis y publicar cuadros de mando que van más allá de un simple mapa.


Usando CARTO Builder para ver datos del servicio municipal de alquiler bicicletas de Barcelona

En realidad como en cualquier curso de este tipo, lo importante es aprender a valorar las herramientas, encontrar sus puntos fuertes y sus puntos débiles así como desarrollar las capacidades para criticar cualquier otro servicio al que los alumnos se enfrenten en el futuro. Como decía un excompañero de trabajo, es esto mismo lo que nos hace tecnólogos en lugar de simples operadores de un software concreto.


Mapbox Studio es gloria bendita

Finalmente comentar que me encanta el flujo de trabajo que la gente del SIGTE lleva, usando para el control de la documentación repositorios git con tareas y pull requests asociadas y Sphinx para la edición de la documentación. Se nota que tienen mucha experiencia y saben lo que hacen.


Poder escribir en restructuredText y mantener un control de cambios “sano” no se paga con dinero

Pues nada, eso es todo, que si lo que he contado te parece interesante y te apetece dedicar unas semanas de esta primavera a aprender más sobre los SIG en la nube, nos vemos en el Moodle de UNIGIS 😉.

Geocamp ES 2017

Quedan poco más de dos semanas para la GeocampES 2017, que este año se va a celebrar el sábado 16 de Septiembre en Almería, organizada por Geoinquietos AlmeríaHacklab Almería y UNIA. Como cada año la idea es organizar un evento sencillo, divertido y muy técnico y participativo. No hay agenda, siguiendo el modelo de desconferencia habitual, al llegar allí se espera que un buen porcentaje de los asistentes se postulen para salir a contar algo: puede ser una charla convencional, una demostracíon de algo en lo que estás trabajando, una dinámica de grupo que te parezca interesante, etc. Cualquier actividad con una componente geo bien definida (o no tanto) es bienvenida.

La idea de Geocamp ES se tomó siguiendo el liderazgo de los compañeros de Portugal. A diferencia sus geocamps en pequeños pueblos más o menos alejados de todo, las anteriores Geocamp españolas se han celebrado en ciudades. En cualquier caso este año nos vamos a Almería, que está un poco más difícil de acceder (sin exagerar) pero sigue siendo una ciudad que seguro va a ofrecernos un espacio agradable y totalmente adecuado para pasarlo bien aprendiendo.

Si lo que te he contado te parece mínimamente interesante, anímate y prepara un fin de semana por el sur de España con el resto de la comunidad geoespacial.

Apúntate aquí.

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!