...
Info |
---|
The following step-by-step instructions and screenshots are based on the Windows 7 10 operating system with the Windows Classic desktop theme and ArcGIS Pro 3.2.1.3 0 software. If your personal system configuration varies, you may experience minor differences from the instructions and screenshots. |
...
- On the Desktop, double-click the Computer icon > gisdata This PC > GISData (\\file-rnassmb.rdf.rice.edu\research\FondrenGDC) (RO:) > > GDCTraining > 1_Short_Courses > > Mapping_Tabular_Data.
- To create a personal copy of the tutorial data, drag the Tabular folder onto the Desktop.
- Close all windows.
...
- Click Tabular.zip above to download the tutorial data.
- Open the Downloads folder.
- Right-click ProjectionsTabular.zip and select Extract All....
- In the 'Extract Compressed (Zipped) Folders' window, accept the default location into the Downloads folder and click Extract.
- Drag the unzipped Tabular folder onto your Desktop.
- Close all windows.
...
You will now add a feature class to a new map that depicts all of the land parcels in downtown Houston. This feature class was originally obtained from the Harris County Appraisal District (HCAD) Public Data GIS website at http://pdata.hcad.org/GIS/index.html. The original parcels shapefile contained all of the parcels in Harris County, but working with such a large file can be time consuming, so, for the purposes of this tutorial, the layer was clipped to contain only those parcels in downtown Houston.
...
The HCAD_NUM field contains the unique 13-digit HCAD account number for each parcel. You are also provided with the owner name and address for each parcel, but no other information. Fortunately, a tremendous amount of information about the parcels can be obtained from HCAD in standalone tabular format. In this case, you would like to know the particular land use for each parcel, such as residential, commercial, or industrial. This information is contained in a separate Land table, which was originally obtained from the HCAD Public Data Download website at the the http://pdata.hcad.org/download/index.html.
...
- Close all table views.
- Above the ribbon, on the Quick Access toolbar, click the Save button.
Joining Tabular Data
- button. (Note: if using a newer version of ArcGIS Pro than version 2.1.0, then you may receive a warning about version incompatibility here. You can confirm the save without any issues.)
Joining Tabular Data
Now that you have examined all of the data tablesNow that you have examined all of the data tables, you are ready to join them all together. It is possible to join tables together in any order, but you will want to join both land use tables to the back of your DowntownParcels attribute table, so that you can later symbolize the parcels based on their land use. You will first join the Land table to the DowntownParcels feature class using the unique HCAD account number as the common join field.
- In the Contents pane, right-click the DowntownParcels layer name and select Joins and Relates > Add Join.
The Geoprocessing pane should open on the right in a new tab overlaying the Catalog paneA tool titled Add Join should pop up on the screen. In the Add Join tool, notice that 'Layer Name or Input Table View' is already set to the DowntownParcels layer, since that was the layer used to open the tool.
- For ‘Input Join Field’, use the drop-down menu to to select the HCAD_NUM field.
- For ‘Join Table’, use the drop-down menu to select the Land table.
- For ‘Output Join ‘Join Field’, use the drop-down menu to to select the ACCOUNT field.
- Ensure that the ‘Add Join' tool settings match those shown below and click Run OK at the bottom of the Geoprocessing panetool.
- In the Contents pane, right-click the DowntownParcels layer and select Attribute Table.
- Scroll to the right and browse through all fields.
...
- In the Contents pane, right-click the DowntownParcels layer and select Joins and Relates > Add Join.
- For ‘Input Join Field’, use the drop-down menu to to select the Land.LAND_USE_C field. If that field does not appear in the list, you may have skipped the step above and are still viewing the previous run of the Add Join tool, which has not been refreshed to load the newly joined field names.
...
- field names
...
- .
- For ‘Join Table’, use the drop-down menu to select the LandUseCode_LookupTable table.
- For ‘Output Join ‘Join Field’, use the drop-down menu to select the LANDUSE_CODE field.
- Click RunOK.
- Once the join is complete, scroll to the far right in the DowntownParcels attribute table.
...
In order to successfully perform a join, the corresponding join fields in both tables must be of the same field type. Unlike in Excel, you cannot simply convert an existing field from one type to another. Instead, you must create a new field of the correct field type and copy the data over into the new field. In this case, you will convert the LAND_USE_C field in the Land table to a text field type to match the text field type used in the LandUseCode_LookupTable table.
- In the Catalog Contents pane, right-click the Land table and select Design > Fields select Open to open the attribute table.
- At the top right of the Land table view, click the collapsed menu icon (or hamburger button) and select Fields View.
- At the bottom of the attribute table, click ‘Click here to add a new field.’
- For the new ‘Field Name’, type “LAND_USE_C_Text” and press Enter.
- For ‘Data Type’, click once to highlight Long and activate the field and then click Long a second time and use the drop-down menu to select Text instead.
- On the ribbon, the Fields tab should now be active. In the Changes group on the far rightManage Edits group, click the Save button to save the changes to the design of the Land table fields.
...
- Close both table Fields: views.
- In the Catalog pane, right-click If not already selected, navigate to the Land attribute table and select Open.
- Right-click the LAND_USE_C_Text field name and select Calculate Field.
On the right, in the Geoprocessing pane, the The Calculate Field tool will openshould pop-up on the screen.
- In the 'Fields' list under the 'Expression' subsection of the tool, double-click the LAND_USE_C field.
Notice that the box below now reads LAND_USE_C_Text = !LAND_USE_C!, as shown below. This statement will copy all of the content from the original LAND_USE_C integer field into the new LAND_USE_C_Text text field.
- Click Run OK.
In the Land table view, ensure that the land use codes have, indeed, been copied over into the new LAND_USE_C_Text field and that they are left-aligned as text, rather than right-aligned as numbers.
...
- In the Contents pane, right-click the DowntownParcels layer name and select Joins and Relates > Remove Join.
In the Geoprocessing pane, the The Remove Join tool will openpop up on the screen.
- For 'Layer Name or Table View', leave the default DowntownParcels layer selected.
- For 'Join', use the drop-down menu to select the LandUseCode_LookupTable table.
- Click Run OK.
You will now repeat the process of joining the lookup table.
- In the Contents pane, right-click the DowntownParcels layer and select Joins and Relates > Add Join.
- For ‘Input Join Field’, select the new Land.LAND_USE_C_Text field.
- For ‘Join Table’, select the LandUseCode_LookupTable table.
- For ‘Output Join Field’, select the LANDUSE_CODE field.
Ensure that the ‘Add Join’ pane matches that shown below and click OK. - If your DowntownParcels table view is not already open, right-click the DowntownParcels layer name and select Attribute Table.
- In the DowntownParcels table view, scroll to the right and browse through all of the attributes.
...
- Right-click the DowntownParcels layer and select Data > Export Features.
- For ‘Output Feature Class’, click the Browse button.
- Double-click the Tabular.gdb geodatabase.
- For 'Name', type “DowntownParcels_WithLandUse” and click Savetype “DowntownParcels_WithLandUse”.
- Click Run OK.
- Right-click the DowntownParcels_WithLandUse layer name and select Attribute Table.
...
- In the Contents pane, right-click the DowntownParcels_WithlandUseWithLandUse layer name and select Symbology.
- In the Symbology pane that opens over the Catalog pane, use the 'Symbology' drop-down menu to select Unique Values.
- For ‘Value field’‘Field 1’, use the drop-down menu to to select the GROUP_DSCR field.
- To the top right of the tabular list of symbols, values, and labels, click the More drop-down menu and uncheck Show all other values, since you will be symbolizing all values.
...
- In the table, click the Commercial value to select it.
- Hold down Ctrl and click the Office value to select it too.
- Right-click the Commercial value and select Group values to combine the two categories into one.
- In the Contents pane, right-click each symbol patch individually and select from the row indicated below.
- Select the colors commonly used by planners to indicate the various represent each land usesuse, as displayed below.
- Above the ribbon, on the Quick Access toolbar, click the Save button.
- Minimize ArcGIS Pro.
...
In this exercise, you will be mapping land cover by state. The original data table was obtained from The the 2011 Statistical Abstract published by the U.S. Census Bureau at http://www.census.gov/compendia/statab/. The particular table was accessed from the “Geography & Environment” section under the “Land, Land Use, and Earthquakes” category and was called table “265, Land Cover/Use by State: 2003”. You will now format this table for import into ArcGIS.
- Double-click the Tabular folder on the desktop Desktop.
- Double-click LandCoverByState to open the file with Excel.
...
- Click and hold row 3 and drag the mouse up past the top of the spreadsheet to select rows 1 through 3.
- Right-click row 3 and select Delete.
...
Ensure that you are left with the five necessary columns previously mentioned unselected.
- Right-click column P and select Delete.
...
Hover your cursor over the small black box in the bottom right corner of the highlighted cell as shown below. Notice that your cursor changes from a thick white cross to a thin black cross.
- Double-click the black box in the bottom right of cell F2 to copy the formula down the table.
...
- In cell G2, use the same formula building techniques to enter “=D2/(B2-E2)*100” and copy the formula down the table.
One last problem with this worksheet, which is difficult to detect, is that there is actually a space stored after every state name. This space will prevent the table from joining properly to another table whose state names do not have a trailing space. You will now perform a series of automated operations use the TRIM function to remove this extra space.
...
The trim function removes all extra spaces from text, leaving only a single space between words and no spaces at the start or end of the text.
- Right-click column D B and select Insert to add three a new columns column in which to work.
First, you will use the Text to Columns tool, which splits the information contained in one column into multiple columns based on a delimiter. The delimiter is the character which is used to signal when to shift the data to the next column. As a result, the delimiter itself is removed and all characters following the delimiter are shifted to the next column. In this case you will use the space as the delimiter to remove it.
- Click column A.
- Click the Data tab at the top of the Excel window.
- Under the Data Tools section, click the Text to Columns button.
- In the ‘Convert Text to Columns Wizard’, click Next >.
- In the Delimiters box, uncheck Tab and check Space and click Next >.
- Click Finish.
- When asked if you want to replace the contents of the destination cells, click OK.
The extra spaces have been removed, but the state names with two words have been separated into two columns. You will need to concatenate them back together into a single column separated by a space, but only for those states with two words. Performing a concatenation on all the states with an added space in between the two words would end up adding the trailing space again. The formula below basically says, if column B is blank, then copy the state name from column A; otherwise, if column B contains text, then concatenate the first part of the state name in column A with the second part of the state name in column B and add a space in between them.
- In cell C2, use your formula building techniques to enter “=IF(B2="",A2,CONCATENATE(A2," ",B2))”. Ensure that there is no space between the first set of quotation marks and a single space between the second set of quotation marks.
In this instance you can’t double-click the black box in the bottom right of cell C2 to copy the formula down, because there is not consistent data in the column the left of it.
- Click and hold the black box in the bottom right of cell C2 and drag down to cell C49 to copy the formula down the table.
At this point it looks good, but the values contained in column C are actually formulas, not text, and ArcGIS will not be able to interpret the formulas.
- Right-click column C and select Copy.
- Right-click column D and select Paste Special….
- In the ‘Paste Special’ window, select to Paste Values and click OK.
Now the states listed in column D are stored as text, rather than as formulas, so you can now delete columns A through C.
- In cell B2, type “=” to indicate the start of a formula.
- Type “trim(”.
- Click cell A2 (Alabama).
- Type “)” (close parenthesis).
- Ensure your cell says “=trim(A2)” and press Enter to finish the formula.
- Copy the formula down the table
- Click column A.
- Hold down Shift and click column C.
- Right-click column C and select Delete.
- In cell A1, type “State”.
Removing all formatting in Excel
At this point your table has everything you need and nothing extra (except for a duplicate state name field, which will be deleted shortly), but it is always recommended to clear all formatting and programming , including special fonts, background colors, borders, formulas, etc., before bringing the Excel file into ArcGIS. before bringing the Excel file into ArcGIS. In addition, columns B, G, and H currently have formulas, which need to be converted to text before ArcGIS can read them properly. To get rid of all formatting and programmingformulas, you will copy all of the data into a new blank worksheet as text.
- Click the Select All button in the top left corner of the spreadsheet.
...
- In the Home tab, click Copy.
...
- Click the File tab and select New.
- Ensure that Click Blank Workbook is selected and click Create.
- In the new workbook, right-click cell A1 and select Paste Special….
- Select to paste Values and click OK.
You should now have a clean version of your data with no special formatting. The last step is to Next you will expand the cells to ensure that all of their content is displayed.
- Click column A.
- Hold down Shift and click column G.
Ensure that all data is still selected from being pasted. Hover your cursor over one of the lines between any two of the column letters. Notice that your cursor changes from a single arrow pointing down to a double arrow pointing left and right.
- Double-click the dividing line between column A and B.and B.
The width of all your columns should now be expanded to show all of the content of the cells.
Finally, you will delete the original State column, which still contains trailing spaces.
- Right-click column 1 and select Delete.
- In the new cell A1, type "State" to fill out the missing field name.
You The width of all your columns should now be expanded to show all of the cells’ content. You are finished formatting your table.
- Click the File tab and select Save As.
- Click the Browse button.
- Navigate to the TabularTutorialData folder on the Desktop.
- For ‘File name:’, type “US_LandCover”.
- In the 'Save As' window, on the left, click Desktop.
- On the right, double-click the Tabular folder on the Desktop.
- For ‘File name:’, type “US_LandCover”For ‘Save as type:’, use the drop-down box to select Excel 97-2003 Workbook.
- Click Save.
- Close Excel.
Setting Up a New Map
...
in an Existing
...
Project
Now you will return to ArcGIS to map the land cover data by state.
- Maximize ArcGIS Pro.
Because you already have the map document from the prior exercise set up with the correct default database, you will use this map document as a template, rather than starting a new one.
- At the bottom of the right pane, click the Catalog tab.
- In the Catalog pane, within the Tabular geodatabase, right-click the US_States feature class and select Add To Click the Insert menu and select New Map.
This creates a new map in the project in which you will map your state land cover data. You will now save this map with a different name, so as to distinguish this exercise from the last one.
- In the Catalog pane, double-click the expand the Maps folder and .
- Rightright-click Map2 Map1 and select Rename.
- Select Rename and change the File name to “USLandCover”.
You will now explore the state data.
...
- Type “US Land Cover” and press Enter.
You will now explore the state data
...
.
- In the Contents pane, right-click the US_States layer and select Attribute Table.
Examine the attribute table for the US_States layer and notice that the field containing the state names is “NAME10”.
- Close the attribute US_States table view.
- In the Analysis tabAt the bottom of the right pane, click the Tools icon Geoprocessing tab.
- At the top left of the Geoprocessing pane, click the Back button.
- In the 'Find Tools' search bar In the Geoprocessing pane on the right, click within the Find Tools search bar and type "excel".
- Click Excel to Table (Conversion Tools).
- For the Input Excel file, click the Browse button to the right and navigate to the TabularTutorialData. Select USLandCover. to your Tabular folder on the Desktop. Select USLandCover.xlsx and click Open.
It will take a moment for the table to load. Note that the 'Sheet' drop-down menu has appeared, and the default sheet selected is 11s0365is now populated and Sheet1 is selected by default. Because a single Excel file can have multiple worksheets and each worksheet is treated in ArcGIS as a separate table, you must always specify which specific worksheet you want to use.
- Rename the Output Table "US_LandCover".
- Make sure Ensure the Geoprocessing pane looks like the picture appears as shown below and then click Run at .
- At the bottom right corner of the pane.
- In the Catalog right pane, double-click the Folders folder to show the TabularTutorialData folder Catalog tab.
- In Within the Contents pane Tabular geodatabase, right-click the newly created US_LandCover standalone table and select Open Add to Current Map.
Examine the US_LandCover table and notice that the field containing the state names is “State”. Using your knowledge from the first exercise, try to join the US_LandCover worksheet to the US_States feature class and symbolize the states by both the percent developed land and the percent forest land. If you get stuck, ask for help.