Effective Cartography

Mapping and Analysis with Categorical Data and SQL

There are two major families of GIS data models: Vector-Relational data models represent classes of things and conditions as rows in tables, each row being distinguished by a collection of attributes. Raster data models represent locations and conditions with cells that are like pixels in an image. This tutorial looks at the basic functions of Relational Databases, extended to handle vector data types. For more of the background on relational data models you can take a look at the Vector Relational Data Models page. This tutorial is also going to be an opportunity to talk about some of the ways that tables are encoded and exchanged -- topics that are discussed in more detail on our page about Data Formats for Geography.

In short, there is a standardized toolkit for using tables to store references to things and their attributes. This toolkit is called Relational Database Management Systems or RDBMS for short. If tables and their rows and columns are structured according to certain rules, then the RDBMS toolkit provides a set of functions (Structured Query L:anguage, or SQL) that makes it easy to explore relationships amongst the things and conditions represented in a table or in a system of tables. Functions of SQL include: Queries, Sorts, Summaries, and Joins. This tutorial will demonstrate some ways that we can employ these tools in ArcMap to perform logical experiments with relational data models. Perhaps we can even use tables of observations to learn new and useful things about the world!

Our purpose in exploring geographic data sets may be to discover useful distinctions and associations among things represented by data and then to portray these on a map. Most of the time, we will be using data that was collected for purposes different from ours. Quite often, the referencing schemes we find in data incorporate very fine categorical distinctions, most of which are irrelevant for our purposes. Because effective, credible communication requires us to be concise, it is usually the case, that we must transform complex category schemes to simpler ones, tailored to the distinctions that are important in our particular situation. This tutorial will examine some typical problems of using categorical referencing systems and to work some techniques for transforming one category scheme to another. This tutorial will take us through some of the fundamental principles and operations pertaining to tables and feature classes in ArcMap, including graphical, spatial, and attribute selections, and table joins.

Download the sample dataset

• Download the Sample Dataset

Download the State-Wide 1971-1999 Land Use Shape File and its Layer File

It happens that the MassGIS download for the state-wide land use layer does not include the .lyr file that contains the symbology and labels for the land use classes. Here is a link to a zip archive that includes the full state-wide 1971-1999 Land Use polygons with the .lyr file. This archive also includes the lookup table that is referred to later in this tutorial. If you just want to lookup table and the .lyr file, here is a zip file that just includes these

Workshop Outline

• Explore Land Use Data
• Discussing Areally and Semantically Categorized (Chunky) Data
• Explore Land Use Change Using Queries and Summaries
• Categorizing Data in Using a Layer's Symbology Properties
• Working with Legends in Layouts
• Fix Legend Headings

Display Tricks

• Using Transparency and an Aerial Photo

Introducing Table Joins and Lookup Tables

• Joining A Lookup Table to a Feature Layer
• Explore a Look-Up Table
• Enjoy Easy Reclassification Using a Lookup Table
• Managing and Creating Categories with a Lookup Table

How to Create your Own Lookup Table

• Tips on working with Excel tables in ArcMap.

Video of this Tutorial!

• Part 1: Queries, Views, Sorts and Summaries
• Part 2: Table Joins -- Recategorizing Data with Lookup Tables

Explore the Land Use Data

For this demonstration we are going to look at a very interesting dataset from MassGIS that reflects land use change from 1971 to 1999. It is very important to understand the spatial and categorical granularity of these data so we can evaluate how well it suits our purposes. So read the metadata and explore the methodology by which the data were collected. The on-line metadata at the MassGIS web site may be easier to read. Especially examine what is said about the development of the 21 class land used coding scheme for 1971 and how it was extended to 37 classes in 1985. See how the code definitions are provided in the section of the metadata entitled, Land Use Code Definitions.

Awkward Lumpiness of Categorical Classification

Take a look at the description for the land use class, Urban Open. When this dataset was being developed, it seems that the distinction between parks, cemeteries and urban institutional campuses was not considered important. It is likely that for your purposes, (for example, understanding bird habitat) you might wish that you could have better control over this class. As it happens, you might be able to use another layer, for example Protected and Recreational Openspace may be useful, as it has many different categories, such as Prim_Purp (Primary Purpose that wil help you discriminate among many different sorts of open space.

Awkward Lumpiness in Aerial Units

Before using a dataset in a real decision-making situation, you should understand the spatial granularity of the data. For example, with the 1999 Land Use layer, the methodology included a rule that no polygons smaller than 1 Acre would be created. Obviously, this is going to lead to further generalization and omission of detaial, that might be important, depending on your specific purposes. When you look at the pattern of polygons that make up this exhaustive identification of Land Use, you can see that this characterization has a lumpiness both in terms of the semantic definitions of what "Land Use" can be, but also a granularity or lumpiness established by the method of observation.

---

Discussing Areally and Semantically Categorized Data

One of the biggest instinctive traps involved with discussing spatially and semantically categorized observations -- is to disregard and completely ignore the idea that there is any problem with lumpiness. Once you are aware of this problem hopefully you will understand that analysts who fail to mention this, are giving the impression that they are not aware that there is a problem. As an analyst yourself, even if you are aware of the problem you should not assume that your audience is immune from this trap.

The way out of this is simple. Consider the Things and Conditions expressed in your conceptual model. And the Spatial Mechaism by which your hypothetical things affect each other. Your conceptual description shuld be specific enough so that we understand the actual granularity of the way these things and conditions mesh in the real world. For example in the case of Bird Habitat, I should consult an avian ecologist who might tell me that the species of birds I am interested in requires patches of trees for nesting, and acertain amount of vegetation on the ground.

On the other hand, when we discuss data, such as land use data, we should keep in the forefront of the discussion, that while certain of the semantic classes may be helpful for distinguishing areas that may be more or less suitable for birds, yet on the other hand, we don't have information about actual clumps of trees. Rather, the Things reflected in the land use data are relatively large polygons of over an acre or more which are uniforml;y assigned to some generalized category. A good way to forefront this idea is to mention the issue of the minimum mapping unit (discussed above) explicitly in your text when you introduce the land use data.

The tricky part of the discussion is that the idea that the land use data is not perfect does not mean that it is utterly useless. (that would be too easy!) In the case of 1971 and 1985, this may be the best information that we have regarding some aspects of Bird Habitat that were observed at that time. What you must do is speak realistically about the specific of errors that you believe may result from the use of these data. Note that the same data-set might overestimate the existiance of a condition in one sort of place, and under-estimate the same condition in other areas. It is not enough to state that these errors may exist, you should point our specific types of important land use conditions that may be missed or falsely indicated with respect to your decision-making context.

Ultimately, this discussion will lead you to make useful recommendations for datasets that may do a better job of representing some aspects of the Things and Conditions that are of interest. -- for example, you might find betterdata about open spaces protected for the purposes of habitat preservations, wetlands data from the USGS.

---

---

Explore Land Use Change Using Queries and Summaries

Having our land use data organizes in a table allows us to filter the collection of polygons according to their attribute values to learn interesting things. IN the next demonstration, we will explore land use change from 1971 to 1999 using relational queries, or as they say in ArcMap, Select by Attributes.

References

• Select By Attributes
• About Building an SQL Expression
• Calculating Statistics for Selected Records

Making queries in ArcMap is very powerful and there are many ways of doing it. A good way to learn about these is to click on the Help button at the bottom of the Select by Attributes Window.

Filter a Layer using a Definition Query

Using Select By Attributes to filter data is a useful way to explore. But what if we wanted to make a map that highlights just the polygons that had changed form Industrial land in 1971 to some other land Use in 1999? We can see those polygons on the map now, but they are all mixed up with the other land use polygons, and it is hard to find just the ones that we are interested in. A Definition Query is a layer property that allows us to restrict the polygons that are visible in a layer using an attribute query.

References

• Using a Definition Query

Notice that most of the polygons form the 1999 Land Use layer disappear. Our layer now shows only the 44 polygons that have changed form from Industrial in 1971 to some other value in 1999! This way of restricting layers according to queries can be very useful. It does not create any new data on our hard drive. So you could copy this to a new layer named Industrial Re-Use, 1971 - 1999 if you wanted to. For now lets just remove the definition query and proceed to explore some other techniques.

---

Categorizing Data in Using a Layer's Symbology Properties

The ArcMap symbology editor is an easy way to apply symbols to features according to their attributes. Fro the purposes of making maps, the symbology editor can do three things for us:

1. Let us apply symbols to features according to their attribute values
2. Let us associate easy-to-understand labels to the legend categories
3. Allow us to make more generel categories by grouping existing, finer-grained categories together.

In real life, we can come across category schemes that have hundreds of potential values. But to communicate ideas about land use concisely we will need to reduce the number of distinctions to highlight those that are critical for our study area and for our decision-making context. Cartographers generally agree that a concise legend should have no more than 7 or 8 color classes. We already know a couple of very effective ways of pruning needless categories from our legend.

Extract the land use polygons so that your shape file includes only those polygons that intersect with your map

There are likely many categories of land use that occur inth4e state-wide layer that do not occur in the limited area surrounding your area of interest. So once you desice on the scale and extent of your map, you can extract a new land use shape file using your trusty techniques that we learned in our tutorial on Organizing Data. Once you do this, you wil find that you have many fewer land use categories to deal with. If you stil have more landuse clsses that you want, and if some of the distinctions are not meaningful in yoru decision-making context, you can combine categories using he legend editor.

References

• Exporting Features
• Changing the Source Reference for a Layer.

This feature class (shape file) and its layer should now have a lagend that is much reduced in terms of the number laf land use categories. And these categories should all be represented withi your study area.

Use the Layer Symbology Properties to Consolidate Categories

Even though your legend has fewer categories, you may stil want to combine some of them to make your description of the setting more concice. To do that you can group categories in the Symbology properties of the layer as we did in the exercise on Nuts and Bolts of Mapping.

References

• Using Unique Values to View Categories.

Consolidating similar classes

Depending on your area of interest and your decvision-making question, you can fine-tune your land use categories to make a more concise presentation. Id the distinctioon between two classes of land use is of no interest to the decision-maker, you can group them together as described below.

Recategorizing data is a very common task in the world of GIS, because people who create data tend to use very fine-grained category schemes. Your aim should be to make a land use map that answers the questions that a a decision-maker might have regarfing your study area and your decision-making context. As a general rule you don't want to have more than 8 categories. Doing a good job with this requires you to look at the metadata to understand what the existing categories mean, and it will also requires that you think about the general land-uses that cover the context of your study area and the specific land uses that may be most important in terms of the things and conditions and spatial relationships that you are concerned with. You don't want to confuse people with a lot of distinctions that are irrelevant. On the other hand, you also shouldn't use categories that are too broad -- because this can make it look like you are deliberately hiding, or unwittingly disregarding something. So picking land use categories is a balancing act.

Use Conventional Land Use Shades

The reason that we are going through all of this trouble to recategorize data is so that we can describe the critical aspects of a situation concisely and efficiently. We want to streamline the process of understanding these things for our clients and the general public. It is also true that in the process of working through this. we are also going to inform ourselves, and will probably also see how we can reformulate our question to make better use of the information that we have. One very important part of communicating concisely is touse the conventional shading scheme for land use categories that is discussed on the page, Elements of Cartographic Style.

---

---

Working with Legends in ArcMap

Thematic maps portray data using a vocabulary of symbols. The legend on a thematic map is one of the first places that a map user will look when trying to figure out what the map is about. It is relatively easy to insert a legend on your map layout, but there are some important aspects of preparing your legend that may not be obvious.

All Legend Classes should Appear on the Map

A good thematic legend should not include more than eight classes. Beyond this number it becomes a challenge to discriminate the meaningful distinctions. Usually these distinctions are part of the argument that you are making with your map. Considering this, it is not a good idea to show categories in your legend that do not appear in the map.

Resources

• Working with Legends in ArcMap

At this point you have a legend, but it usually has too many items in it. In the next stage, you can reduce the number of items toinclude just those that apper on your map.

Fixing Legend Headings and Labels

By default, the legend heading wil be the name of the field that the categorical symbolization is based on. Inmost cases this field name will not be meaningful to the average person. You also may have a legend category for All other Items You will want to change this. The following steps discuss how to do this.

Resources

• Drawng a Layer Using Categories

---

Using Transparency and an Aerial Photo

The spatial and categorical lumpiness of our land use data most definitely pose a problem for many applications where knowing the actual land use is important. There are definitely questions for which this data-set can provide useful answers. But in any analytical context, the methodology and the categorical and spatial lumpiness needs tobe discussed,. If it is not, then the analyst is implying that the data are perfect.

One very evocative way of calling attention to the complexity of the real-world patterns is to make your land use and open-space layers transparent, and show them on top of an Aerial photo. Changing the transparency of a layer can be done by simply tweaking the Transparency parameter in the Display Properties of the layer. Simple enough; but the problem is that if you have two layers, like Open-Space and 1999 Land Use, you will find that the Transparency (or Opacity...) of two layers adds up. So that the aerial will be more difficult to see in areas where Parks are over-layed on Land Use. To fix this, you can create a group layer for transparent layers, put your parks and land use data into it, and adjust the transparency of the group. Of course your Aerial photo would be underneath.

References

• Working with Group Layers.
• How to Set Layer Transparency

Some Examples of Effective Graphical Hierarchy with Land-Use-Tinted Aerioal Photography

---

Note that these topics are not required for your categorical data exercise!

Part 2: Introducing Table Joins and Lookup Tables

The process of recategorization may require several trials and adjustments. Using the arcmap legend editor to group categories as demonstrated above may not be too difficult when you have only 35 categories, but this is quite aggravating if you have datasets that include hundreds of distinct values. This is why professional data wranglers use a powerful relational database techniques called Table Joins and Lookup Tables to manage categorical data.

A Lookup Table is a table that translates a complex set of categorical codes (like our MassGIS Land Use codes). to one or more generalzed categorical Schemes.

Notice how the table pictured above has a row for each of the MassGIS Lu_21_ land use codes. And for each code there are four different categories applied. The most diverse class is carried in the Definition column. There is a more general classification given in the column named Category in which all four Residential codes are lumped together into a common class. Another column, BirdHab shows a classification of land use that was developed for the purposes of evaluating land use from the perspective of a person interested in bird habitat.

Joining A Lookup Table to a Feature Layer

The names for the alternate land use classifications that appear in the columns of our lookup table would be very useful if they were columns in the attribute table of the MassGIS Land Use layer. If they were, we could apply the categorization using the legend editor in the layer's symbology properties. We can accomplish this using a technique known as a Table Join.

References

• An Overview of Tables
• Adding Tables in ArcGIS
• Using the ArcMap table of Contents
• Common Tables and Attribute Tasks

Hopefully that idea of how the attributes for rows in one table can reference rows in a lookup table makes sense. Because it turns out that we can automate this process with a procedure known as a Table Join

References

• About Joining Tables

This table join is a virtual view of the two tables joined togethr which is part of the Simplified 1999 Land Use Layer but it has changed nothing about the original shape file on the disk. To see this you could look at the attribute table of the original Land Use (1999 37 Classes) layer and see that it is not joined.

Enjoy the Easy Reclassification and Labeling of your Legend

Joining tables can be a little confusing and intimidating at first, but the next step will show you why it is worth it. We will go back into the properties of our new Simplified 1999 Land Use Layer, and see how easy it is to create a new legend based on any of the land use classifications in the lookup table.

References

• Using Unique Values to View Categories.
• Working with Legends in ArcMap

---

Managing Categories in the Lookup Table

You see how the lookup table has made the application of categories fairly easy. The next step should make it clear how to use the lookup table to adjust and manage classes. In map making, you never get things right the first time. Adjustments are always necessary at some point. In our case, once we look at the map, we can see that we have too many categories. The distinction between wetlands and woodlands is not necessary in our case. So we need to create an even simpler classification. To fix this we will create a new column in our lookup table to hold a new version of our pbcSimple classification. Then we can edit a few of these values to create an even simpler scheme. Finally we will re-join the lookup table and apply the new, even simpler scheme.

References

Adding Fields to a Table

Making SImple Field Calculations

Creating a New Classification in our Look-Up Table

1. Find our Lu21_lut_pbc.dbf lookup table near the bottom of your ArcMap table of contents. You may need to change the table of contents back to list by Source mode.
2. Right-click the table and choose Open so you can view it in the Table window.
3. Pull down the Table Options pull down at the upper left corner of the Table window and choose Add Field.
4. Create a new field named myClass. Make sure to choose Type: Text. See screen-capture.

Now that you have a new field, there are a few different ways of setting its values. In the next several sterps we will explore some of the ways of doing this. First we can set all the values based on the values of another field in the table. After that, we can select a specific row oe rows and set their values.

Setting All of the Values for a Field based on the Values of Another Field

1. You should begin this step with the Lu21_lut_pbc.dbf table open and no rows selected.
2. If you have any rows selected, you should un-select them with the Clear Selection button at the top of the Table window.
3. Now right-click the heading of your new myClass field and choose Field Calculator.
4. In the field Calculator dialog, find the pbcSimple field in the Fields list, and double-click it.
5. This will cause the name of the field to appear in lower panel as shown in this screen shot.
6. Press OK in the field Calculator and you wil see that al lof the values of MyClass have been calculated to be the same as the values of pbcSimple.

Setting the Values for Selected Records

1. Note that this operation does not work correctly if you are in View Layout mode. Make sure that you use the View menu and choose Data View
2. with theLu21_lut_pbc.dbf table open, select one or more rows in the table by clicking in the little white box at the very left side of a row. Control-click to select several rows. I suggest that you click the rows for Codes 3,4,14 and 21, for Forest, Wetland, Salt Wetland, and Woody Perennial. In our new class system, we want all of these to be in a new class called Wooded and Wetland.
3. Now right-click the heading of your new myClass field and choose Field Calculator.
4. In the field Calculator dialog, clear out the bottom panel to remove the reference to the [pbcSimple] field if necessary.
5. Now type "Wooded and Wetland" into the lower panel - including quotes as shown in This screenshot
6. Press OK in the field Calculator and you will see that the values of MyClass have been calculated for just the selected rows!

Now that you have modified the lookup table to add your own classification scheme, you can use it to create a new legend. But before you do, you have to un-join the lookup table from the Simplified Land Use 1999 layer and join it again. Otherwise, your new column does not show up in as one of the choices in your Val;ue Field pull-down menu.

References

• About Joining Tables

Removing a Join

1. Right Click your Simplified 1999 Land Use layer, and choose Joins > Remove Joins > Remove All Joins
2. Now add the join again as you did earlier See screen shot.

You now have a mastery of using lookup tables to manage categories! You now can assign appropriate land use symbols to your legend classes according to the conventions for land use shading discussed on the page Elements of Cartographic Style.

Creating Lookup Tables

The topics below cover one way of creating a lookup table for yourself using Microsoft Excel. These steps are not necessary for completing the homework for Fundamentals of GIS. Nevertheless, the techniques of passing tables in between Excel, ArcMap and dbf format are good to know if you need to create your own lookup tables. Another way of creating a lookup table is covered in the ArcMap on-line help topic Summarizing a Table before Joininig it.

Resources

• Creating New Tables especially see "from text Files"
• About Working with Excel Tables in ArcGIS

Some Notes on Exchanging Tables between Excel and ArcMap

1. The most flexible way of dealing with tables in ArcMap is the Dbase (.dbf) format. This format can be read and written by OpenOffice. Excel can read dbf, but won't write to it in the latest version.
2. ArcMap can open Excel's .xlsx format directly, but these tables cannot be modified when they are open in ArcMap. So if you create tables in Excel, it is best to convert them to DBF right away by right-clicking them in the ArcMap table of contents and choosing Data Export Data. Note you must choose Save as type: dBase Table in the export dialog
3. A DBase table cannot have more than 9 characters in its field names, nor can these names contain spaces or special characters that aren't simple letters, numbers and underbar characters. Nor can a field name begin with a numeral. So keep this in mind if you are creating tables in excel.
4. Another gotcha of using excel with ArcMap is that sometimes ArcMap won;t let you use a table if it is open in Excel and Vice Versa. A good way around these problems is to use Excel to create new versions of tables, and export them as CSV. When you want to edit a table in Excel, create a new version.
5. A Comma-delimited text file (.csv) is an easy way to encode and exchange information between programs, although some semantic information about data types, such as dates and numbers can get lost in the translation.
6. The problem of data types not being discriminated in text files emerges most often when data values consist of numerals but are actually character strings (like zip codes.) In this case, leading zeros are chopped off, and can sometimes be difficult to add back on.
7. You can also save tables in an ArcGIS Geodatabase. This format has fewer restrictions on field names, etc, but is less portable. For example it can't be opened in Excel.

For deeper reading on spatial data formats, see A Discussion of Spatial Data Formats

Create a Lookup Table in Excel from the MassGIS Metadata

1. Explore the Data Dictionary for the 21 Class land use code from the Land Use metadata.
2. Consider how you might use the legend editor to reclassify these codes into a concise categorization that emphasizes the distinctions that are critical for our question.
3. Copy and paste the data dictionary from the metadata into excel. Today we are lucky because we can paste this into excel and it simply fills in rows and columns of a table that requires few modifications. Often making a lookup table is more of a headache than this. Note: THis trickworks from the metadata web page, but not the PDF version!
4. Save your excel spreadsheet as an .xlsx file in your Project_Data folder.

Open Excel Table in ArcMap and Save it as a DBF

1. Use the Add Data button to add the excel table in ArcMap. Note that you have to double-click the excel icon in the add data dialog to see the various worksheets in the excel table.
2. While ArcMap can open an excel table, it can't alter them, and often does not register updates that you make in Excel. It is best to export the table in DBF format and add this table to ArcMap before going further.
3. Right-Click your lookup tabel in the ArcMap table of Contents, and choose Data > Export and click the little Folder Icon to enter the Save As dialog.
4. Save your lookup table as Type: DBase. Make sure you save this to a location in your project folder that you will be able to find again.
5. Now you can open your new table in ArcMap.

Advantage of DBase Format in ArcMap

Now that your lookup table is in Dbase format, you can add columns and change the values of data fields within ArcMap. While it may seem easier to do these things in Excel, the trouble is that ArcMap does not always see the changes that you make in Excel, and it is tricky to keep saving the Exel document and re-opening it in ArcMap if you wnt to make adjustments.

Creative Commons Attribution-NonCommercial-ShareAlike 3.0 Unported License by Paul Cote.