Creating a custom polygon map for Connecticut towns 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 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.

Linking the custom polygon map to town-level data

Option 1 – connecting separately to the data source

  1. From the Data menu, New Data Source
  2. Navigate again to the  to the CT_Town_Polygons_for_Tableau.xlsx workbook. Drag the Sample data sheet into the data area. Then return to the sheet on the taskbar in Tableau where you created the map.
  3. Because the Sample data sheet has a Town column, Tableau should indicate that the new data sheet has been joined to the Polygon sheet by the common Town field (with the chain icon as in the Dimension pane below). If your data source has a variation on this field label (e.g. County Subdivision instead of Town)  from the Data menu go to Edit Relationships and add a custom join based on the common fields)3
  4.   In the map above, Town is on Color on the Marks card. We want to create a choropleth map based on the Millrate measure, but we want to keep Town in the visualization, letting Tableau that town is the unit of analysis or “level of detail”. Move Town from Color onto Detail on the Marks card. You should see a the outline of the state but the state is changed to a solid color.4(If you like, you can show the borders of the towns as in the image above by selecting a Border color from the Color menu on the Marks card: Click Color, then under Effects change Border from Automatic to a color. This step isn’t necessary, but illustrates that Tableau really is showing the individual towns at this point)
  5. Now drag the measure you want to show on the map, e.g. Millrate if you’re using the Sample data, to Color on the Marks card.

5

 

Option 2 – join the polygon sheet with the data sheet

Joining the Polygon sheet with the data sheet is what Tableau recommends in its support pages and may improve performance with some data sets. (It may not be the best option if your data sheet includes data for multiple years for each town.)

  1. Drag down on the Data menu, and from the carrot menu for the Polygons (CT_Town_Polygons_for_Tableau) data source select Edit Data Source. Drag the Sample data sheet into the data workspace.6
  2. Normally you will want to join the Polygon sheet with your data sheet using a Left join. Click on the venn diagram in the data workspace to bring up the Join dialog box. Click on the image illustrating Left join. The Polygon Data Source sheet should be joined to the Sample data sheet with the join clause Town = Town (Sample data):7

 

 

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

Demographic and Economic Profiles of Connecticut’s Electorate

In advance of the Connecticut primary on April 26, the U.S. Census Bureau presents a variety of statistics that give an overall profile of the state’s voting-age population and industries. Statistics include:

Electorate Profile: Connecticut

 

Per-pupil expenditures by school district, fiscal year 2013

This visualization uses data from the 2013 Annual Survey of School System Finances which provides per-pupil expenditures for various instructional and support services functions for over 13,000 school districts, including salaries, wages, and benefits of instructional staff, as well as administrative, instructional staff support, and other support service costs.

(Note that total per-student expenditures include expenses not included separately in Instruction and Support Services costs (see footnote [1] in original American FactFinder table).

 

Per-pupil spending of public elementary and secondary school systems: Fiscal year 2013

More great data sets seem to be added to the U.S. Census Bureau’s  American FactFinder data engine all the time. This visualization uses data from the 2013 Annual Survey of School System Finances: Per pupil amounts for current spending of public elementary-secondary school systems by state , allowing comparison of expenditures on  instruction and support services among states.

Increases in income inequality within states, 2007-2014

According to the the latest American Community Survey data, the period from 2007 to 2014 saw a statistically significant increase in income inequality – represented by the Gini index – in 32 states. The Gini Index represents the concentration of income in a given state or country, in a range from 0 to 1. A higher Gini index indicates greater inequality – where income is concentrated among a relatively few individuals or households; a lower Gini score represents more even income distribution.

The ACS 1-Year reports the Gini index for households in table B19083 as the middle point of a 90% confidence interval, along with a corresponding margin of error. The visualization below calculates significant change in Gini index scores from 2007-2014 – shifts outside the margin of error. In 32 states, the lower end of the range of the 2014 Gini estimate was greater than the upper range of the 2009 Gini Index estimate. In the remaining states, there was no statistically significant change from 2009 to 2014: in these cases, taking into account the margin of error, the estimates from 2009 overlapped with those from 2014.

Gini index is a commonly used economic measure, reported by organizations such as the World Bank and CIA, in its World Factbook.

WMS Server Outage – 1/27/2016

MAGIC_WMSThe Web Mapping Service (WMS) for the Connecticut State Data Center and MAGIC, a service that provides access to the 1934 aerial photography layer and historical maps for use within GIS applications and is utilized within a number of the interactive map mash-ups for MAGIC will need to be offline part of today (1/27/2016) to resolve connectivity issues with the server. This server experienced an unplanned outage and we are working to resolve this issue ASAP.

During this WMS server update period there may be periods of time when the WMS service could be temporarily unavailable or load times for layers may be impacted.

This maintenance will identify and address additional performance issues with the WMS server and we apologize for the inconvenience any short duration outages of the server may cause.

For users needing access to aerial photography layers via a WMS, the Connecticut Environmental Conditions Online (CT ECO) site offers a WMS with several aerial photography layers which can be accessed at: http://cteco.uconn.edu/map_services.htm

Posted in GIS