This is the obvious step. My template then just grabs the viz.json and reads some basic information from it, including username and tablename.
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
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
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;}
}
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;}
}