官术网_书友最值得收藏!

首頁 >

QGIS 2 Cookbook

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:

  1. 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;
  2. 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.
    How to do it…

    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:

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

  5. 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)).
  6. 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

主站蜘蛛池模板： 柘城县| 新昌县| 华坪县| 襄城县| 吴堡县| 赫章县| 彰化市| 乌兰县| 山西省| 方正县| 东阿县| 内江市| 资阳市| 宽城| 阿巴嘎旗| 凉城县| 定远县| 慈溪市| 固镇县| 论坛| 远安县| 济阳县| 余江县| 响水县| 遵化市| 米脂县| 常德市| 横峰县| 元氏县| 文化| 玛多县| 赤壁市| 德化县| 阜康市| 习水县| 永宁县| 江山市| 清镇市| 叶城县| 光山县| 兴文县|