1. technology and computing
2. software
3. databases

GIS Fundamentals
Lab 7 Table Operations
Lab 7: Tables Operations in QGIS
What You’ll Learn: This Lab provides more practice with tabular data management in
QGIS. In this Lab, we will view, select, re-order, and update tabular data.
You should read chapter 8 in the GIS Fundamentals textbook before performing this
Lab.
Data: are in the \L7 directory, with census data demographics.shp in decimal degree
coordinates, and soils.shp in UTM Zone 17, NAD83 coordinates, and the units are
meters.
What You’ll Produce: Three maps, two of selections based on census data, and one of
a soils data set. You’ll also produce a table of soil properties.
Background: Most spatial data in a GIS consist of at least two types of data, those data
depicting the location and shape of objects, and text or numerical data describing the
objects. These text and numerical data are most often contained in tables, and most
GIS packages have some way of creating and editing these data tables.
QGIS provides a rich set of tools for viewing and displaying attribute data. However,
you don’t have as many options for manipulating and saving new data, as with a fullfeatured database manager, so we’ll do some rather simple operations in this lab.
Joining Two Existing Tables
(Video: Join Tables)
Start QGIS, and add the theme demographics.shp.
Open the Attribute Table (right click in TOC, then Open Attribute Table). Note the fields,
especially one called Blkgrp (the last column).
Add the data table name more_data.dbf, using (as you would add a vector layer, but
while browsing make sure you have File Types: set to all…and you may have to do
this, back up a directory, then try again for the all types to be applied. Then double click
on the more_data.dbf to display).
Display the more_data.dbf (right click in the TOC, and left click on Open Attribute
Table)
Notice that more_data.dbf also has an item named Blkgrp.
Look at all the variables (columns) in each table, using the scroll bars if needed.
1
GIS Fundamentals
Lab 7 Table Operations
Are the variables ordinal, nominal, or interval/ratio? Which variables are found in both
tables? Which variables might serve as keys for the table, and which would be
inappropriate as keys? (See Chapter 8 in the textbook, if you’re unsure on these
concepts)
Each record (row) in each table corresponds to each polygon in this US Census Bureau
demographic data, displayed in demographics.shp. These files were produced from
U.S. Census data, which uses a variable named Blkgrp as unique identifiers, typically
groups of city blocks. Each record in our tables corresponds to a block group.
The file more_data.dbf includes populations at various dates for each block group
polygon, e.g., Hh80=population in 1980, Hh90= population in 1990, etc.
Table Join
This is a common operation in GIS analyses. We often want to combine data from
different sources.

Right click on the demographics.shp layer in the TOC

Left click on Properties, then select Joins, most of the way
down the left side of the window:

Click on the add button near the bottom left

Then specify the joint layer as more_data, the join
field (from more_data) as BLKGRP, and the Target
field (in the demographics layer table) as BLKGRP.
Note that the field names don’t have to be the
same, they just happen to be in this example.

Now click on apply and O.K.
Now reexamine the demographics.shp attribute table.
Notice the demographics.shp table has the more_data fields append to the end of each
record.
2
GIS Fundamentals
Lab 7 Table Operations
You’ve just connected the two tables, matching the records in one table to the records
in another table that have the same value for BLKGRP.
This is a temporary join; the original files/data have not been modified. QGIS keeps
track of joins within a project, and how to display the various joined files. If you were to
display these data sets in another project, they would not appear joined. The data are
not copied to a new, combined, file. Rather, this join tells QGIS to display these two
data sets within this particular view, matching each row by the join variable.
Selecting on a Joined Table
Now, let’s select items based on the joined tables.
(Video: Select Tables)
 Open the Attributes Table of demographics. It
should display both the original data plus the data
from more_data.dbf.

Left click on Select by Expression tool
the upper frame of the table window

From the popup window (see right), enter the
selection equation
on
3
GIS Fundamentals
Lab 7 Table Operations
“more_data.Hhpctgrowt “> 0
You add columns by clicking on those listed under Fields and Values in the upper
left window, and operators or conditionals to build and expression, shown in the
entry tab near the bottom (see lower right).

When you’re done, left click on the Select button displayed along the bottom.
This should select most of the rows in the table, displaying them with a blue or similar fill
color in each cell.

Examine your selected block groups on the map.
You have just identified all the block groups with positive population growth.

Clear your selection by clicking on the Unselect All icon at the top of the
table

Use the Select by Attributes tool again on this same table, selecting blocks that
have both population growth greater than 0 and income less than $30,000. This
requires entering the following equation in the SELECT FROM portion of the:
(“more_data.Hhpctgrowt”>0) AND (“more_data.Hhincavg”<30000).

Apply and examine your selection.

Activate the Moved Selected to Top button
(top margin of the table), to only
focus on your selected features. Were 17 of 167 records selected? If not re-check
your selection expression.
Now create a new field to store the results of
this query.
Open the attribute table, toggle editing, and open the Field
calculator.
Select Create a new field named Grow_Low and assign the
value of 1 to the 17 selected records.
.
Next invert the selection with the button (left) and
then use the Field calculator to assign the value of
0 to “Update existing field” Grow_Low field for the remaining
150 records.
4
GIS Fundamentals
Lab 7 Table Operations
Produce a map (as a .pdf) of the view with this
compound selection ([Hhpctgrowt]>0) and ([Hhincavg]
<30000), with only those block groups meeting both
criteria highlighted and those not meeting the criteria
in a contracting color. Remember to include a title,
descriptive legend, name, scale bar, and north arrow.
Saving a Copy of a Joined Layer and Table
Right click on the demographics.shp layer in the TOC.
Many operations will default to acting only upon the selected records, if there is a
selected subset. Failure to clear means the operation will on produce partial results

Unselect all features in the table, as shown above.

The left click on the demographics.shp in the TOC, and Save As a new file
named something like tablejoin.shp
This step copies the data in your temporary join to permanent storage, as a shapefile.
All items from the joined tables are saved to a new, usually larger table, with associated
polygons (or points or lines).
Note: that in some older versions of QGIS and files the column names are not copied, e.g., each of the
joined table columns is named something like “more_data, more_data1, more_data2….” Somewhat of a
pain, but easy, if tedious, to fix by renaming. If you have a new version of QGIS skip this highlighted
section.

Clear you old project or open a new QGIS project, and display only the Tablejoin.shp layer that
you just created.

Right click the Tablejoin.shp layer, open the attribute table and left click on Open Attribute Table

Add and open the more_data table, and sort in ascending order based on the column named
BLKGRP (remember, click on the name until you have an upward pointing triangle)

Also sort the Tablejoin.shp attribute table by the BLKGRP attribute.

Look at the set of rows, and notice how the columns match in Tabjoin shapefiles:
o
o
change more_data_1 to HH90,
change more_dat_6 to HHINCAV
5
GIS Fundamentals
Lab 7 Table Operations
As we said earlier, it would be better of QGIS always automatically copied the names after the join, but
often it doesn’t.

No matter, we can start the Table Manger plugin and do this (Plugins – Manage and Install
Plugins – Table Manager), displaying this icon, usually near the lower left margin of QGIS:

Left click on the Tablejoin layer in the
TOC to make it active (it should have
a blue or grey bar surrounding it):

Click on the Table Manager icon to
activate it.

Change the names for the two columns, by clicking on a
column to activate it (more_data_1), then the rename
button on the right, the fill in the new name (HH90) on the
popup.

Do the same for the more_dat_6 to HHINCAV

Save and close the table, ignoring the note about layer
styles
Calculating into New Columns
Now we shall add an item (column), and modify the values.
We wish to know per capita income. We calculate this by multiplying the average
household income by the number of households, and then dividing the result by
population.

Toggle the editing on (

Name the field Tot_Income (no spaces, no more than 10 characters),

Make the Type “Decimal number”, Width 12, Precision 2 then left click on OK
), then add a new column (
6
)
GIS Fundamentals

Lab 7 Table Operations
Now Add a second field, call it per_cap_in,
with the same specifications as Tot_Income.
Calculate the total income as HH90 * HHINCAVG,
and click OK (see at right).
After the calculation completes, inspect the
Tot_Income column you just calculated, and check a
few of the values. Are the values equal to the
number of households’ times the average income?
Use the Field Calculator to calculate the per capita
income as Tot_Income / POPBASE (see figure).
Again, check the results, this time in per_cap_in.
Save the edits, and stop editing.
7
GIS Fundamentals
Lab 7 Table Operations
When you create a map of continuous variables such as per capita income, you often
wish to display them in categories, e.g., 0 to 1000, then 1000 to 2000, and so on. You
can do this through the layer PropertiesSymbology, and specify
Quantities,
Graduated
colors
A Value
item in
Fields of
per_cap_in
Classification with 7 Classes.
Pick a color ramp that appeals to you, and then left
click Apply and OK.
Compose a map (as a .pdf), with 7 categories of
per capita income. Remember to include all the
usual required map components.
8
GIS Fundamentals
Lab 7 Table Operations
Creating New Tables
Creating a table and joining it to existing tables is a common operation. Often, this join
involves a one-to-many relationship between tables. Each record in one table matches
many records in the second table. For example, a typical county may have
approximately 80 different soil types, but over 100,000 different soil polygons of these
types. Therefore, we may have properties for each of the 80 different types, e.g., crop
productivity, engineering properties, moisture characteristics. We may format these in a
table, and join this table to our existing county data layer. The repeated properties aren’t
copied, just displayed for the appropriate polygon. This saves space, because we don’t
have redundant copies of the soil properties information saved for each instance of a
soil polygon in our data layer.
This exercise will give you practice in creating and joining tables, and the other
techniques you learned in the first section of this lab (Video: Create Tables).
Open a new blank .
 Add the soils.shp
data layer, set the
PropertiesStyle to
Categorized, based
on the item soil_type,
with a random color
ramp,

Click Classify to add
the values.
9
GIS Fundamentals
Lab 7 Table Operations
Open the soils.shp attribute table.
You should have a view similar to the figure below. Review the layer attributes, and in
particular notice the soil_type attribute.
The soil_type attribute contains a code corresponding to the soil type of each individual
polygon. Notice there are 15 different soil types designated by numbers between 18
and 69. There are 122 different soil polygons.
Our job is to create a new table, enter important information for each of the 15 different
soil types, and join this data with the soils data layer.
In this exercise you will use the “soil_type” variable in the soils shapefile as the join
item, or join column. This is the “key” variable that will be used to match the rows from
the new soil properties table you will create to the soil polygon data in soils.shp. The join
item must be defined the same in both tables, with the same type (long or short integer,
text, etc.)
Let’s examine the “key” or join column in the target table. Do this by a right click on
soils.shp file in the Table of Contents window, then left clicking on Properties > Fields
tab.
This should display the window below. Note the type and other properties of the
soil_type item, especially the Type, but also the length and precision. It is generally best
for join keys to have the same type in both tables, e.g., we wouldn’t want to try to join an
int to a double, or to text variables.
10
GIS Fundamentals
Lab 7 Table Operations
Now we need to create a new data table
with information not included in the current
table. We’ll then join this new table to
soils.shp.
You first must create a CSV, or comma
separated value file. This is a text file with
commas between each column entry, with a
line in the file corresponding to each row.
Excel, OpenOffice, or LibreOffice are all
spreadsheet software that allow you to
easily enter the data in cells, then export it
as a CSV formatted file.
The figure to the right shows data entered in
the Mac version of EXCEL.
Note that there are many versions of
“standard” text files. Generally, exporting in
the “Windows” version identified returns the
“UTF-8” version, so selecting those on the
save/export or import gives a compatible file for exchange. However, you may have to
try various format options depending on the spreadsheet or text editing package.

After exporting the csv/text file, use Layer – Add Delimited Text Layer in QGIS to
add a file
11
GIS Fundamentals
Lab 7 Table Operations
This will open a window in
which specify the source file,
Browse to specify the
soil_props text file you just
created,
Specify it as a CSV via the
radio button, with UTF-8 (or
other appropriate) encoding,
Note that you have a first
record as field names,
And that there is no
geometry, then OK
This should add the table to
your QGIS TOC. Left-click
and open the new table,
verifying the values for rows
and columns.
Examine the properties of the soil_props.dbf table (remember; right click in the TOC,
then Open).
12
GIS Fundamentals
Lab 7 Table Operations
With the Attributes of soilprops open, Select Options and Add the table to the Layout.
You need to narrow the columns for the whole table, then add your name and Export
your completed table as a .pdf file.
Remove the soilprops table
from your layout (print view)
Select the soils layer and join it
to the soilprops table.
Remember to use the common
field soil_type, to join the files.
If you are unclear on how to
join, refer to the instructions
under “Joining Two Existing
Tables” earlier in this Lab.
After you’ve completed the join,
create a map using the new soils table. Display soils by fertility class.
Recolor the map with a different color or shade of grey for each of the five fertility
classes. See the example map on the next page.
Set the fertility classes as categories (Properties – Style – Categorized then select the
Fert_Class field, Add and then Apply).
Again, I’d like to stress the utility of what you’ve just done. Managers and scientists
often want information grouped and displayed different ways, and joins are then used to
13
GIS Fundamentals
Lab 7 Table Operations
add information to and produce maps upon which decisions are based. Geographic
data may be joined to many different sets of tabular data. These joined sets may be
selected based on many combinations of attributes, greatly increasing the flexibility and
utility of data in a GIS.
TO TURN IN as .pdf
 Map1: Tempe, AZ block Groups
 Map2: Tempe, AZ Per Capita Income by Block Group
 Map3: Macon County, NC Soil Fertility

Soil Properties data table as .pdf
14