GEO 353 Fall 2015

GEO 353 Fall 2015 Lab 06: Working with Tables aka – Attribute Data Lab 6 Assignment 1. Open the table associated with each of the four geospatial data layers for this lab one at a time. a. How many records are in each of the four geospatial data layers? 2. Open the hospitals (Hospitals) table. a. How many hospitals are in Boone County and what are the names of the hospitals? b. How many hospitals are in Hardin County and what are the names of the hospitals? c. How many hospitals are in zip code 40509 and what are the names of the hospitals? d. How many hospitals are in Lancaster and what are the names of the hospitals? 3. What is the average population for Kentucky counties in 1990? In 2010? What is the total population for Kentucky in 1980? In 2000? Use the fields POP90 and POP10. 4. What county had the second smallest population in 1990? What county had the lowest population density in 2000? Use fields POP90 and POP00SQ. 5. Explain what the Summarize function does. How does it work? What are some of the summary options? 6. Hand in the table you created in the section about Summarizing Fields. Summarize the addname field. 7. To join or link two tables, what properties must a common field have? 8. When you joined the tables for Madison_water_wells and well_data.dbf in the Joining Tables section, what is the source table and what is the destination table? What is the difference between the two? Submit your answers by the beginning of next lab. GEO 353 Fall 2015 Lab 06: Working with Tables aka – Attribute Data Objectives: ? Viewing attribute data ? Changing the table display properties ? Adding and deleting fields in a table ? Selecting, adding and editing records in a table ? Summarizing fields in a table ? Joining tables Instructions: ? Copy the folder Lab_06 from the L:Drive as you have done in previous exercises o L:\cas\Geography\GEO353\Lab_06 Remember to save your downloaded files in an appropriate location and don’t forget where you put the files Data used in this lab: ? Counties o An outline of Kentucky counties ? Hospitals o A layer of hospitals in Kentucky ? Madison_County o An outline of Madison County ? Madison_Water_Wells o A selection of water wells within Madison County ? Well_data o A table of additional attributes about the water wells - 2 - Viewing Attribute Data aka Tables: Add all the data layers for this lab into ArcMap. You can view the attribute data stored about a geospatial data layer. This can be done by RIGHT CLICKING on a data layer and choosing Open Attribute Table (Figure 1). The resultant table contains all of attributes for each of the spatial objects that comprise the geospatial data layer. Open the table associated with each of the geospatial data layers used in this exercise. A portion of the attribute table for the Hospitals geospatial data layer is shown in Figure 2. The table that is associated with each geospatial data layer represents the ATTRIBUTE DATA for that data layer. The ROWS represent each of the entities or objects in the geospatial data layer. The COLUMNS represent the attributes for each of the layers. Figure 1: Opening an attribute table Figure 2: A portion of the attribute table associated with the Hospitals data layer - 3 - Notice that each feature, or hospital, contained in the Hospitals geospatial data layer has a row of attributes. With the table open, you can manipulate the table in several ways. ? The attributes are listed across the top in the header row. ? You can scroll across the table to see all of the attributes using the bar along the bottom of the table. ? You can click and drag the various attributes if you need to place the attributes in a different order. ? You can right click on a header and sort the all of the records based on that attribute. Figure 3 shows the records being sorted alphabetically based on the county in which the hospital is located. Adding and Deleting Fields in a Table: In certain situations, it will be necessary to add and delete fields in a table. Adding a field: ? Click on the Table Options button on the table and then Add Field… (Figure 6). This will open the Add Field dialog box (Figure 7). Figure 6: Adding a field Figure 7: Add field dialog box Figure 3: Sorting an attribute - 4 - ? Several properties of the field must be defined. It is important to pay attention to what you are doing at this stage, because they cannot be changed later. ? Give the field a Name. The field Type must be set. Decide on the Precision (Width) to be allocated for this field if it is a number. As stated above, the field definitions you select now are permanent, therefore when designing a table think about its structure carefully. ? Try adding a field named POP_2007 that is a short integer, with a precision 16 and then Click OK (Figure 8). A portion of the table for Counties with the new field is show in Figure 9. Figure 9: Table with the new field, POP_2007 added ? In order to calculate values for a new field, simply RIGHT CLICK on the field POP_2007 then click on Field Calculator (Figure 10). Here, you can enter algebraic expressions for the new field (Figure 11). Figure 8: Adding a Field - 5 - ? Using the field calculator, you can calculate measures such as population change, population density, etc. ? This is a very useful tool that can be used to create new attributes within a table. ? You WILL BE using this in future labs so keep in mind that you might need to refer back to this lab at some point Deleting a field: ? Make sure the attribute table associated with Counties layer is open. ? RIGHT CLICK the field you just created, POP_2007, and choose Delete Field and then YES (Figure 12). o Please note the warning that pops-up before you delete a field. o You will not be able to delete the FID and SHAPE fields. Figure 10: Opening the field calculator Figure 11: The field calculator - 6 - Figure 12: Deleting a field Selecting Records in a Table: While editing a table, there are several useful buttons. To select a record, use the cursor to click on the record. Once selected, the record will change color (Figure 13). Figure 13: Selecting a record Take a look at the data layer associated with this table and notice the corresponding geospatial feature has also been selected (Figure 14). - 7 - Figure 14: Selecting a record part 2 CLICK on the Clear Selection Button to clear the selection Statistics for a Field: ArcGIS allows you to calculate some simple statistics on any field in a table. Example: ? Open the table associated with the Counties geospatial data layer. ? RIGHT CLICK on the field Pop70 and then CLICK Statistics (Figure 15). Figure 15: Opening a field’s statistics The resultant simple statistics are shown in Figure 16. - 8 - Figure 16: Simple statistics for the Field Pop70 Summarizing a Field: You can summarize a table based on a particular field's values. The results are stored in a new table. If some records in the table are currently selected, only these records are summarized. If no records are selected, the whole table is summarized. Summarize creates one record in the new table for each unique record value in the field you chose. Each record contains a count showing how many records in the table being summarized have this value. Each record also contains the result of any summary statistics you requested in the Summarize dialog box, such as the sum, minimum, maximum and standard deviation for any other field in table. Example: ? Looking at the table associated with Counties layer, you will see a field called Addname. This field is the Area Development District that the county is a member of. ? RIGHT CLICK the Addname field and select Summarize (Figure 17). ? You should see the dialog box in Figure 18. - 9 - Figure 17: Summarize Figure 18: Summarize Dialog Box ? This will create a new .dbf table. Be sure to give it a name that is useful in recognizing it later. The name give in the example is add_summary Be sure to navigate to an appropriate directory, CLICK on the Browse Button so that you can access the summary table at a later time and set the Save as type to dBASE Table (figure 19). The new table will open (Figure 20). If you did not add any field calculations, the table will just have two fields, ADDNAME and Count_ADDNAME. The first lists each unique Figure 19: Summary Table Definition Dialog Box - 10 - entry in the table, while Count reports how many times each unique item appears. In the example, the Barren River ADD has 10 counties and the Big Sandy ADD has 5 counties, etc. Joining Tables: One common task in database management it joining tables. ArcGIS makes a distinction between relating and joining tables. Read the two help files about Relating Tables and Joining Tables. In most situations in this class, we will be joining tables. The table that receives data is the destination table, while the other table is called the source table. The destination table is most often the table associated with geospatial data layer while the source table is a table of attributes only. In order to joins one table to another, you will have to select a field to use as the join item. The name of this join item does not matter, the only restriction being it should be the same item type in both tables (i.e. number, text. etc). Figure 20: Add_summary table - 11 - Example: Open the table associated with Madison_water_wells (Figure 21). This geospatial data layer details all of the water wells in Madison County. Notice that there is very limited number of attributes associated with the data layer. The attributes include an ID number (Kgs_id) and the coordinates of the well locations (North_lati and West_longi). If you wanted to use this data layer in some analysis, you might need additional attributes. These are provided in a .dbf file named well_data. Add the well_data table, in the Lab_05 folder, to your table of contents using the ADD DATA button. If you do not see the table listed in the table of contents, switch from Display to Source view for the table of contents (Figure 22). Figure 21: Attributes of Madison_water_wells Figure 22: Source mode - 12 - Open the well_data, RIGHT CLICK ? Open, table as you would any other table associated with a geospatial data layer (Figure 23). Figure 23: Well_data.dbf table Notice there are a number of additional attributes such as Quadrangle, primary_us, total_dept etc.. You will now join this non-spatial attribute table to the spatial data. Steps in joining tables ? Remember, in order to join two tables, each table must have a field that is the same data type. You join numbers to numbers, strings to strings, and so on. Look at the Madison_water_wells table and the well_data table and notice the field KGS_ID (Figure 24). This is an ID from the Kentucky Geological Survey. You can use this field to join the two tables together. Figure 24: Common field used in joining table - 13 - ? RIGHT CLICK on the Madison_water_wells layer and select Joins and Relates ? Join… (Figure 25). ? Use the following settings in order to join the well_data table to the table associated with the Madison_water_wells geospatial data layer (Figure 26): o What do you want to join to this layer? ? Join attributes from a table o Choose the field in this layer that the join will be based on: ? KGS_ID o Choose the table to join to this layer…? well_data o Choose the field in the table to base the join on ? KGS_ID o CLICK OK You have how added non-spatial data to your spatial data so that you can conduct further analysis. Figure 25: Joining tables Figure 26: Join data dialog box