Tutorial 04 – Introduction to tabular data

Topics Covered:

Exercises 1: Settings and Workspaces

As we have already started to experience – collecting spatial data can be quite a bit of work.  The space necessary can grow quite quickly and setting up a workspace for the different topics you will be working on is good practice.

The data we collect today will be used in the labs throughout the course.  Not all the steps involved in obtaining and understanding the use of the data we are acquiring are written into the tutorials and Lab, so take notes as this type of data acquisition may be useful for your projects.

The UNBC student computer lab we are using for this tutorial has all of the software we need to obtain data for the tutorials, labs and your projects. This computer lab (and others in the school) do not however have the different geospatial  software (GIS software) we will need to create spatial data.  We will head down to the GIS Lab for the last part of the tutorial.  You can always connect to our Window terminal server (osmotar) if you need all the tools without being in the GIS Lab.

Connecting to osmotar (the windows server) for the GIS lab :

We will begin by using the Remote Desktop Connections program on the student labs machines.  You can find it by typing remote after clicking the start button in the bottom corner of your desktop.   The server is named osmotar.unbc.ca (or just osmotar if you are at the school). Scott will show you how to connect to this server (take notes as this same procedure can be utilized from home).

If you wish to connect to osmotar from home, you will also need to install and setup the Virtual Private Network (VPN) software (http://www.unbc.ca/vpn)

Map a drive:

The drives we will be using for the course are automatically mapped for you on osmotar – but not in the student labs. To be able to access your GIS data (your home workspace for instance) from the student labs, you need to run a command in the command prompt window on the student lab machines. If you are looking to map the drive from the computers in the student labs, you can do so in the following manner:

– start the command prompt – type cmd in the search box when you click on the start button
– then type the following command
net use k: \\gisfs2.unbc.ca\yourusername /persistent:YES

This will give you the same k drive as you see on osmotar.  If you want access to the lab folder for Geog 204 or other courses, you execute the same command with different parameters:
net use L: \\gisfs2.unbc.ca\gisfolders /persistent:YES

Scott will illustrate this.

Create a directory :

Once we have a K: drive connected to the lab machines, we will need to make a directory in your geog204 directory called tut1

Exercises 2: Data downloads

After creating the tut1 directory open up the firefox web browser (NOT Internet Explorer)  and go to www.library.unbc.ca –> Collections –> Data and Statistics  –> Canadian Data –> Canada Census Analyzer (it is now buried under the DLI section down the page) –> Enumeration Dissemnination Areas.  We will use the data from 2016, but we will have a quick look at data for 2006 (for reasons we will discuss in a later tutorial and lecture).  Lets start with the 2006 Cummulative link.

We are looking for data for Prince George, but there is no way to find these data for a specific city.  Instead look for the data for the Fraser Fort George Regional District.  You should have something like this:

Next select the total population and a couple other statistics (Use the control key -Ctrl- to highlight more than one record):

Select the Division Code checkbox and leave the rest as the defaults – then submit query.

You can see that the resultant query provides a text file made of three parts.The first group would be considered meta data. It could contain information that described who put the data together, when and how it was collected , what algorithms may have been applied to it or a descriptive index indicating what the tabular records equate to. These are just some of many possible meta descriptors. In the case of this file the header titles are described.

The second line is the header information for the file. As all values are separated by a space (or tab) you can see what the header columns will be once taken into a spreadsheet.

The third is the listings of all census data in Prince George (the raw data). We are going to clean up this data somewhat so it can be  used in GIS Software.

We will also bring the data down in html format – much easier for working with, but Scott makes you do things the hard way first!

Save this file to your tut1 directory on your K: drive (web page only for the html download).

Exercise 2: data cleanup

We have to clean up the data somewhat before it can be brought into QGIS. We will take two approaches: the first being a text editor, the second being a spreadsheet program. We are not actually doing that much to the data but it introduces two key pieces of software we will use in these tutorials.

Text Editor:

Start the program wordpad programs –> accessories –> wordpad (or notepad++ – much better). Then open up the file you just downloaded. Use this software to remove the metadata, and properly locate and name the column headers. Scott will illustrate. Once done save it with a new name.

Spreadsheet (Excel):

Start the program Excel and open the HTML file in it. Scott will illustrate (TAKE NOTES). Once in excel, this data can be saved as variety of types.

Lets fire up QGIS and see if we can now add the tabular (attribute data – the I in GIS) we just created into QGIS.  On osmotar, you will want to open QGIS version 2.18.  This can be found by type QGIS after clicking the start shell button, or navigate by clicking the start shell button and navigate to the program as follows: Start Shell –> All Programs –> OSGeo4W –> QGIS Desktop 2.18…)

Exercise 3: More cleanup and bringing text data into QGIS

Cleaning the data Using Gedit on Linux or either Notepad++ or Wordpad on Windows), open the file /home/labs/geog204/tut1/postal_2004.txt (L:\abs\geog204\tut1\postal_2004.txt in windows) Clean off the metadata , set up the columns and save the file in your tutoriaL2 folder.  What are the commas used for?  Open the file theftsfrom.csv in both in a text editor and in a spreadsheet program (LIbre Office or Excel)  – do you understand how the data is put together?  What type of data is being recorded? Bringing text data with coordinates into QGIS Open QGIS.  In the lab we loaded data by opening a project or by adding layers.  We are going to add a vector layers, but not a shape file.  Open the postal code data by: –   Layer –> Add Layer –> Add Deliminted Text Layer  –> choose your postalcode file you created above –   Set up the import panel to make use of the x, y coordinate in the file (what are the x and y coordinates?) to create a point layer.  The image below should help you out
–   Import the data by clicking OK

Bringing text data without coordinates into QGIS Load the tabular t

• Open up the properties of this layer
• click upon the joins tab and the plus button
• Add the postal.shp file with the items (columns) to join together being the postal codes
• View the results in the attribute table
• Save the joined layer as a CSV file
• You can bring it in as a delimited text layer and convert to a shape file if you wish

hefts data into QGIS in the same manner as above, but select the option that tells QGIS to not consider the file a spatial layer.

We now have a point layer to play with.  We need to Find the locations of the automobile thefts -how do we do this?

Exercise 4: Joining Data using our first plugin

We have two data sets in our TOC (table of contents).  Lets look at the postal code layer with a background map.  We will use a plugin for this

Plugins in QGIS

Plugnis are plentiful for QGIS and easy to use.  They are created by smart  community minded users of QGIS to extend the software.  We are going to use the OpenLayers plugin to put Open Data into the background of our map by:

• Plugnis Menu –> Manage and Install Plugins
• Search Openlayers
• Install Plugin
• Web Menu –> OpenLayers –> OpenStreetMap –> Open Street Map
• Lower the new layer to the bottom of you TOC

Joining Tables in QGIS

• Right click on the thefts layer  –> properties –> joins tab
• Add a join (plus button)
• Select the common fields
• Check “common field name prefix”  –> set it to empty
• Click Appl
• Click OK

Now that the table is joined save the thefts from layers as a CSV type and bring it back in as a spatial layer

• Right click thefts –> save as
• Format Comma Separated Value
• Choose a name and location (tut1 folder)

• Add it back into QGIS
• Layer –> add layer –> Add delimited Text Layer

Now save both the postal layer and the thefts point layers as shape files in your tut1 folder

We will discuss what we have done in next weeks tutorial

Exercise 5: Downloading and adding 2016 StatsCan data to a QGIS project – Next Tutorial

This section is a class led section to see if we now have the skill to add data to a QGIS project.

We will use the Census Analyzer as before, but we will be looking at the Dissemination Area data for 2016.  The interface is a different design for 2016, but the data available is the same.  Lets see how far we get to gather the total population and the number of people with Bachelor Degrees for DA in the Fraser Fort George area.