SpatiaLite Cookbook

	recette #11 Livre Guinness des records
F�vrier 2011

Pr�c�dent

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.

Pr�c�dent

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.