Note: these pages are no longer maintained

Never the less, much of the information is still relevant.
Beware, however, that some of the command syntax is from older versions, and thus may no longer work as expected.
Also: external links, from external sources, inside these pages may no longer function.

	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

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

	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.