Monday, June 17, 2013

Show & Tell for Module 6: Safe Relationships

In this "Show & Tell" session, I'll show you some useful things that were not included in  LAD module 6. This blog covers table joins and relates. Another blog covers census data.

If you'd like you can try these yourself. The data is included in the Challenge6 zip file which you can download and unzip. Here are the notes to re-create what I show you.

TABLE JOINS AND RELATES






In LAD module 6 you learned how to join two tables together using a common attribute field. The lesson showed one way to join two tables, but in real life it gets a bit more complicated. Here are some simplfied examples to help explain the four main types of relationships between two tables and the correct method to use in ArcMap for each type.

Open "BeSafe.mxd" in the Challenge6 folder.

1) One-to-One Relationships

Open the "parcels" attribute table.

There are three parcels, each with a unique TMK (tax map key) ID.  Now open the "tenure" table.

This table shows the tenure type for each parcel - whether it's F (Fee Simple) or L (Leasehold). If you study the two tables, you'll note that there is one, and only one, record in the tenure table for each record in the parcels table.

This is a one-to-one (1-1) relationship. For this type of relationship you can use the Join command in ArcMap.  From the "parcels" table, click "Joins and Relates" and then click "Join..." and fill in the window as below:


After you click "OK" you will see that the two tables have been joined into a single virtual table. The two tables are still stored separately, but within ArcMap you can work them as a single table.


2) Many-to-One Relationships

You could label your map now with the tenure type, but it might be better to show the full word instead of the one letter abbreviation. This is commonly done using a lookup table.  Open the "tenure type" table.


There are only two records in this table, one for each tenure type. If you look back at the "parcels" tables, you'll see that many records in the "parcels" table relate to one record in the "tenure type" table.

This is a many-to-one (m-1) relationship. For this type of relationship you can use the Join command in ArcMap.  From the "parcels" table, click "Joins and Relates" and then click "Join..." and fill in the window as below. Note that the two field names don't have to be the same, but their type (numeric or string) must be the same.


After you click "OK" you will once again see that the two tables have been joined into a single virtual table. Now all three tables are still stored separately, but within ArcMap you can work them as a single table.  


Now you can label your map to show both the TMK and the tenure type.


3) One-to-Many Relationships

The first two relationship types are fairly simple, now the story gets a bit more complicated. Let's look at a table of building values for each parcel. Open the "buildings" table.


If you look at the TMK values you'll note that there is more than one building on some of the parcels. This is a one-to-many (1-m) relationship. For this type of relationship you SHOULD NOT use the Join command in ArcMap. However, ArcMap will let you so let's do it just this one time to see what happens. From the "parcels" table, click "Joins and Relates" and then click "Join..." and fill in the window as below. 


It seemed to work, but now look at the "parcels" table:


Only one building value has been joined! This could lead to a very inaccurate analysis - A WRONG ANSWER! You can remove a join, from the "parcels" table, click "Joins and Relates" and then click "Remove Join(s)".


The incorrectly joined table is removed.

Since this is a one-to-many (1-m) relationship, you should use the Relate command in ArcMap. From the "parcels" table, click "Joins and Relates" and then click "Relate...". The two tables will be related so you can select record in one table and find the related records in the other table, but the two tables are not joined into a virtual table.

But there is a way to work with the building values. You could add up the two building values for parcel 6 and for parcel 8 and then you'd only have one record for each parcel. In ArcMap you can do this using the Summarize command.

In the "buildings" table, right click on the TMK field name.


Fill in the window as below:


You are summarizing by the field TMK.  ArcMap knows this already because you right clicked on that field name.  ArcMap will count the number of records for each unique TMK.  While it's doing that, you also want it to add up (sum) the BldgVal (building value) field. A new table is created with the summary values.


You can see that in this table there is one record for each TMK and that record also shows the number of original records for that TMK (Count_TMK) and the total building value for each TMK. Notice that you now have a one-to-one relationship so you can join this summary table with parcels.


Now the parcels table includes building count (Count_TMK) and total building value (SumBldgVal).


You can use these fields to label your map.


You can also use them to symbolize your map.


In this case the darker colors indicate a higher total building value.

You can label, symbolize and perform other operations on a virtual table just like a plain table.  This is very powerful!

4) Many-to-Many Relationships

There's one more relationship type to cover. Let's look at a table of owner names for each parcel. Open the "owners" table.


Notice that one parcel can have many owners, and also that one owner can own many parcels. This is a many-to-many (m-m) relationship and you should use the Relate command in ArcMap. From the "parcels" table, click "Joins and Relates" and then click "Relate...". The two tables will be related so you can select record in one table and find the related records in the other table, but the two tables are not joined into a virtual table.

Now you may be wondering, can you Summarize the owner table like you did the buildings.  Yes!  You use the same Summarize command but since name is not numeric you can't add it up.  Instead you want to note the first recorded owner. In the "buildings" table, right click on the TMK field name and fill in the window as below:




You can see that in this table there is one record for each TMK and that record also shows the number of original records for that TMK (Count_TMK) and the first recorded owner.  Notice that you now have a one-to-one relationship so you can join this summary table with parcels. However, before you do that, you'll want to change First_Owner for parcel 7 since there is more than one owner for that parcel.


Now you can go ahead and join the tables.


Now you've got even more information in your virtual table.

Understanding these types of table relationships is critical to correctly perform analysis and reporting in ArcMap.

Table Relationships Checklist

Here is a checklist to go through when you're dealing with related tables:

 Check the relate field in each table
     o Field names don’t have to match
     o Field types do have to match: number to number, text to text

 Check relationship type: 1-1, m-1, 1-m, m-m
     o Quick check – sort relate fields and look for duplicates
     o Full check – summarize by relate field and look for count > 1

 Check match rate
     o Create “Relate” between the two tables
     o Select all records in Table 1 and “push” the selection to Table 2
          - Are all records in Table 2 selected? If no, why not?
     o Select all records in Table 2 and “push” the selection to Table 1
          - Are all records in Table 1 selected? If no, why not?

 Check join or relate
     o 1-1 or m-1: Join is fine, Relate is fine
     o 1-m or m-m: DO NOT JOIN! Relate is fine
          - Optional: turn a Relate into a Join
               - Summarize Table 2 by relate field
               - Select additional summary items
                    - Numeric: sum, min, max, etc.
                    - Text: first, last 
               - Create summary table – Table 3
               - Cleanup text summary records in Table 3 where count > 1
               - Join Table3 to Table 1

No comments:

Post a Comment