In Lesson 1 we saved a number of our MS-Access queries so that we could easily re-run them later and in a couple of cases, to build a query upon another query rather than a table. In Postgres and other sophisticated RDBMS's stored SQL statements like these are called views. In this section we'll see how views can be created in Postgres.
A. Create a view based on attribute criteria
- In the pgAdmin Query dialog, execute the following query (which identifies the state capitals):
SELECT * FROM usa.cities WHERE capital = 1 ORDER BY stateabb;(Sorry Montpelier, I guess you were too small.)
- After confirming that the query returns the correct rows, copy the SQL to your computer's clipboard (Ctrl-C).
- Back in the main pgAdmin window navigate to the usa schema.
- Right-click on the Views node and select Create > View.
- Set the view's Name to vw_capitals and its Owner to postgres.
- Click on the Definition tab and paste the SQL statement held on the clipboard (Ctrl-V) into the Definition box.
- Click Save to complete creation of the view.
- Select View > Refresh (or hit F5) if you don't see the new view listed.
- In the tool bar above the Query window, find and execute Clear Query Window.
- Right-click on vw_capitals and select View/Edit Data > All Rows. With the flickering and the fact that the query has appeared in the Query window, you should believe that the Data Output pane has been re-filled with the results of having ‘run’ the view.
You could now use this view anytime you want to work with state capitals. If the underlying source table were updated, perhaps to add Montpelier, those updates would automatically be reflected in the view
B. Build a query based on a view
Just as we saw in MS-Access, the records returned by views can be used as the source for a query.
- In the pgAdmin Query dialog execute the following query, which identifies the 18 relatively small capitals. Note the use of the view we just created.
SELECT * FROM usa.vw_capitals WHERE popclass = 2;
C. Create a view based on a spatial function
Views can also include spatial functions, or a combination of spatial and non-spatial criteria, in their definition. To demonstrate this let's create views that re-project our states and cities data on the fly.
- Study then execute the following query:
SELECT gid, name, pop2008, sub_region, ST_Transform(geom,2163) AS geom FROM usa.states;
- Follow the procedure outlined above to create a new view based on this query. Assign a name of vw_states_2163 to this view.
- Again, repeat this process to create an on-the-fly re-projection of the cities data called vw_cities_2163. Define the view using the following query:
SELECT *, ST_Transform(geom,2163) AS geom_2163 FROM usa.cities;
D. Display views in QGIS
QGIS makes it possible to add both tables and views as layers. We'll take advantage of this feature now by creating layers from the views we just created.
- Open a new project in QGIS.
- Go to Project > Project Properties and verify that the check box for Enable 'on the fly' CRS transformation (OTF) is NOT checked.
- Now, click the Add PostGIS Layers button.
- Your Lesson3 connection should be remembered and show in the pick list in the Connections section.
If your Lesson3 connection is not available you will need to recreate it.
With the Lesson3 connection selected, click the Connect button.
If an Enter Credentials dialog pops up just supply the postgres user name and the password you established for it.
- Expand the object list associated with the usa schema. You should see the original cities and states tables. In the Table column, disregarding the two tables that show Select... in the Spatial Type column, you should also see vw_capitals, vw_states_2163 and two versions of vw_cities_2163.
Before we can use any of the views that show the '!' in a triangle in the Schema column, we need to let QGIS know what the Primary Key field is for each table.
So, in the Feature id column click on the Select... in order to bring up a pick list of the fields that are in the table. Do you recall what the primary key field is for each of these table? You are correct if you think, 'gid'.
So, check the box for 'gid' for each of the four views (the ones with defined Spatial Types).
The requiremnt to specify the Primary Key this way is indicative of the fact that you will always need an integer primary key in the views that you add to QGIS. This will come up later.
You see two versions of vw_cities_2163 because that view outputs all of the columns from the cities table, including geom, plus a column of geometries re-projected into SRID 2163 (geom_2163).
- Add the six usa schema layers to the QGIS project.
- Go back to Project > Project Properties. You should see that the check box for Enable 'on the fly' CRS transformation (OTF) is now checked. Apparently QGIS detected the fact that the spatial reference property settings for the layers we added are not all same, so it engaged the OTF capability.
- Un-check the box for Enable 'on the fly' CRS transformation (OTF) and hit the Apply button.
Close the Project Properties dialog.
- Spend a few minutes playing with the view (zoom to the extent of the different layers and turn the views on and off). You will note that, indeed, without the OTF engaged the fact that some of the layers are in lon/lat coordinates and some are in meters based on an equal area map projection becomes apparent; cities, states, vw_capitals and vw_cities_2163 (the one based on the original geom column) align correctly with one another but they do not align with the other vw_cities_2163 (the one based on the geom_2163 column) and vw_states_2163.
To get all of the layers to realign with one another we would need to re-enable on-the-fly re-projection.
This section showed how to save queries as views which can then be utilized in the same way as tables. In the next section we'll go into a bit more detail on the topic of spatial references.