Views

Introduction

We cannot specify spatial SQL commands in QGIS as we might like (it would be a good extension in the future) but we can create "Views" of data that will be saved in PostgreSQL. Views are stored in the database and allow us to setup predefined queries for data.

Note: There are a great many variations to the steps below and some of them will work. However, the following steps have been tested repeatedly by me on a virtual machine running on a USB drive and if you vary from them you may find more problems than if you stick with them.

Creating Views with the GUI

You can create views using dialog boxes quickly and easily.

1. Enter the "natural_earth2" database and open a query window.
2. In this window, create the SQL query that you want to include within a view such as:
   1. SELECT * FROM ne_10m_populated_places WHERE pop_max > 1000000;
3. Make sure the query works and creates the output you expect
4. Open the "Views"
5. You'll see there are already a number of views that have been created.
6. Right click on "Views" and select "New View..."
7. Name this view, "large cities"
8. In the "Definition" tab copy the query from the query window and paste it into the Definition.
9. Add a semi-colon at the end of the statement to terminate the SQL command.
10. Click OK.
11. Right click on the view and select "View Data -> View All Rows...". You should see a table appear with all the cities that are over 1,000,000 in population.
12. Return to QGIS and click on the elephant icon to add a layer as you did before.
13. Select the "NaturalEarth" connection and you'll see your "large_cities" view has been added as a table.
14. Go ahead and add this "table" to QGIS and you'll see it is only a subset of all the cities.

Note: You may see a warning (triangle with an exclamation point). Expand the window and you'll see you may need to select an "SRID" and/or a "Feature id". Select "0" for the "SRID" as you can specify this later and select a unique ID for the feature ID.

Editing Views

If you right-click on a view and select "Properties", you'll notice a message indicating you might want to edit the view in the script editor. This is because the script editor is a more full-feature environment.

1. Right click on the view you created above and select "Scripts -> Create Script...". You'll see the script editor appear with some interesting SQL code in it.

Note: Remember that you can always copy the "SELECT" portion of the query and edit it in a regular query window. This method will make it easier to see errors and debug them.

Optional Material

Creating Views with SQL

Another way to create views is with T-SQL. Open a SQL window and enter the SQL below and execute it. Then, look at the "Views" in the database and see what you find.

CREATE VIEW <name> AS
	SELECT *
	FROM cities2sp
	WHERE acres>1000;

You can add spatial SQL commands into the View like the one below. Now we have a way to filter our data spatially within the database.

CREATE VIEW <name> AS
	SELECT ST_AsText(geom) 
	FROM cities2sp 
	WHERE ST_Within(geom,POLYGON((0 0,1300000 0,1300000 500000,0 500000,0 0)) 

Additional Resources

PostgreSQL Documentation on Views