Warning

This page is obsolete and contains outdated information. It's still available only to preserve full historical record.

Please go to the current home page of the SpatiaLite project.



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.