Amherst College I.T. I.T. home. Amherst College.
I.T. home.
IT > Software > Geographic Information Systems > Mapping Place-Name Data

Geographic Information Systems

Mapping Place-Name Data

Previous: Constructing and Sharing Maps
 
Following: Mapping Coordinate Data


Not all geographic data is in a GIS-ready format. Commonly it is in the form of a table of values assigned to place names representing geographic features such as states or street addresses.

Topics

Procedures

  1. Joining a Table to a Layer

  2. Saving a Copy of a Data Set

  3. Downloading Census Data from the US Census Bureau

  4. Making a Census Excel File Compatible with ArcGIS

  5. Determining Map Layer Attribute Types

  6. Formatting Excel Data for Compatibility with ArcGIS

  7. Formatting Excel Cells for Compatibility with ArcGIS

  8. Making an Excel File Compatible with ArcGIS

  9. Geocoding a Table of Street Addresses


Getting Started

Since this tutorial will be using specific maps and data, the first step is to make your own copy of the tutorial data.

Set Up: Getting the Tutorial Data

  1. In the Windows Explorer, navigate to the network drive  K: (aka \\Software\Winsoft), open the folder  Maps, and then open the folder  Introduction to GIS.
  2. Drag the folder  MappingPlaceNames and its contents to either:
    1. your network drive  U:, e.g. into the folder  My Documents; or
    2. the local hard drive  C:, e.g. onto your Desktop.

The folder  MappingPlaceNames contains the following files:

 states.shp  stateinfo.xls  MASSCENSUS2010TRACTS.shp  HolyokeSchools.xls

Since some uses of ArcGIS require names without spaces or special symbols, do not rename the folders or files.

Set Up: Initializing ArcMap and Adding the States Layer

  1. Start up the  ArcMap software (see Constructing and Sharing Maps for details).
  2. In the toolbar Standard, click on the button Add Data Icon Add Data.
  3. In the dialog Add Data, navigate into the folder  MappingPlaceNames; if necessary, make a new connection to it first (see Constructing and Sharing Maps for details).
  4. In the folder  MappingPlaceNames, click on the file  states.shp.
  5. Click on the button Add.

ArcMap will now display the map of the United States that we saw before:

As before, it’s a good idea to save your map early and often.


Joining Tabular Place-Name Data

When data is associated with place names, we can join it to an existing layer providing the geography.


Place-Name Data

Geographic data ideally comes in the form of a layer, which is a set of geographic features with attributes assigned to them. We’ve already seen examples of state polygons and city points, whose attribute tables associate these features with information such as area, population, etc.

Layers also include information about the geography of the features, so they can be immediately displayed as a map, as above. Recall that the details of the geography are hidden inside the Shape field:

Very often, however, you may obtain or create a table of data whose only geographic connections are a set of place names that abstractly represent the geographic feature, e.g. “Massachusetts”, “Alabama”, etc. In other words, it doesn’t have a Shape field:

Such place-name data is also known as toponymic data.

Fortunately, if you have access to a map layer that defines the same geographic regions with the same names, you can join your table to that layer. Joining essentially extends its attribute table with new fields (columns), which you can then use to symbolize it, etc.

Exercise: The Structure of an ArcGIS-Compatible Table

  1. In the Windows Explorer, navigate into the folder  MappingPlaceNames.
  2. Locate the file  stateinfo.xls, which is a Microsoft Excel workbook, and double-click on it to open it in Excel.
  3. Examine the workbook’s structure:
    • The simple rectangular arrangement of the data;
    • The initial row containing the attribute names;
    • Subsequent rows each containing the data of one feature (called a record);
    • Each column containing a single attribute as it varies from feature to feature (called a field).
    • The worksheet names, e.g. Dates, in the tabs at the bottom of the window. You can have many worksheets in the same workbook.
  4. Close the workbook.

Besides Microsoft Excel XLS or XLSX files, ArcGIS can also read tables in several other formats:

CSV
Comma-separated values files are simple text files with each field in the table separated by commas (if the data includes commas, the field value must be enclosed in double quotes).
TXT or TAB
Tab-separated values files are also simple text files, with each field in the table separated by tabs.
DBF
dBASE files are an old database format that are still used for Shapefile attribute tables.
MDB
Microsoft Access files are a newer database format.

All of these formats can also be read by Microsoft Excel (but it will only open TAB files directly if the extension is changed to TXT).

If you have data in these formats and are going to be making any changes with Excel, it’s generally better to first save it as XLSX; otherwise Excel will always complain about potential data loss when you save.

Warning: To successfully make use of tables, their file names should start with letters and afterwards include only letters, numbers, and underscores.


Joining Tables with a Place Name Attribute

Because the map layer and table shown above have a matching place name attribute:

  • the map layer  states.shp and its field STATE_NAME, and
  • the table  stateinfo and its field State.

they can be joined together, row by matching row.

Procedure 1: Joining a Table to a Layer

Any map layer can have a table joined to it, as long as they have an exactly matching attribute that can be used to relate data for each feature.

  1. In  ArcMap, in the toolbar Standard, click on the button Add Data Icon Add Data.
  2. In the dialog Add Data, navigate into the folder with the table to be joined, e.g.  MappingPlaceNames.
  3. Double-click on the table to be joined, e.g the Excel workbook  stateinfo.xls.
  4. Adding an Excel WorksheetIn ArcGIS dialogs, Excel workbooks are workspaces, meaning you can open them like folders and see an overview of their contents.

    In particular, they display their worksheets and named regions (discussed below) as separate “files”.

    Worksheets all have a $ at the end of their names, as in the image at the right.

    If the join table is inside a workbook, double-click on the latter to see its contents, and then add the desired table to your map document by double-clicking on it, e.g.  Dates$.
  5. After adding a table, the Table of Contents will switch to the  List by Source view; the reason is that a table by itself is not displayable on the map, and therefore won’t show up in the  List by Drawing Order view. You wil probably want to switch back to the latter, though, since it’s simpler.
  6. Join Data DialogIn the Table of Contents, right-click on the layer to be joined, e.g.  states.shp.
  7. The layer’s contextual menu will now appear; select the menu item Joins and Relates, followed by the menu item Join....
  8. In the dialog Join Data, in the menu What do you want to join to this layer?, make sure that Join attributes from a table is selected.
  9. In the menu 1. Choose the field in this layer that the join will be based on:, select the matching field in the map layer, e.g. STATE_NAME.
  10. In the menu 2. Choose the table to join to this layer, or load the table from disk:, select the table to be joined, e.g.  Dates$.
  11. In the menu 3. Choose the field in the table to base the join on:, select the matching field in the table to be joined, e.g. State.
  12. Click on the button OK.

Alternative: Rather than use the “internal join” described above in Steps 1-5 and 10, you can directly join an external table in Step 10 by clicking on the button Document Open Browse; this is a slightly easier one-step process.

However, this “external join” has the disadvantage that you won’t get error messages describing incompatibilities in your table. It also obscures the location of the table from the ArcMap user, and if the map document loses track of it you can’t see its path to help you find it again to fix the join.

To see the results of the joining tables procedure, try out the new attributes:

Exercise: Working with a Joined Table

  1. In the Table of Contents, right-click on the layer  states.shp and select the menu item  Open Attribute Table.


     
  2. Examine the attribute table’s structure: the fields from the shapefile are now immediately followed by the fields from the joined table.
  3. Right-click on one of the field headers, e.g. State, to bring up its contextual menu, and select the menu item Properties… .
  4. In the dialog Field Properties, note that the visible field name is just an alias, and the actual field name begins with the table name, e.g. Date$, followed by a period.

    This field-name prefix ensures that there are no conflicts between fields from the joined tables that might have the same name.

    Note that Alias: is editable, so you can use it to change a field name if you think it could be more descriptive; it has fewer restrictions on length and characters than the actual name.
  5. Close the dialog Field Properties and then the attribute table.
  6. Symbolize the layer with one of the quantitative fields from the joined table.
  7. Save the document by clicking on the button  Save.

Important Note: The additional fields from the joined table have not been permanently added to the map layer, just temporarily linked to it.

This means that if you move the table file or the map document to some other location, the map may no longer work because ArcMap could be looking in the wrong place for the table file.

When ArcMap can’t find a file, it places a red exclamation point ! next to its name in the Table of Contents, and you can click on ! to start a dialog to relink it.

Once you’ve joined a layer and a table, sometimes you may want to save the result as a new shapefile with a merged attribute table:

Procedure 2: Saving a Copy of a Data Set

  1. In ArcMap Icon ArcMap, in the Table of Contents, right-click on the name of the layer, e.g.  states.
  2. In the layer’s contextual menu, point at the menu item Data, then in the submenu that appears click on the menu item Export Data….
  3. In the dialog Export Data, in the menu Export, make sure that All features is selected.
  4. Near the text field Output shapefile or feature class:, click on the button Document Open Browseto navigate to an appropriate location for the new data set, e.g. the folder MappingPlaceNames, and give it a descriptive name, e.g. states_and_dates.shp. Remember that file names should start with letters and afterwards include only letters, numbers, and underscores
  5. Click on the button OK.
  6. The dialog ArcMap will now appear, asking if you want to add the exported layer to the map; click Yes or No.

One advantage of using joins is that you can more easily change the contents of the joined table (there will be more about editing from within ArcMap later).

However, many applications such as Excel won’t allow you to edit the table if ArcGIS opened it first.

If you aren’t changing the join attribute name, the simplest thing to do is quit ArcMap, edit the table, and reopen the map; the table will be rejoined as previously defined, with the new data.

Otherwise, you’ll need to unjoin the table, remove it from ArcMap (if an internal join), edit it, re-add it to ArcMap, and rejoin it.


Mapping Census Data

An important example of data that is commonly joined to map layers comes from the U.S. Census Bureau.


Downloading Census Data

As another example of commonly available place name data, we’ll work with some more census data. As you probably already know, every ten years there is a census that tries to obtain basic information from 100% of the U.S. population; this is the primary source of the data we looked at previously in the states layer. In addition, the U.S. Census Bureau provides many variations of this data, as well as the results of the annual American Community Survey that tracks detailed information from a small subset of the population. This data is in tables that you must join to existing layers.

We’ll also use a type of geographic region that you might not be familiar with, census tracts. According to the Bureau, “Census tracts are small, relatively permanent statistical subdivisions of a county....Designed to be relatively homogeneous units with respect to population characteristics, economic status, and living conditions, census tracts average about 4,000 inhabitants.”

Two smaller subdivisions of census tracts are also available, though we won’t use them here. Census blocks are “the smallest geographic unit for which the Census Bureau tabulates 100-percent data....Many blocks correspond to individual city blocks bounded by streets, but blocks — especially in rural areas — may include many square miles.” Census block groups are just that, and are the smallest region available for some sensitive attributes such as income.

Set Up: Adding a Census Tract Layer

  1. In  ArcMap, click on the button  New Map File.
  2. Click on the button Add Data Icon Add Data.
  3. In the dialog Add Data, navigate into the folder  MappingPlaceNames.
  4. In the folder  MappingPlaceNames, click on the file  MASSCENSUS2010TRACTS.shp.
  5. Click on the button Add.

ArcMap will now display a map of the Year 2010 census tracts in Massachusetts:

If you right-click on the layer  MASSCENSUS2010TRACTS and select the menu item  Open Attribute Table, you should see the following:

The census tracts are uniquely named by their FIPS (Federal Information Processing Standards) code, which begins with the two-digit state code in the field STATEFP10 (e.g. 25), followed by the three-digit county code in the field COUNTYFP10 (e.g. 001), and finally the tract code in the field TRACTCE10 (e.g. 015300). By combining these three together into a text string in the field GEOID10 (e.g. 25001015300), we have a unique identifier that can be used to join other census data to this layer.

Be aware that census tracts change from decade to decade. The Census Bureau provides cartographic boundary shapefiles for its online data (including other regions such as congressional districts and metropolitan statistical areas) at http://www.census.gov/geo/www/cob/. (This particular data set actually comes from MassGIS.)

The Census Bureau also lets you download much of their data from http://www.census.gov/. As an example, we’ll map some sampled poverty data from the latest census, 2010, which is available through the second version of the American FactFinder.

Procedure 3: Downloading Census Data from the U.S. Census Bureau

  1. If necessary, start up a web browser:
    1. Click on the menu Start Menu Icon Start.
    2. Point at the menu item All Programs.
    3. Locate your preferred web browser,  Chrome or  Firefox or  Internet Explorer (perhaps in the folder Networking and Communications), and click on it.
  2. In your web browser, visit the web address www.census.gov.
  3. On the top of the web page Census.gov, point at the menu Data to let it drop down its menu, then point at the link Data Tools and Appsto let it display a secondary list of links, and finally click on the link American FactFinder.
  4. The web page American FactFinder has several useful links that let you explore census data on-line. In particular, the section What We Provide leads to general information about the Census Bureau’s data and how it’s collected and structured.

    To get to the data, click on the link Advanced Search.
  5. On the left side of the web page American FactFinder – Search there are several ways to access census data: by topic, by geography, by race and ethnic groups, etc.:
  6. American FactFinder

    At some point you must specify at least geographies and topics, and from the latter the year of interest (more specifically the dataset).

    In this example we will start by clicking on the button Geographies.

  7. Hampden County with Census TractsGeography must be described in two steps, by specifying:
    • The geographic type, the unit for which data will be summarized, for example the census tracts in the image at the right; and
    • The geographic area, a larger “containing” geography within which the above data is located. It is generally specified by place name, for example Hampden County at the right.

    In the dialog Select Geographies, click on the tab List (if necessary):

    American Factfinder Geography List

  8. In the menu Select a geographic type:, select, for example, Census Tract.

    Note that not all available geographies are automatically listed here; to see census blocks, for example, you must first click on the button all geographic types.
  9. In the menu Select a state:, select, for example, Massachusetts.
  10. In the menu Select a county:, select, for example, Hampden (this is the county south of Amherst, containing Springfield, Chicopee, and Holyoke).
  11. In the menu Select one or more geographic areas…:, select, for example, All Census Tracts within Hampden County, Massachusetts.
  12. Click on the button ADD TO YOUR SELECTIONS.
  13. Census SelectionsClick on the gray button Close X, which will return you to the web page American FactFinder – Search.

    Note the box Your Selections in the upper left corner of the web page, showing your geographic type and geographic area.
  14. Now you can browse the list of available data sets for this geography, but to find a specific type of census data, it’s more useful to click on the button Topics.
  15. In the dialog Select Topics, click on, for example, People:

    American Factfinder Select Topics PovertyAmerican Factfinder Select Topics Dialog

  16. In the expanded list under People, click, for example, the item Poverty:

  17. In the expanded list under Poverty, click on the blue item named Poverty (449).

    The number in parentheses indicates the number of related tables of information that will be shown.
  18. Click on the gray button Close X, which will return you to the web page American FactFinder – Search.

    Note the box Your Selections in the upper left corner of the web page, which now also shows your topic choice:

    American Factfinder Selections 2
  19. In the area Search Results:, filter the available data with the menu Show results from:, selecting the most recent year listed that has data sets, e.g. 2013 (73).
  20. Locate the list item POVERTY STATUS IN THE PAST 12 MONTHS, table S1701, which is noted as being from the American Community Survey 5-Year estimates.

    Click on the text link (not the checkbox).
  21. You will now be switched to the web page American FactFinder – Results, where you can see the data and scroll through it.

    Click on the button  Download.
  22. In the dialog Download, in the area Comma delimited (.csv) format (data rows only), make sure that the button Data and annotations in a single file is selected.
  23. Click on the checkbox  Include descriptive data element names.
  24. Click on the button OK.
  25. In the next dialog, wait for the message Your file is complete, and then click on the button DOWNLOAD.
  26. The data will be downloaded as a single compressed file named something like ACS_13_5YR_S1701.zip.

    Locate this file, open it, and drag its contents to your folder  MappingPlaceNames.

    The ZIP file contains four files; the actual census data is in the file ACS_13_5YR_S1701_with_ann.csv; the other two provide descriptive information about the data, which is known as metadata.

You may have noticed in this last procedure that the Census Bureau only has data online starting in 2000. If you want data and boundary files from earlier years, visit the National Historical Geographic Information System, which provides a similar system.

Procedure 3.5: Downloading Census Data from the National HIstorical Geographic Information System

  1. If necessary, start up a web browser:
    1. Click on the menu Start Menu Icon Start.
    2. Point at the menu item All Programs.
    3. Locate your preferred web browser,  Chrome or  Firefox or  Internet Explorer, and click on it.
  2. In your web browser, visit the web addresshttp://www.nhgis.org/.
  3. In the upper right corner of the web page Welcome to NHGIS, click on the link Login.
  4. On the web page Login, log in with an existing account or create a new one and log in with it.
  5. Back on the web page Welcome to NHGIS, on the left side there are a number of useful links that provide general information about the data provided by NHGIS, including a User’s Guide.
  6. American FactFinderTo get to the data, click on the link Select Data.
  7. On the web page NHGIS data finder there are several ways to access census data: by geographic level, by years, by topic, by datasets (see the image at the right).

    At some point you must specify all of these, but in this example we will start by clicking on the button Geographic Levels.
  8. Hampden County with Census TractsGeography must be described in two steps, by specifying:
    • The geographic level, the unit for which data will be summarized, for example the census tracts in the image at the right; and
    • The geographic area, a larger “containing” geography within which the above data is located. It is generally specified by place name, for example Hampden County at the right. In NHGIS you specify the geographic area later.

    In the dialog Geographic Levels, select one or more levels, e.g. Census Tract:

    American Factfinder Geography List

    Your selection(s) appear in the section Selected Geographic Level Filters at the top of the dialog.

  9. Click on the button Submit.
  10. A very, very long list of matching data sets appears:

    NHGIS Data Finder Geography-Based Tables List

    To filter the list further, click on the button Years.
  11. In the dialog Years, select one or more years, e.g. 1970.
  12. Click on the button Submit.
  13. Your selection(s) in the section Selected Geographic Level Filters at the top of the web page will now be updated with the year, and the list of matching data sets will be substantially filtered:

    NHGIS Data Finder Geography and Year-Based Tables List


    To filter the list further, click on the button Topics.
  14. In the dialog Topics, in the list on the left side, click on a general subject area, e.g. Population , and then click on a particular subject , e.g. Income – Poverty.

    Census Data Finder Topics

    Your selection(s) appear in the section Selected Topic Filters at the top of the dialog.
  15. Click on the button Submit.
  16. Click on the gray button Close X, which will return you to the American FactFinder Search window.

    Note the box Your Selections in the upper left corner of the web page, which now also shows your topic choice:

    American Factfinder Selections 2
  17. In the area Search Results:, click on the list item POVERTY STATUS IN THE PAST 12 MONTHS, which is noted as being from the American Community Survey 5-Year estimates.
  18. You will now be switched to the American FactFinder Results window. Click on the button  Download.
  19. In the dialog Download, in the area Comma delimited (.csv) format (data rows only), make sure that the button Data and annotations in a single file is selected.
  20. Click on the checkbox Include descriptive data element names.
  21. Click on the button OK.
  22. In the next dialog, wait for the message Your file is complete, and then click on the button DOWNLOAD.
  23. The data will be downloaded as a single compressed file named something like ACS_13_5YR_S1701.zip; locate this file, open it, and drag its contents to your folder  MappingPlaceNames. The ZIP file contains three files; the actual census data is in the file ACS_13_5YR_S1701_with_ann.csv; the other two provide descriptive information about the data, which is known as metadata.
  24. In the menu Select a state:, select Massachusetts.
  25. In the menu Select a county:, select Hampden (this is the county south of Amherst, containing Springfield, Chicopee, and Holyoke).
  26. In the menu Select one or more geographic areas…:, select All Census Tracts within Hampden County, Massachusetts.
  27. Click on the button ADD TO YOUR SELECTIONS.
  28. Census Selections

Joining Census Data

Once you’ve downloaded census data, you’ll need to clean it up to make it compatible with ArcGIS.

Procedure 4: Making a Census Excel File Compatible with ArcGIS

  1. Double-click on the file ACS_13_5YR_S1701_with_ann.csv to open it automatically in  Excel and inspect its contents:
  2. Census Data in Raw Form

    The first rows have the annotations mentioned in the previous procedure, with the actual data beginning in row 3. To easily see the annotations, you have to either make the columns wider or make the rows higher and let the text wrap. For the latter:

    1. Select row 2 by clicking on the row header (i.e. the numbered box 2);
    2. Right-click on any one of them, and in the contextual menu that appears select  Format Cells…;
    3. In the dialog Format Cells, click the tab Alignment (if necessary), and in the area Text control check on Wrap text;
    4. Click the button OK and the row heights should increase to display their contents;
    5. Adjust the row heights and column widths to the desired amount by clicking and dragging the bottom and right borders of their headers, respectively.
  3. The column labeled GEO.id2 may initially appear as scientific notation, e.g. 2.5E+10, but if you double-click the border between the column headers B and C, it should expand to show the original numbers as character strings of digits (as above).
  4. You will hopefully recognize this second “geography identifier” as being the same as the FIPS number in the attribute table of the layer  MASSCENSUS2010TRACTS. We may therefore use these two fields to join the tables together — but because FIPS is in text format, we must first ensure that Excel retains this format rather than converting it to its internal numeric format:

    1. Right-click on the header of column B, and in the contextual menu that appears select  Format Cells…;
    2. In the dialog Format Cells, click the tab Number (if necessary), and in the list Category: select Text;
    3. Click the button OK.
    4. You may notice the numbers in this column shift from the right side to the left side, which is how Excel indicates that it is interpreting the data as text rather than as numbers.
  5. The descriptive labels in the second row of a Census Excel file are important for understanding the meaning of the attributes. However, they are too complex to be used as column headers by ArcGIS, so we will use the first row instead. Because column headers must be immediately above the data, we will swap the first two rows:
    1. In the application  Excel, click on the row header 1, just to the left of the column header GEO.ID.
    2. Select the menu Edit, and then click on the menu item Cut.
    3. Click on the row header 3.
    4. Select the menu Insert, and then click on the menu item Cut Cells. This should put the column header GEO.ID into the cell A2.
  6. The column labels GEO.id, GEO.id2, and GEO.display-label include dots and hyphens, which are not allowed for ArcGIS attribute names, so double-click on each cell in turn and replace these characters by underscores.
  7. Because we are retaining the descriptive labels in the worksheet, there will be non-table data present. We must therefore name the region of cells covered by the table, and use that name when joining, instead of the worksheet name (here called Sheet0).
    1. Click and hold on the cell in the first row and first column of the table, the cell A2 containing the column header GEO_ID.
    2. Select the entire table by dragging down and to the right, until you reach the cell in the last row and column of the table.
    3. Click in the Name Box that’s located above column A, type a descriptive name, e.g. Poverty, and press the key Enter. Note that spaces, numbers, and most special characters aren’t allowed in these names.

    Named cell references are known everywhere inside a workbook, including within other worksheets. Names can be added and deleted by selecting the menu Insert, followed by the submenu Name, and then clicking on the menu item Define....

  8. Select the menu File, and then click on the menu item Save.

To see the results of the Making Census File Compatible procedure, try out the new attributes:

Exercise: Working with a Census Table in ArcGIS

  1. Follow Procedure 1 and join the census table ACS_13_5YR_S1701_with_ann.xlsx to the layer  MASSCENSUS2010TRACTS using the fields GEO_ID2 and FIPS, respectively.
  2. Symbolize the layer with one of the quantitative fields from the second layer, e.g. VC01_HC02_EST (Population for whom poverty status is determined: Below poverty level), normalized by VC01_HC01_EST (Population for whom poverty status is determined: Total).

Formatting Tables for Joins

While joining tables may appear straightforward, they need to be in a certain format to ensure success.


Making Attribute Fields Compatible

Before joining two tables using a particular attribute, it’s generally a good idea to ascertain the data type of that attribute in the layer’s table. The reason is that not only the join attribute’s values but also its type must be compatible in the two tables, and appearances can be deceiving.

For example, the POP2010 number in the attribute table for  MASSCENSUS2010TRACTS may appear to be an integer but it could actually be text or a real number.

The following table describes the most common data types.

Some Common ArcGIS Attribute Data Types

Data Type Value Represented Minimum Value Maximum Value Maximum Significant Digits/Characters
Short Integer number
-32,768
32,767
5
Long Integer number
-2,147,483,648
2,147,483,647
10
Float Real number
-3.4 x 1038
1.2 x 1038
6
Double Real number
-2.2 x 10308
1.8 x 10308
15
Text String of characters
254

Procedure 5: Determining Map Layer Attribute Types

  1. In ArcMap Icon ArcMap, in the Table of Contents, double-click on the layer of interest, e.g.  MASSCENSUS2010TRACTS.
  2. In the dialog Layer Properties, click on the tab Fields:
  3. In the list of fields, scroll down until you find the attribute name you are interested in joining, and note its type.

We can see in the image above that the data type of the FIPS attribute in  MASSCENSUS2010TRACTS is Text.

Question: Could it be another type?

To use this attribute to join an Excel table, the corresponding attribute data type must also be Text. If you look at this Excel table and the join field we used, GEO_ID2, you’ll note the little green flag in the upper left corner of each cell; it indicates that the numbers are actually formatted as text. (Hint: this can also be ascertained from their alignment on the left; Excel aligns numbers on the right.)

Procedure 6: Formatting Excel Data for Compatibility with ArcGIS

  1. In the application  Excel, click on the column header of the data whose format you want to change, e.g. A (you can also click and drag over specific cells in that column to select them).
  2. Select the menu Format, and then click on the menu item Cells....
  3. In the dialog Format Cells, click on the tab Number.
  4. In the list Category, you can choose a numbe of different formats:
  5. If you want the data to be treated as text, select Text.
  6. If you want the data to be treated as integers, select Number, and then change the number of Decimal Places: to 0.
  7. If you want the data to be treated as real numbers, select Number, and then change the number of Decimal Places: to a value that matches the data in the map layer’s join attribute. Note: because of rounding errors, joining real data should be avoided.
  8. Click on the button OK.
  9. In the menu File, select Save. If necessary, change the name of the file so that it starts with a letter and afterwards include only letters, numbers, and underscores.

Another formatting incompatibility to be aware of is that the content of a cell in the Excel join column cannot wrap, i.e. it must all be on one line, and cells in the table shouldn’t be merged cells, either.

Procedure 7: Formatting Excel Cells for Compatibility with ArcGIS

  1. In the application  Excel, click on the column header of the data whose format you want to change, e.g. A (you can also click and drag over specific cells in that column to select them).
  2. Select the menu Format, and then click on the menu item Cells....
  3. In the dialog Format Cells, click on the tab Alignment.
  4. In the section Text Control, if necessary turn off the checkbox Wrap Text.
  5. Also, if necessary, turn off the checkbox Merge Cells. Note: this may produce empty cells that misalign your data, so some clean-up may be necessary.
  6. Click on the button OK.
  7. In the menu File, select Save. If necessary, change the name of the file so that it starts with a letter and afterwards include only letters, numbers, and underscores.

More generally, the data in the two join columns must match exactly. In particular, watch out for extra spaces between words and at the beginning and end of data.


Summary: Making an Excel File Compatible with ArcGIS

While Microsoft Excel can make it easy to manipulate data tables, it also has its own way of doing things with which you’ll need to be familiar to make extensive use of it. Such use is outside of the scope of this class, but if you follow the recommendations below, your data tables should be compatible with ArcGIS.

Procedure 8: Making an Excel File Compatible with ArcGIS

Generally speaking there are five things you need to do to make Excel data compatible with ArcGIS: name it properly, create a row of field names, below that arrange your data in a plain rectangular array, name the table, and make sure the join fields match. Below are general descriptions of how to do this.

  1. Name the file: it should start with a letter and afterwards include only letters, numbers, and underscores.
  2. Create a row of field names:
    1. The very first row in a table must contain unique names for each column. Usually they will in some way describe the attribute that will be in that field, e.g. Population, ZipCode, etc.
    2. Field names must begin with letters, and after that can contain letters, numbers, or the underscore _ . They cannot contain other special characters such as the period . or hypen -, or spaces (and be careful that you don’t have any spaces at the beginning or end of the names, too!). Note that field names are also case sensitive (upper and lower case letters are distinguished). If your joined data appears as <null>, then check the field names for an illegal character.
    3. Field names cannot be one of a long list of reserved words, e.g. All. If your joined data appears as <null>, then check the field names against this list.
    4. For some types of joined tables, field names must be ten characters or less, though in other types you can use up to 64 characters. It’s a good idea to use short names in any case.
  3. Arrange your data in a plain rectangular array:
    1. Every map feature, such as a state or city, must have its data in a single row.
    2. Every column/field should contain the same kind of information for each feature, e.g. all population values should be in a single column. Blanks are allowed if particular data is missing. Also make sure the values have a consistent data type, e.g. all text, all integers, or all real numbers.
    3. All record and feature data must be contiguous, i.e. there must be no other data or blank rows or columns separating the data, and it must begin immediately below the field name row.
  4. Name the table:
    1. If you have non-table data in the cells around your table, e.g. explanatory notes, you’ll want to select just the range of cells covered by the table and give it a unique name.
    2. If you don’t have any other data in the worksheet besides your table, and it begins in cell A1, then the table can be referenced by its worksheet name. It’s highly recommended that you change the worksheet name to something more illuminating than Sheet0. Whatever name it has, ArcGIS will see it with a $ at the end, e.g. Sheet0$, indicating that it’s using the entire contents of the worksheet.
    3. Unlike field names, worksheet and cell range names have few restrictions like those described in (1)(b) above. However, names with spaces and special characters in them will appear with single quotes around them, e.g. 'My Sheet$'.
  5. Make sure the join fields match:
    1. Make sure the data types in the join fields are compatible: both text, both integers, or both real numbers.
    2. Make sure the Excel join field doesn’t wrap its text and doesn’t have any merged cells.
    3. Make sure the values in the two join fields match exactly, e.g. there are no extra spaces, variations in case, etc.
    4. The two tables don’t need to have the same number of records, e.g. some features could be missing in one table or the other. If the map layer is missing a record that appears in the join table, the latter will be ignored, and if the join table is missing a record that appears in the map layer, its values will appear as <Null>.

Geocoding Street Addresses

Street addresses can be geographically located when you have a special street layer and a geocoder.


Street Addresses

Geographic data often comes in the form of a table of street addresses, for example of schools or businesses, along with attributes describing them such as their name, purpose, etc.:

Geocoding Linear InterpolationA full street address includes a street number, street name, city, state, and zip code. Like other place name data, street names can be associated with a street layer to get a very rough location. To get a more accurate position, we need to know where the street numbers fall along the street.

If a street layer contains details about which addresses lie within which blocks, it can be used with a program called a geocoder to place the addresses on the map as points along the streets. Address locations will be approximate, because only a block’s beginning and ending addresses are usually provided, and others are linearly interpolated along the street (see the block at the right, which places the address 317). In addition, street layers have varying degrees of accuracy.

Street layers are commonly available from cities and towns as well as commercial entities. The ArcGIS software suite comes with the collection ESRI Data and Maps, which includes the commercial package called Street Atlas North America, and a geocoder that works with it.

Set Up: Adding the Street Atlas Layer

  1. In  ArcMap, in the toolbar Standard, click on the button Add Data Icon Add Data.
  2. In the dialog Add Data, make a new connection to the folder  K:\Maps (see Constructing and Sharing Maps for details). This network folder is where Amherst College stores a large amount of data for use in maps.
  3. Navigate into the folder  ArcGIS Books-n-Data\ESRI Data & Maps 2008\streetmap_na.
  4. Click on the file  StreetMap North America.lyr. This is a layer file, which references data in one or more additional files, along with information about how to symbolize them.
  5. Click on the button Add.
  6. You will now see the dialog Geographic Coordinate System Warning; you don’t have to worry about this for now, so click on the button Close.
  7. Click on the button  Zoom In, and click-and-drag a rectangle around the City of Holyoke (north of Springfield and I-90, east of I-91).
  8. In the Table of Contents, turn off and back on the layer  StreetMap North America by clicking on the checkbox to the left of its name, and notice the relationship of the two data sets.

Let’s now geocode a set of addresses using Street Atlas’ street information:

Procedure 9: Geocoding a Table of Street Addresses

  1. In  ArcMap, click on the menu Tools, then select the submenu Geocoding, followed by the menu item  Geocode Addresses….
  2. The dialog  Choose an Address Locator to use… will appear with a list of address locator databases. If this is first time you’ve geocoded this list will be empty, and you’ll need to choose the correct encoder for the street address database format:
    1. Click on the button Add….
    2. Geocoding Setup Dialog In the dialog Add Address Locator, navigate into the folder  K:\Maps\ArcGIS Books-n-Data\ESRI Data & Maps 2008\streetmap_na\data.
    3. Double-click on the file  Street_Addresses_US.
  3. Continuing in the dialog  Choose an Address Locator to use…, click on the button OK.
  4. In the dialog Geocode Addresses: Street_Addresses_US, the menu Address table: might already list your data set if you’ve previously added it. Otherwise:
    1. Click on the button Document Open Browse and navigate into the folder with the table to be joined, e.g.  MappingPlaceNames.
    2. Double-click on the table to be joined, e.g the Excel workbook  Holyoke Schools.xls.
    3. In the workbook, double-click on a sheet or a named region, e.g.  HolyokeSchools.
  5. Continuing in the dialog Geocode Addresses: Street_Addresses_US, verify that the Address Input Fields match the correct ones in your data table.
  6. Make sure the Output shapefile or feature class: is an appropriately named and located file, e.g. HolyokeSchools.shp in the folder  MappingPlaceNames.
  7. Click on the button OK.
  8. Geocoding Addresses DialogThe dialog Gecoding Addresses… will now appear and provide a summary of the geocoding. It will tell you how many of the addresses in the table were matched, tied, or unmatched (e.g. if a zip code is incorrect it may end up tied with a slightly different street name).
  9. If you have not matched all of the records (very common), you may want to look at how the matches are made. Click on the button Rematch; the dialog Interactive Rematch will appear. It lists each record and gives it a score from 0 - 100 judging the quality of the match. You can use this dialog to interactively improve the match, e.g. by correcting bad data or choosing a more likely address (N.B. sometimes the geocode database can be wrong, too!).
  10. Geocoding Interactive Rematch Dialog

    This dialog can be recalled later by clicking on the menu Tools, then selecting the submenus Geocoding and Review/Rematch Addresses, and finally clicking on the menu item Geocoding Result: ….

  11. When you are finished matching, click on the button Close.
  12. The resulting data will be added to the map with a distinguishing name, e.g. Geocoding Result: HolyokeSchools.

Geocoded Holyoke Schools on the map

Note that these points on the map are created relative to Street Atlas’ description of the streets; this may differ from other descriptions, e.g. the local Planning Department may have more accurate data, while an old map may be less accurate.


Previous: Constructing & Sharing Maps

Mapping Place Name Data

Top of Page  | Using IT DocumentationIT Home  |  IT Site Map  |  Search IT