SpatiaLite logo

Recipe #4:
About VIEW

2011 January 28

Previous Slide Table of Contents Next Slide

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:

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

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.