2018

### Sarah Battersby's Blog

March 2018

Posted by Sarah Battersby Mar 24, 2018

This year I thought it would be fun to make some vizes to explore the NCAA March Madness tournament.  One of the things that I have long wondered about is if the teams with shorter travel distances or fewer time zone changes fare better in the tournament.

This year I finally took the time to collect up a bunch of data to help me explore this and started making some vizes to explore, but now I’m running out of time and am running up against the great obstacle that I really just don’t care enough about basketball to finish this out. My vizzing laziness is your gain in terms of data!

So, I’m handing over the data that I’ve collected so that people who do care, have time on their hands, have questions, and want to viz the bejeebers out of it to make something cool can have some fun.  The Excel file attached to this post has data through the results of the Sweet 16 (3/23/18).   The TWBX has assorted calculations for jittering and calculating distances in KM (if it's useful to you...but if not, just grab the data and have fun)!

This should be enough detail for all sorts of other mapping hijinks.

Here’s what I’ve got for you:

Team Data

• School
• Seed
• Conference
• Record
• Berth Type
• Overall Rank
• Tournament Region
• Time zone
• City
• Lat/Lon

Game Data

• All rounds of tournament
• All games so far + blanks to fill in for the remaining games (update the teams, scores, win/loss column, and details on opponent name / score)
• Unique game ID & Game Number (I created these for simplifying LOD calcs in Tableau, not an official ID of any sort)
• Round
• Scores
• Some random fields that I used for jittering points in Tableau (quicker than my fiddling with a table calc for this)
• Arena name
• Time zone
• City
• Lat/Lon

Good luck and good mapping!  Holler if you have questions about how I've put the data together... I think it's self explanatory, but that may just be because I put it all together

# Because sometimes you just want your map to shine…

Posted by Sarah Battersby Mar 12, 2018

The inspiration behind this project is largely from John Nelson’s firefly cartography blog posts and maps, and Jonni Walker's cool Fires on Federal Land map on Tableau Public.  The motivation to actually do it and write it up, however, was seeing Kent Marten eyes light up last week when I said the magic words: ‘firefly map.’

So…let me introduce a new Tableau point symbol set that I’ve created to make glow-y, firefly-esque maps easier to make in Tableau.

If you want to just jump to making some maps, go ahead and just grab the point symbol sets that you can drop straight into My Tableau Repository/Shapes.   There are two folders of data – Firefly and Firefly_Sparkle (think of these as the official Kent Marten Firefly Sparkles™ because Kent, and I’m sure others, deserve extra sparkly symbols for mapping).  These are not subtle mapping styles, so why not go the extra mile and add some weird sparkly bling?  And, actually, these might be pretty cool shapes to use for a solar system map...

If you want to know how I made these styles so that you can make your own or want edit the styles to meet your special mappy needs, read on.

What is a ‘firefly’ map?

The best, detailed, description comes from John Nelson’s blog post on the topic (Firefly Cartography), so I won't go into a lot of detail.  The short story is that for this type of map we want:

• Dark base map
• Bright, glow-y symbols

The contrasts between base map and the high-contrast, bright symbols draws the reader’s eye (because it looks really awesome)!

When is this type of symbol appropriate?

These symbols are more for display / communication than for data exploration. I  think of them mostly as eye catching bling to liven up your map.  They will make your dashboard look way hipper than you ever imagined.

How do you use these symbols in Tableau?

As of now we don’t have a way to add a transparent gradient colored halo around point symbols natively, so my work around was to create a set of point shapes in bright colors that can be used to symbolize your map data.

Because each of these are little .png graphics, you will use them on the shape shelf instead of the color shelf.

Once you download the zipped set of shapes that I have attached to this post (FireflyTableau.zip), unzip them, and move the folders of shapes in your Tableau Repository folder you should be good to go. Here is where you can find the folder on a Windows machine.  I have no clue how Macs work or where you’ll find the folder, but I’m sure a search can unearth it (or someone with a Mac will just send me a screen capture of where My Tableau Repository lives...):

Open up Tableau, drop some point data on your map.

Change the base map to the dark style and remove most of the layers – you want a nice, clean, dark base map with minimal clutter so your points can shine

Change the mark type for your map to ‘Shape’

Click on the Shape shelf to update the symbol used and in the Shape editor, select the folder with the symbols you want (Firefly or Firefly Sparkle):

Assign the symbols to your points.

Resize on the map until they look awesome.

If you didn't see the symbols showing up as an option, try hitting the Reload Shapes button.

If that doesn’t work, check that you dropped the folders in the right place in My Tableau Repository.

If that still doesn’t work, check if you have a My Tableau Repository (Beta) folder, and drop the shapes there…because you may be Beta testing and want the symbols to be usable in your Beta version.

Additional styling suggestion: Color + the glow-y symbols (and maybe Size if you want to get all super fancy).

You can also put color and/or size on top of the point symbols.  With color, the bright white center will be replaced with color, but it is still pretty cool.  Here is what they look like with the “Jewel Bright” colors assigned to the shapes:

How do you make your own?

Maybe these aren’t quite what you needed, so you want to fix up your own set of colors or shapes or change the gradient or….  I’ve attached my Adobe Illustrator file to use as a starter.  The way that I approached the problem is to pick some nice, bright colors and use a radial gradient with transparency to add a nice glow.

There are probably many other ways to tackle this problem, but hopefully this information has given you some good ideas on how to get started with Firefly-style mapping in Tableau.

# Dot Density Maps In Tableau (+ PostgreSQL)

Posted by Sarah Battersby Mar 7, 2018

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.

But, how do you make it in Tableau???

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,

st_x(pointDump),

st_y(pointDump)

from dots_temp

In non-SQL terms, this is what that clump of SQL does:

1. 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.
2. 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

By date: By tag: