Creating a custom polygon map for Connecticut towns in Tableau

This is the first in a series of posts on using Tableau Desktop or Tableau Desktop Public Edition to visualize Connecticut data with custom polygons, which let Tableau create filled maps for geographic entities not recognized innately in the software’s mapping functionality. See these other posts for more information on creating choropleth/filled maps for Connecticut Census Tracts and school districts in Tableau:

Tableau can instantly recognize and map the boundaries of many types of geographic entities (e.g. states, counties, countries) –  but for states like Connecticut with town-based governments, displaying town-level data on a map in Tableau isn’t quite as easy. Fortunately, a workaround exists that allows Tableau users of town-level data to create custom polygons maps to represent these areas. Tableau support documentation on this feature includes instructions on converting ArcGIS shape files into spreadsheet files that Tableau can use to construct custom polygon maps.

The Connecticut State Data Center has converted a number of U.S. Census Bureau TIGER/Line shape files into polygon data files for use in Tableau, including polygon  files for Connecticut towns, school district, Census tracts, and legislative district boundaries. If you would like to visualize Connecticut town-level data on a map (as in this dashboard) in Tableau, this Excel workbook of Connecticut town polygons:

CT_Town_Polygons_for_Tableau.xlsx

contains a Polygon spreadsheet that will render a map of Connecticut towns in Tableau, and an additional sheet of town tax mill rate data that you can link with the polygon map (following the steps below, to create a choropleth map like this (click image to see full size):

8

Each row of the Excel Polygon sheet describes a single point on the outline of a single Connecticut town’s boundaries, with a field for longitude and latitude of the point. Another field for each row is Point Order, which tells Tableau in which order to ‘connect the dots’ to form each town’s boundaries on the map. For example, Mansfield’s shape has 208 points in the data set. Tableau draws each polygon by starting with the latitude and longitude of point 1, then continues drawing the shape until it gets to point 208, completing the outline of the town. The software does this for all 16,000+ points on the map instantly, whether in Tableau Desktop or Public, and map tools seem to work as quickly with a polygon map published to Tableau Public as any boundaries innately recognized in Tableau. Amazing, and really useful, functionality!

Setting up the polygon map:

  1. Save a copy of the Excel workbook of Connecticut town polygons locally. In Tableau Public or Desktop, from the Data menu navigate to the workbook and drag the Polygons sheet into the data space. Click Go to Worksheet or open a new New Sheet on the task bar.1
  2. Under Measures in the Data pane, drag Longitude to the Columns shelf. Note that the Aggregation for this pill should be average; i.e. the pill should say AVG(Longitude). Aggregations can be changed if necessary (e.g. from Sum to Average) from the carrot menu for the measure in the rows or columns shelf.
  3. Drag Latitude to the Rows shelf. The aggregation should also be average (AVG) in the pill.
  4. In the Marks card, change the view type menu from Automatic to Polygon
  5. If Pointorder appears in the Measures pane, it must be converted to a Dimension – simply drag it from Measures to Dimensions. Then, drag Pointorder to Path on the Marks card. (Don’t panic, it is normal to see a strange Rorschach test-like image on the map at this stage! Until we tell Tableau that we want Town to be the level of detail, it’s plotting the average latitude/longitude of each Pointorder value for all the rows in the data set, resulting in a bizarre polygon approximately where Madison is).
  6. Drag Town from Dimensions and place it on Color on the Marks card. (Select Add all members in the dialog window).2You should now see the outlines of all 169 Connecticut towns (click the image above to see the full view) and you can now  link or join this base map to other data following steps in either Option 1 or Option 2 below.

Continue reading

Educational attainment and earnings in Connecticut towns

The latest data from the American Community Survey 2010-2014 Estimates shows a clear correlation between educational attainment and median earnings in Connecticut towns. This visualization includes data on educational attainment levels for all 169 towns for persons 25 and older, as well as median earnings for men and women of different education levels. Follow the links that appear in the mouseover tooltip to download the original tables from the Census Bureau’s American FactFinder data tool.

Census Tracts Data Browser updated with latest American Community Survey Data

The map below of Connecticut Census Tracts data provides links into the Census Bureau’s American FactFinder data engine to gain easy access to tables of economic, housing, demographics and other data from the 2010-14 American Community Survey 5-Year Estimates. Hover over any Census Tract on the map to see links to eight data tables for the tract. You can use the map tools to pan or zoom into a particular area of the state, and by holding down Control (Command on a Mac), you can select multiple tracts and follow the links to see data for all selected areas. See the Instructions tab for more information.

Linked data tables include:

  • DP05 ACS Demographic and Housing Estimates: age, race/ethnicity, and housing unit counts
  • S1501 Educational Attainment: educational attainment and median earnings by level of education for the population age 25 and over
  • S2701 Health Insurance Coverage Status: insurance coverage rates by age, race, and income
  • S1101 Households and Families: characteristics of household structures
  • S1702 Poverty Status in the Past 12 Months of Families: poverty status by age, race, educational attainment, and presence of children in the household
  • DP03 Selected Economic Characteristics: unemployment, occupation,  employment by industry, and income and benefits data
  • DP04 Selected Housing Characteristics:  size, value, age, and other characteristics of housing units in the tract
  • DP02 Selected Social Characteristics: includes marital status, fertility, place of birth, language spoken at home, ancestry, and disability status characteristics of the tract’s residents