Purpose: This post is intended to emphasize and explain the necessity of having a unique identifier (attribute) for each feature in a given map layer, especially if you intend to establish data connections between that map layer and an external data source (e.g. an Excel spreadsheet). Likewise, that unique identifier value must be present in any external data table to which you want to establish a data connection with that map layer.
1-to-1 Linkage - First, it must be understood that for any data connection between a map layer and an external data table, there must be a one-to-one relationship between the map layer and the external data table…that is, for each record (row) in the table, there should be (at most) only one corresponding feature on the map, and vice versa. That said, in order to be able to ‘match up’ the correct map feature with its corresponding row of data in the data table, there must be some unique identifier that ‘links’ the two. That is where the unique identifier comes into play.
‘FIPS’ ID - In the U.S., we have a system referred to as ‘FIPS’…that is, every administrative, geographic entity (state, county, census tract, etc) has a unique FIPS ID. All of the ‘political boundary’ map layers that we have available (in our various data sets) for the US (be they a state layer, county layer, census boundary layer, etc) will have this FIPS ID as one of the attributes. This is handy for data connections.
How would FIPS be used? - Say you have a table of county-level data for the US, most likely (depending on the source of the table), one of the fields in that table is going to be a ‘FIPS’ field, containing the unique FIPS number for each county (US counties have a 5-digit FIPS number, which includes a leading-zero in some cases). You would use that ‘FIPS’ field in the data table and the ‘FIPS’ attribute in a US county map layer in AWhere, to establish a linkage/data connection between the table and the map (using the Data Connection tool...click here to read more about that.)
This sample image below shows southern Florida, with a selection of counties each labeled with its unique, 5-digit FIPS code (labeled and colored here for visual reference only…you are not required to have the map layer labeled with its FIPS code or colored in order to establish a data connection). Also, next to the map is (a portion of) a table of external data that we want to connect to the county map layer. Note that the table has one record (row) of data per county (identified by the county’s FIPS code). The red arrows simply demonstrate that each record in the table is dynamically ‘connected to’ (or, ‘associated with’) its corresponding map feature in AWhere.
With a data connection now established between the map and the external data table, you could now display the counties using, for example, a color gradient, based upon the ‘Per Capita Income’ values in the table (i.e. counties with higher Per Capita Incomes colored with a darker shade, counties with a lower Per Capita Income values using lighter shades...that is not what is demonstrated in this image, however). All this to say, with a data connection now established, the data in the table can now be displayed/represented visually via the map, by editing and customizing the display properties of the map layer.
Is FIPS code the only option for connecting data tables to U.S. political-boundary maps? - For U.S. state-level data, no. But once you get lower than state-level (i.e. if you want to map county-level data and below)…then yes, FIPS is the logical way to go, especially if you are working with counties of multiple states at once. At the state level, you only have 50 entities…each with a unique name or unique abbreviation (i.e. only one state is named ‘Florida’…only one state has the abbreviation ‘CA’, etc)…so you could use state name or state abbreviation as the unique identifier. But, once you get down to the county level, especially if you are connecting a table of county level data for the entire nation (or at least in multiple states) at one time…you want to use FIPS code as your unique identifier.
Why? Think about this…there are multiple ‘Madison’ counties across the US (to be exact, 20 of the states in the U.S. have a 'Madison' county). Say you have a table of county-level data for the entire US, and you want to connect that table to a US county map layer. Your table, under the ‘County Name’ field, is going to have 20 instances of ‘Madison’. Thus, you cannot use the ‘County Name’ field for connecting to the map layer, because you will violate that #1 rule that I mentioned at the top of this post…all data connections must be based upon a one-to-one relationship between the data table and the map layer.
So, how do you get around this? Easily. Each of those 20 ‘Madison’ counties does have a unique FIPS ID, not shared by the other 19 ‘Madison’ counties. Your data table will (hopefully) have a ‘FIPS’ field as well…and so, that is the field upon which you base the data connection. Same with census tracts, census block groups, and census blocks. (see this other forum post for more information about the FIPS codes of census units)
What about Data Connecting to map layers with features that do not inherently have FIPS IDs? - What about map features in map layers that you create (e.g. point map layers created by importing GPS coordinates, or point map layers created by importing geo-coded addresses, or territories that you create by aggregating existing map features - like counties - into larger polygons). The map features in those layers that you create will not have a ‘FIPS’ code that you can use as a unique identifier. What do you do, then?
If you are creating map layers of your own, you should make it standard practice to employ some system of assigning some sort of unique identification value to each feature. If you are imported coordinates collected using a GPS unit, refer to the 'importing GPS coordinates' link above for tips on unique IDs. On the other hand, say you are creating a point map of your customers, you probably already have some system of uniquely identifying your customers in your internal record-keeping system, like a unique customer ID of some sort. Well, in the whole process of geo-coding and importing your customer addresses into AWhere (click on the link above for more about that process), you just need to make sure that that unique customer ID value is included in any tables that you use in that geo-coding/importing process. Thus, when your point map layer is created, one of the inherent attributes of your mapped customer locations will be that unique customer ID…therefore, you have essentially created your own version of a ‘FIPS’ code, in a sense, in your map layer…enabling you to perform any subsequent data connections between that customer location map layer, and any data tables that you might create over time, just like the southern Florida example above. One difference here is simply that instead of connecting a data table to a map layer containing 2-dimensional polygon features (i.e. counties)…you are connecting data to 1-dimensional point features…but the concept is all the same. You then have various options as far as how to display those point features, to best visually represent the data, from the external table, on the map. For example, display the points using variably sized circles representing a gradient of numeric values from the table (read through this forum post to see an example of what I am talking about).
Do other countries or regions of the world have a FIPS-type ID system for Political Administrative Boundaries? – Not all other nations will have a system of unique identification for their various political, administrative units the way the US does with FIPS (Europe has a similar system referred to by the acronym 'NUTS'). So, when using AWhere data connection tool for mapping data for other nation’s political administrative units, you might end up having to use the political units’ names. That is a discussion for another forum post (click here to read that post).