Geo325: Business Geographics
Lesson 10 ~ 'Joining' Layers to External Tables; Using CACI Data

Lesson Overview: In this lesson we will use one of GIS most powerful tools—that of joining data from an external database to a map layer. The external database does not contain geographic features and so could not—on its own—be mapped. But by joining it to a layer that can be mapped, we can create thematic maps using information from our external database.

We will join consumer behavior information to a map layer. Our data provider is CACI, a  leading provider of business and economic data. We can then make business decisions based on this consumer behavior data by looking at our map.

Prepare for this lesson as in previous lessons. Create a folder on your storage disk, naming it Lesson10 or similar.

Part 1. Add the Map Layer, Add the Tabular Data, Create the Join

Launch ArcMap. Click the Add Data button on the toolbar, or select File, Add Data, on the menu. Navigate to C:\Geo325\AttributeData\CACI_Data\Maps. Add the census tracts layer and the counties layer. Note. This is a different census tract layer than we have used previously. You should see parts of Pennsylvania, New Jersey, Maryland and Delaware on your map. Set your view's projection as in Lesson 6. Parameters are Projected Coordinate Systems, UTM NAD 1983, Zone18N.

Let's look at the data that comes with the census tract layer. Right-click on the census tracts layer name, and then select Open Attribute table. Whoa! Not much there. This layer only has three columns—an ID column (FID), a shape column and a TRACT_FIPS column.

We will now join a data source that contains lots more interesting information to this boring old census tract layer. The map layer and the data table will need a common field so that a value in one finds its mate in the other. Note that the last column in our census layer is called TRACT_FIPS and contains 11-digit numbers.  As we will see later, the CACI data also contains an 11-digit number; this will enable us to join the data.

(Before closing the table, select Selection on the menu, and then select Clear Selected Feature. Now close the table by clicking on the X, in the upper right corner.

Add Our External Information File--CACI Data

Click on Add Data and navigate to C:\Geo325\AttributeData\CACI_Data\Databases. You will see three subfolders ( Finance, Restaurant, and Retail). Click on a folder which would help you locate your new business. For example, if you are interested in opening a new bank, select the Finance folder. For the demo we will look at restaurants. Double click on the Restaurants folder and then to the Census_tract folder. Click on the file named rest_tr.dbf

Open the data table to see what it contains. To open a table in ArcMap, select the table iconin your map's table of contents. Right click on the table icon and select open.

Explore the data in the table by paging down and to the right. Notice the following:

Close the table when you are through exploring the data.

Now what do all those fields mean? Click on your Start button and select My Documents. Navigate to C:\Geo325\AttributeData\CACI_Data\Databases. In this folder will be a file called Restrant-decoder.txt. Double click on the file. The text file should open and we can now see what the codes mean. Scroll down through the file. For example, the code A26006_B means "Take Out: Mexican Restaurant: Expected Adults (Age 18+) " We will use this file later when we decide what variables to use for our thematic map. Leave the file open but minimized so we can look at it later in the exercise.

We are now ready to join our CACI data table to our census map layer.

Join Data Table to a Map Layer

Go back to your ArcMap project. In the lower left corner of Arcmap, click on the Display tab. See image below.

We will now join data from a table to our spatial layer. Always start a join by clicking on the spatial layer in your table of contents. You will join tabular data to this layer. Right-click, select Joins and Relates and select Join. A Join Data window will open.


For dropdown 1, select TRACT_FIPS. For drop-down 2, (the join table) select rest_tr. The field in the table we are joining to the census layer (drop-down 3) will be TRACT_FIPS. Set your values in the Join Data window and then click OK.

If ArcMap warns you about needing to create an index, say Yes, or Ok.

Although nothing changed in your view, a lot changed behind the scene. The GIS now knows much more about each feature in the layer. Let's confirm that by opening the attribute table for our layer again. Go to your map's table of contents and click on the layer named Census Tracts. Right click on the layer's name and select Open Attribute Table. You'll see that where before we had just three columns, we now have many more columns of information! In fact, we now have all the information from the CACI table in our mapped layer.

You'll see that many records do not contain information; the cell shows  <Null>. Page down through the table until you see some non-null entries. The null records simply indicate that no matching record was found in the CACI data table; for this exercise this is not a concern. (If all your rows show <Null> the join did not work correctly; try again or ask a lab assistant for help.) Also, scroll over to the right of the table to see the long list of data fields.

A join is not permanent. Before continuing the exercise we are going to save this new layer in a permanent way so you can take it with you on your flash disk. Right-click on the layer's name in the table of contents, click Export Data, and then fill in the dialog box as shown below. Be sure to save this new layer to your flash disk.

When the computer asks if you want to add this new layer to your project, click Yes. Now remove the original layer from your ArcMap project by clicking on its name in the table of contents, right-clicking and selecting Remove.

We are now ready to make a thematic map using the supplemental CACI data.Now would be a good time to save your project file (.mxd).

Creating Thematic Map

We want the census layer to be colored according to a value in the CACI data. Also, we want the counties layer to draw on top of the census layer, with a hollow interior and a dark border, so that the CACI information can show through. We'll do that now.

Make sure that the counties layer draws above the census tract layer. If needed, drag counties above the your new layer in the table of contents. Symbolize the counties so that you can see through them to the census tract below. To do this, right click on the Counties layer, select properties. Select the Symbology tab. Click on the colored rectangle that shows how counties are currently drawn. Select "No Color" for your color. Make sure the border is black and set it to a 1.5 pixels wide.

Let's label all the counties so the map reader can see where they are. Rather than manually labelling each feature with your label tool simply right-click the layer name (Counties) and select the option to Label Features. Voila. All features are now labeled.

Right click on Census Tracts layer and select Properties. In the Layer Properties box select the Symbology tab. Click on the Quantities option on the left. This will display the sub-options.

The default display is Graduated Colors. No field has yet been selected for our thematic map. Click on the Value: option and change the selection to a desired variable. Look in the restrant-decoder file to see what you'd like to use for your variable. If you've followed along with the directions, that text file is opened but minimized.

For example, map A26001_B, which is the number of adults who dined out in the last 18 months. Once you selecte an attribute, the computer fills in the data ranges. (See image following.) Select a color ramp that you like, click Apply, then Ok . Remember, colors should ramp from light to dark as values go from less to more!


 

We have just completed the first part of the exercise. We added a census layer to our project, we added some external data to our project, we joined these two layers so we could map them, we saved this layer as a new layer, and then created a thematic map based on the information from the external data source.

We will now go through those same process again, only this time we will join the very same CACI data to a zip code layer. Zip code information is readily available but, as we will see, does not produce as valid results as do more finely detailed shapes. They often aggregate information in undesirable ways.

Use Zip Code Layer to Map CACI Data

Follow these abbreviated steps to perform the above steps but using zip code data.

  1. Add Zipcodes from c:\geo325\AttributeData\CACI_Data\Maps.
  2. Drag them below the counties layer.
  3. Add CACI external database from c:\geo325\AttributeData\CACI_Data\Databases\samefolder as you chose above\Zip_Code\same type of file you chose above.dbf. For example, if you chose restaurants above, use them for this step as well.
  4. Join this new data table to your zip code layer. For dropdown 1, select ZIP. For drop-down 2, (the join table) select rest_zip, or whatever file you added that is based on zips. The field in the table we are joining to the census layer (drop-down 3) will be ID. Set your values in the Join Data window and then click OK. See image following.



     
  5. Export this new joined layer to your flash drive. Name it restZip or something that will help you remember the type of data and that it is 'by zipcodes'. Remove the original zip code layer from the project to eliminate any confusion.
  6. Create a thematic map classifying the zip codes based on the same field you used for Census tracts (e.g, A26001B, dining out.) Use the very same color scheme as well.

Evaluate Your Map; Which is Better Data: Census Data or Zip Code?

We can now evaluate which of our data sources is better. Remember the CACI data about people's consumer behaviour is exactly the same for both census tracts and zip codes. The only thing that is different is the spatial layer. First, turn the census layer off so we can focus just on the zip code layer. We want to compare how the same information looks based on census tracts and then based on zip codes.

We are ready to decide where our business location should be. To decide on this consult your two new layers: the CACI data mapped by census tract and by zip codes. Turn census tracts off and zip codes on. Look at your map. Then turn census tracts on and zip codes off. Do this several times and see how they change.

Which shows greater variation in patterns of dining out?

Select An Optimal Business Location

You would want a restaurant to be located where people are dining out a lot. Look at both layers and see if they indicate the same thing. Do the census tract layer and the zip code layer suggest the same optimal site for your business or different optimal sites?

Click on New Marker button on the drawing toolbar and place a point on your map that you think is a good location for your business. Use your text button to label this point as "Proposed Location" or similar.

Create two maps, one showing the census tracts and one showing zip codes. Both should show the counties layer. Add the standard items: a legend, a scale bar, a north arrow; a title, such as "Population Dining Out." Be sure to reference the layer you used for each map, that is, either Zip Codes, or Census Tracts should show up somewhere on the map. Print the map.

Lesson documentation updated 08/01/07; Ellen Bryson.
The Center for Geographic Information Systems and Spatial Analysis at West Chester University