How the Twitter Template works

description.md

Step 1, provide a viz.json

This is the obvious step. My template then just grabs the viz.json and reads some basic information from it, including username and tablename.

Step 2, set the bounds

Okay, this was a bit of guessql. So, it creates the grid and measures all the points in each cell. It then selects the most dense cells that account for the first 60% of records. It then uses those to create the bounding box for the map :)

SELECT the_geom 
FROM 
  (SELECT 
    *, SUM(p) OVER (ORDER BY p DESC) aq  
   FROM 
    (SELECT 
      g as the_geom, count(*)::float/ct p 
     FROM 
      (SELECT 
        CDB_rectanglegrid((SELECT ST_SetSRID(ST_Extent(the_geom),4326) FROM "+table+"), 4,4) g
      ) a, 
      (SELECT count(*) ct FROM "+table+") b, 
      "+table+" t 
     WHERE 
      t.the_geom && a.g 
     GROUP BY a.g, b.ct 
     ORDER BY p DESC
    ) agg
   ) k 
WHERE aq < 0.6

Step 3. Figure out our Category Terms

I use this to display the X versus Y at the top. The whole template could have been expanded to handle more than 2, but for speed I just did the 2 category one.

select 
  distinct category_name, category_terms 
from 
  '+table+' 
ORDER BY 
  category_name ASC

Step 3. Hex map of top followed tweeters

This just creates a hex grid from the fly. It then does simple arithmatic,

• If the category_name is the first one, its value is -1
• If the category_name is the second one, its value is +1
• Add them all up.

This standardizes all cells in a nice way. All cells=0 are neutral, no winner. The more negative a cell, the more the first category dominates. The more positive a cell, the more the second category dominates.

SELECT 
  a.the_geom_webmercator, 
  sum(floor(t.category_name*1.5)-2) as leaning 
FROM 
  (SELECT CDB_HexagonGrid(ST_SetSRID((SELECT ST_Extent(the_geom_webmercator) FROM "+table+"),3857), 100000) as the_geom_webmercator) a, 
  "+table+" t 
WHERE 
  ST_Intersects(a.the_geom_webmercator, t.the_geom_webmercator) 
GROUP BY 
  a.the_geom_webmercator

Here is the style

#tablename{
  polygon-fill: blue;
  polygon-opacity: 0.3;
  line-color: black;
  line-width: 0.5;
  line-opacity: 1;
  [leaning < 0]{polygon-fill: magenta;}
  [leaning > 0]{polygon-fill: aqua;}
  [leaning < -5],[leaning > 5]{polygon-opacity: 0.4;}
  [leaning < -10],[leaning > 10]{polygon-opacity: 0.5;}
  [leaning < -15],[leaning > 15]{polygon-opacity: 0.6;}
  [leaning < -20],[leaning > 20]{polygon-opacity: 0.8;}
}

Step 4. Impactful tweets map

This one is super simple, it is just the tweets from people with most followers. I market tested it on a PR girl on a plane ride, she loved this one the most actually.

The SQL is simple, but the ORDER BY is important for later on the map,

SELECT * FROM "+table+" ORDER BY actor_followerscount DESC

The magic of this one comes in the CartoCSS. It uses the follower count as the label, so you see the nice big numbers. It also uses the follower count to change the size of the label. The text-allow-overlap: false is critical because now, with the ordered SQL, we know that our big guys get drawn first but the map doesn't over clutter. So now when you zoom in, you actually paint a new picture, seeing more and more fine scaled users tweeting things.

#tablename{
  text-name: [actor_followerscount];
  text-face-name: 'Lato Bold';
  text-size: 18;
  text-label-position-tolerance: 40;
  text-fill: #000;
  text-halo-fill: fadeout(black, 88%);
  text-halo-radius: 2.5;
  text-dy: 0;
  text-allow-overlap: false;
  text-placement: point;
  text-placement-type: simple;
  text-min-distance: 2;
  [category_name=1]{text-fill: magenta;}
  [category_name=2]{text-fill: aqua;}
  [category_name>2]{text-fill: yellow;}
  [actor_followerscount<10000]{text-size: 11; text-halo-radius: 0.1; text-opacity: 0.6;}
  [actor_followerscount<5000]{text-size: 8; text-halo-radius: 0.0;text-opacity: 0.4;}
  [actor_followerscount<1000]{text-size: 5; text-halo-radius: 0.0;text-opacity: 0.3;}
  [actor_followerscount<100][zoom<9]{text-size: 0;text-opacity: 0;}
}