A Giant's Causeway of Data

Create the Grid

We need to create a grid over the ne_50m_land geometries. Then, intersect it with the actual geometries to get an 'adaptative' grid.

WITH grid AS(
SELECT 
  CDB_HexagonGrid(the_geom_webmercator,150000) AS the_geom_webmercator 
FROM ne_50m_land
  )

SELECT 
  grid.the_geom_webmercator
FROM
  grid, 
  ne_50m_land l
WHERE 
  ST_Intersects(grid.the_geom_webmercator, l.the_geom_webmercator)
GROUP BY
  grid.the_geom_webmercator

Create dataset from query and rename to world_hex_grid

Count the number of cities per grid

SELECT
  a.cartodb_id,
  a.the_geom_webmercator,
  a.the_geom,
  counts.number_cities,
  counts.sum_pop,
  counts.names
FROM
  world_hex_grid a
CROSS JOIN LATERAL
  (
    SELECT
      count(*) as number_cities,
      sum(pop_max) as sum_pop,
      string_agg(b.name, ',') as names
    FROM
      populated_places b
    WHERE
      ST_Intersects(a.the_geom, b.the_geom)
  ) AS counts
WHERE ST_XMax(the_geom) - ST_XMin(the_geom) < 180 
ORDER BY ST_Y(ST_Centroid(a.the_geom)) DESC

We have aggregated the names of the cities included in each grid cell, apart from the number of aggregated cities and the sum of the population in those cities.

CROSS JOIN LATERAL does all of it in a quick and efficient way.

Note we have also filtered out the hexagons that crossed the date line, so they do not get an incorrect data aggregation.

The query results should be ordered by descending latitude, in order to be correctly rendered

CartoCSS

To assign an height to each hexagon cell, we need to use the building CartoCSS rules:

• building-fill for the color
• building-fill-opacity for the opacity level
• building-height for the height of the extruded polygon. We used an expression to get an adequate height for each. Each hexagon aggregates the population of the same area, so just dividing by 10 to get height in pixels is enough to have a meaningful visualization.

Find these and other CartoCSS properties in our complete documentation.

We used the awesome ColorScales tool to find an appropriate color scale for our data. The number of cities per hexagon is the variable we used to generate the choropleth visualization.

/** Log shifted to POI scale */

@color0: #045275;
@color1: #00718b;
@color2: #089099;
@color3: #46aea0;
@color4: #7ccba2;
@color5: #b7e6a5;
@color6: #f7feae;

#world_hex_grid{
building-fill-opacity: 0.9;
building-fill: @color0;
building-height:[sum_pop]/10;

	[number_cities>0]{
		building-fill: @color0;
       }
	[number_cities>0]{
		building-fill: @color1;
       }
	[number_cities>0]{
		building-fill: @color2;
       }
	[number_cities>0]{
		building-fill: @color3;
       }
	[number_cities>1.4367954709954522]{
		building-fill: @color4;
       }
	[number_cities>3.4618449501357844]{
		building-fill: @color5;
       }
	[number_cities>6.923689900271569]{
		building-fill: @color6;
       }
}