A Useful USA County Shapefile For #Tableau and #Alteryx

featured


Introduction

Fate is a funny thing. Just days after publishing this article on a Zip Code shapefile I had created, I had the need for a US county coverage shapefile just like the one I had built for the Zip Codes. This is what happens when Big Magic makes its presence known in your life.

For this reason, I created a USA County shapefile that can also be used in Tableau, Alteryx or other programs that read shapefiles. This article is simply meant to share the files I used and created and to document the original data sources.

I cannot promise that the final product is of perfect quality, but I suspect it is pretty good considering the US Census Bureau is the data source. I blended a couple of other data sources together to add information to the county polygons, so I hope my blending is all complete. I include the data sources I used to help anyone track issues that they may discover with this file.

Here is one tip for you when using this shapefile. If you plan on working at the state level and are filtering on county, be sure to place the State in the context in Tableau so that you get only the county for the state you are working within. If you don’t do this, you will go batty wondering why Tableau won’t properly sort the counties on some measure you are using. This occurs because county names are not unique and can occur in more than one state.

Background

Now that Tableau uses shapefiles so brilliantly, I decided to use Alteryx to process the 1:500,000 county shapefile that I downloaded from here to add some additional information.

The reason I did this has to do with the utility of such a file. The raw county shapefile is empty by itself. It only has polygonal boundaries for each county boundary. That might be helpful for geospatial operations, but it doesn’t help us much when doing BI studies. The primary weakness of that file is that there is no state information in the file. I did the data joining operation in Alteryx to make a much more useful shapefile.

The resulting Zip_Code_Polygons in shapefile format should be useful for a lot of purposes. As shown in Figure 1, I had Alteryx add some goodies like county areas, perimeter lengths, centroids, etc. I also added fields that might be useful like State abbreviations, capitals, etc.

Alteryx_Workflow

Figure 1- The Alteryx data joining operation used to create the county shapefile. The original files can also be downloaded in this article.


The Counties Rendered in Tableau

US_Counties (you must rename the *.zip to *.twbx) of this county information. The shapefile has been imported and an extract was created for this data.

Figures 2 through 6 are some renderings of this data at the national and state level, as well as showing a potential (anonymous) usage of this type of data.

All_USA_Counties

Figure 2 – All USA counties.


US_Counties_State

Figure 3 – Lower 48 states and their counties.


CA_Counties

Figure 4 – California counties.


IL Counties

Figure 5 – Illinois counties.


 

Blank Db

Figure 6 – Using county level data to visualize details within any county in California. This was generated in Tableau version 10.1, so it doesn’t take advantage of the shapefile capabilities currently available in 10.2 and beyond.


Final Thoughts

As I discussed in this article about shapefiles in Tableau, having a good library of commonly used geographical coverages is going to become more important over time. The geographical possibilities for us in Tableau are exploding as the developers add new shapefile features. I hope to find some time to continue to create additional coverages like these for usage in Alteryx and Tableau.

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