Figure 1. shows a typical and practical setup, where users run QGIS on local machines and shared data is stored on a PostgreSQL/PostGIS server. The spatial database on the server provides access to spatial/gis layers and to non-spatial attribute tables. Users can connect to PostGIS with QGIS. For more advanced work with remote data, pgAdmin can be used. The database provides access to:

PostGIS databases have a special auxiliary table "GEOMETRY_COLUMNS" which consists of a list of spatial layers available for QGIS. In other words, when you use QGIS and add a spatial layer from a PostGIS database to a current project, you see a list of available layers to add which matches a list of layers in the "GEOMETRY_COLUMNS" table.

Taking into the account this observation, let's outline the major steps in the process of joining a spatial layer and a non-spatial table (Figure 2).

Figure 2

The major steps are:

Step 1, You already know how to perform a JOIN of two tables (with the JOIN keyword or with WHERE clause). We will discuss steps 2 and 3 in more details below.

As you remember, the result of any SQL query on table(s) is a table (expressing it in simple terms). The natural question after doing so many SQL queries is how to save results of the SELECT query? For example, if we need to repeat our analysis on a table after adding new records, or if some processed data are needed for users. It can be done with the "CREATE TABLE" or "CREATE VIEW" SQL statements. The general format of these statements are:

CREATE TABLE <nameOfNewTable> AS
SELECT <select statement>

or

CREATE VIEW <nameOfNewView> AS
SELECT <select statement>

The "idea" of both statements is to perform the SELECT statement on table(s) and to store the results in a table or in a view. What is a view? What is the difference between a table and a view? When you use the "CREATE TABLE" statement, it will create a new table and populate it with the data according to the SELECT statement. What is important to understand is that data will be cloned/copied into the new table permanently. We can think of VIEW as a filter or a recipe. There is no copying of data or replication of data, data are stored in the original tables, but "view" is constructed and data gathered as you need them. If we use the "cooking recipe" analogy:

To add new data to a table (in other words, to add a new row/record to a table), the SQL INSERT statement can be used. This statement 'adds/inserts' new records and assign values to fields/attributes. The format of the INSERT statement is:

INSERT INTO VALUES(<list of values>) Please, note that list of values should repeat the order of columns. This general explanation will be more clear later. For now just remember that new records can be added with the INSERTstatement. Now we know all of the operations to join the spatial layer and non-spatial attribute table. [edit]

Let's study our data before proceeding further. Please start up the pgAdminIII tool and connect to the TestGIS database on the FossGIS server. If you are having trouble connecting to the database, please look back at this exercise: Using Data Layers from Spacial Data Engines

Please, run the following queries to get a feel for the data. Pay attention to the execution time while running queries:

SELECT * 
FROM ma_bldngs2005;

SELECT * 
FROM matowns;

(It usually takes about 25 seconds to execute the query, this can however differ greatly depending of your internet connection).

We will narrow the select query since we don't need all fields/attributes. The following attributes will be used: gid, objectid (both are internal fields for the spatial layer), town_id (id of town), town (town name), pop2000 (population according to the 2000 Census), shape_area (area of each town in sq.meters).

SELECT gid, objectid, town_id, town, pop2000, shape_area
FROM matowns;

execute time ~0.7sec

We will also use a subset of attributes from the building permits table. We will use town_id(id of town), building (number of buildings allowed to construct), units (number of units) and constrcost (estimated cost of construction). Please run the following query:

SELECT town_id, building, units, constrcost
FROM ma_bldngs2005;

execute time ~0.5sec

It's important to note that tables have fields which store 'common' information for both tables: field town_id. This field is a primary key for the building permit table and the foreign key for the matowns table. We can use this field to join tables.

At this stage of our project, lets perform a SELECT query to JOIN the spatial layer (which is table matowns) and attribute table ma_bldngs2005. Please analyze the query below:

SELECT gid, objectid, t1.town_id, town, pop2000, shape_area, t2.town_id, building, units, constrcost
FROM matowns AS t1, ma_bldngs2005 AS t2
WHERE t1.town_id = t2.town_id;

Please note:

Please run this query and see the results. Now the question is how could we save the results of the query for future use with PostGIS or with QGIS?

To save data which we have merged from two tables, we can add the "CREATE VIEW <nameOfyourViewHere> AS" statement before SELECT query:

CREATE VIEW tmpMaHousing AS 
SELECT gid, objectid, shape_area, shape_len, the_geom, t1.town_id, town, pop2000, building, units, constrcost
FROM matowns AS t1, ma_bldngs2005 AS t2
WHERE t1.town_id = t2.town_id; ( Please Note: this file already exists on the sever)

This query creates a VIEW with the name tmpMaHousing which is defined by the SELECT statement. So this view is a recipe which 'recreates' and merges data for you. The important point is that you can use the VIEW (in this case tmpMaHousing) in your queries in the same way you use TABLES.

You can also see a list of available views in the database with the pgAdmin application. Please see the Figure below:

Figure 3. List of VIEWS in pgAdmin console

At this point in our project, we have defined a new spatial layer/VIEW which combines data from the town spatial layer and the building permits attribute table.

To make this layer visible/available for QGIS users, we need to register the VIEW in GEOMETRY_COLUMNS table. So we need to add a record with information about our new layer. After that the layer will be available for usage in QGIS (in other words, when a user connects to the PostGIS server, this new layer will appear in the list of available spatial layers to add).

We will add new records with the following INSERT statement:

Figure 4. INSERTing new record into GEOMETRY_COLUMNS table

Please note that you need to substitute 'tmpmahousing' with the name of your view. Here are some details about this query:

Please see the results of the query in Figure below:

Figure 5. Result of the insert query

The value for the field oid was generated automatically, we specified all other values.

So let's just review what we did:

Now let's try to use the layer with QGIS. You can close the pgAdmin application and start QGIS application.

Now let's switch our role from database gurus to QGIS users. Please switch to QGIS and add the PostGIS layer you have created . Click on the "add PostGIS layer" button and connect to the TestGIS server. You will see a list of available spatial layers in the window (Figure 6):

Figure 6. A list of spatial layers stored on PostGIS server and available for QGIS

Select the layer you have created and add it to the QGIS project. (you can see our layer 'tmpMaHousing' which the FossGIS team created).

Figure 7. Attribute data of the newly created layer

Open the attribute table for the layer (this may take a minute, and we have have observed QGIS .8 for Windows freezing on this step, but wait a couple of minutes before you assume that the application has frozen. If the application does freeze restart QGIS and attempt the step again.). Take a moment to analyze a list of available attributes and the number of records in the dataset. What is important is that you HAVE ACCESS to building permits data in QGIS: check fields buildings, units, etc. These are data from the building permits table joined to the spatial layer (Figure 8).

Figure 8. Building permit data are available within QGIS

Now you can create a thematic map, showing the number of building permits issued by towns in Massachusetts. To do that you need to change the symbology settings and use the "Buildings" field for that. (Please note that this process may also take a couple of minutes, and we have observed QGIS .8 for Windows freezing on this step, please wait a couple of minutes before you assume that QGIS has frozen. If you are sure that it has frozen, restart the application and attempt the step again.)

Figure 9. Final thematic map

The final thematic map could look like the Figure above, where darker colors represent greater building activity.