- QGIS 2 Cookbook
- Alex Mandel Víctor Olaya Ferrero Anita Graser Alexander Bruy
- 662字
- 2021-07-16 11:12:40
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.
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>asSELECT: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); - 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.
See also
- Read more about writable-view at https://www.gaia-gis.it/fossil/libspatialite/wiki?name=writable-view. This recipe is extremely similar to the next one on PostGIS and demonstrates how interchangeable the two can be if you are aware of the slight differences.