Sevilla

III Edición Geocamp ES


A través de Geoinquietos Sevilla

El próximo 20 de junio se celebrará en Sevilla la tercera edición de la versión española de la Geocamp este año organizada por Geoinquietos Sevilla.

¿Qué es una Geocamp?

– Una desconferencia sobre temática GEO y software libre.
– Evento basado en el concepto de Barcamp y que se viene celebrando desde hace tiempo en Portugal.

El mundo de las tecnologías de la información geográfica (TIG) y el software libre está teniendo cada vez más peso en el mundo en que vivimos, tanto desde el punto de vista personal como profesional. Muestra de eso, en últimos años, se está generando un movimiento muy significativo formado por gente con grandes inquietudes sobre estos temas, a la par que están apareciendo cada vez más  profesionales del sector que participan en proyectos y jornadas de carácter nacional e internacional.

A partir de una de estas participaciones, concretamente en la Geocamp del año 2012 en Campo Maior (Portugal), nace la idea de crear un evento similar aquí en España, donde compartir experiencias y conocimientos sobre temas geo y software libre. Aquí está el germen de la Geocamp española.

Organizada por un grupo de Geoinquietos y apoyados por un conjunto de empresas e instituciones, la Geocamp es un evento con formato desconferenciaque intenta favorecer la participación y la colaboración, puesto que serán los propios participantes de la Geocamp los que conformen el programa de charlas el mismo día de su celebración, justo antes de comenzar. Por supuesto, está garantizada la participación de grandes nombres del mundo del software libre y de la geomática a nivel nacional e internacional.

La Geocamp 2015, que viene precedida de sus anteriores ediciones, realizadas en La Coruña y Vigo, respectivamente, se celebrará en el Parque del Alamillo, parque metropolitano que discurre entre el municipio de Santiponce y la propia ciudad de Sevilla y es de acceso libre (previo registro) y gratuito. Además, se ofrecerá a los asistentes coffe break y almuerzo para reponer fuerzas, regalos, etc., así como alguna sorpresa final. Todo esto gracias a los patrocinadores del evento.

Patrocinadores III Geocamp.es

Para estar atentos a todas las novedades sobre la Geocamp 2015 está disponible su portal web (http://geocamp.es/) y el blog de Geoinquietos (http://geoinquietos.blogspot.com.es/), así como sus perfiles de Twitter y Facebook.

Página de registro: http://geocampes2015.eventbrite.es/

¡Os esperamos en Sevilla!

Fuente de la foto original: https://flic.kr/p/ambxAZ

Dear OSM, just chill out.


This is kinda a reply to Gary Gale’s “Dear OSM, it’s time to get your finger out“. The more I read that, the less sense it makes to me.


Update 2015-04-30: Read also Richard Fairhurst’s Realpolitik and the OpenStreetMap License and Simon Poole’s Is OSM business unfriendly?

I think of myself as a Linux nerd. I consider myself a hacker. And I’ve spoken so many times about open/libre licensing in conferences the issue became boring.

A couple of years ago, a psychologist asked me a question as part of a job interview: What makes you angry?. And my response was clear: Things not working and logical fallacies. So my brain hurt a little when I read these particular sentences in Gary’s blog post:

There are really only three sources of global mapping […]: NAVTEQ, TeleAtlas , and OpenStreetMap. […]

Surely now is the moment for OpenStreetMap to accelerate adoption, usage and uptake? But why hasn’t this already happened?

See, a tiny part of my brain screams “fallacy“. «OpenStreetMap has things in common with NAVTEQ and TeleAtlas, ergo it has to play in the same field and by the same rules as NAVTEQ and TeleAtlas».

Maybe OSM was given birth by SteveC to cover the lack of affordable datasources, and then a way for him and his VC-fueled CloudMade to compete with them. But for me and a whole lot of nerds, OSM basically was, and still is, a playground where we can run database queries all night long. OSM is a wholly different animal.

In 2010 I argued that Geo businesses and Geo hackers are playing the same game, but with different goals, which makes for an interesting game; a game in which it would be foolish to think that the opponent will play for the same goal as you. You have to play this game to maximize your score, which is not a synonim of decreasing the opponent’s score.

In other words: when I put something into OSM, I don’t frakking care what happens to NAVTEQ or TeleAtlas. The same way when I cook something for friends I don’t care what happens to the local pizza joint.

See, maps are an infrastructure. In Spanish GIS circles, specially those around the Spanish NSDI, cartography is often called “the infrastructure of infrastructures” You need maps to plan roads, power lines, land zoning. You need stupidly accurate maps to collect taxes based on how many centimeters square your house has, or to give out grants based on exactly how many olive trees you own.

During the late 2000’s I heard a lot of criticism about OSM. But most of it didn’t come from data-collecting companies – it came from public servants. “Volunteers use cheap GPS with low accuracy, OSM will never be accurate”, they said. “The OSM data model cannot be loaded into ArcGIS and won’t be of any use”, they said. “OSM data will never be authoritative”, they said. And a few years later, this happened:

That, right there, is the Guardia Civil (who are public servants) in a freakin’ control room using OSM for freakin’ pan-european coastal border patrols. Which means OSM is a freakin’ de facto infrastructure for sovereignty.

Fact is, government agencies play a wholly different game than commercial providers. The goal of a govt’ agency (and specifically those which maintain infrastructures) is to provide a service to citizens, not to make money. As a thought exercise, think about the public servants who place the border milestones, or the ones checking road surface quality, and guess how many fucks they give about NAVTEQ or TeleAtlas.

OSM is about the ownership of infrastructure. It’s about the efficiency of copyright law. It’s all about the digital commons, dammit.

And this is where I kick a wasps’ nest by turning this post into a political one.

A capitalistic liberal will argue that infrastructure is better handled by competing private entities, renting it to citizens for a fee. But remember, I’m a Spaniard, and that means that I’ve seen infrastructures such as power lines, water companies, telcos, motorways, airports and banks privatized under the excuse of theoretically running more efficiently in private hands. We have a nice word for what happened in the real world: “expolio“, which english-speakers might translate as “plunder”.

Thanks but no, thanks. I want infrastructures to be as close to the commons as possible. Maybe it’s because I now live in the land of the dugnad, and my bias makes me see maintaining the commons as beneficial for the society as a whole.

So when you look at OSM (or at the Wikipedia, for that matter) you’re really looking at a digital common good. Which puts OSM in the same basket as other digital common goods, such as programming languages, the radioelectric spectrum, technical RFCs, or state-owned cartography. Now go read the tragedy of the digital commons.

It’s sad to live in a world where the money-making is held above many commons, even at the expense of the commons. Fortunately it’s difficult for a private entity to pollute air, water or the radioelectrical spectrum without going unnoticed, but unfortunately copyright law cares next to nothing about intellectual commons.

<rant>Please, someone explain to me how giving me intellectual ownership of something I thought about until 70 years after my death makes me think about better things; then explain to me how that reverts into the common good. </rant>


TL;DR: Dear OpenStreetMap: just chill out and don’t listen to what they say. Corporations may come and go, but a common infrastructure such as you is here to stay.

Mapping Party en Poio, Pontevedra


El sábado 11 de abril en el Centro Xaime Illa de Raxó (Poio) llega la fiesta de las fiestas, la Mapping Party Poio’15 con el objetivo de pasarlo bien mientras aumentamos los datos geográficos de la costa de Poio con una licencia libre.

http://www.openstreetmap.org/export/embed.html?bbox=-8.757382929325104%2C42.403051783861024%2C-8.752903640270233%2C42.40485013856018&layer=mapnik&marker=42.4039509676542%2C-8.755143284797668

Este taller está organizado por la asociación de software libre Xeopesca y cuenta con la colaboración del Concello de Poio y las asociaciones SCD Raxó y ACDeM Armadiña.

Horario

  • 10:00-11:00 Presentación de OSM, organización de equipos para la zona a cartografiar.
  • 11:00-14:00 Trabajo de campo con explicaciones de como emplear osmAndroid.
  • 14:00 -16:00 Comida.
  • 16:00-20:00 Trabajar con las computadoras para el volcado de los datos OSM
  • 20:00-20:30 Clausura del curso.

Asistentes

El número de asistentes será de 25. La selección de los candidatos se realizará por orden de inscripción. Se recomienda la disposición de cualquiera de los siguientes dispositivos:  GPS, teléfono con GPS y cámara digital.

Formulario de Inscripción

Para inscribirse a Mapping Party Poio’15 cubre el formulario. (ver aquí) (poio.xeopesca.gal) .

Material fungible

Se hará entrega a cada uno de los asistentes de un bloc de notas, un bolígrafo y un lápiz.

Redes SociaLes

Establecemos el hashtag #mappingpartypoio para seguir el evento a través de las redes sociales. Además también puedes seguir la  Mapping Party Poio’15 a través de twitter mediante el hashtag #mappingpartypoio o en la  página de facebook de XeoPesca.

 

SITIO WEB OFICIAL

http://poio.xeopesca.gal/

 

EMT Madrid, or Open Data antipatterns


Today, february 21st 2015, is the Open Data Day. And given that I’m far asay from my favourite Open Data nerds down at the Medialab Prado, I decided to work on giving the old ¿Cuánto Tarda Mi Autobús? website a facelift.

The story behind ¿Cuánto tarda mi autobús? is rather simple. A couple of years ago I got my first smartphone, and one of the things I wanted to do is check for the bus times in Madrid. Back then, EMT Madrid (the public company which runs the buses) was heavily promoting its new website in the Spanish GIS scene. The major problem was that the website for checking the times was made with Flash (actually, with ESRI Flex) and simply there is no way to use that with a smartphone.

So I reverse-engineered the protocol (if you call “reading a WSDL document” reverse engineering), did a bit of PHP plus Leaflet, and I was able to check the bus times with a web app.


 

Fast-forward to the present. EMT had released the API details under the banner of «Open Data EMT», I had a saturday to devote to the Open Data Day, and my little web app needed some love after two years of no updates at all.

But, oh boy, every time I try to develop anything with interfaces made by big subcontractors, I cannot stop cringing at the amount of WTF found around.

The antipatterns

Antipattern 1: «Open Data» which isn’t actual Open Data.

In the same way that Open Source software can only be Open Source if it meets the Open Source Definition, Open Data is only Open Data if it meets the Open Definition. Period. These definitions are evolved versions of the DFSG and Free Software Definition, curated with years of experience and discussions about what is open and what is not.

So, the Open Definition states:

2.1.8 Application to Any Purpose

The license must allow use, redistribution, modification, and compilation for any purpose. The license must not restrict anyone from making use of the work in a specific field of endeavor.

From «OpenData EMT»’s terms and conditions:

1. The re-user agent is explicitly prohibited from distorting the nature of the information, and is obliged to:
a. Not to manipulate nor falsify the information.
b. Ensure that any information displayed in your system is always up to date.
c. Not to use the information to undermine or damage EMT’s public image.
d. Not to use the information in sites that might lead to a relation with illegal acts or attempts to sabotage EMT or any other entity, organization or person.

So apparently I cannot:

  • Display historical information (because my data must be up-to-date).
  • Say that the system is a complete piece of steaming crap from a technological point of view.
  • Use the information in sites such as Facebook or Twitter, because those sites might be used for an attempted sabotage to «any entity or person». Who the fuck wrote this blanket clause, folks?

Please, don’t call it «Open Data».

I have to give praise to the EMT, though. The previous version of the agreement obliged the reuser to not disclose that he/she signed an open data agreement. At least they fixed that.

Antipattern 2: Your SOAP examples contain raw XML.

The whole point of SOAP was to abstract data access. And, still, every public SOAP interface I’ve ever seen includes examples with raw XML fragments that you’re supposed to build up.

If you cannot write code that access SOAP without writing XML, you’re doing it wrong.

Think about how WMS interfaces work nowadays: you just publish the WMS endpoint, and your GIS software takes care of the capabilities and the

Antipattern 3: Keep default fake values in your production code.

From the docs:

tempuri

Note «tempuri.org». A quick search will tell you that the system was designed using Visual Studio, and some lazy so-called software engineer didn’t bother to change the defaults.

Antipattern 4: Fuck up your coordinate systems

Note to non-spaniard readers: The city of Madrid is located roughly at latitude 40.38 north, longitude 3.71 west.

Now look at this example from the EMT docs about how bus coordinates are returned:

positionbus

Antipattern 5: Mix up your coordinate systems

Write things like “UTM” and “geodetic” in your documentation, but do not write which UTM strip you’re referring to (hint: it’s 30 north, and the SRS is EPSG:23030). Make some of your API methods to return coordinates in EPSG:23030 and some others to return coordinates in EPSG:4326.

And for extra fun, have your input coordinate fields accept both of those SRSs as strings with comma-defined decimal point, and then do this in the documentation:

coordinateint

Antipattern 6: Self-signed SSL certificates

Srsly?

sslcert

Antipattern 7: Wrap everything up in HTTP + JSON and call it “REST”

REST is a beautiful technology because it builds up pretty much on top of raw HTTP. Every object (“resource”) has its own URI (“universal resource identifier”), and the HTTP verbs are used semantically (GET actually gets information, POST modifies information, DELETE deletes a resource, and so on).

Also, HTTP status codes are used for the return status. An HTTP status code 200 means that the data is OK, a 404 means that the resource doesn’t exist, a 401 means that you are not authorized to get/post/delete the resource. Reusing the semantics of HTTP is way cool.

So, in a REST interface for bus stops, the stop number 1234 (a resource) would be located at its URI, e.g. http://whatever/stops/1234. It’s beautiful because the URI has a meaning, and the HTTP verb GET (which is the default when a web browser is fetching something) has a meaning. The server would answer with a “200 OK” and then the resource.

Low-level, it should look like:

GET /stops/1234 HTTP/1.1

-----

HTTP/1.1 200 OK
Content-Type: text/JSON

{
"stopId": 1234, 
"latitude": 40.3,
"longitude": -3.71,
"stopName": "Lorep Ipsum Street",
"lines": ["12", "34"]
}

Now compare the theoretical RESTful way to fetch one bus stop with the real-life mess:

POST /emt-proxy-server/last/bus/GetNodesLines.php HTTP/1.1
idClient=user&passKey=12345678-1234-1234-12345678&Nodes=1234

-----

HTTP/1.1 200 OK
Content-Type: text/JSON

{
"resultCode":0,
"resultDescription":"Resultado de la operacion Correcta",
"resultValues":{
  "node":1234,
  "name":"ROBERTO DOMINGO-AV.DONOSTIARRA",
  "lines":["21\/1"],
  "latitude":40.434861209797,
  "longitude":-3.6608600156554
  }
}

So, meaningless URI, POST request to fetch a resource, duplicated return status codes (for the HTTP layer and some other underlying layer).

Let me say this very, very clearly: In REST, you do not wrap a resource in a call to get that resource. Geez.

My takeaway

Readers will do good to keep in mind that I’m using my spare time in order to participate in the Open Data Day, for fun. The problem is that working with such an arquitecture is no fun at all. I can deal with these kind of enterprisey, Dilbertesque software stacks in a day-to-day basis, but only if I’m getting paid to endure the cringing and teeth-grinding.

I think it’s because the mind of an Open Source / Open Data nerd like me is difficult to understand by the classic propietary software people. I develop software just for the fun of doing so, just because I can, just because I like the feeling of empowerment of creating my own tools.

I write a lot of open source stuff. I fix wikipedia from time to time, I map stuff on OpenStreetMap if something’s wrong. I like to do it, and I empower other people to build upon my work.

Building upon good Open Source/Data doesn’t feel like standing on the shoulders of giants. It feels like standing on the soulders of a mountain of midgets… and if you’re lucky, you’ll be one of those midgets and someone will stand upon your shoulders.

For me, that involves a great deal of humility. When I watch the closed-source crowd talk about their latest product or software, they are congratulating themselves, but when I watch the open-source crowd, they’re constantly critizising themselves. And I can critizise them and they can critizise me and we all learn and that’s why they’re awesome.

</rant>

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 http://antipodes.decasaalcole.com.

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!!

The Null Island Algorithm


We geomaticians like to gather around a mythical place called Null Island. This island has everything: airports, train stations, hotels, postcodes, all kinds of shops, a huge lot of geocoded addresses, and whatever geographical feature ends up with null coordinates due to whatever buggy geoprocessing pipeline and ends up in the (0,0) coordinates.

But earlier this year, some geonerds such as @mizmay and @schuyler realized that there is no one Null Island, but one Null Island per datum / coordinate system (depending on who you ask). And @smathermather had the spare time to find out how the “Null Archipielago” looks like:

(Null archipielago image by @smathermather, containing Map tiles by Stamen Design, under CC BY 3.0. Data by OpenStreetMap, under CC BY SA)

Fast forward a few months. I received an e-mail from one of my university peers, asking for help with a puzzle:

A friend of mine received a puzzle with some coordinates. He has to find a place on earth represented by 861126.41, 941711.64.

It’s supposed to be a populated place. Any ideas?

Well, off the top of my head, those looked slightly like UTM coordinates – two digits after the decimal point, suggesting centimeter precision… but the easting is way off the valid range for UTM coordinates.

And I realized this is the Null Archipielago problem, all over again; but instead of plotting (0,0) on a map, let’s plot all points having (861126.41, 941711.64) coordinates in any reference system.

Cue PostGIS. We can create a point in every CRS like so:

select srid, ST_GeomFromText('POINT(861126.41 941711.64)',srid) as geom
 from spatial_ref_sys;

Note the complete absence of PL/SQL in there.

But it will be much easier to work with the data if all the points are in our beloved EPSG:4326 latitude-longitude coordinate system. And while we’re at it, let’s materialize that data into a table:

select srid, ST_Transform(ST_GeomFromText('POINT(861126.41 941711.64)',srid),4326) as geom
 from spatial_ref_sys;

But there is a problem with this – the PostGIS query will crash due to some CRSs having an empty Proj4 string. This took me a while to trace and fix:

select srid, ST_Transform(ST_GeomFromText('POINT(861126.41 941711.64)',srid),4326) as geom
 from spatial_ref_sys where proj4text!='';

And now we can take this data out into a file… but once again, there’s a catch: some of the coordinates are out of bounds and represented by (∞, ∞) coordinate pair. Even though file formats can handle ∞/-∞ values (good thing we know how IEEE floating point format works, right folks?), some mapping software can not accommodate for these values. And I’m looking at you, CartoDB upload page.

In this particular case, there are only points for (∞, ∞) so the data can be cleaned up in just one pass:

delete from archipielago where ST_X(geom)>180;

Then just add a tiny bit of CartoDB magic, and publish a map:

nullarchipielago

https://ivansanchez.cartodb.com/viz/1ac4a786-805a-11e4-bc48-0e853d047bba/public_map

I still don’t know if the original puzzle has anything to do with any obscure used-in-the-real-world CRS, but at least it’s worth a try.

Bicipaseo 21Sep

Mapping for the busy cartographer: today moving dots


This article describes how to make a quick map using some nice services we have at our hands. Nowadays almost everyone can create a maps using services like CartoDB, Mapbox, uMap or even Google My Maps. In this case I’ll show how I used the incredible flexibility of CartoDB to combine some Postgres/PostGIS SQL with CartoCSS to animate some dots on top of OSM cartography rendered by Mapbox.

This combination is really unique and convenient, other services only allow you to upload or draw some features and decide some static styling for them. But with this combination, using old SQL you can adapt your data for different uses, with CartoCSS the power of the Mapnik rendering library is available and finally, using the awesome Torque capabilities, animation can be added to our map.

About

The idea of this map is to represent a crowd of cyclists running along the future bike line by the interior ring of the city of Valencia. Tomorrow Sunday 21 September there will be a march to show the interest of city bikers for this line so my idea was to make people think about how the city look like with this (still imaginary) bike lane full of cyclists, instead of cars.

Data preparation

  1. Trace a line that represents the route
  2. Transform the line to EPSG:3857
  3. Make the line denser, placing points every 25 meters using the «Densify geometries given an interval» QGIS processing tool
  4. Convert the line to points (again with Processing) and give them these properties:
    • route it will serve to produce more routes in the future
    • lap to separate the points of the route of other points of interest outside the route
    • id to order the rendering of the points

Visualization

After uploading the dataset to my CartoDB account I’ve created a new visualization that will have these layers:

  1. A blurred line with the route
  2. A point marking the meeting place to start the activity, just in front of the city hall.
  3. The animated points moving over the route

Line

Load the layer paseo and customise the SQL. The SQL is quite self-explanatory, first we filter the points over the line and then we use the ST_MakeLine aggregated function to rebuild our original line.

WITH route AS (
  SELECT *
  FROM paseo
  WHERE route = 1 AND lap>0
  ORDER BY id)
SELECT
  1 cartodb_id,
  ST_MakeLine(the_geom_webmercator) as the_geom_webmercator
FROM route
GROUP BY lap

The styling of this layer is a simple CartoCSS rule with the only trick of a heavy blur filter.

#paseo[cartodb_id=1]{
    line-color: #A53ED5;
    line-width: 8;
    line-opacity: 0.7;
    line-comp-op: lighten;
    image-filters: agg-stack-blur(10,10);
}

Moving dots

This is the most important part of the map, of course. I have a path of points ordered and what I want is to show a more or less crowded ring of people moving. To do it, I’ve created a UNION of ten SELECTs to the table offsetting the id over the full range of id’s. To acieve that I’ve used this long SQL:

WITH route AS (
    SELECT * FROM paseo WHERE lap>0 AND route = 1
),
laps AS (
    SELECT
        cartodb_id, the_geom_webmercator,
        id
    FROM route r1
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 25 THEN id - 25 ELSE id - 25 + 254 END id
    FROM route r2
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 50 THEN id - 50 ELSE id - 50 + 254 END id
    FROM route r3
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 75 THEN id - 75 ELSE id - 75 + 254 END id
    FROM route r4
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 100 THEN id - 100 ELSE id - 100 + 254 END id
    FROM route r5
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 125 THEN id - 125 ELSE id - 125 + 254 END id
    FROM route r6
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 150 THEN id - 150 ELSE id - 150 + 254 END id
    FROM route r7
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 175 THEN id - 175 ELSE id - 175 + 254 END id
    FROM route r8
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 200 THEN id - 200 ELSE id - 200 + 254 END id
    FROM route r9
    UNION SELECT
        cartodb_id, the_geom_webmercator,
        CASE WHEN id  > 225 THEN id - 225 ELSE id - 225 + 254 END id
    FROM route r10
)
SELECT
    cartodb_id, the_geom_webmercator,
    ((random()*10-10) + id) id
FROM laps

The first with subquery filters the points of the path for this route that feed the next subquery: 10 unions with an id offset separation of 25 points. This subquery is passed to the main query that finally randomizes the id by +-5 positions, that is the order, so the moving dots are not regular, giving a more interesting (anarchic?) effect.

Using the wizard, the main aspects of the Torque animation are set up. It’s important to use a proper resolution, duration and frame count to adjust the rendering to a nice motion. Afterwards some last touches to the CSS to adjust the compositing operation and specially the trails, leaving just one more rendering of a similar point, instead of the default bigger and more transparent feature.

Map {
-torque-frame-count:64;
-torque-animation-duration:30;
-torque-time-attribute:"id";
-torque-aggregation-function:"count(cartodb_id)";
-torque-resolution:4;
-torque-data-aggregation:linear;
}

#paseo{
  comp-op: minus;
  marker-fill-opacity: 1;
  marker-line-color: #FFFFFF;
  marker-line-width: 0.5;
  marker-line-opacity: 1;
  marker-type: ellipse;
  marker-width: 6;
  marker-fill: #41006D;
}
#paseo[frame-offset=2] {
 marker-width:6;
}

Meeting point

To add a feature to the map to render the meeting point, I manually added a new feature to the layer using the CartoDB editor. This feature will have the property lap=0 so it won’t be on the other layers. The SQL for this layer is just a

SELECT * FROM paseo WHERE route = 1 and lap = 0

And the CartoCSS is quite simple with the only important trick to use an external SVG. I’ve used directly the town-hall marker from the Mapbox Maki repository.

#paseo{
  marker-fill-opacity: 0.9;
  marker-line-color: #FFF;
  marker-line-width: 1.5;
  marker-line-opacity: 1;
  marker-placement: point;
  marker-type: ellipse;
  marker-width: 40;
  marker-fill: #3B007F;
  marker-allow-overlap: true;

  marker-file: url(https://raw.githubusercontent.com/mapbox/maki/mb-pages/src/town-hall-24.svg);

Fixed info window

On this layer I’ve also configured an infowindow so when you click on the town hall icon you get some data about the schedule for the event.

Base map

I started using the Nokia day grey base map offered by CartoDB, but after a couple of iterations on the design, I thought it could be great to use a pale purple base map so I went to Mapbox web and quickly crafted a variation of their Mapbox Streets base layer.

Other components

Finally, using the new nice CartoDB layout capabilities I’ve added a simple title for the mobile version of the rendering and a couple of texts and an image (uploaded to imgur) for the logo of the group promoting this activity.

Conclusion

Well that’s all. You can check the visualization here. The job took like 4 to 5 hours. I finished the first animated version in 2/3 hours but you know, devil is in details and designing is always about iterations and refinement. Anyway I’m quite satisfied on the result and I think it serves for its purpose. Definitely I’ll have the opportunity to review and refine this process, as I imagine more routes and bike marches will happen in Valencia where bikers are winning the battle :-)

What do you think about this visualization. What do you like and what do you hate? Improvements? I’d love to hear your thoughts and comments to make better maps.

Update: almost same effect without crazy UNION

This morning Pedro-Juan asked my, why so many UNIONs? why not using just one long CASE?. After accepting the challenge I did something with CASEs but then realized that I wast just looping over a smaller set of id values, so I could use the modulo function. So the long UNION SQL could be reduced to this easy and simple SQL:

SELECT
    cartodb_id, the_geom_webmercator, 
    ((random()*10-10) + id%3) id
FROM paseo WHERE lap>0 AND route = 1

Wow, that’s so concise compared with the huge SQL above!! Using this id%3 I forced all the values to be just 1,2,3 but with the afterwards random the moving effect is achieved.

The CartoCSS would need also some changes to allow to “fill” the rendering over all the animation time. Check the differences with the above code, specially the number of offsets added:

Map {
-torque-frame-count:50;
-torque-animation-duration:8;
-torque-time-attribute:"id";
-torque-aggregation-function:"count(cartodb_id)";
-torque-resolution:2;
-torque-data-aggregation:linear;
}

#paseo{
  comp-op: minus;
  marker-fill-opacity: 1;
  marker-line-color: #FFFFFF;
  marker-line-width: 0.5;
  marker-line-opacity: 1;
  marker-type: ellipse;
  marker-width: 6;
  marker-fill: #41006D;
}
#paseo[frame-offset=4] {}
#paseo[frame-offset=8] {}
#paseo[frame-offset=12] {}
#paseo[frame-offset=14] {}
#paseo[frame-offset=16] {}
#paseo[frame-offset=18] {}
#paseo[frame-offset=20] {}
#paseo[frame-offset=22] {}

The resultant visualization can be accessed here. Which one do you like more? Do you think it’s worth the simplicity over the (in my opinion) slightly worse effect?