Recently Lilach Manheim asked me whether it was possible to make dot density maps in Tableau. My initial thought was ‘that would be cool’ and that it shouldn’t be difficult to do with some pre-processing of the data outside of Tableau. But, too much pre-processing isn’t fun; it would be way better to be able to have a dot density map that could be adjusted dynamically from inside Tableau. That sounded harder. Hmm…
So I went out for a walk, let my mind wander, and figured out that maybe I could do it with some spatial calculations in PostgresSQL… After wrapping up a few more miles of thinking and then hustling home for some relaxing Tableau mapping fun, this is what I’ve learned: dot density maps are totally do-able, and can be totally awesome! I’m going to do the full-on professorial ramble about this and go through some background on the maps to start out, but if you want to jump straight to the how to do this in Tableau part, go ahead and skip ahead.
What is a dot density map?
Dot density (or ‘dot map’) is a mapping technique where each mark serves as a proxy location for more than one discrete item (e.g., one mark equals 10,000 people). This type of dot maps is created through random or semi-random placement of a pre-determined number of points across a polygon region. The exact location where a point mark is placed on the map is not indicative of the true location of where the phenomena is located (e.g., there are not really 10,000 people right here in this exact location), it’s just an approximation. However, since point locations are within the polygon in which they were originally counted, they end up placed in locations where the phenomenon is fairly likely to occur.
Dot density maps are great when you want to show a more realistic pattern of where some discrete phenomena (something like people or cows or cars) that is countable within a polygon region is located and how it changes smoothly across space (e.g., not constrained to polygonal boundaries like it would be in a choropleth map).
Dot density of population (left) vs Choropleth of population count (right):
How do you make one (in general)?
At least in a GIS, creating a dot density map starts with a polygon dataset having counts assigned to each region (e.g., number of people per Census block).
Then you figure out how you want to scale the count so that you know how many points are needed inside the polygon. For instance, for a map with 1 point per 100 people, a polygon with 1,228 people in it would have 12 points (or maybe 13, depending on which way you round).
Randomly place the appropriate number of points (after scaling based on the dot value) inside each polygon. And now you have the basis for your dot map.
Dot density maps aren’t a standard feature of mapping in Tableau, but if you can get your data into a spatial database like PostgresSQL with PostGIS it’s a really simple process. The example I’m going to give uses a block-level population dataset that I downloaded from the US Census.
Here is what the table looks like in Postgres:
In Tableau I connect up to my Postgres database:
Then I drop in some custom SQL in the data pane.
To make the dot density map I need to do a few calculations in my SQL query. They key functions are:
- ST_GeneratePoints – this creates a multipoint geometry with a specified number of randomly placed points within a polygon geometry. We’ll use this to randomly place the dots for our dot map in each polygon.
Note that ST_GeneratePoints is only available starting in PostGIS version 2.2
- ST_Dump – this takes our multipoint geometry from ST_GeneratePoints and turns it into a separate row for each point. If one polygon returns a geometry with 12 points in it, ST_Dump turns that into 12 rows with one point in each row.
- ST_X and ST_Y – Converts our points from ST_Dump into separate columns of latitude and longitude. Note that it is important to make sure that your dataset is in a coordinate system where you’ll end up with latitude and longitude as the resulting coordinates. My US Census dataset was in NAD83 (EPSG: 4269), but any geographic coordinate system where the coordinates are latitude and longitude would work (e.g., WGS84 (EPSG: 4326), etc.).
Putting all of these together, my final query in the Tableau custom SQL ends up looking like this:
Or if you want to just copy/paste and edit for your own file... I've put the Tableau Parameters in bold you can both create these (and insert them) using the 'Insert Parameter' button on the bottom of the Edit Custom SQL window. The dotCount parameter should be a whole number (but not 0!) and the minDots can be any positive number.
with dots_temp as (
select statefp10, countyfp10, tractce10, blockid10, pop10,
(st_dump(st_generatepoints(block.geom, round(block.pop10/ <Parameters.dotCount>)::numeric))).geom as pointDump
from tabblock2010_45_pophu as block
where block.pop10/<Parameters.dotCount> >= <Parameters.minDots>
select statefp10, countyfp10, tractce10, blockid10, pop10,
In non-SQL terms, this is what that clump of SQL does:
- Make a temporary table (cleverly named dots_temp) with some identifying attributes (statefp10, countyfp10, etc.) AND a fancy spatial calculation
- ST_GeneratePoints gives us a set of randomly placed points inside each Census block. The number of points is equal to the Population attribute (pop10) divided by a parameter that I set up in Tableau. This makes the calculation dynamic so that I can adjust how many people each dot is equal to.
- ST_Dump takes the multi-point geometry returned from ST_GeneratePoints and makes each point a separate row in the table. I need this so that I can draw the individual points in my viz.
- Take the dots_temp table and give me all of my good identifying attributes (statefp10, etc.) and give me the X and Y coordinate for each point in the dots_temp table. Dots_temp had the points as geometries and I just want the plain lat/lon coordinates – because Tableau can map those directly.
Note that I’m using a few parameters in that query…they’re part of the secret sauce here. I find it pretty much impossible to figure out a good value for each dot – should one dot be equal to 100? 200? 10,000?, so I’m using a parameter (dotCount) that lets me dynamically adjust the value for each dot until my map looks juuuuuust right.
I also use a parameter called “minDots” to restrict the minimum number of points that you want to draw per polygon (sometimes you don’t want every polygon to get a point…). It’s easier to see that impact with a map of the US, so I’m going to jump datasets quickly to a dot density map created with county-level population data. Compare this to the second image where the counties with small populations don’t have any marks – the minDot parameter is set so that you have to have at least a certain number of people before a mark is drawn. I think the second map is more effective.
Using these two parameters in Tableau on my worksheet allows me to adjust the map on the fly until it looks good.
The last step in making a dot density map is to consider a bit of fun symbolization to spice up the map. With my population map, I added a bit of color and transparency to emphasize the pattern. The densest places are highlighted because enough points overlap that they are still nice and visible with the transparency…but the singletons in lower density areas fade into the background a bit more.
And that’s all there is to it! I’m looking forward to seeing your uses for this – send your best Tableau dot maps over to me @mapsOverlord