Organisé en deux parties, ce cours présente les bases théoriques et pratiques des systèmes d’information géographique. - Il propose une introduction aux systèmes d’information géographique qui ne requiert pas de connaissances préalables en informatique - Il donne la possibilité d’acquérir rapidement les notions de base qui vous permettent de créer des bases de données spatiales et de fabriquer des cartes géographiques - Il s’agit d’un cours pratique qui repose sur l’utilisation de logiciels libres, notamment QGIS En somme, si vos études ou votre profession comprennent des activités liées à la gestion de territoires, à l’analyse d’objets distribués dans l’espace géographique (aménagement du territoire, biologie, santé publique, écologie, énergie, etc.), ce cours est fait pour vous! En suivant cette première partie du cours, vous explorerez les principes de base de la numérisation du territoire et du stockage des géodonnées. Vous apprendrez notamment à : - Caractériser des objets et/ou phénomènes spatiaux (modélisation du territoire) du point de vue de leur positionnement dans l’espace (systèmes de coordonnées et projections, relations spatiales) et en fonction de leur nature intrinsèque (mode objet ou vecteur vs. mode image ou raster), - Utiliser les diverses méthodes d’acquisition de données (mesure directe, géoréférencement d’images, digitalisation, source de données existantes, etc.) - Utiliser les divers modes de stockage des géodonnées – Fichiers simples et/ou bases de données relationnelles - Utiliser des outils de modélisation des données pour décrire et implémenter une base de données - Créer des requêtes dans le langage d’interrogation et de manipulation des données La seconde partie du cours portera sur les méthodes d'analyse spatiale et les techniques de représentation des géo-données. Vous apprendrez notamment à: - Analyser les propriétés spatiales de variables discrètes, par exemple en quantifiant l’autocorrélation spatiale - Travailler avec les variables continues (échantillonnage, construction de courbes d’isovaleurs, méthodes d’interpolation) - Utiliser les modèles numériques d'altitude et leurs dérivées (pente, orientation, etc.) - Utiliser les techniques de superposition de géodonnées et d'interaction entre elles - Produire des documents cartographiques selon les règles de la sémiologie graphique - Explorer d’autres formes de représentation spatiale (cartographie interactive sur internet, représentations 3D, etc.) La page https://www.facebook.com/moocsig fournit un forum interactif pour les participants à ce cours.
Fusion de requêtes, requêtes emboîtées
Loading...
En provenance du cours de École Polytechnique Fédérale de Lausanne
Systèmes d’Information Géographique - Partie 1
106 notes
À partir de la leçon
Stockage - Gestion de données avec le langage SQL
Le langage SQL constitue le vecteur privilégié de l’accès aux bases de données relationnelles, que ce soit pour rechercher des données répondant à certains critères (requêtes conditionnelles), agréger et calculer des statistiques sur des sous-ensembles de données (requêtes d’agrégation), combiner les résultats de plusieurs requêtes (emboîtement et fusion) ou encore éditer et modifier des données, voire manipuler la structure de données (DDL, DML).
Rencontrer les enseignants
Stéphane Joost
Laboratoire de systèmes d'information géographique
Marc Soutter
Laboratoire de Systèmes d'Information Géographique
Fernand Koffi KouaméProfesseur
Centre Universitaire de Recherche et d'Application en Télédétection (CURAT) - Université Félix HOUPHOUET-BOIGNY d'Abidjan-Cocody (Côte d'Ivoire)
Amadou Sall
Département de Géomatique - Centre de Suivi Ecologique (CSE), Sénégal
[MUSIQUE]
[MUSIQUE]
[MUSIQUE]
[MUSIQUE] Bonjour.
La présente leçon va porter sur la fusion de requêtes, que l'on
utilise lorsque l'on souhaite associer les résultats de deux ou plusieurs requêtes,
pour en extraire les éléments communs, ou alors,
pour retirer de l'ensemble des résultats d'une requête, le
groupe des éléments qui seraient présents dans les résultats d'une autre requête.
Nous aborderons également,
l'utilisation de résultats de requêtes comme éléments de, d'une autre requête,
donc le cas où on emboîte une requête dans une autre requête, ce que l'on fait,
par exemple, lorsque l'on souhaite utiliser une fonction d'agrégation,
comme le nombre moyen d'employés d'une série d'entreprises, comme critère
pour sélectionner les PME qui auraient moins d'employés que la moyenne.
L'objectif de cette leçon est donc de décrire le principe des requêtes de
fusion, et de montrer que des requêtes SQL peuvent contenir d'autres requêtes SQL,
de sorte qu'au terme de la leçon, vous soyez capables d'utiliser des
requêtes de fusion pour assembler des résultats, associer des résultats de
plusieurs requêtes, et de décrire des requêtes emboîtées.
Dans cette leçon nous allons donc aborder successivement le thème de la fusion de
requêtes, puis nous verrons les requêtes emboîtées dans la clause WHERE,
avant de passer aux requêtes emboîtées dans la clause FROM, et nous terminerons
avec l'utilisation des opérateurs IN et NOT IN dans les requêtes de fusion.
[MUSIQUE] En reprenant ce tableau
qui résume les éléments de la syntaxe de base du language SQL, nous retrouvons,
en fait, l'ensemble des clauses de sélection, de filtre conditionnel,
d'agrégation et de tri, que nous avons vues lors des précédentes leçons.
Nous voyons qu'il nous reste encore à traiter de ces clauses de fusion,
avec les mots-clés UNION, INTERSECT, et EXCEPT.
La clause UNION du language SQL, permet de mettre bout à bout les résultats de
plusieurs requêtes, qui utilisent elles-mêmes la commande SELECT.
C'est donc une commande qui permet de concaténer les résultats de deux requêtes,
ou davantage.
Pour pouvoir utiliser cette clause de fusion, il est important que les deux
requêtes que l'on cherche à associer sont structurées de la même manière, donc elles
ont le même nombre et le même type de colonnes et que ces colonnes apparaissent
dans le même ordre, dans les deux tables qui sont associées par la requête d'union.
À partir de là, la syntaxe est simple, c'est un,
deux clauses SELECT, qui sont simplement reliées par le mot-clé UNION.
Dans l'esprit, il s'agit d'une opération qui est similaire à celle du Full Join que
nous avons vue dans les leçons précédentes, à ceci près que
dans la jointure, en fait, la structure des deux ensembles que l'on associe
peut être complètement différente, on a des champs qui sont totalement différents,
alors que ici, lorsque l'on fait l'union de deux tables,
de deux requêtes, il faut vraiment que la structure soit la même dans les deux cas.
La particularité de la clause Union All, consiste à ne
pas éliminer les doublons, donc si nous avons des éléments qui sont présents à la
fois dans la première et dans la seconde table, ils apparaissent les deux,
alors que dans la requête d'union simple, les doublons sont éliminés.
Seconds types de requêtes de fusion, les requêtes d'intersection,
avec la clause INTERSECT
qui permet d'obtenir l'intersection des résultats de deux requêtes.
Comme dans le cas de l'union, il est important, il est même essentiel
que les deux tables que l'on utilise, présentent le même nombre et le même type
de colonnes et que ces colonnes soient listées dans le même ordre.
À partir de là, la syntaxe est de même nature, avec deux requêtes de sélection
classique, reliées par le mot-clé INTERSECT, par la clause INTERSECT.
Comme précédemment, on peut faire un lien avec l'idée de jointure,
où nous avions la jointure simple, qui consistait,
en fait, à relier les éléments de deux ensembles,
de deux requêtes, par l'intermédiaire d'un champ commun.
La différence ici, c'est que l'intersection entre deux requêtes,
dans une clause de fusion, implique que la structure des deux ensembles soit la même,
et non pas qu'on associe deux ensembles avec des attributs tout à fait variables.
Un troisième type de requête de fusion, qui permet d'extraire
des résultats d'une requête, ceux qui ne feraient pas partie d'une seconde requête,
avec la clause EXCEPT, ou MINUS, dans certains SGBD,
en particulier le MASQL, c'est la clause MINUS qu'il faut utiliser.
À nouveau, il faut avoir le même nombre et le même type de colonnes dans
le même ordre, dans les deux tables, avec une syntaxe, toujours de même nature,
deux requêtes de sélection reliées par la clause de fusion.
Ce type de fusion s'apparente à une jointure gauche, sans intersection,
donc, en language ensembliste, avec cette représentation,
à ceci près, toujours, que dans le cas d'une jointure,
les structures des tables ne sont pas les mêmes.
Pour illustrer ces requêtes de fusion, nous prenons donc la base de données des
Seychelles, avec la table des districts et celle de lieux dits,
et les champs qui contiennent les noms de ces deux tables.
La requête elle-même, qui associerait, en fait, ces deux tables,
elle s'écrit comme vous le voyez sous les yeux, et l'on copie simplement
cette requête pour la reproduire une seconde fois, avant de mettre de l'ordre,
en supprimant dans le premier cas, les éléments qui sont liés aux lieux dits,
et dans le second cas, les éléments qui sont liés aux districts,
de sorte à obtenir, en fait, la table qui associe
les noms des districts et les noms de lieux dits, avec 330 résultats.
On peut ensuite simplement ajouter le mot-clé ALL,
pour conserver les doublons, et on voit que l'on a une,
on passe à 371 retours, donc il y a une quarantaine de doublons qui apparaissent.
L'intersection de deux ensembles, cette fois, nous donne 17 résultats,
donc 17 lieux dits qui sont similaires, dans leurs noms, aux districts.
Finalement, la soustraction des lieux dits à l'ensemble des noms de districts, donne
8 résultats, donc 8 districts qui n'ont pas d'équivalents dans les lieux dits.
On peut affiner un petit peu la recherche,
en transformant les requêtes de sélection, et en limitant,
en fait, la recherche des districts à ceux qui commencent par la lettre T,
et en faisant la même chose, ensuite, pour les lieux dits.
Donc on copie cette clause, on la colle, à la suite, à la fin de la seconde requête,
et on remplace l'attribut sur lequel porte le filtre,
par le mot-clé lieux dits, entre guillemets, puisque,
en fait, on a un tiret qui pourrait, qui pourrait être cause de problèmes.
Mais on voit qu'on a 4 résultats,
donc 4 districts et lieux dits qui commencent par T.
[MUSIQUE]
[MUSIQUE] Dans le monde SQL,
on parle de requêtes emboîtées, requêtes imbriquées, ou encore de sous-requêtes,
lorsqu'une requête est exécutée à l'intérieur d'une autre requête.
Ce genre de disposition peut se rencontrer dans le cas de clauses SELECT,
de clauses FROM, de clauses WHERE, ou encore de clauses HAVING.
Comme il y a plusieurs formes d'utilisation de
l'emboîtement de requêtes, il existe aussi plusieurs types de syntaxes,
que nous allons voir un peu plus en détail, en commençant par la clause WHERE.
La syntaxe d'une requête emboîtée dans la clause WHERE, consiste donc à remplacer
dans la condition que doit vérifier la clause WHERE, remplacer donc le critère,
par une requête SQL, placée entre parenthèses.
Pour illustrer l'utilisation de ce type de requête emboîtée, on reprend cet exemple,
où l'on cherchait la valeur maximale du nombre de lits des hôtels des Seychelles,
et puis, on souhaitait extraire, en fait, le nom de l'hôtel qui avait le plus
grand nombre de lits, chose qui pouvait se faire assez facilement, si vous vous en
souvenez, avec SpatialLite, mais qui n'était pas praticable avec postgres.
Donc il faut une requête un peu plus élaborée dans ce cas là,
donc on sélectionne le nom et le nombre de lits de l'hôtel, de la table hôtels,
et, comme condition dans la clause WHERE, on aimerait que le, sélectionner les
hôtels dont le nombre de lits correspond à la valeur maximale du nombre de lits.
On l'écrit ici un peu naïvement mais en fait ce maximum peut
être décrit par la requête de sélection qu'on avait tout à l'heure.
Donc le maximum du nombre de lits des hôtels, provenant de la table des hôtels.
Avec comme condition, pas de condition.
Et là retrouve le Berjaya.
[AUDIO_VIDE] On peut aussi transformer la requête,
pour rechercher l'ensemble des hôtels dont le nombre de lits est supérieur
à la moyenne et on trouve 23 établissements.
La moyenne, si on s'en souvient, dans un précédent exercice,
on avait vu qu'elle était de l'ordre de 38 lits.
Donc, on a la liste des hôtels qui ont plus de 38 lits.
Dans le cas de NaviCat, la chose se fait de manière graphique, donc on sélectionne
les champs dans la table et puis ensuite, dans le constructeur de requêtes,
on peut ajouter les différents paramètres.
Là, on va chercher le nombre de lits supérieurs à la moyenne.
Alors le critère, la requête emboîtée doit quand même être écrite manuellement.
L'interface graphique qui facilite la vie a ses limites.
Donc comme tout à l'heure, on associe comme critère de recherche
la valeur moyenne du nombre de lits du champ des hôtels et en plus, on va trier
en ordre descendant, en taille descendante, ces hôtels.
[MUSIQUE] [MUSIQUE]
Dans le cas d'une requête emboîtée dans la clause FROM, le principe est exactement
le même, avec la requête de sélection qui remplace,
en fait, le nom de la table sur laquelle porte la requête que l'on fait.
Ce type de syntaxe est possible car, en fait, une requête de
sélection renvoie l'équivalent d'une table de la base de données.
Comme nous allons utiliser plusieurs fois la même table hôtel dans la clause FROM,
nous allons devoir recourir à des alias,
ce qui facilite aussi un petit peu l'écriture de la requête SQL.
On commence par ajouter une deuxième clause FROM qui se présente sous la forme
d'une requête et dans cette requête, on va rechercher la moyenne du nombre de
lits des hôtels d'une table hôtels, à laquelle on va donner l'alias h2 pour
la distinguer de la première, qui a l'alias h.
[AUDIO_VIDE] Ce
nombre moyen de lits extrait de cette deuxième table,
va être utilisé dans la clause conditionnelle,
avec la nécessité d'utiliser un alias pour cette table de la clause FROM,
donc s pour la table de synthèse et puis,
un alias également, pour la colonne qui contient les valeurs moyennes,
de sorte que l'on puisse référencer cet alias dans la clause conditionnelle.
On obtient bien les mêmes 23 résultats que dans le cas
où l'on utilisait la clause WHERE.
Même chose ici, dans le cas de NaviCat, où l'on remplace
la table hôtels par un alias, ce qui met à jour automatiquement les champs et après,
la suite peut de nouveau se faire de la même manière dans l'éditeur.
Donc, on copie ici la requête de calcul
de la moyenne, on met des alias sur la deuxième table d'hôtels,
un alias sur cette requête emboîtée, un alias
sur la moyenne, ce qui permet de compléter la requête de sélection,
ce qui permet de compléter la clause conditionnelle et puis,
on conserve l'idée du tri en ordre décroissant.
[MUSIQUE]
[MUSIQUE] Nous
avons vu, dans le cas de la requête emboîtée dans la clause WHERE,
que l'on utilise, en fait, une requête SQL pour définir le critère qui
est utilisé dans la condition que doit vérifier la clause conditionnelle.
C'est en fait un cas particulier
où la requête que l'on utilise renvoie une seule valeur.
Il peut arriver et même, souvent, que la requête utilisée renvoie
plusieurs valeurs et que la condition que l'on souhaite vérifier, c'est que le
critère de sélection corresponde à l'une des valeurs de cette collection.
C'est dans ce cas-là, que l'on va utiliser les opérateurs IN et NOT IN pour
tester les résultats de la requête emboîtée.
Donc, la syntaxe se présente comme précédemment, avec simplement l'opérateur
qui change, devenant IN à la place du égal ou du plus grand que.
Pour illustrer ce type d'utilisation de requêtes emboîtées,
nous allons reprendre l'exemple des requêtes d'intersection, où l'on
recherchait les districts qui ont même nom que des lieux-dits, ou au contraire,
des districts qui n'ont pas d'équivalent dans la collection des lieux-dits.
Nous ajoutons donc la table des districts et celle des lieux-dits.
Sélectionnons le nom de la table des districts et on ajoute
un critère pour définir la clause WHERE, critère qui va porter sur le
nom de district et un opérateur, l'opérateur IN pour dire que l'on
aimerait que le nom figure dans la collection des noms de lieux-dits.
Cette collection des noms de lieux-dits, on doit l'écrire à pied
avec le nom de la table, toujours entre guillemets à cause des tirets,
la clause FROM et le critère étant défini, on peut encore mettre un peu d'ordre,
en supprimant la table des lieux-dits de la clause principale.
Et lorsqu'on exécute cette requête, on voit que l'on trouve bien
les 17 districts qui ont un équivalent dans la collection des lieux-dits.
Pour retrouver les districts qui n'ont pas d'équivalent, on inverse simplement
le sens de l'opérateur, NOT IN et l'on trouve les huit districts qui n'ont pas
de correspondant dans la collection des lieux-dits.
Dans le cas de NaviCat, on ajoute donc le district,
on sélectionne le nom et puis, dans la clause conditionnelle,
on va ajouter simplement le champ nom de district,
remplacer l'opérateur, alors ici, il s'appelle IS IN LIST,
mais qui est traduit en SQL par, simplement, le IN et puis,
comme précédemment, il faut écrire la requête emboîtée
de manière explicite, sans oublier les guillemets pour la table lieux-dits
et en désactivant l'ajout automatique
d'apostrophe, dans le critère de sélection.
Lorsqu'on exécute cette requête, on trouve bien les 17 districts que l'on recherchait
et pour le complément, on inverse l'opérateur et on trouve les
huit districts qui n'ont pas d'équivalent dans la collection des lieux-dits.
[MUSIQUE]
[MUSIQUE] Nous avons vu,
dans cette leçon, qu'il est possible d'utiliser des clauses de fusion,
pour associer les résultats de plusieurs requêtes.
Soit simplement, les associer, soit extraire les éléments communs,
ou soustraire les éléments d'une requête des résultats d'une autre requête.
Nous avons également vu que l'on pouvait utiliser des résultats de requête comme
éléments d'une requête, d'une autre requête, dans la clause FROM,
dans la clause WHERE ou bien, aussi, à d'autres emplacements.
Et finalment, nous avons vu que dans certains cas, on pouvait utiliser ces
requêtes emboîtées, avec l'opérateur IN ou NOT IN pour obtenir
les mêmes résultats que ceux que l'on obtiendrait avec des requêtes de fusion.
[MUSIQUE]
[MUSIQUE]
Explorer notre catalogue
Rejoignez-nous gratuitement et obtenez des recommendations, des mises à jour et des offres personnalisées.
Coursera propose un accès universel à la meilleure formation au monde,
en partenariat avec des universités et des organisations du plus haut niveau, pour proposer des cours en ligne.
© 2018 Coursera Inc. Tous droits réservés.
