Hex Grids into Tableau?

Afternoon All,

So, I have some data (zip codes) which I have been able to give Long/Lat to, from which I have created points in a field called Centroid. I have then used the magic "Make Hex Grid" macro from here and viola, it has created a grid of Hexs (I created a single layer, 3 miles each)

But here is the problem, the data that comes out of above Macro is just labelled as "GridName" and "Grid".

How on earth do i link this back to the original data? When looking at examples for Tableau, it suggests linking the data by noting matching fields but the above are new fields.

Many thanks!

Tableau

Accepted answers

All comments

CharlieS

Hi @Bobbins

That's a cool macro that Alex built a while back, basically a "hexbin" conversion process for the input. When you say "link back to the original data", what are you looking to do? I'm not quite sure what you did in your first few steps of ZIP data to centroids to grids. Could you share or show what you did there?

FYI the term "grid" comes up because that hex macro uses the Make Grid tool to divide up the area before reshaping each object from grids to hex-shaped. The "GridName" is a unique name for each hex and the "Grid" is actually the hex spatial object (use a Browse tool to view spatial objects). The macro was designed to take a polygon and break it down into hex areas like this (a ZIP I clipped to the polygon boundary)

Maybe you have a centroid to represent a ZIP code, then created a single grid for that ZIP to build some sort of relational map? A Spatial Match or Find Nearest might be involved to related the hexes back to the ZIP origins, but let's keep this conversation going.

Bobbins

HI @CharlieS ,

Thank you for your reply. I have taken "snips" of the process. If you want data, I may need to generate some duff data to explain the problem.

Okay in this flow, I am taking my data (cake sales per zip code) and then giving each zip code an Long and Lat from a large database of zip code data.

Getting Hexagonal Grid Data.PNG

I then follow a process of creating a "centroid" (see next "flow"). I followed a guide which told me to produce a poly build and then a grid which gives me a grid and also for each data point, tells me which grid this belongs too. However its a grid map and I want a hex map (just to be difficult!)

So into flow 2, I take the Centroid data and plug that into the "Make a grid". This gives me a hexagonal grid, but literally gives me a grid number and "grid". (I have noticed this grid goes down rather then across). But there is no way to tie this information back to the original data,

So what am i missing or doing wrong?

Thank you for your assistance.

CharlieS

Thanks for that information. I've got a few more follow up questions.

1. ZIP centroids: So if you have multiple records at a ZIP, then append lat/long info to those records, wouldn't all the points for the ZIP be the same?

2. Poly Build: What are you grouping by when you use this tool? If you group by ZIP, then from the question above, aren't all the points the same so you would just have the same point? if not grouping, then it's all the ZIPs you're studying?

- Please place a browse tool after the Poly Build tool and take a screenshot of what the output looks like on a map.

3. The hex macro does not work with centroids/point spatial objects, it will just return the same point back. The hex macro is supposed to have a polygon input and it will generate hexes over that area (like you see in my post above)

Bobbins

Hi @CharlieS ,

For the purpose of building a hex map, I have already made sure i have unique zip codes only with the idea being I can then go back in tableau and apply the multiple zip codes to the hex which will hold that zip code.

Your point 3 request is shown below.

However, i took on board this and just saved this data and put this into the hex macro. I now have some success as seen below, but I have a (few) new questions

1) How do you refer to that hex with the original zip data, especially in Tableau. It appears the macro just gives it a grid name, but that grid name does not exist in the original data nor after the poly.

2) If i turn off clip then i get a square oh hexagonals, but is there any way to remove hexs not in use (ie no data for them), or is that done in Tableau?

Apologies for these questions and thank you in advance.

CharlieS

Nice images! Don't forget to set a base map layer for some visual reference. At the top where it says "[No Base Map}", drop that down and select something like "Alteryx Light" so you can see what this looks like on a map.

Next, I would recommend performing all these functions in the same workflow (instead of a separate one for the hex process) because we will more than likely need the original data to associate hexes back to ZIP data.

We're going to associate the hexes to ZIPs in Alteryx (before you take it to Tableau), but we still need to sort out a few things first. Before we get caught up on individual procedures, could you help us understand what you would like the output to look like?

1 hex per ZIP?

Each ZIP area filled with hexes?

A canvas of hexes assigned to the nearest ZIP?

We can help make these happen as long as we know what the goal is.

Bobbins

HI @CharlieS,

Thank you for your continued guidance with this and I am sure others will be using this as path forward in their maps!. I chose not to include the map in the screenshot for obvious reasons but yes, it looks ace with the background map included.

So the intention is that one zip = one hexagonal because some zip codes cover large areas and some are quite small but i wish to remove the "Alaska" effect and in addition, I do not have a greater detail then a 5 digit zip code so having multiple hexes in a zip wouldn't be any use but I if using the nearest hex to zip would be possible, that would be great. If i need not to use the city below, then that's not a problem but I am aware that some parts of the city there is no cakes sold so no data is present for that zip.

Hope that helps?

CharlieS

Hey @Bobbins. Sorry it's taken so long to follow up; it's been a very busy week for me.

Let's talk through this problem first, then I'll suggest a different route.

To solve this problem we'll need to generate grids over the area (we've already gotten this far), and then we can use a Find Nearest tool with the grids and the original points to find the grid nearest to each point. The problem that you'll run into with this process is scaling the size of the grids (or hexes). ZIP codes have a wide range of area covered, which means using one grid size is going to cause problems. Either the grids will be small enough to make sure only one ZIP is in each once, but will leave lots of uncovered area, or the grids will be so large that multiple ZIPs are covered, forcing you lose the "ZIP" layer and this turns into a report of sales by grids.

I've put together and example workflow to show this with some example ZIPs in the Fort Worth area:

Could you dynamically scale the grid sizes to fit population density and data availability? Sure, but I think there's a better use of our time. How about we use a unique polygon for each ZIP? The Census Bureau publishes ZCTA boundaries available for download here: (no cost)
https://www.census.gov/cgi-bin/geo/shapefiles/index.php?year=2021&layergroup=ZIP+Code+Tabulation+Areas

That's a link to the FTP to download them in Shapefile format, so unzip that file and drop the .shp file into an Input tool. Now you can use the ZIP code to Join to your original data and you'll have a unique polygon to use. There's a LOT more to mention about ZIPs vs ZCTAs and there might still be some discrepancies (like you have a ZIP codes not in the file or vise versa), but that's just the nature of ZIP codes.

More information about ZCTAs:
https://www.census.gov/programs-surveys/geography/guidance/geo-areas/zctas.html

I thought this might be a better way to visualize the data in Alteryx, Tableau, or any other format. Check it out and let me know what you think.

20211118-ZIPGrids.yxmd

Quick Links

This months top contributors

mceleavey 383

AkimasaKajitani 382

mbarone 337

Hollingsworth 335

LanisC 335