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:

030515_1412_ImTooHexyFo1.png

Or like this:

030515_1412_ImTooHexyFo2.png

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:

030515_1412_ImTooHexyFo3.png

Hexbin Density 2:

030515_1412_ImTooHexyFo4.png

Hexbin Density 10 (zoomed in on Sydney):

030515_1412_ImTooHexyFo5.png

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:

030515_1412_ImTooHexyFo6.png

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:

030515_1412_ImTooHexyFo7.png

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

About Alan Eldridge

Hi. I'm Alan. By day I manage the sales consulting team for Tableau Software in Australia/New Zealand. 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. Pingback: Even Hexier than Before… | The Last Data Bender

  6. Pingback: I’m Too Hexy For This Viz… | Tableau Love

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

  8. Pingback: Use Tableau to Create Census Tract Map – Data & Web

  9. Pingback: Custom Map – Data & Web

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s