Yearly Archives: 2014

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.

Advertisements

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?