Bases de données
Introduction aux requêtes SQL (y compris sur données spatiales)
Moritz Lennert
Table des matières
La composition une requête de sélection..............................................................................................2
Introduction......................................................................................................................................2
Exemple...........................................................................................................................................2
Jointures................................................................................................................................................3
Introduction......................................................................................................................................3
Exemple...........................................................................................................................................3
Les fonctions........................................................................................................................................4
Introduction......................................................................................................................................4
Exemple...........................................................................................................................................4
Les requêtes spatiales...........................................................................................................................5
Calculer la longueur d'une balade....................................................................................................5
La reprojection des données dans PostGIS......................................................................................5
Sélectionner les communes traversées par votre balade..................................................................6
Identifier les stations de métro se trouvant à moins de 500m d'une balade.....................................7
Remplir la colonne distance de la table balades sur base de la longueur calculée dans la table
traces................................................................................................................................................8
Jointures entre plusieurs tables dans une seule requête........................................................................8
Introduction......................................................................................................................................8
Exemple...........................................................................................................................................9
Exercice de vacances............................................................................................................................9
Bases de données – Introduction aux requêtes 1/9
La composition une requête de sélection
Introduction
Une requête de sélection comporte trois parties principales :
• Quoi : Quels champs (ou résultats de calculs sur des champs) veut-on voir dans le résultat de
la requête ? (=> limitation des champs parmi l'ensemble des champs disponibles)
Mot clé SQL correspondant : SELECT
• D'où : D'où proviennent les informations que l'on souhaite voir ou les informations que l'on
utilise pour les critères de sélection ? (=> choix de la ou des table(s) desquelles on extrait les
données, y compris les règles de jointure entre ces tables)
Mot clé SQL correspondant : FROM
• Selon quels critères : Quels sont les critères permettant de limiter les lignes (tuples) dont on
extrait les champs définis dans le « Quoi » ? (=> limitation des lignes parmi l'ensemble des
lignes disponibles)
Mot clé SQL correspondant : WHERE
Souvent il faut avoir répondu à « quoi ? » et à « selon quels critères ? » pour pouvoir répondre de
façon complète à « d'où ? ».
Pour comprendre comment fonctionne une requête, il faut aussi comprendre dans quel ordre le
système effectue le travail (qui n'est pas dans le même ordre que la composition de la requête):
1. Chargement des sources (« d'où), y compris jointure entre tables : FROM
2. Limitation des lignes selon les critères de sélection (« selon quels critères ») : WHERE
3. Choix des champs à afficher dans le résultat : SELECT
Exemple
Table
balades avec les champs id_balade, nom_balade, description_balade, duree_balade, mode_balade.
Question
Quelles sont les noms et la durée des balades qui sont prévues pour les cyclistes ?
Analyse
Quoi : nom et durée de la balade
D'où : la table balades (qui contient à la fois l'information sur le nom, la durée et le mode de
déplacement)
Selon quels critères : il faut que la balade soit pour cyclistes
Requête SQL
SELECT nom_balade, duree_balade
FROM balades
Bases de données – Introduction aux requêtes 2/9
WHERE mode_balade='cycliste'
Explication du processus
Le système charge la table balades en mémoire, en extrait toutes les lignes (tuples) où le champ
mode_balade est égal à 'cycliste' et ensuite extrait de ces lignes les champs nom_balade et
duree_balade.
Jointures
Introduction
Dans les bases de données relationnelles les liens entre tables se font par des champs contenant les
mêmes données. Autrement dit : le champ dans une table contient une référence vers les mêmes
données dans une autre table.
Exemple
Tables
• projets avec les champs id_projet, titre_projet, id_chef_projet et
• personnel avec les champs id_personne, nom_personne, telephone, email.
Le champ id_chef_projet contiendra des valeurs représentant les id_personne des personnes
responsables des différents projets.
Question
Quels sont les titres des projets dont le chef est Arsène Muller ?
Analyse
Quoi : les titres des projets
D'où : la table projet pour les titres des projets (dans le « quoi »)et la table personnel pour le
critère sur le nom de la personne (dans le « selon quel critère ») - il faut donc procéder à une
jointure entre les deux tables et les champs contenant les mêmes données permettant une telle
jointure sont id_chef_projet dans la table projets et id_personne dans la table personnel.
Selon quel critère : le chef de projet doit s'appeler Arsène Muller
Requête SQL
SELECT titre_projet
FROM projets JOIN personnel ON (id_chef_projet = id_personne)
WHERE nom_personne = 'Arsène Muller'
Explication du processus
Le système charge les tables projets et personnel en mémoire, lie chaque ligne de projets avec la
ligne dans personnel dont le id_personnel correspond au id_chef_projet de la ligne de projets.
Bases de données – Introduction aux requêtes 3/9
Ensuite il extrait de cette nouvelle table virtuelle (càd le résultat de la jointure) toutes les lignes dont
le champ nom_personne est égal à 'Arsène Muller'. Finalement, il extraite de toutes ces lignes le
champ titre_projet.
Les fonctions
Introduction
Comme dans quasi tous les langages de programmation, on peut définir des fonctions dans SQL.
Une fonction est un ensemble de commandes (un algorithme) auquel on donne un nom et alors
chaque fois qu'on appelle ce nom, on demande en fait que l'ensemble de commandes soit exécuté.
Eventuellement, on doit fournir à la fonction quelques renseignements ou objets nécessaires à
l'exécution de ces commandes. Ces renseignements ou objets sont appelés les paramètres de la
fonction. La syntaxe générale pour l'appel d'un fonction est nom_fonction(paramètre1, paramètre2,
etc).
Exemple
Fonction
La fonction upper(chaîne de caractères) est une fonction qui prend comme paramètre une chaîne de
caractères et transforme toutes les lettres de cette chaîne en majuscules.
Table
personnel avec les champs id_personne, nom_personne, telephone, email.
Les noms sont écrits avec la première lettre en majuscules et le reste en minuscules (ex : 'Arsène
Muller').
Question
Afficher le nom du membre du personnel s'appellant Arsène Muller en majuscules.
Requête
SELECT upper(nom_personne)
FROM personnel
WHERE nom_personne = 'Arsène Muller'
résultat : ARSÈNE MULLER
Astuce
L'utilisation de upper() ou de son pendant lower() permet souvent de travailler avec des champs de
texte avec un mélange non standardisé de majuscules et minuscules. Par exemple, si on reprend
l'exemple de la recherche des projets d'Arsène Muller, mais en supposant que l'on ne sache pas si le
nom a été entré dans la base de données avec les majuscules ou des minuscules, on peut reformuler
la requête ainsi :
SELECT titre_projet
Bases de données – Introduction aux requêtes 4/9
FROM projets JOIN personnel ON (id_chef_projet = id_personne)
WHERE lower(nom_personne) = 'arsène muller'
Les requêtes spatiales
Les requêtes sur des données spatiales (des géométries) suivent exactement la même logique, sauf
que potentiellement la jointure peut se faire par une relation spatiale, au lieu d'une concordance de
données dans les champs des tables à lier et que l'on a toute une série de fonctions d'analyse spatiale
à sa disposition.
Calculer la longueur d'une balade
Table
traces contenant des tracés de balades avec champs gid et the_geom.
Question
Quelle est la longueur de la balade numéro 1 ?
Requête
SELECT ST_Length(the_geom)
FROM traces
WHERE gid = 1
Remarque
L'unité de longueur dans le résultat de cette requête dépend évidemment du système de projection
dans lequel sont définies les géométries. Si les données sont en degrés (par exemple des données
sortant d'un GPS), le résultat de la requête est en degrés. Pour avoir un résultat en mètres, il faut
décider d'une projection de ces degrés sur le plan, sachant que la longueur calculée peut être
différente selon le système de projection utilisé.
La reprojection des données dans PostGIS
Dans une base de données PostgreSQL pour laquelle l'extension PostGIS a été activée, on trouvera
une table appelée spatial_ref_sys. Cette table contient les définition de toute une série de systèmes
de projection. On peut soi-même y introduire d'autres systèmes si nécessaire. Quasi toutes les
définitions contenues par défaut dans la table proviennent de la liste EPSG (cf [Link]
[Link]) et les codes SRID identifiant ces systèmes dans PostGIS correspondent aux codes
EPSG pour ces mêmes systèmes. Donc, un système (utilisé dans les GPS) non projeté (donc en
degrés) et calé sur le datum WGS84 porte le code EPSG 4326 et le code SRID interne à PostGIS
4326. De même le Lambert belge de 1972 a le code EPSG 31370 et le code SRID 31370.
Dans PostGIS la fonction ST_Transform(geometry, srid) permet de reprojeter une géométrie vers le
système portant le SRID indiqué comme paramètre.
Nous prendrons le même exemple de calcul de la longueur d'une balade, mais cette fois-ci en
mètres.
Bases de données – Introduction aux requêtes 5/9
Requête
SELECT ST_Length(ST_Transform(the_geom, 31370))
FROM traces
WHERE gid = 1
Remarque
On voit ci-dessus que l'on peut insérer l'appel à une fonction comme paramètre d'une autre fonction.
Les fonctions seront exécutées de la plus interne (càd entourée par le plus de paranthèses) à la plus
externe. Dans le cas ici, PostgreSQL exécutera donc d'abord la fonction ST_Transform avant
d'exécuter la fonction ST_Length.
Pour rendre la transformation permanente, il faut créer un nouveau champ de type geometry dans la
table (ex : the_geom31370). Ensuite on peut utiliser la commande UPDATE :
SELECT AddGeometryColumn('traces', 'the_geom31370', 31370, 'LINESTRING', 2)
UPDATE traces SET the_geom31370 = ST_Transform(the_geom, 31370)
Ici le système prend donc la géométrie contenue dans le champ the_geom, la reprojète vers le
Lambert belge de 1972 et écrit le résultat de la projection dans le champ the_geom31370.
Attention : Comme dans n'importe quel logiciel de SIG toute transformation ne peut réussir que si le
système est bien renseigné sur le système de projection de départ. Dans notre cas ici, il faut donc
que PostgreSQL ait l'information sur le système de projection dans lequel se trouve le champ
the_geom dans la table traces. Il peut souvent arriver que lors du chargement d'un shapefile dans
une base de données PostgreSQL/PostGIS on oublie d'indiquer le système de projection. Pour
remédier à cela, on peut utiliser la fonction ST_SetSRID(geometry, srid) qui ne va donc pas
reprojeter, mais simplement enregistrer le fait que la géométrie en question est dans le système de
projection indiqué.
Sélectionner les communes traversées par votre balade
Tables
• communes avec champs gid, nom, the_geom.
• traces avec champs gid et the_geom.
Question
Quels sont les noms des communes traversées par la balade 5 ?
Analyse
Quoi : noms des communes
D'où : communes pour les noms et traces pour l'identifiant de la balade 5 – il faut donc procéder à
une jointure entre les deux tables, mais dans ce cas-ci, il n'y a pas de champ en commun, mais
uniquement la localisation spatiale comme clé de jointure. On considérera comme à joindre toutes
géométries qui s'intersectent (mais on peut aussi utiliser d'autres opérateurs spatiaux comme
'touche', 'contient', 'est à l'intérieur de', etc.
Selon quels critères : le gid de la balade doit être égal à 5.
Bases de données – Introduction aux requêtes 6/9
Requête
SELECT nom
FROM communes JOIN traces ON ST_Intersects(communes.the_geom, traces.the_geom)
WHERE [Link] = 5
Remarques
Quand le nom d'un champ apparaît dans plusieurs tables impliquées dans la requête, il faut indiquer
la table d'où provient le champ. On fait cela avec la syntax [Link].
Le résultat de cette requête sera vide si les deux géométries (traces et communes) sont définies dans
des systèmes de projections différents.
Dans ce cas, on peut reformuler la requête en incluant une transformation :
SELECT nom
FROM communes JOIN traces ON ST_Intersects(communes.the_geom,
ST_Transform(traces.the_geom, 31370))
WHERE [Link] = 5
Identifier les stations de métro se trouvant à moins de 500m d'une
balade
Tables
• stations_metro avec champs gid, nom, the_geom.
• traces avec champs gid et the_geom.
Question
Quels sont les noms des stations de métro à moins de 500m de la balade 3 ?
Analyse
Quoi : noms des stations de métro
D'où : stations_metro pour les noms et traces pour l'identifiant de la balade 3 – il faut donc procéder
à une jointure entre les deux tables, avec de nouveau la localisation spatiale comme clé de jointure.
On considérera comme à joindre toutes géométries qui sont à moins de 500m l'un de l'autre.
Selon quels critères : le gid de la balade doit être égal à 3.
Bases de données – Introduction aux requêtes 7/9
Requête
SELECT nom
FROM stations_metro JOIN traces ON ST_Distance(stations_metro.the_geom,
ST_Transform(traces.the_geom, 31370)) < 500
WHERE [Link]=3
Remarques
Dans l'exemple avec l'intersection, ST_Intersects renvoie un booléen, donc vrai ou faux, et peut
donc être utilisé tel quel comme condition de jointure. ST_Distance renvoie une valeur, la distance,
et il faut donc utiliser un autre opérateur (<) pour formuler une condition complète.
Remplir la colonne distance de la table balades sur base de la longueur
calculée dans la table traces
Tables
• traces avec les champs gid, the_geom
• balade avec les champs id, distance
Requête
UPDATE balade
SET distance = ST_Length(ST_Transform(traces.the_geom, 31370))
FROM traces
WHERE [Link] = [Link]
Remarques
Le résultat de la requête est en mètres. Pour avoir des kilomètres, il suffit de diviser par 1000 :
UPDATE balade
SET distance = ST_Length(ST_Transform(traces.the_geom, 31370))/1000
FROM traces
WHERE [Link] = [Link]
Puisque c'est la table balade que l'on met à jour, elle n'est pas mentionné dans la clause FROM,
même si on l'utilise dans la condition WHERE.
La requête ci-dessus met à jour le champ distance pour toutes les balades. Si l'on souhaite mettre à
jour uniquement une seule balade (par exemple la 4), il faut ajouter une deuxième condition :
UPDATE balade
SET distance = ST_Length(ST_Transform(traces.the_geom, 31370))/1000
FROM traces
WHERE [Link] = [Link] AND [Link] = 4
Bases de données – Introduction aux requêtes 8/9
Jointures entre plusieurs tables dans une seule
requête
Introduction
Très souvent, il ne suffit pas de joindre deux tables pour pouvoir formuler la requête souhaitée, mais
il faut joindre trois, voire plus de tables. Même si cela complexifie légèrement la formulation de la
requête, on garde fondamentalement la même logique.
Exemple
Tables
• communes avec champs gid, nom, the_geom
• traces avec champs gid et the_geom
• balade avec champs id, id_auteur
• auteur avec champs id, nom
Question
Quels sont les noms des communes traversées par la balade dont l'auteur est Jean-Patrick ?
Analyse
Quoi : noms des communes
D'où : communes pour les noms et traces pour la géométrie de la balade, auteur pour le nom de
l'auteur et son identifiant, balade pour le lien entre identifiant de balade et identifiant de l'auteur – il
faut donc procéder à une jointure entre les quatre tables, avec un mélange de critères spatiaux
(intersections entre traces et communes) et conditions classiques de jointure (id du tracés qui doit
être égal à l'id de la balade et l'id de l'auteur qui doit être égal au champ id_auteur dans la table
balade).
Selon quels critères : le nom de l'auteur doit être égale à 'Jean-Patrick'
Requête
SELECT nom
FROM communes JOIN traces ON ST_Intersects(communes.the_geom,
ST_Transform(traces.the_geom, 31370))
JOIN balade ON [Link] = [Link]
JOIN auteur ON balade.id_auteur = [Link]
WHERE [Link]='Jean-Patrick'
Bases de données – Introduction aux requêtes 9/9
