Jointures et agrégations-PostGIS

A Rappel sur les jointures SQL

Les liens suivants sont de qualité et expliquent bien les différents types de jointure. Une base essentielle à comprendre avant de poursuivre le module, qui se sert de ces notions dans des montages de plus en plus complexes :

AA Jointures CROSS et INNER, implicites et explicites

Une jointure implicite est réalisée en indiquant plusieurs tables dans la clause FROM sans opérateur JOIN . C’est conceptuellement une jointure croisée (CROSS JOIN )

La première requête (jointure implicite) est équivalente à la seconde (explicite avec CROSS JOIN ) :

SELECT * FROM table1, table2

SELECT * FROM table1 CROSS JOIN table2

On peut ajouter une condition dans le WHERE limitant la jointure, dans ce cas il s’agit conceptuellement d’une jointure INNER JOIN . De ce fait, les 2 requêtes suivantes sont équivalentes :

SELECT * FROM table1, table2 WHERE table1.id = table2.id

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id

On notera que pour ces jointures, on peut permuter table1 et table2 sans changer le résultat, mis à part l’ordre de tri des enregistrements et des colonnes.

Soient les tables products et tags suivantes :

CREATE TABLE products
(
 id serial,
 name character varying,
 CONSTRAINT products_pkey PRIMARY KEY (id)
)

CREATE TABLE tags
(
 product_id integer NOT NULL,
 tag character varying NOT NULL,
 CONSTRAINT tags_pkey PRIMARY KEY (product_id, tag),
 CONSTRAINT tags_product_id_fkey FOREIGN KEY (product_id)
     REFERENCES products (id)
)

Noter que la contrainte FOREIGN KEY garantit ici que tout enregistrement de tags a un product_id qui correspond bien à un id de la table products . On sera alors certain que tout tags correspond bien à un products , mais l’inverse n’est pas nécessaire (un products peut ne pas avoir de tags ).

Insérer les contenus suivants, respectivement dans les tables products et tags :

"1";"poirot"
"2";"poire"
"3";"orange"
"4";"carotte"
"5";"radis"

1;"légume"
1;"tige"
2;"fruit"
3;"fruit"
4;"légume"
4;"racine"

QUESTION AAA La requête SELECT retournant tous les champs issus de la jointure CROSS implicite entre products et tags

QUESTION AAB Nombre de résultats

QUESTION AAC La requête SELECT retournant tous les champs issus de la jointure CROSS explicite entre products et tags

QUESTION AAD La requête SELECT retournant tous les champs issus de la jointure INNER implicite entre products et tags sur la clef étrangère tags_product_id_fkey

QUESTION AAE Nombre de résultats de AAD

QUESTION AAF La requête SELECT retournant tous les champs issus de la jointure INNER explicite entre products et tags sur la clef étrangère tags_product_id_fkey

AB Jointures OUTER : LEFT et RIGHT

Si la jointure INNER multiplie les tables entres elles, en revanche elle ne laisse pas passer les enregistrements qui n’ont pas leur correspondance dans l’autre table. Dans bon nombre de cas, on sert de la jointure comme pour associer (on dit aussi apparier ) une table d’attributs à une table de départ. On souhaite alors qu’en l’absence d’attributs, un enregistrement de la table de départ soit préservé, quitte à mettre les champs sans correspondance à NULL .

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id

Dans cette requête, tous les enregistrements de table1 seront présents à la sortie, bien que répétés pour ceux qui disposent de plusieurs équivalents (d’après id ) dans table2

Permuter table1 et table2 ne produit pas le même résultat. La table de gauche est première et celle de droite seconde.

En Français : On requête la table table1 en y associant table2 par correspondance sur le champ id .

QUESTION ABA La requête SELECT retournant tous les champs issus de la jointure LEFT OUTER entre products (gauche) et tags (droite) sur la clef étrangère tags_product_id_fkey

QUESTION ABB Nombre de résultats de ABB

Remplacer LEFT par RIGHT rend primaire la table de droite, celle dont tous les enregistrements seront tous conservés. Ces 2 requêtes sont équivalentes :

SELECT col1, col2 FROM table1 LEFT JOIN table2 ON table1.id = table2.id

SELECT col1, col2 FROM table2 RIGHT JOIN table1 ON table1.id = table2.id

QUESTION ABC La requête SELECT strictement équivalente (y compris les colonnes) à la requête ABB en terme de résultats, en faisant un RIGHT JOIN .

AC Jointures en cascade

Effectuer plusieurs jointures implicites est facile :

SELECT * FROM table1, table2, table 3

SELECT * FROM table1, table2, table 3 WHERE table1.id = table2.id AND table2.id = table3.id

Dans cet exemple, à table1 est jointe table2 , et enfin, à ce résultat intermédiaire, est jointe table3 : cet ordre (de gauche à droite) détermine l’ordre des colonnes et des enregistrements, mais à part cet ce tri le résultat est le même : la jointure CROSS est commutative et associative .

Il en va de même pour les jointures CROSS et INNER explicites :

SELECT * FROM table1 CROSS JOIN table2 CROSS JOIN table 3

Les jointures INNER sont également commutatives et associatives (leur ordre n’importe pas) :

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id
                    INNER JOIN table3 ON table2.id = table3.id

Les jointures OUTER (c’est-à-dire LEFT , RIGHT ou FULL ) par contre, ne sont pas commutatives . Il faut donc bien partir de LA table à requêter (ce qu’on cherche en premier lieu), puis y joindre les autres tables :

SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id
                    LEFT JOIN table3 ON table2.id = table3.id

Mixer des jointures INNER et LEFT (ou autres) est possible. Les jointures sont effectuées de gauche à droite.

SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id
                    LEFT  JOIN table3 ON table2.id = table3.id

AD Agrégations

L’agrégation a lieu après les jointures (le FROM est évalué) mais avant évaluation de la clause SELECT . Dans cet exemple :

SELECT age FROM table1 GROUP BY age

Les enregistrements de table1 sont groupés par age . Seuls les groupes peuvent sortir dans le résultat. On ne pourrait pas écrire

SELECT id, age

car id n’est pas agrégé par le groupe. En revanche on pourrait écrire ceci :

SELECT age, count(id) FROM table1 GROUP BY age

Car on ne peut inclure dans le SELECT que :

Les champs utilisés pour le groupement (champs du GROUP BY )
D’autres champs ou expression de champs non groupés à condition de les agréger par une fonction d’aggrégation telle que count() , min() , max() , etc.

QUESTION ADA Requête SELECT sur products et tags qui retourne la liste de tous produits (identifiant et nom) ainsi que leur nombre de tags associés dans une colonne nommée tag_count

AE Sous-SELECT

Le sous-SELECT est un SELECT utilisé en tant que table dans la clause FROM d’une requête SELECT . Ces 2 requêtes sont équivalents :

SELECT nom FROM (SELECT id, nom FROM table1 WHERE nom LIKE ’A%’) AS q WHERE id > 10

SELECT nom FROM table1 WHERE nom LIKE ’A%’ AND id > 10

La technique devient indispensable pour effectuer des jointures d’agrégations , c’est-à-dire une agrégation (SELECT [...] GROUP BY [...] ) elle-même jointe à une table (voire une autre agrégation...). Exemple :

SELECT FROM
 (SELECT nom, count(id) AS id FROM table1 GROUP BY nom) AS sub
 LEFT JOIN table2 ON sub.id = table2.id

Solliciter le professeur pour des explications au tableau si des zones d’ombres persistent : différence entre jointures d’aggrégation et aggrégation de jointures, etc. Ce sont des notions pas toujours intuitives mais absolument nécessaires aux opérations complexes qui vont suivre.

B Jointures & aggrégations spatiales

BA Jointures sur champ spatial

Le mot-clef ON permet, si on y regarde, demande une expression booléenne (qui s’évalue en vrai ou faux ). Cette expression peut être autre chose q’une égalité d’identifiants numériques. Par exemple :

SELECT * FROM table1 INNER JOIN table2 ON table1.id > table2.id

SELECT * FROM table1 INNER JOIN table2 ON (table1.id + 1) > table2.id

Utiliser des expressions prend tout son sens avec les aggrégations (qui permettent ensuite de réduire ce qui a été développé avec l’expression).

Plus généralement, l’expression indique le mode de relation qu’on souhaite entre les 2 tables ainsi jointes. Spatialement, cela permet de récupérer par exemple la liste des points d’intérêts et pour chacun le nom de la commune dans laquelle il se trouve , avec une requête du type :

SELECT poi.*, communes.nom AS commune
 FROM poi LEFT JOIN communes ON ST_Within(poi.geom, communes.geom)

À partir de la table etablissements_culturels importée du module Import-Export :

QUESTION BAA Requête SELECT affichant les colonnes organisme , adresse , cpville de etablissements_culturels ainsi que la colonne nom de la communes dans laquelle il se trouve spatialement

BB Aggrégation spatiale

De la même façon que la fonction count() aggrège un groupe de valeurs en une valeur unique, certaines fonctions PostGIS fonctionnent en mode aggrégation . Notamment :

ST_Union : retourne une unique géométrie qui est l’union de toutes les géométries en entrée
ST_Collect : assemble les géométries en une MULTIGEOMETRY
ST_Extent : retourne la box2d (enveloppe) englobant toutes les géométries

À partir de la table communes , écrire la requête qui groupe les communes par département selon les 2 premiers chiffres du code insee . Elle doit sortir le champ code_dpt (2 chiffres), communes (le nombre de communes) et geom (polygone du département, union des polygones des communes).
Documentation utile : String Functions and Operators

QUESTION BBA Durée de l’exécution de la requête (en millisecondes)

QUESTION BBB La requête SELECT [...] GROUP BY

Enregistrer le résultat dans la table departements en faisant :

CREATE TABLE departements AS ... requête SELECT telle quelle ...

Afficher la carte des départements sous QGIS , pour se faire plaisir :)
Utiliser l’outil d’Identification (l’icône avec le i ) pour cliquer sur les départements et vérifier les attributs

Nous allons a présent créer une carte des départements de France avec un cercle proportionnel sur chaque centroïde représentant le nombre de d’établissements culturels dans le département.

Il s’agit de la table etablissements_culturels importée dans le module Import-Export , à joindre à departements
La carte est composée de 2 couches. Le fond est simplement la table departement et la couche thématique est la requête à écrire.
Il faut sortir les champs: code_dpt , centroid (geometry ), nb_etablissements
Sur QGIS , pour le style de la couche thématique, créer un symbole de point (disque) et cliquer sur Avancé → Champ de proportion . Couleur libre.

QUESTION BBC Requête SELECT pour la couche thématique

QUESTION BBD PNG QGIS sur l’étendue de la France métropolitaine

QUESTION BDE Requête SELECT de BBC sans passer par la table departements (tout en une seule grosse requête)

C Données carroyées

CA Découper les carroyages selon les limites administratives

Nous allons reprendre les carroyages de la table r_rfl09_laea1000 importée dans le module Import-Export pour produire une nouvelle table, r_rfl09_cut_dpt , où les carreaux à cheval sur 2 départements ou plus sont coupés en morceaux qui épousent la frontière.

QUESTION CAA SRID du carroyage r_rfl09_laea1000

QUESTION CAB SRID des departements

QUESTION CAC Faut-il transformer r_rfl09_laea1000 dans le SRID de departements ou bien transformer les departements dans le SRID de r_rfl09_laea1000 ?

Faire la transformation de SRID nécessaire sans remplacer les géométries d’origine , mais en ajoutant une colonne geom_xxxx (où xxxx est le SRID)
(il s’agit du même type d’opération que dans le module Type GEOMETRY → Reprojeter une table entière )

Comment penser la jointure ?

Les départements servent à découper les carreaux
Pour chaque carreau , on produit autant de bouts de carreaux que de départements qu’il intersecte
Chaque bout de carreau est l’intersection entre un carreau et un département

Écrire la jointure avec les champs SELECT suivants :

r_rfl09_laea1000.ogc_fid AS carreau
departements.code_dpt
ind_carreau qui est la valeur d’origine de r_rfl09_laea1000.ind
pct_aire , rapport de l’aire de l’intersection sur l’aire du carreau entier
la géométrie résultant de l’intersection entre le carreau et le département AS geom

Indices :

la condition de jointure (ON ) est la pour restreindre la jointure (l’intersection) aux combinaisons qui intersectent (on ne veut pas d’intersections vides)
c’est une jointure INNER car on ne conçoit pas de carreau en dehors de tout département
il n’y a pas de condition WHERE
il est impensable d’exécuter la jointure d’intersections sans avoir au préalable créé les index spatiaux sur les champs géométriques intersectés
... car sans index, PostGIS doit tester une à une toutes les combinaisons, soit 375 279 carreaux avec 97 départements, c’est-à-dire 36 402 063 (36 millions !) d’intersections.
→ faire EXPLAIN SELECT pour vérifier que la condition d’intersection exploite l’index (Index Cond: (geom && [...]) )
Même avec index, la requête peut durer 20-30 minutes. Pour des essais, restreindre à quelques départements (ex: WHERE code_dpt IN (’75’, ’92’, ’93’, ’94’) ).

QUESTION CAD Requête SELECT de la jointure

Créer la table en exécutant la requête précédée par CREATE TABLE r_rfl09_cut_dpt AS

QUESTION CAE Durée d’exécution de la requête, en millisecondes

Puis ajouter un index spatial à cette nouvelle table, pour que la navigation QGIS soit fluide
Afficher la couche sur QGIS

CB Grouper par territoire et appliquer la somme pondérée par la quote-part de surface

Premiers enregistrements de la table departements_popul

Reprendre la requête précédente en groupant par departements.code_dpt pour produire la table departements_popul listant les départements avec les colonnes :

code_dpt issu de departements.code_dpt
geom issu de departements.geom
popul calculé de la somme des indices de population (r_rfl09_laea1000.ind ) des morceaux de carreaux intersectant le département pondérés par leur quote-part d’aire dans l’aire du carreau d’origine

Documentation PostgreSQL : Fonctions d’aggrégation

QUESTION CBA La requête CREATE TABLE departements_popul AS SELECT ...

QUESTION CBB Quantité d’habitants à Paris

QUESTION CBC Quantité d’habitants en Seine-et-Marne

CC Produire la carte des stocks de population

L’applat de couleurs (chloroplethe) n’est pas un mode de représentation cartographique adapté à un stock, car l’importance du phénomène serait doublement représenté (variable de couleur et taille de la surface). Il s’agit là de règles de sémiologie. Pour des stocks (de population ou autre), il convient d’utiliser des symboles proportionnels centrés sur le centroïde du territoire dont ils représentent la variable.

Nous allons donc utiliser produire une carte avec 2 couches :

Au fond : les polygons des départements, en bordure gris foncé, remplissage gris clair, bordure de 0.1 millimètre
Par dessus : disques de couleur bleu foncé, centrés sur le centroïde de leur département et dont l’aire est proportionnelle à la quantité de population du département.

Sachant que :

Les cercles plus petits doivent s’afficher par dessus les plus gros, donc il faut faire un tri dans la requête par taille de cercle
Le centroïde doit être calculé dans la requête SQL (et non pas dans QGIS)
On produira côté requête le diamètre à appliquer aux cercles, en millimètres écran (pas en unités de carte )
On demandera à QGIS un style Symbole unique , Symbole simple , un disque bleu ciel à fine bordure noire et de diamètre proportionnel au champ size qu’on aura produit dans la requête
La taille du symbole, qui sert de facteur constant de taille, sera à ajusté pour une carte belle, où les départements restent visibles avec une taille de disques correcte.
On fera la vue departements_popul_centroid à partir de la table departements_popul
Pour que QGIS traite correctement la vue, il faut contraindre le type de la géométrie produite : utiliser le casting ::geometry(Point,4326)

QUESTION CCA Requête SELECT de la vue departements_popul_centroid

QUESTION CCB Carte QGIS sur l’emprise France métropolitaine

CD Produire la carte des densités de population

De la même façon que pour CB , créer la vue departements_popul_ratio reprenant les colonnes de departements_popul en ajoutant la colonne popul_densite représentant la densité de population en nombre d’habitants par kilomètre-carré dans la projection Lambert 93 .

Dans QGIS , faire la carte de densité par Style gradué sur la colonne popul_densite en 5 classes divisées en Quantiles (effectifs égaux)

QUESTION CDA SRID de la projection Lambert 93

QUESTION CDB Requête SELECT de la vue departements_popul_ratio

QUESTION CDC Carte QGIS sur l’emprise France métropolitaine

D Production de géométries à partir d’une jointure

DA Générer des frontières

L’intersection entre 2 polygones ne produit pas toujours un polygone. Si les polygones se touchent (au sens de ST_Touches ), alors l’intersection produit une ligne, celle que partagent les polygones. Quant à 2 polygones qui se touchent sur un seul sommet, leur intersection produit un point.

Il est possible d’effectuer une jointure d’une table avec elle-même en précisant des alias différents, comme ceci :

SELECT [...] FROM table t1 INNER JOIN table t2 ON [.. expression impliquant t1 et t2 ..]

Une couche territoriale (où aucun territoire ne chevauche un autre) peut être jointe à elle-même sur la condition ST_Touches (t1.geom, t2.geom) pour récupérer la ligne de frontière avec l’expression ST_Intersection (t1.geom, t2.geom) .

Exécuter la requête suivante pour générer les frontères des departements :

CREATE TABLE departements_frontieres as
SELECT
 a.code_dpt || ’-’ || b.code_dpt AS id,
 a.code_dpt AS a, b.code_dpt AS b,
 ST_Intersection(a.geom, b.geom)::geometry(Geometry, 4326) AS frontiere

FROM departements a
  INNER JOIN departements b ON ST_Touches(a.geom, b.geom)

QUESTION DAA Temps d’exécution en millisecondes

Puis créer la clef primaire :

ALTER TABLE departements_frontieres ADD PRIMARY KEY (id);

Et l’index spatial :

CREATE INDEX ON departements_frontieres USING gist (frontiere);

Puis l’afficher dans QGIS (couleur libre)

QUESTION DAB Carte QGIS sur l’emprise France métropolitaine

DB Étudier les discontinuités

À partir de la table departements_frontieres , écrire la requête pour produire la vue departements_frontieres_popul_disc avec la colonne supplémentaire disc valant le delta sur zéro du rapport des densités des départements de part et d’autre de la frontière concernée.

En jointant sur la vue departements_popul_densite créée précédemment
Inclure les colonnes densite_a et densite_b correspondant à la valeur popul_densite des départements de part et d’autre de la frontière
Des densités égales donnent le delta zéro (+ 0 %), des densités de 1 et 2 donnent le delta 1 (+ 100 %), des densités de 150 et 100 donnent le delta 0.5 (+ 50 %), etc.
Il n’y a pas de sens à avoir un rapport négatif (la frontière est symétrique entre A et B), il faut donc prendre la valeur absolue
Documentation PostgreSQL : Expressions conditionnelles

Puis dans QGIS , produire la carte de discontinuités avec 2 couches :

Fond : rendu CDC (densités des départements en dégradés de bleu)
Discontinuités : frontières de la vue departements_frontieres_popul_disc en rouge de largeur 0.1 millimètres (écran) multipliés par le delta disc

QUESTION DBA Requête SELECT de la vue departements_frontieres_popul_disc

QUESTION DBB Carte QGIS sur l’emprise France métropolitaine

QUESTION DBC Quel est le delta de densité entre la Seine-et-Marne et la Seine-Saint-Denis (exprimé en + x % ) ? Laquelle est la plus dense ?

QUESTION DBD Par ailleurs, quel est le delta des stocks de population ? (+ x % en nombre d’habitants)

PostGIS › Jointures et agrégations

Objectifs du module

À retenir par coeur