SpatiaLite Cookbook

	Recipe #4: About VIEW
2011 January 28

SQL supports a really useful feature, the so called VIEW.
Very shortly explained, a VIEW is something falling half-way between a TABLE and a query:

a VIEW is a persistent objects (exactly as TABLEs are).
you can query a VIEW exactly in the same way you can query a TABLE:
there is no difference at all distinguishing a VIEW and a TABLE from the SELECT own perspective.
but after all a VIEW simply is like a kind of glorified query.
A VIEW has absolutely no data by itself.
Data apparently belonging to some VIEW are simply retrieved from some other TABLE each time they are actually required.
in the SQLite's own implementation any VIEW strictly is a read-only object:
you can freely reference any VIEW in SELECT statements.
But attempting to perform an INSERT, UPDATE or DELETE statement on behalf of a VIEW isn't allowed.

Anyway, performing some practical exercise surely is the best way to introduce Views.

CREATE VIEW view_lc AS
SELECT lc.lc_id AS lc_id,
  lc.lc_name AS lc_name,
  lc.population AS population,
  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,
  lc.geometry AS geometry
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);

Et voila, here is your first VIEW:

basically, this looks exactly as any query you've already seen since now.
except in this; this time the first line is: CREATE VIEW ... AS
and this one is unique syntactical difference transforming a plain query into a VIEW definition.

SELECT lc_name, population, county_name
FROM view_lc
WHERE region_name = 'LAZIO'
ORDER BY lc_name;

lc_name	population	county_name
ACCUMOLI	724	RIETI
ACQUAFONDATA	316	FROSINONE
ACQUAPENDENTE	5788	VITERBO
ACUTO	1857	FROSINONE
AFFILE	1644	ROMA
...	...	...

You can actually query this VIEW.

SELECT region_name,
  Sum(population) AS population,
  (Sum(ST_Area(geometry)) / 1000000.0)
    AS "area (sq.Km)",
  (Sum(population) /
    (Sum(ST_Area(geometry)) / 1000000.0))
      AS "popDensity (peoples/sq.Km)"
FROM view_lc
GROUP BY region_id
ORDER BY 4;

region_name	population	area (sq.Km)	popDensity (peoples/sq.Km)
VALLE D'AOSTA	119548	3258.405868	36.689107
BASILICATA	597768	10070.896921	59.355984
...	...	...	...
MARCHE	1470581	9729.862860	151.140979
TOSCANA	3497806	22956.355019	152.367656
...	...	...	...
LOMBARDIA	9032554	23866,529331	378.461144
CAMPANIA	5701931	13666.322146	417.224981

You can really perform any arbitrary complex query using a VIEW.

SELECT v.lc_name AS LocalCouncil,
  v.county_name AS County,
  v.region_name AS Region
FROM view_lc AS v
JOIN local_councils AS lc ON (
  lc.lc_name = 'NORCIA'
    AND ST_Touches(v.geometry, lc.geometry))
ORDER BY v.lc_name, v.county_name, v.region_name;

LocalCouncil	County	Region
ACCUMOLI	RIETI	LAZIO
ARQUATA DEL TRONTO	ASCOLI PICENO	MARCHE
CASCIA	PERUGIA	UMBRIA
CASTELSANTANGELO SUL NERA	MACERATA	MARCHE
CERRETO DI SPOLETO	PERUGIA	UMBRIA
CITTAREALE	RIETI	LAZIO
MONTEMONACO	ASCOLI PICENO	MARCHE
PRECI	PERUGIA	UMBRIA

You can JOIN a VIEW and a TABLE (or two VIEWs, and so on ...)
Just a simple explanation: this JOIN actually is one based on Spatial relationships:
the result-set represents the list of Local Councils sharing a common boundary with the Norcia one.
You can get a much more complete example here (Haute cuisine recipes).

VIEW is one of the many powerful and wonderful features supported by SQL.
And SQLite's own implementation for VIEW surely is a first class one.
You should use VIEW as often as you can: and you'll soon discover that following this way handling really complex DB layouts will become a piece of cake.

Please note: querying a VIEW can actually be as fast and efficient as querying a TABLE.
But a VIEW cannot anyway be more efficient than the underlying query is; any poorly designed and badly optimized query surely will translate into a very slow VIEW.

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.