## I’m Too Hexy For This Viz…

I meant to have an early night tonight but after reading Kristopher Erickson’s wonderful blog Native Hexbin support in Tableau 9 my head was filled with thoughts of hex!

As Kristopher writes, there are now two new formulae in Tableau 9 that enable hexbin support:

• HEXBINX(x,y)
• HEXBINY(x,y)

He has done a great job coming up with a way to scale the hexbin count. By default the hexbins are created with a spacing of 1.5° Longitude and 0.866° Latitude but with Kristopher’s scaling functions you can make as many as you want…

• Hex Lat = HEXBINY([Longitude]*[Sizing], [Latitude]*[Sizing])/[Sizing]
• Hex Lon = HEXBINX([Longitude]*[Sizing], [Latitude]*[Sizing])/[Sizing]

This is great as you can increase the density of the hexbins as you zoom in, but if you are using a shape marker it won’t zoom in conjunction with your map so you can end up with something that looks like this:

Or like this:

It got me wondering if we could come up with an adaptive solution using two of my favourite techniques… polygons and data densification. And the answer is (after some pain with the trigonometry), yes we can! Here is the result:

Hexbin Density 1:

Hexbin Density 2:

Hexbin Density 10 (zoomed in on Sydney):

As explained in a previous post, we need to make two passes over the data so we can have two points to interpolate between. In this case we need 6 points to plot the vertices around the hexbin, so my query looks like this:

I create a bin over the PointID field to drive the domain padding. And I have three table calculations (which I run over the bin field to make them densify):

• Angle = (1.047198 * INDEX())
• The constant value is 60° expressed in radians – i.e. π/3
• Plot Lat = WINDOW_AVG(AVG([Hex Lat])) + (1/[Hexbin Density])*SIN([Angle])
• Plot Lon = WINDOW_AVG(AVG([Hex Lon])) + (1/[Hexbin Density])*COS([Angle])

Here’s what the viz looks like – in the end I was surprised with how elegant and simple it turned out:

You can download the workbook from here. Now go forth, and be hexy!

Hi. I'm Alan. By day I manage the APAC sales engineering team for Snowflake Computing. By night, I'm a caped crusader. Or sleeping. Most often it's sleeping.
This entry was posted in Uncategorized. Bookmark the permalink.

### 13 Responses to I’m Too Hexy For This Viz…

1. Sweet, this method handles the Zoom and High Latitude problems very well. Thanks for sharing!

2. Mary S says:

The custom SQL is no longer necessary, you just need some field in your data you feel relatively confident will always have at least 2 values, like Customer ID:

Dummy Field: IF [Customer ID] = {MIN([Customer ID])} THEN 1 ELSE 6 END
Create bins on [Dummy Field] as before.

3. Anatoliy says:

I’m trying to recreate your hexbin approach on my own spreadsheets however I’ve come up against a problem that I can’t seem to fix that happens on the sample spreadsheet you posted as well as my own spreadsheets.

I’m able to set everything up however when trying to add the hexes to the map nothing gets plotted. When using your sample spreadsheet, if I remove Plot Lon and Plot Lat from the Columns and Rows respectively, then add them back, the map goes from showing hexes to not showing anything at all. Nothing else is modified or altered except for removing and then adding Plot Lon and Lat.

I also noticed that the hexbin density can’t be a value greater than 15 otherwise the shapes do not line up correctly any more. How can I increase the density so that I’m able to draw hexes down to individual addresses rather than multiple blocks as it is in your sample file. I tried modifying the values in the formulas to have more significant digits thinking that rounding errors where causing these problems with the hexes not lining up but wasn’t able to find anything.

Thanks

• Hi Anatoliy,

All I can think of is that you don’t have your table calculations working right. Make sure the compute using is set to use the PointID bin field for *all* the table calculations – the lat, the lon and the colouring field.

I was able to increase the hexbin density in my example so that it goes from 1 (i.e. 1 hex per degree) to 2000 (i.e. each hex is 1/2000th of a degree) this allows me to get pretty much to street address level. However if you are doing this why not just do a dot plot on the map? If you are devolving to individual points it will be much more efficient and yield the same result.

Hope this helps.

Cheers,
Alan

4. Karen Hinson says:

Just found this – it’s awesome!

5. cw226blog says:

I know this over a year later, but I’ve been unable to get MaryS’ dummy field mentioned above to work: when I do so (using the ID field in your workbook), the hexes are transformed into a short, single line at the southwest intersection of Adelaide and Brisbane. I presume it might be due to every point of data needing a duplicate with a pointID of 6, which can’t be done with the dummy field above?

Do you know of a way to achieve all this without doubling-up on the rows? I’m working with over 18 million rows of data and this is really taking a toll, even if I set my refreshes to incremental extracts.

6. Pingback: Custom Map – Data & Web