Dipslay graduated quantities from relate table?

AndrewFialka

Is there a way to display graduated quantity data from a 1:M relationship? I have an 1860 U.S. county map from NHGIS (attribute table below) and a table of mortality data from the same decade (second attribute table below). The mortality dataset describes different age groups who perished from different diseases in every 1860 U.S. county.

I would like to display each disease and age group by graduated colors for the entire U.S. county map. Is there a way to do this through a table relationship or does anyone have any tips for how I could reorganize these datasets to achieve my desired display? Thank you!

RichardFairhurst

This question comes up frequently and the answer is, Not easily.  Some people have success with putting the data in a single geodatabase and using the Make Query Table tool.  Personally, I have no use for that tool given it has consistently performed horribly and it does not support an outer join.

Since ArcGIS version 10.1, I prefer creating a 1:1 feature class by using a standard join and exporting the data (both the feature class and table should be in the same geodatabase to do this).  The export duplicates the polygons to match the record set in the table and also retains features that are unmatched by any table records (Make Query Table drops all features that have no match in the table, which is why it does not support an outer join).

Both approaches create overlapping polygons.  It is nearly impossible to display simultaneously all the categories in your table with overlapping polygons.  Hatched symbols are best, transparent symbols won't work at all unless you divide the categories into separate feature groups and even then the merging together of the transparent colors results in a meaningless legend.

Potentially you should use a pie graph for the groups if that fits your analysis needs.  However, for that you do not want to duplicate your County features, instead you need to create a pivot table of the data to change the many rows associated with each county into table columns of a single row per County that would match 1:1 with your feature class.  The Pivot Table tool can do this, but it takes about 5 steps to create the new table and a separate table has to be created for each attribute of the group sets.  So for each column of FREQ_1_M, etc. you would have to create a separate pivot table to get one row for each county and set of disease groups and have to combine all of the tables to do all of the age groups.

A fourth option is to write a custom cursor and dictionary routine to do the double pivot in one go.  You are doing a double pivot because you want to convert to columns all grouping on disease together with all groupings of age breaks for your incidents.  The technique is an extension of the process I describe in this blog.  However, although I have created 1:M dictionaries to do this, I did not really go into that option in the blog.  My best post on that subject is here, although rather than doing a pivot I did a duplication of features.  However the technique illustrated in my second link could be used to do a multi-group, multi-column values pivot in one pass with a bit of reworking.  Mainly rather than creating a new feature I would create a new column and have to map each pivoted value to the appropriate column.  It is an interesting enough task that I would be willing to help write the code for that if you are interested.  Let me know if that last option sounds interesting to you.

I don't know of any way to use such data to control color graduation across a polygon.  Additionally, gradient fills on each County polygon has no real relationship to the statistics you are dealing with, since the geographic sizes and population densities of each County can vary widely.  A larger band of color on a larger county could mean the same thing as a smaller band of color on a smaller county, which would not be apparent to the reader of your map and therefore would be misleading.  Unless you are correcting your statistical data to account for the relative population densities of each County, a Pie chart should give a much more standardize presentation of the relative sample sizes between the various Counties for comparison if all you are presenting is actual disease incident count data.