![]() |
Recipe #4: |
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:
|
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); |
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 |
... | ... | ... |
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 |
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 |
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. |