SpatiaLite logo

recipe #20
Spatial Views

2011 January 28

Previous Slide Table of Contents Next Slide

SpatiaLite supports Spatial Views: any properly defined Spatial View can then be used as any other map layer, i.e. can be displayed using QGIS .

Please note: any SQLite VIEW can only be accessed in read-mode (SELECT);
and obviously such limitation applies to any Spatial View as well (no INSERT, DELETE or UPDATE are supported).

Using the query composer tool

spatialite_gui supports a query composer tool; in this first example we'll use exactly this one.

query composer #1

Step 1: selecting the required tables and columns, and defining the corresponding JOIN condition.
In this first example we'll JOIN the local_councils and the counties tables.

query composer #2

Step 2: now we'll set an appropriate filter clause;
in this case only local_councils and counties belonging to Tuscany Region (region_id = 9) will be extracted.

query composer #3

Step 3: and finally we'll set an appropriate VIEW name: during this latest phase we'll select the Geometry column corresponding to this VIEW.

query composer #4

We are now able to display this Spatial View using QGIS (an appropriate thematic rendering was applied so to evidentiate Counties).

Hand-writing your own Spatial VIEW

CREATE VIEW italy AS
SELECT lc.ROWID AS ROWID,
  lc.lc_id AS lc_id,
  lc.lc_name AS lc_name,
  lc.population AS population,
  lc.geometry AS geometry,
  c.county_id AS county_id,
  c.county_name AS county_name,
  c.car_plate_code AS car_plate_code,
  r.region_id AS region_id,
  r.region_name AS region_name
FROM local_councils AS lc
JOIN counties AS c ON (lc.county_id = c.county_id)
JOIN regions AS r ON (c.region_id = r.region_id);
You are not obligatorily compelled to use the query composer tool.
You are absolutely free to define any arbitrary VIEW to be used as a Spatial View.

INSERT INTO views_geometry_columns
    (view_name, view_geometry, view_rowid, f_table_name, f_geometry_column)
  VALUES ('italy', 'geometry', 'ROWID', 'local_councils', 'geometry');
Anyway you must register this VIEW into the views_geometry_columns, so to make it become a real Spatial View.

SELECT * FROM views_geometry_columns;

view_name view_geometry view_rowid f_table_name f_geometry_column
tuscany geometry ROWID local_councils geometry
italy geometry ROWID local_councils geometry
Just a simple check ...

query composer #5

And finally we can display this Spatial View using QGIS (an appropriate thematic rendering was applied so to evidentiate Regions).
Previous Slide Table of Contents Next Slide

CC-BY-SA logo Author: Alessandro Furieri a.furieri@lqt.it
This work is licensed under the Attribution-ShareAlike 3.0 Unported (CC BY-SA 3.0) license.

GNU logo Permission is granted to copy, distribute and/or modify this document under the terms of the
GNU Free Documentation License, Version 1.3 or any later version published by the Free Software Foundation;
with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts.