Creating views in PostGIS_QGIS 2 Cookbook-QQ阅读女频现言网

上QQ阅读APP看书，第一时间看更新

Creating views in PostGIS

In a database, a view is a stored query. Every time that you open it, the query is run and fresh results are generated. To use views as layers in QGIS takes a couple of steps.

Getting ready

For this recipe, you'll need a query that returns results containing a geometry. The example that we'll use here is the query from the Joining tables in databases recipe where attributes were joined 1:1 between the census polygons and the population CSV.

How to do it…

The SQL method is described as follows:

In Database | DB Manager, open SQL Window.
Write a query; in this example, this is the join query that was written in the previous exercise. If you want to see it right away but not necessarily retain it, check the Load as new layer checkbox near the bottom:
```
SELECT *
FROM census_wake2000 as a
JOIN census_wake2000_pop as b
ON a.stfid = b."STFID";
```
Now, execute the query by clicking on the Execute (F5) button:
After executing the query, to load it to the map check the Load as new layer box, which will expand some additional options. Pick your unique integer (id_0) for Column with unique integer values and geom for Geometry column. Name your result in the Layer name (prefix) textbox and click on Load now!.

Note

If you only needed to see this data in this particular QGIS project, you can stop here. In order to make the database store this query for other projects and users, continue this recipe.

Convert this query to a view by adding CREATE VIEW <name> AS SELECT:

CREATE VIEW census_wake2000_pop_join AS SELECT *
FROM census_wake2000 as a
JOIN census_wake2000_pop as b
ON a.stfid = b."STFID";

Go back to DB Manager and hit the Refresh button (on the left). You should now see your new view listed and be able to add it to the map.

How it works…

QGIS reads the metadata tables or views of PostGIS in order to figure out what layers contain spatial data, what kind of spatial data they contain, and which column contains the geometry definition. Without creating entries in the metadata, the tables appear as normal PostgreSQL tables, and you can only load attribute data without spatial representation.

As this is a view, it's really reading the geometries from the original tables. Therefore, any edits to the original table will also show up.

There's more…

QGIS is really picky about having a unique ID for PostGIS tables and views. There are a few tips to make this always work. Always include a numeric unique ID (as the first column is recommended but not required, IDs must be integer columns (usually int4, but int8 should work now too). Autoincrementing IDs are good idea. When you don't have such an ID field to use from one of the underlying tables, you can add an ID on the fly with the following:

SELECT row_number() OVER() AS id_qgis, <add the other fields you want here> FROM table;

The downside of this is that you now have to list out all the fields that you want to use in the view rather than using *. When creating tables, you'll want to turn this id_qgis field into an auto-incrementing field if you plan to add records.

The other big catch is that if you make a new geometry by manipulating existing geometries, QGIS isn't always aware of the results. In the previous example, the geometry is just passed from the original table to the view unchanged, so it is properly registered in the geometry_columns metadata of PostGIS. However, a new geometry doesn't exist in the original table, so the trick is to cast the geometry result, as follows:

CREATE VIEW census_wake2000_4326 AS
SELECT id_0,
stfid,tractid,ST_Transform(geom,4326)::geometry(GeometryZ, 4326)
As geom
FROM census_wake2000;

QGIS doesn't always think that this is a valid spatial layer but adding to the Canvas should work.

Tip

The more specific you can be, the better. If you're not sure what geometry type it is or if you have 3D (aka Z), check the entries in the geometry_columns view.

Also, keep your eyes on Postgres's relatively new feature called Materialized Views. This is a method of caching view results that don't update automatically, but they also don't require whole new tables.