Requête

GEO6352
SIG – Aspect pratique
La gestion de base de
données, les requêtes
pseudo-SQL, l’ODBC,
les requêtes spatiales
et l’analyse spatiale
vectorielle
Ref : Fish Finder newsletter
Jean Daoust
2015-2017
Plan du cours
Le modèle relationnel
Concept de relation
Concepts de lien et de clé
Propriété des tables relationnelles
Requêtes et types de requêtes
Langage de requête SQL
Les systèmes de gestion de base de données
(SGBD)
L’ODBC
Les requêtes spatiales
L’analyse spatiale vectorielle
Copyright Jean Daoust 2015-2017
Le modèle relationnel …
Ce modèle a été développé par Codd en
1970
Les données sont stockées dans un
ensemble de tableaux appelés tables
relationnelles
Copyright Jean Daoust 2015-2017
Le modèle relationnel …
Une table relationnelle
est un fichier composé
Colonnes (champs ou variables)
d’un ensemble de
colonnes et de lignes
Les colonnes ou champ
correspondent aux
attributs des entités
Les lignes de la table
correspondent à des
enregistrements des Lignes
(enregistrements)
valeurs d’attributs
Copyright Jean Daoust 2015-2017
Table
Le modèle relationnel …
Une valeur est stockée
à chaque intersection
d’une ligne et d’une
colonne correspondant
à la valeur d’attribut
Chaque champ
(variable) possède un
type et/ou domaine qui
détermine l’étendue
des valeurs que peut
prendre un champ
Copyright Jean Daoust 2015-2017
Les types de variable
Le type d’une variable est défini en fonction
du genre d’opération que l’on peut être
appelé à effectuer sur la variable et de
l’espace mémoire que l’on réserve pour cette
variable
Généralement, on retrouve les types
suivants : texte, entier, réel, date, logique
Copyright Jean Daoust 2015-2017
Les types de variable
Type texte (text, string ou character)
S’emploi pour contenir du texte et des nombres
sur lesquels il n’y aura jamais d’opérations
arithmétiques d’effectuées (ex. Une adresse)
Le nombre maximum de caractères que peut
contenir la variable doit être spécifié. On le
trouve en comptant le nombre de caractères que
possède la chaine de caractères la plus longue
qui sera contenue dans la variable. Tout doit être
compté; y compris les espaces et apostrophes.
Copyright Jean Daoust 2015-2017
Les types de variable
Type entier (integer)
S’emploi pour contenir des nombres entiers
Ex . Une population d’étudiants
Existe généralement en deux versions :
16 bits (court) – permet de stocker des nombres
compris entre -32768 et 32767
32 bits (long) – permet de stoker des nombres
compris entre -2147483648 et 2147483647
Copyright Jean Daoust 2015-2017
Les types de variable
Type réel (float ou double)
S’emploi pour contenir des nombres possédant une
partie décimale ou de grande dimension
Ex. Une densité de population
Existe généralement en deux versions :
32 bits (float) – permet de stocker des nombres
dont l’exposant est compris dans une étendue de
10-38 à 1038
64 bits (double) – permet de stocker des nombres
dont l’exposant est compris dans une étendue de
10-308 à 10308
Copyright Jean Daoust 2015-2017
Les types de variables
Type date
S’emploi pour contenir des dates et des heures
Ex. 24 mai 2011
Type logique (logical ou boolean)
S’emploi pour contenir Vrai ou Faux (True ou
False)
Pratique pour stocker la réponse d’une requête
Copyright Jean Daoust 2015-2017
Le domaine d’une variable
Le domaine d’une variable est constitué
des valeurs permises pour cette variable
(ex. nombres entiers compris entre 5 et 18
correspondant à des mineurs d’âge
scolaire)
Copyright Jean Daoust 2015-2017
Liens entre tables
Un lien est une association entre deux tables
Ces liens sont exprimés à l’aide de clés :
primaires
étrangères
Copyright Jean Daoust 2015-2017
Clés primaires et étrangères
Une clé primaire est un champ dans une
table dont les valeurs identifient de façon
unique chaque ligne ou enregistrement de la
table
Une clé étrangère est un champ dont les
valeurs sont les mêmes que la clé primaire
d’une autre table
Copyright Jean Daoust 2015-2017
Liens entre tables …
Un lien est créé entre 2
tables relationnelles en
faisant correspondre
les valeurs de la clé
étrangère dans une
table avec celles de la
clé primaire dans l’autre
Clé étrangère
lien
Clé primaire
Copyright Jean Daoust 2015-2017
Liens entre tables …
Les clés sont fondamentales dans une base
de données relationnelles parce qu’elles
permettent de relier les tables entre elles
Copyright Jean Daoust 2015-2017
Types de lien
Il existe 3 principaux types de lien dans une base
données relationnelles:
1:m (un à plusieurs)
Dans un lien de type 1:m, un enregistrement de la table A peut
être associé à plusieurs enregistrements de la table B, mais
un enregistrement de la table B ne peut être associé qu’à un
enregistrement de la table A
m:n (plusieurs à plusieurs)
Dans un lien de type m:n, un enregistrement de la table A peut
être associé à plusieurs enregistrements de la table B et viceversa
Ce lien requiert la définition d’une troisième table dont la clé
primaire comprend 2 champs: ceux correspondant aux clés
primaires des tables A et B
1:1 (un à un)
Dans un lien de type 1:1, un enregistrement de la table A ne
peut être associé qu’à un enregistrement de la table B et viceversa
Copyright Jean Daoust 2015-2017
Propriétés des tables
relationnelles
Chaque position ligne/colonne doit avoir une seule
valeur
Les valeurs dans les colonnes doivent être de
même type (i.e. appartenir au même domaine)
Chaque ligne est unique; deux lignes ne peuvent
avoir des valeurs identiques
Chaque colonne doit avoir un nom unique
La séquence des colonnes (gauche à droite) n’a pas
d’importance (i.e. les données peuvent être
accédées dans n’importe quel ordre)
Copyright Jean Daoust 2015-2017
Règles pour assurer l’intégrité de
la base de données
La clé primaire doit toujours avoir une valeur connue
et spécifiée; les opérations dans la table
relationnelle (ajouts, changements, …) ne doivent
jamais compromettre le caractère unique de la clé
primaire
Si une table possède une clé étrangère, alors
chaque valeur de la clé étrangère doit être nulle ou
bien correspondre aux valeurs de la table dans
laquelle la clé étrangère est une clé primaire
Copyright Jean Daoust 2015-2017
Requête
Une requête est une question adressée à la
base de données pour visualiser, trier,
modifier ou analyser les données
Copyright Jean Daoust 2015-2017
Le langage de requêtes SQL
L’interface standard utilisé pour interagir avec une base de
données relationnelle est le SQL (Structured Query Language)
Il permet de gérer la base de données, d’y apporter des
modifications et de l’interroger à l’aide de requêtes
Il est basé sur l’algèbre relationnelle et permet de réaliser une
série d’opérations sur la base de données
Copyright Jean Daoust 2015-2017
Opérateurs logiques et booléens
L’algèbre relationnelle comprend 6 opérateurs
logiques :
Égal : = ou EQ
Non égal (différent) : <> ou != ou NE
Plus petit que : < ou LT
Plus grand que : > ou GT
Plus petit ou égal : <= ou LE
Plus grand ou égal : >= ou GE
Et 3 booléens :
Et : AND
Ou : OR
Non (négation) : NOT
Copyright Jean Daoust 2015-2017
Opérations mathématiques et
statistiques
Des opérations mathématiques :
Addition et soustraction
Produit et division
Exposant et racines
Fonctions trigonométriques
Et statistiques :
Somme et moyenne
Valeurs maximale et minimale
Écart-type
...
Copyright Jean Daoust 2015-2017
Opérations relationnelles …
Sélection (select)
Sélectionne certains champs des enregistrements d’une
base de données.
Projection (project)
Sélectionne certains champs dans une table et élimine les
enregistrements qui se répètent.
Union (union)
Combine tous les enregistrements de deux tables dans
une seule; les enregistrements qui se répètent sont
éliminés. Les deux tables doivent être compatibles; elles
doivent avoir le même nombre de champs et ces champs
doivent être de même type (et posséder le même
domaine).
Copyright Jean Daoust 2015-2017
Opérations relationnelles
Intersection (intersection)
Trouve tous les enregistrements identiques dans deux tables relationnelles
et produit une nouvelle table. Les deux tables doivent être compatibles.
Différence (difference)
Trouve tous les enregistrements d’une première table qui ne sont pas
contenus dans une seconde table et produit une nouvelle table qui contient
les enregistrements qui apparaissent dans la première table mais pas dans
la seconde. Les deux tables doivent être compatibles.
Produit (product)
Trouve toutes les combinaisons possibles d’enregistrements de deux tables
relationnelles.
Jointure ou fusion (join)
Fusionne deux tables sur la base de valeurs identiques contenues dans des
champs de contenus communs aux deux tables.
Copyright Jean Daoust 2015-2017
Construction d’une requête
SQL - SELECT
Permet de sélectionner des enregistrements
d’une table correspondant à une expression
conditionnelle
Syntaxe :
SELECT liste des variables
FROM nom de la table ou des tables
WHERE condition
GROUP BY liste des variables
ORDER BY liste des variables
Copyright Jean Daoust 2015-2017
Construction d’une requête
SQL – SELECT – Exemple 1
Vous disposez d’une table appelée Lot
comportant des numéros de lot (no), la valeur de
chaque lot (val) et le nom du propriétaire de
chaque lot (proprio). Vous désirez obtenir une
liste des propriétaires qui possèdent au moins
un lot dont la valeur est supérieure à $25000 et
vous désirez connaître le nombre de lots par
propriétaire.
Copyright Jean Daoust 2015-2017
SQL – SELECT – Exemple 1
Vous bâtirez la requête suivante :
SELECT proprio, Count(*) FROM Lot
WHERE val > 25000 GROUP BY proprio
Count(*) est une fonction travaillant
étroitement avec l’option GROUP BY.
Copyright Jean Daoust 2015-2017
Construction d’une requête
SQL – SELECT – Exemple 2
Dans un SIG vous pouvez utiliser une
requête SQL SELECT pour unir 2 tables;
l’une comportant les noms de MRC déjà
associés aux objets graphiques représentant
la géométrie des MRC et l’autre comportant
les noms de MRC et d’autres variables telles
que la population, le revenu moyen, etc.
Copyright Jean Daoust 2015-2017
Construction d’une requête
SQL – SELECT – Exemple 2
Si votre table comportant une composante
graphique s’appelle MRC_carte et l’autre table
s’appelle MRC_donnees et votre variable de la
première table contenant les noms des MRC
s’appelle nom et celle de la deuxième s’appelle
noms; vous pouvez combiner les 2 à l’aide de la
requête suivante :
SELECT * FROM MRC_carte,MRC_donnees
WHERE MRC_carte.nom=MRC_donnees.noms
Copyright Jean Daoust 2015-2017
Exemple de requête dans
ArcGIS
Table employée
Liste des
variables
Condition(s)
Copyright Jean Daoust 2015-2017
SQL Spatial
Le language SQL n’a pas été développé, à
l’origine, pour effectuer des opérations
spatiales.
Cependant, la plupart des SIG offrant la
possibilité de contruire des requêtes SQL
offrent également des opérateurs spatiaux.
Le logiciel MapInfo, par exemple offre, les
opérateurs suivants : Contains, Contains
Entire, Within, Entirely Within, Intersects.
Copyright Jean Daoust 2015-2017
Exemple de requête spatiale
dans MapInfo
Copyright Jean Daoust 2015-2017
Avantages du modèle relationnel
Il n’y a pas de hiérarchie entre les entités; chacune
peut avoir sa propre liste d’attributs et être associée
aux autres entités
Une recherche peut être faite à partir de n’importe
quelle table en utilisant les clés
On peut facilement ajouter autant de tables que
désiré sans modifier l’ensemble de la structure
Il offre beaucoup de flexibilité
Copyright Jean Daoust 2015-2017
Qu’est-ce qu’un SGBD ?
C’est un Système de Gestion de Base de Données
C’est un logiciel spécialisé qui permet de gérer et
réaliser des opérations sur l’ensemble des données
enregistrées dans une base de données
Il permet de manipuler, d’insérer et de mettre à jour
de grandes quantités de données de manière
concurrente (plusieurs utilisateurs simultanés) et
sûre
Copyright Jean Daoust 2015-2017
Logiciels de gestion de bases de
données et SIG
Les SIG offrent tous des fonctions minimales
permettant de gérer des données de type attribut et
d’effectuer des requêtes
Cependant, certaines applications requièrent
l’utilisation de SGBD spécialisés qui sont
généralement compatibles avec des SIG, on
retrouve par exemple les SGBD :
Oracle
MySQL
Informix
Postgres
Access
Copyright Jean Daoust 2015-2017
Open DataBase Connectivity
Logiciel qui permet de mettre en relation des
tables provenant de plusieurs SGBD
différents au moyen de requêtes SQL et de
façon dynamique.
Disponible sous la forme d’un pilote ODBC
(driver).
Les logiciels SGBD ou SIG voulant utiliser les
fonctions du pilote ODBC doivent avoir
développer une fonction de connection au
pilote ODBC.
Copyright Jean Daoust 2015-2017
Les requêtes spatiales
Les SIG offrent généralement la possibilité
de sélectionner des objets d’une couche en
se basant sur les relations spatiales qu’ils
présentent avec les objets d’une autre
couche
Copyright Jean Daoust 2015-2017
Les requêtes spatiales
Par exemple,
Arc/GIS offre la
fonction “Select By
Location”
La nouvelle sélection
peut s’effectuer à
partir de rien ou à
partir d’une sélection
existante
Copyright Jean Daoust 2015-2017
Les requêtes spatiales
Les opérations généralement effectuées
entre couches vectorielles sont :
Trouver les objets d’une couche qui intersectent
les objets d’une autre couche
Trouver les objets d’une couche qui sont
contenus dans les objets d’une autre couches
Trouver les objets d’une couche qui contiennent
les objets d’une autre couche
Trouver les objets d’une couche qui sont à une
certaine distance des objets d’une autre couche
Copyright Jean Daoust 2015-2017
L’analyse spatiale vectorielle
L’analyse spatiale vectorielle consiste
essentiellement à effectuer des analyses de
proximité, de voisinage et de superposition
d’objets
Copyright Jean Daoust 2015-2017
La création de corridors
(buffer)
Le corridor est une zone de distance
déterminée qui est construite autour des
objets sélectionnés
Lorsque la génération de corridor s’effectue à
partir d’une couche comprenant plusieurs
objets, il est généralement possible d’obtenir
un corridor pour chaque objet ou un seul
corridor résultant pour chaque ensemble de
corridors qui se touchent
Copyright Jean Daoust 2015-2017
La création de corridors
Emprise de 26 m autour
de l’axe médian de la route
Zone de coupe prohibée
de 20 m autour des plans
d’eau
Copyright Jean Daoust 2015-2017
La création de corridor
Épicentre d’un
tremblement de
terre dans une
ville avec zone
de desctruction
de 2 kms
Copyright Jean Daoust 2015-2017
Le plus proche voisin
Trouver le plus proche voisin d’un objet est
souvent très pratique.
Dans le logiciel ArcGIS, la fonction Near, utilise
2 couches vectorielles et trouve le numéro de
l’objet le plus proche de chaque objet de la
première couche dans la 2e couche et ajoute cet
information à la table d’attributs de la première
couche. La distance est également déterminée
et stockée dans la table d’attributs de la
première couche.
Copyright Jean Daoust 2015-2017
Les plus proches voisins
Trouver le plus proche voisin d’un objet est
souvent très pratique
À partir d’un ensemble de points, la recherche
du plus proche voisin s’effectue en calculant les
distances qui séparent chaque paires de points
Un tableau ou une carte de polylignes
connectées peuvent être produits. La carte ainsi
produite correspondra à ce que l’on appelle la
triangulation de Delaunay
Copyright Jean Daoust 2015-2017
Triangulation de Delaunay
Copyright Jean Daoust 2015-2017
Le plus proche voisin
Le dual de la triangulation de Delaunay
correspond à une polygonisation de Thiessen
ou chaque polygone construit représente la
zone d’influence d’un point en ne tenant
compte que de la distance qui sépare les
points
Copyright Jean Daoust 2015-2017
Les polygones de Thiessens
Copyright Jean Daoust 2015-2017
Opérations arithmétiques
entre objets vectoriels
Des opérations d’addition et de soustraction
peuvent être effectuées entre les objets
vectoriels
On retrouve essentiellement 3 opérations
L’addition (combine, union)
La soustraction (erase, clip)
L’intersection (intersect)
Copyright Jean Daoust 2015-2017
L’addition d’objets
L’addition permet de combiner deux ou
plusieurs objets pour n’en former qu’un
Le résultat d’une addition entre 2 objets
partageant une frontière commune sera un
nouvel objet ou la frontière est disparue
avant
après
Copyright Jean Daoust 2015-2017
La soustraction d’objets
La soustraction d’un ou plusieurs objets d’un
ou plusieurs autres objets doit généralement
s’effectuer en 3 étapes
D’abord sélectionner les objets desquels seront
extrait les autres objets et les définir comme cible
Ensuite sélectionner les objets dont la forme sera
extraite de la cible précédemment définie
Finalement, effectuer l’opération de soustraction
Copyright Jean Daoust 2015-2017
La soustraction d’objets
Avant
(la cible est en rouge)
Copyright Jean Daoust 2015-2017
Après
L’intersection d’objets
L’intersection permet de ne
retenir que les parties communes
à deux ou plusieurs objets.
Elle est généralement réalisée
entre 2 couches vectorielles.
avant
après
Copyright ESRI
Copyright Jean Daoust 2015-2017
L’intersection
Pour trouver les divers
ponts et ponceaux
d’une région on peut
faire l’intersection
entre une couche de
routes (polylignes) et
une couche de rivières
(polylignes). Le
résultat sera une
couche de points
correspondant aux
ponts.
Copyright Jean Daoust 2015-2017
La dissolution de frontières
La dissolution de frontières (dissolve) permet
de créer des objets plus grands par
regroupement d’objets plus petits possédant
des valeurs d’attribut identiques
Exemple : création d’une carte de MRC à partir
d’une carte de municipalités
Copyright Jean Daoust 2015-2017
Dissolution de frontières
Copyright Jean Daoust 2015-2017