SpatiaLite Cookbook

	recette #11 Livre Guinness des records
Février 2011

Table des matières

Local Council	County	Region
ATRANI	SALERNO	CAMPANIA
BARDONECCHIA	TORINO	PIEMONTE
BRIGA ALTA	CUNEO	PIEMONTE
CASAVATORE	NAPOLI	CAMPANIA
LAMPEDUSA E LINOSA	AGRIGENTO	SICILIA
LU	ALESSANDRIA	PIEMONTE
MORTERONE	LECCO	LOMBARDIA
NE	GENOVA	LIGURIA
OTRANTO	LECCE	PUGLIA
PINO SULLA SPONDA DEL LAGO MAGGIOR	VARESE	LOMBARDIA
PREDOI	BOLZANO	TRENTINO-ALTO ADIGE
RE	VERBANO-CUSIO-OSSOLA	PIEMONTE
RO	FERRARA	EMILIA-ROMAGNA
ROMA	ROMA	LAZIO
SAN VALENTINO IN ABRUZZO CITERIORE	PESCARA	ABRUZZO
VO	PADOVA	VENETO

La liste de local councils ci dessus est une sorte de live Guinness des Records.
Pour une raison ou pour une autre, chacun possède une caractéristique exceptionnelle.
Peut-être êtes-vous étonnés, car (excepté Rome) aucune d'elles ne fait partie des lieux les plus renommées d'Italie.
Voici l'explication:

Roma à le plus d'habitants: 2,546,804 habitants
Morterone à le moins d'habitants: 33 habitants
Roma est la plus grande ville: 1,287 km²
Atrani est la plus petite ville: 0.1 km²
Casavatore à la plus forte densité de population: 13,627.5 hab/km²
Briga Alta à la plus faible densité de population: 1.2 habs/km²
Pino sulla sponda del Lago Maggior(e) et San Valentino in Abruzzo Citeriore partagent le privilège d'avoir le nom le plus long.
de l'autre coté Lu, Ne, Re, Ro et Vo ont les noms les plus courts.
Predoi est situé le plus au nord
Lampedua e Linosa est situé le plus au sud
Bardonecchia est situé le plus à l'ouest
Otranto iest situé le plus à l'est

Mettre au point une telle table, aussi inutile soit-elle, est assez facile.
Voici la requête SQL correspondante.

SELECT lc.lc_name AS LocalCouncil,
  c.county_name AS County,
  r.region_name AS Region,
  lc.population AS Population,
  ST_Area(lc.geometry) / 1000000.0 AS "Area sqKm",
  lc.population / (ST_Area(lc.geometry) / 1000000.0)
    AS "PopDensity [peoples/sqKm]",
  Length(lc.lc_name) AS NameLength,
  MbrMaxY(lc.geometry) AS North,
  MbrMinY(lc.geometry) AS South,
  MbrMinX(lc.geometry) AS West,
  MbrMaxX(lc.geometry) AS East
FROM local_councils AS lc
JOIN counties AS c ON (c.county_id = lc.county_id)
JOIN regions AS r ON (r.region_id = c.region_id)
WHERE lc.lc_id IN (
    SELECT lc_id
    FROM local_councils
    WHERE population IN (
      SELECT Max(population)
      FROM local_councils
    UNION
      SELECT Min(population)
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE ST_Area(geometry) IN (
      SELECT Max(ST_area(geometry))
      FROM local_councils
    UNION
      SELECT Min(ST_Area(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE population / (ST_Area(geometry) / 1000000.0) IN (
      SELECT Max(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils
    UNION
      SELECT MIN(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE Length(lc_name) IN (
      SELECT Max(Length(lc_name))
      FROM local_councils
    UNION
      SELECT Min(Length(lc_name))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxY(geometry) IN (
      SELECT Max(MbrMaxY(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinY(geometry) IN (
      SELECT Min(MbrMinY(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxX(geometry) IN (
      SELECT Max(MbrMaxX(geometry))
      FROM local_councils)
  UNION
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinX(geometry) IN (
      SELECT Min(MbrMinX(geometry))
      FROM local_councils));

Et oui, cette requête est assez complexe.
Mais vous êtes dorénavant dans la section Haute cuisine.
Donc je suppose que vous êtes à la recherche de requêtes goûteuses et épicées : les voilà !

Après tout, cette requête n'est pas aussi complexe qu'elle en a l'aire, sa structure est même plutôt simple.
analysons la étape par étape:

Vous pouvez maintenant reconnaître l'architecture de cette requête et comprendre ce qu'elle fait.
Rien de trop complexe ni de difficile:

la table local_councils est JOINte à la table counties .
la table counties est JOINte à la table regions.
la fonction spatiale ST_Area() retourne la superficie de la géométrie en m², la division permet d'obtenir des km².
une expression numérique est utilisée pour calculer la densité de population en habs/km²
la clause WHERE ... IN (...) permet de fileter les résultats.

Vous savez déjà tout ça, et je suppose que la suite vous intéresse davantage.
La partie la plus intéressante est située dans la clause WHERE ... IN (...)

...
SELECT Max(population)
FROM local_councils
...
SELECT Min(population)
FROM local_councils
...

Ce code SQL est vraiment simple: chaque requête détermine une valeur Min / Max.

...
  SELECT Max(population)
  FROM local_councils
UNION
  SELECT Min(population)
  FROM local_councils
...

C'est la première fois que nous introduisons la clause UNION:

cela permet de coller ensemble deux résultats, de façon à n'en former plus qu'un.
Il y a une condition à satisfaire: à la fois le résultat de droite et de gauche doit retourner le même nombre de colonne, et les colonnes correspondantes doivent contenir le même type de donnée.

SQL implémente un mécanisme fantastique: la sous-requete.
Vous pouvez définir une sous-requête, qui sera exécutée en premier, dont les résultats seront insérés dans la requête principale.
Maintenant, le code SQL ne paraît plus si mystérieux:

dans ce cas, la sous-requête est en fait une UNION.
la requête gauche retourne la valeur de Max(population).
la requête gauche retourne la valeur de Min(population).
puis UNION assemble les deux résultats:
le résultat de la sous-requête est constitué de de cet assemblage .
ainsi la clause WHERE population IN (...) de la requête principale va simplement recevoir deux valeurs de population à vérifier.
Enfin, la requête principale va retourner un résultats constitué d'une liste de lc_id

...
  SELECT lc_id
  FROM local_councils
  WHERE population IN (
      SELECT Max(population)
      FROM local_councils
    UNION
      SELECT Min(population)
      FROM local_councils)
UNION
  SELECT lc_id
  FROM local_councils
  WHERE ST_Area(geometry) IN
      SELECT Max(ST_area(geometry))
      FROM local_councils
    UNION
      SELECT Min(ST_Area(geometry))
      FROM local_councils)
...

Rien ne nous empêche de faire plus d'une UNION: cette requète est valide.
Ainsi, le code SQL ci-dessus s'interprète de cette façon:

comme nous l'avons vu précédemment, la requête de gauche fournis une liste de valeur de lc_id (Max/Min population).
la requête de droite effectue une opération équivalente et retourne une autre liste de valeur de lc_id (Max/Min ST_Area(geometry)).
Ainsi, l' UNION de second niveau va simplement retourner une liste allongée de valeurs de lc_id, i.e. la première assemblée à la deuxième
et ainsi de suite ...

SELECT lc.lc_name AS LocalCouncil,
  c.county_name AS County,
  r.region_name AS Region,
  lc.population AS Population,
  ST_Area(lc.geometry) / 1000000.0 AS "Area sqKm",
  lc.population / (ST_Area(lc.geometry) / 1000000.0)
    AS "PopDensity [peoples/sqKm]",
  Length(lc.lc_name) AS NameLength,
  MbrMaxY(lc.geometry) AS North,
  MbrMinY(lc.geometry) AS South,
  MbrMinX(lc.geometry) AS West,
  MbrMaxX(lc.geometry) AS East
FROM local_councils AS lc
JOIN counties AS c ON (c.county_id = lc.county_id)
JOIN regions AS r ON (r.region_id = c.region_id)
WHERE lc.lc_id IN (
--
-- a list of lc.lc_id values will be returned
-- by this complex sub-query
--
    SELECT lc_id
    FROM local_councils
    WHERE population IN (
--
-- this further sub-query will return
-- Min/Max POPULATION
--
      SELECT Max(population)
      FROM local_councils
    UNION
      SELECT Min(population)
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE ST_Area(geometry) IN (
--
-- this further sub-query will return
-- Min/Max ST_AREA()
--
      SELECT Max(ST_area(geometry))
      FROM local_councils
    UNION
      SELECT Min(ST_Area(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE population / (ST_Area(geometry) / 1000000.0) IN (
--
-- this further sub-query will return
-- Min/Max POP-DENSITY
--
      SELECT Max(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils
    UNION
      SELECT MIN(population / (ST_Area(geometry) / 1000000.0))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE Length(lc_name) IN (
--
-- this further sub-query will return
-- Min/Max NAME-LENGTH
--
      SELECT Max(Length(lc_name))
      FROM local_councils
    UNION
      SELECT Min(Length(lc_name))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxY(geometry) IN (
--
-- this further sub-query will return
-- Max NORTH
--
      SELECT Max(MbrMaxY(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinY(geometry) IN (
--
-- this further sub-query will return
-- Max SOUTH
--
      SELECT Min(MbrMinY(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMaxX(geometry) IN (
--
-- this further sub-query will return
-- Max WEST
--
      SELECT Max(MbrMaxX(geometry))
      FROM local_councils)
  UNION -- merging into first-level sub-query
    SELECT lc_id
    FROM local_councils
    WHERE MbrMinX(geometry) IN (
--
-- this further sub-query will return
-- Max EAST
--
      SELECT Min(MbrMinX(geometry))
      FROM local_councils));

D'après la syntaxe SQL, l'utilisation consécutive de deux tirets (--) permet de marquer un commentaire.
i.e. tout le texte situé après -- ne sera pas interprété par le moteur SQL.
Commenter ces requêtes permet de rendre plus accessibles des requêtes complexes.

Conclusion: SQL est un langage fantastique, basé sur une syntaxe simple et intuitive.
Chaque fois que vous serez face à une requête complexe, ne soyez pas intimidés. Essayez simplement de la découper en plusieurs parties et vous découvrirez que la complexité était moins grande que ce que vous imaginiez.

Astuce: debugger une requête SQL très complexe peut vite devenir fastidieux. Le meilleur moyen d'y parvenir est de la découper en requêtes simples, et de les tester individuellement.

Table des matières

	Author: Alessandro Furieri a.furieri@lqt.it Traduced From English by RIVIERE Romain
	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.