A series of unfortunate maps. Additional Info.

readme.md

A SERIES OF UNFORTUNATE MAPS

AND HOW TO FIX THEM WITH CARTO BUILDER

http://bit.ly/170601-carto-fixes

Slides

HOW TO VISUALIZE ALL NON-DOMINANT CATEGORIES FROM A 9M DATASET

v0.1 - Category Map
v0.2 - Dot Density Map
v1.0 - Category Map without ordering
v2.0 - Category Map with ordering and styling

Subsampling

   SELECT 
     t.* 
   FROM 
     table_name t tablesample system(30)

In addition, to subsample a big dataset, another good cartographic practice focused on performance is adding overviews.

*You can also use Subsample percent of rows analysis from CARTO BUILDER UI.

Ordering

1. Create a master table to order our categories based on the number of points.

WITH a AS (
	SELECT 
	  category, 
	  count(*) as num_points 
	FROM 
	  table_name 
	GROUP BY 1 
	ORDER BY 2 DESC)
SELECT 
  a.*, 
  row_number() OVER() as number 
FROM 
  a

2. Join both tables and order rows based on the number of points.

SELECT
  m.number,
  t.*
FROM 
  table_name t, master_table m
WHERE 
  t.category = b.category
ORDER BY 1 ASC

*You can also use Join columns from 2nd layer analysis from CARTO BUILDER UI.

Styling

@blg: #027091; // telefonica
@org: #cc751f; // orange
@red: #cc3333; // vodafone
@blu: #0353e7; // yoigo
@pur: #7400b7; // other

#layer {
  marker-fill: @pur;
  marker-fill-opacity: .1;
  marker-allow-overlap: true;
  marker-line-width: 0;
  marker-line-color: #FFF;
  marker-line-opacity: 1;
  
  [category = 'telefonica']{
    marker-fill: @blg;
    marker-fill-opacity: .7;
  }
  [category = 'orange']{
    marker-fill: @org;
    marker-fill-opacity: .2;
  }
  [category = 'vodafone']{
    marker-fill: @red;
    marker-fill-opacity: .2;
  }
  [category = 'yoigo']{
    marker-fill: @blu;
    marker-fill-opacity: .1;
  }
  [zoom>=7] {
    marker-width: .6;
  }
  [zoom>=11]{marker-width: .75;}
  [zoom>=12]{marker-width: 1.5;}
  [zoom>=13]{
    marker-width: 2.0;
    
    [category = 'telefonica']{
    	marker-fill-opacity: .6;
  	}
  }
  [zoom>=14]{marker-width: 2.5;}
  [zoom>=16]{  	
    [category = 'telefonica']{
    	marker-fill-opacity: .5;
  	}
  }
  
}

HOW TO SHOW MANY POINTS THAT SHARE THE SAME LOCATION

Stacking chips

The original technique was developped by Chris Whong as explained on this blog post, and then improved by Abel Vázquez.

WITH
m AS (
	SELECT 
	  count(*) n, 
	  array_agg(cartodb_id) id_list, 
	  the_geom_webmercator, 
	  ST_Y(the_geom_webmercator) y 
	FROM 
	  carto_employees 
	  GROUP BY 3 ORDER BY 4 DESC),
f AS (
	SELECT  
	  n, 
	  generate_series(1, array_length(id_list,1)) p, 
	  unnest(id_list) cartodb_id, 
	  the_geom_webmercator 
	FROM 
	  m)
SELECT
	t.the_geom_webmercator,
	t.the_geom,
	f.cartodb_id,
	f.p,
	t.id,
	t.workemail,
	t.displayname as name,
	t.gender,
	t.location,
	t.jobtitle,
	t.department,
	t.photourl
FROM 
	f, carto_employees t
WHERE 
	f.cartodb_id = t.cartodb_id

Map{
buffer-size:2048px;
}

#layer { 
  marker-fill: ramp([department], (#5F4690, #1D6996, #38A6A5, #0F8554, #73AF48, #EDAD08, #E17C05, #CC503E), ("Sales", "Engineering", "Marketing", "Operations", "Product", "Research & Data", "Finance", ), "=");
  marker-fill-opacity: 1;
  marker-line-color: #424141;
  marker-line-width: 0.6;
  marker-line-opacity: 1;
  marker-placement: point;
  marker-type: ellipse;
  marker-width: 10;
  marker-height: 8;
  marker-allow-overlap: true;
  marker-transform: translate(0, -2*([p]-1)); // this is the key line
  }

CARTO Employee Map using stacking chips method.

Aggregating points. JSON on SQL and loops on popups

Visit Geomaticblog post to read a full detailed tutorial.

CARTO Employee Map using JSON aggregation methods.

HOW TO SYMBOLIZE DIFFERENT ATTRIBUTES ON THE SAME LAYER GEOMETRY

You can learn more about data-driven cartography in CARTO following @mamataakella and reading CARTO blog posts.

Click on auto-style button on a category or histogram widget! ✨

US Counties Census Explorer
Drought Severity map