Working with Views

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 RDBMSs, 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 Code tab, and paste the SQL statement held on the clipboard (Ctrl-V) into the text box.
Click Save to complete creation of the view.
Select View > Refresh (or hit F5) if you don't see the new view listed.
Assuming the original query and its output is still being displayed, click the X in the upper right of the page to close the query, then click Don't Save.
Right-click on vw_capitals in the Browser pane, and select View/Edit Data > All Rows. The Data Output pane will re-fill with the results of having ‘run’ the view.

You could now use this view any time you want to work with state capitals. It's important to note that the output from the view is not a snapshot from the moment you created the view; 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.

Open a new Query Tool tab and 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 > Properties, check the No CRS check box, and click OK.
Now, look under the PostGIS heading in the Browser Panel.
Your Lesson3 connection should be remembered in the Browser Panel. If your Lesson3 connection is not available, you will need to recreate it.

Click the arrow next to your Lesson3 connection selected to view the available schemas.

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. You should also see vw_capitals, vw_states_2163 and two versions of vw_cities_2163.

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 > Properties. You should see that the checkbox for No CRS is now unchecked. Apparently, QGIS detected the fact that the spatial reference property settings for the layers we added are not all the same, so it engaged the on-the-fly projection capability.
Check the No CRS box again, 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 on-the-fly projection 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.geom align correctly with one another, but they do not align with vw_cities_2163.geom_2163 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.

Working with Views