Designed as a companion data table for the DC_Countries project, the DC_Localities_USC table adds the ability to definitively identify states, provinces, districts, territories, possessions, islands, and military postal destinations within the United States and Canada (thus the “USC” designation). Second level designations (the fancy name for political subdivisions like these) are pretty complex because every country identifies them differently, and many countries use more than one designation for similar types of administrative areas (e.g., Canada’s provinces and territories). That is why this table is limited to only US/Canadian localities. Someday we hope to expand this table (or create a new one) that encompasses many more countries. Any volunteers?
Thankfully the US and Canadian postal systems have established unique and very recognizable 2-letter designations for each locality, but there is no easy way to cross-reference that back to the country. Are “ON” and “RI” in Canada or the USA? It’s impossible to tell looking at the 2-letter designation, so we have included the DC_Countries‘ key (DC_Country_Key) in the DC_Localities_USC table (country_fk), and we have created a unique 5-character designation (locality_pk) for each locality that includes either “US” or “CA” in the designation. This way, you can easily tell, without even a database lookup, which country the locality “belongs to.” With the locality key, you can easily tell that “CA-ON” and “US-RI” from the previous example belong to Canada and the USA respectively.
We have also included the standard postal abbreviation (abrv) and generally recognized proper name (name) of each locality to help with converting between various data sources. By “generally recognized proper name” we mean “Massachusetts” instead of “Commonwealth of Massachusetts,” which is the full formal and legal name of the locality. If there is demand for it we can add in the legal names, too. Or you can add them in and send us a copy! 🙂