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:

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.

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.

See also