Displaying SpatialLite view in custom data sheet with Print Composer in QGIS

Question

I created a SpatiaLite Database with a point layer (every point is a tree). Additional I created several tables to fill the columns of the spatial layer with attributes using the editing widget “value relations”.

I need this because I want to speed up data entry. For some other attribute fields the data entry occurs via “line edit”. I also designed a custom form like Nathan Woodrow describes it in his blog. This works pretty good.

However, for output I want to create several custom data sheets with the print composer - one for every tree. Because I want to display the values from my linked tables and not the common keys I created a view that is composed of all attributes I need inclusive the geometry column (geom) of the spatial layer.

CREATE VIEW tree_attrib1 AS
    SELECT
        baumkataster.ROWID AS ROWID,
        baumkataster.baum_ID,
        baumkataster.geom AS geom,
        gattung.gattung AS gattung,
        art.art AS art
    FROM
        baumkataster
    JOIN gattung
        ON baumkataster.gattung = gattung.gattung_ID 
    JOIN art
        ON baumkataster.art = art.art_ID;

To use the atlas generator in the print composer I need to “convert” my view into a point layer.

My questions now are:

1. How can I display my view as an point layer in QGIS?
2. A view is stored only temporary. Will this new point layer be updated when I insert new features (trees)?

Answer 1

You can add the view as you add a normal spatialite table, if the view has field of unique integers. In the add dialog, to the right of the view name is an ID field drop down, select your unique integer field there.

You could also enter your SQL statement in DB Manager's SQL window, and add the layer from there. You'll still need a unique integer field in your query.

As for question 2, yes, the view will be updated the next time you refresh your QGIS window after the background data has been updated.

EDIT: For QGIS to see your view you need to register it with your geometry_columns table. For details on how see the SpatiaLite Cookbook's section on views.

Answer 2

Additional to the SpatiaLite Cookbook's workflow I found a good solution for for this problem here.