Creating views in SpatiaLite_QGIS 2 Cookbook-QQ阅读女生中文现言网

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

Creating views in SpatiaLite

In a database, view is a stored query. Every time 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 is the query from the Joining tables in databases recipe (the previous recipe) where attributes were joined 1:1 between the census polygons and the population CSV. The QSpatiaLite plugin is recommended for this recipe.

How to do it…

The GUI method is described as follows:

Using the QspatiaLite plugin (which is in the Database menu, if you've activated it) place the following in the query:
```
SELECT *
FROM census_wake2000 as a
JOIN census_wake2000_pop as b
ON a.stfid = b.stfid;
```
From the Option dropdown, select the last choice, Create Spatial View & Load in QGIS, and set the Geometry field box value to the name of your geometry field from your spatial layer. In this example, this is geom.

Tip

You can explore your data table fields in the left-hand side to check the name of the fields that you need.

The SQL method is as described, as follows:

In Database | DB Manager, open SQL Window.
Write a query. In this example, this is the Join query from the previous 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;

Register the view with the SpatiaLite metadata backend with a follow up query. This function is case sensitive:
```
CREATE VIEW census_wake2000_pop_join AS
INSERT INTO views_geometry_columns
(view_name, view_geometry, view_rowid, f_table_name, f_geometry_column,read_only)
VALUES ('census_wake2000_pop_join', 'geom', 'rowid', 'census_wake2000', 'geom',1);
```
Tip

This only works when the view geometry is based on the geometry of a single table. If you need to generate new geometries, you probably need a table.
The pattern is ('name of view','name of view geometry field','A Unique ID','name of table the view gets its geometry from','name of geometry field in the original table',read-only (1) or writable(0)).
After running the second query, you should be able to load the view in QGIS and see the same fields as the join query.

How it works…

A view is actually stored in the database and is triggered when you load it. In this way, if you change the original data tables, the view will always be up to date. By comparison, creating new tables makes copies of the existing data, which is stored in a new place, or creates a snapshot or freeze of the values at that time. It also increases the database's size by replicating data. Whereas, a view is just the SQL text itself and doesn't store any additional data.

QGIS reads the metadata tables of SpatiaLite 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 SQLite tables, and you can only load attribute data without spatial representation.

As it's a view, it's really reading the geometries from the original tables. Therefore, any edits to the original table will show up. New in SpatiaLite 4.x series, this makes it easier to create writable views. If you use the spatialite-gui standalone application, it registers all the database triggers needed to make it work, and the changes made will affect the original tables.

There's more…

You don't have to use ROWID as unique id, but this is a convenient handle that always exists in SQLite, and unlike an ID from the original table, there's no chance of duplication in an aggregating query.