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!
The Last Data Bender 
Sweet, this method handles the Zoom and High Latitude problems very well. Thanks for sharing!
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.
Great suggestion! Thanks, Mary.
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
Just found this – it’s awesome!
Thanks, Karen. Glad you liked it.
Pingback: Even Hexier than Before… | The Last Data Bender
Pingback: I’m Too Hexy For This Viz… | Tableau Love
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.
Hi – great question, and it made me go back and really review Mary’s suggestion. I’m sad to admit I hadn’t followed her logic all the way through before, but I’m glad I have now. So glad in fact, that I wrote a blog explaining it.
https://blog.databender.net/2016/05/10/you-can-never-be-too-hexy/
Hopefully this will help answer your question.
Pingback: Use Tableau to Create Census Tract Map – Data & Web
Pingback: Custom Map – Data & Web