M.

SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

SQL – Base de données spatiales

Découvrir le langage de requête SQL pour répondre à des besoins d'analyse spatiale dans QGIS.

Introduction aux SGBDR

Un Système de Gestion de Base de Données (SGBD) est un logiciel permettant de stocker de
la donnée dans une base de données en garantissant la qualité, la pérennité et la confidentialité
des informations. La complexité des opérations de traitement des données ne nécessite pas
d'être totalement connue par les utilisateurs. Ce module ne vise donc pas à former des
spécialistes des SGBD. Les SGBD que nous utiliserons sont basés sur un modèle de
données relationnel (SGBDR). Dans ce modèle, la base de données est composée d'un
ensemble de tables et chaque ligne d'une table est un enregistrement.

Dans ce cours nous n'exploiterons que des modèles très simples. Nous utiliserons le SGBD
postGIS qui est installé avec QGIS.
Le SGBD PostGIS qui est beaucoup plus complet et avancé. Il doit être envisagé, pour ce qui
est du partage de données, comme un composant du système d'information d'un service avec
un administrateur dédié. Une utilisation personnelle de ce SGBD est cependant envisageable
pour certains besoins d'analyse ou de production de données, c'est dans ce cas également un
très bon outil.
SQL (Structured Query Language qui signifie langage de requêtes structuré) est un langage
destiné à la manipulation des bases de données au sein d'un SGBD.

SQL est composé de trois sous-ensembles :

Le Langage de Définition de Données (LDD) qui permet de créer et supprimer des objets dans
la base de données et que nous n'aborderons pas explicitement dans le cadre de cette formation.
Le Langage de Contrôle de Données (LCD) pour gérer les droits sur les objets et que nous
n'aborderons pas non plus.
Le Langage de Manipulation de Données (LMD) pour la recherche, l'insertion, la mise à jour
et la suppression de données et qui sera le seul abordé partiellement.

A noter que le SQL est utilisé également dans QGIS dans les requêtes de filtrages sur les tables
et qu'il est également possible au travers du mécanisme des virtual layer d'utiliser le SQL sur
les couches ouvertes dans QGIS. Les Virtuals layers utilisent de façon sous-jacente le
mécanisme des drivers virtuels de Sqlite. Ils ne portent pas directement les données, mais sont
des Vues (requête SQL) sur d'autres couches.

Syntaxe générale sur la sélection

La requête de sélection est la base de la recherche de données en SQL.
Une requête SQL respecte une syntaxe de type :

SELECT (liste des attributs) FROM (liste des tables) WHERE (Conditions)

La partie SELECT indique le sous-ensemble des attributs (les colonnes) qui doivent apparaître
dans la réponse.
La partie FROM décrit les relations (les tables) qui sont utilisées dans la requête. Les attributs
de la clause SELECT doivent appartenir aux tables listées dans la clause FROM.
La partie WHERE exprime les conditions, elle est optionnelle.
Nous verrons d'autres options plus tard...
Exemple 1: SELECT * FROM commune WHERE population > 1000
Sélectionne les enregistrements de la table COMMUNE dont la population est supérieure à
1000 avec tous les attributs (c'est le sens de *) de la table COMMUNE
Exemple 2 : SELECT nom_comm, insee_comm, population FROM commune
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Sélectionne tous les enregistrements de la table COMMUNE (cf pas de conditions, c'est à dire
pas de clause WHERE) et renvoi une table avec les attributs NOM_COM, INSEE_COMM et
POPULATION.

Il est possible de donner un nom d'alias aux attributs en sortie avec le mot clef AS.
Exemple 3 : SELECT nom_comm AS COMMUNE, insee_comm AS INSEE, population
FROM commune
On peut également écrire directement (on omet le AS) :
SELECT nom_comm COMMUNE, insee_comm INSEE, population FROM commune

Les opérateurs de comparaison et les opérateurs logiques

Les opérateurs de comparaison
La clause WHERE est définie par une condition qui s'exprime à l'aide d'opérateurs de
comparaison et d'opérateurs logiques.
Les opérateurs de comparaison sont :
A=B
A <> B (différent)
A<B
A>B
A <= B (inférieur ou égal)
A >= B (supérieur ou égal)
A BETWEEN B AND C (compris entre B et C)
A IN (B1, B2,...) liste de valeurs :
Exemple : SELECT nom_comm, insee_comm, population FROM commune WHERE statut
IN('Commune simple', 'Chef-lieu de canton')
A LIKE 'chaîne'
Permet d'insérer des caractères jokers dans l'opération de comparaison, % désignant 0 à
plusieurs caractères quelconques, _ désignant un seul caractère.
Exemple : SELECT * FROM commune WHERE nom_comm LIKE 'A%' sélectionne toutes les
communes dont le nom commence par A
SELECT * FROM commune WHERE nom_comm LIKE '%SAINT%' sélectionne toutes les
communes dont le nom contient la chaîne 'SAINT'

Les opérateurs logiques

OR : pour séparer deux conditions dont au moins une doit être vérifiée.
Ex : SELECT * FROM commune WHERE statut = 'Commune simple' OR STATUT = 'Chef-
lieu de canton'
Cette requête sélectionne les communes pour lesquelles le statut est commune simple ou chef-
lieu de canton.
Bien penser dans l'exemple ci-dessus que le OR lie deux conditions. Une condition contient
nécessairement un des opérateurs de comparaison. Ainsi on ne peut écrire

SELECT * FROM commune WHERE statut = 'Commune simple' OR 'Chef-lieu de

canton'
AND : pour séparer deux conditions qui doivent être vérifiées simultanément.
Ex : SELECT * FROM commune WHERE statut = 'Sous-préfecture' AND population > 10000
seules les sous-préfectures de plus de 10 000 habitants sont sélectionnées.
Attention, l'opérateur AND ne peut être utilisé pour vérifier des conditions basées sur le
même champs, auquel cas il sera impossible que les conditions soient respectées. Par
exemple, une commune ne peut pas être à la fois chef-lieu de canton et commune simple.
Ainsi on ne peut pas écrire
SELECT * FROM commune WHERE statut = 'Commune simple' AND statut = 'Chef-
lieu de canton'
NOT : permet d'inverser une condition.
Ex : SELECT * from commune WHERE NOT (statut = 'Commune simple' OR statut = 'Chef-
lieu de canton')
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Sélectionne les communes qui ne sont ni commune simple, ni chef lieu de canton.

Les types de données et les fonctions

Les types de données
Les principaux types de données en SQL sont :
CHARACTER (ou CHAR) : valeur alpha de longueur fixe.
CHARACTER VARYING (ou VARCHAR) : valeur alpha de longueur maximale fixée.
TEXT : suite longue de caractères (sans limite de taille).
NUMERIC (ou DECIMAL ou DEC) : décimal
INTEGER (ou INT) : entier long
REAL : réel à virgule flottante dont la représentation est binaire.
BOOLEAN (ou LOGICAL) : vrai/faux
DATE : date du calendrier grégorien.

Les fonctions
SQL propose des fonctions dont on trouvera une description par exemple ici
Examinons en quelques unes...
Fonctions de transtypage:
cast (expr as type) : Est la fonction standard SQL qui permet de convertir un type en un autre.
Exemple :
Si x_commune est un champ de type INTEGER dans la table commune
SELECT x_commune FROM commune LIMIT 1
renvoi 478935
(noter l'utilisation de la clause LIMIT qui permet d'indiquer le nombre maximum
d'enregistrements en retour. Il est également possible d'utiliser la clause OFFSET pour décaler
le nombre de lignes à obtenir
Ex : SELECT * FROM commune LIMIT 10 OFFSET 5 (pour renvoyer les enregistrements de
6 à 15)
SELECT cast(x_commune as real) FROM commune LIMIT 1 renvoie 478935.0
SELECT cast(x_commune as text) FROM commune LIMIT 1 renvoie '478935' c'est à dire une
chaîne de caractère, puisque entre ''.
PotsgreSQL propose une notation compacte sous la forme expr::type
Exemple : SELECT x_commune :: real FROM commune
Une opération de transtypage est parfois nécessaire pour obtenir le résultat souhaité, en
particulier avec SpatiaLite. Prenons l'exemple de calcul d'un indicateur (ratio de deux entiers)
avec SpatiaLite.
Exemple : SELECT (population/superficie) AS densite FROM commune LIMIT 10
renvoie :

Ce résultat est inattendu !

Il est dû au fait que dans SpatiaLite, le résultat de la division de deux entiers est un entier.
Pour obtenir un résultat satisfaisant il faut au minimum convertir le numérateur ou le
dénominateur en flottant:
SELECT cast(population as float)/superficie AS densite FROM commune LIMIT 10
On remarquera à nouveau l'utilisation de LIMIT qui permet d'indiquer le nombre maximum
d'enregistrements retournés... c'est une clause très utile pour la mise au point de requêtes sur
des grosses tables ou pour récupérer juste le premier enregistrement après un tri.
Le résultat devient bien celui attendu :

Fonctions de chaînes de caractères :

LENGTH : renvoie la longueur d'une chaîne
exemple : SELECT length(nom_comm) FROM commune
CHR : renvoie le caractère correspondant au code ASCII (exemple CHR(184) renvoi ©)
|| : concatène deux chaînes (on obtient ce symbole en tapant ALTGr 6)
exemple : SELECT nom_comm|| ' '||insee_comm FROM commune LIMIT 1
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

renvoie 'SAINT-JEAN-DE-LA-MOTTE 72291'

SUBSTR : extraction d'une sous-chaîne de caractères substr(chaîne, position , longueur)
Exemple : SELECT * FROM troncon_hydrographique WHERE substr(ID_BDCARTO, 1, 3)
= '239'
sélectionne tout les tronçons dont l'identifiant commence par '239'
UPPER : convertit en majuscule
LOWER : convertit en minuscule
exemple : SELECT lower(nom_comm) FROM commune renvoie les noms de communes en
minuscules.

Fonctions mathématiques et numériques :

SQL dispose des fonctions mathématiques classiques... notons en particulier :
POW : pour élever à une puissance quelconque ex : POW(champ, 2) pour élever au carré.
SQRT : pour obtenir la racine carrée.
ROUND : qui permet d'arrondir un résultat
exemple : SELECT round(cast(population AS float)/superfice,2) AS densite FROM commune
renvoie les noms de communes en minuscules.

SQL dispose des fonctions mathématiques classiques... notons en particulier :

POW : pour élever à une puissance quelconque ex : POW (champ, 2) pour élever au carré.
SQRT : pour obtenir la racine carrée.
ROUND : qui permet d'arrondir un résultat
exemple : SELECT round(cast(population AS float)/superfice,2) AS densite FROM commune
renvoie :

Tri et agrégation
Tri
Il est possible de classer le résultat d'une requête en ajoutant le mot clef ORDER BY suivi
d'une liste de champs.
Exemple : SELECT * FROM commune ORDER BY nom_comm : pour classer le résultat par
nom de commune.
Un tri décroissant peut-être obtenu en ajoutant DESC.
Exemple : SELECT * FROM commune ORDER BY nom_comm DESC
SELECT nom_comm, round(cast(population as float)/superficie,2) AS densite FROM
commune ORDER BY densite : retourne la densité de population par ordre croissant de densité.

Agrégations
Une agrégation est une opération qui permet de regrouper les enregistrements de la table en
sortie selon des critères et d'obtenir des informations statistiques sur ces regroupements. Il faut
utiliser l'expression GROUP BY suivi du critère de regroupement.
Prenons un exemple à partir de la table COMMUNE. Nous souhaitons obtenir la population
totale par département.
SELECT Nom_comm, nom_dept, population FROM commune
nous donne :
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

la requête :
SELECT nom_dept, sum(population) AS population_dept FROM commune GROUP
BY nom_dept
renvoie :

La clause GROUP BY fonctionne de concert avec les fonctions d'agrégation (ici sum()). Les
principales fonctions d'agrégation sont :
count() : renvoie le nombre d'enregistrements
sum() : renvoie la somme
max() : maximum
min() : minimum
avg() : moyenne

Il se peut que l'on souhaite mettre un critère de sélection sur une colonne calculée par l'opération
d'agrégation. Dans l'exemple ça serait le cas si on souhaite n'afficher que les départements de
plus de 20000 habitants.
On pourrait être tenté d'écrire une requête de la forme :
SELECT nom_dept, sum(population) AS population_dept FROM commune WHERE
population_dept > 20000 GROUP BY nom_dept
ça ne marche pas car la clause where est exécutée avant l'agrégation.
La clause HAVING permet d'indiquer au SQL d'effectuer une nouvelle sélection à la fin du
calcul sur les résultats du regroupement.
On écrira donc :
SELECT nom_dept, sum(population) AS population_dept FROM commune GROUP BY
nom_dept HAVING population_dept > 20000
Sous PostgreSQL il faut répéter la fonction d'agrégation dans la clause having
SELECT nom_dept, sum(population) AS population_dept FROM commune GROUP BY
nom_dept HAVING sum(population) > 20000
nb : On n'utilisera la clause HAVING que dans le cas ou la sélection porte sur une colonne
d'agrégation calculée, pour une sélection sur une colonne existante dans la table de départ on
utilisera une condition dans la clause WHERE.
 Extensions spatiales
PostgreSQL propose l’extension spatiale (PostGIS) permettant d'ajouter le stockage et la
manipulation d'objets spatiaux en ajoutant :
• Les types de données géométriques,
• Les index spatiaux
• Les fonctions spatiales.
Cette extension n’est pas installée par défaut avec PostgreSQL mais l’utilitaire d’installation de
PostgreSQL (avec Windows) propose son installation.
Pour ajouter l’extension à votre base de données, utilisez la requête suivante (après avoir
installée l’extension) : CREATE EXTENSION postgis ;

Avec l’ajout de l’extension, vous verrez apparaitre les éléments suivants dans votre BDD :
• La table public.spatial_ref_sys : liste des systèmes de projection supportés par PostGIS
(plus de 3000 SRID).
• La vue public.geometry_columns : description de toutes les colonnes géométriques
présentes dans votre BDD.
• La vue public.gegraphy_columns : description de toutes les colonnes géographiques
présentes dans votre BDD.
• La vue public.raster_columns : description de toutes les colonnes contenant des rasters
dans votre BDD.
• La vue public.raster_overviews : liste des miniatures disponibles pour chaque raster.
• Les nombreuses fonctions spatiales public.st_XXX permettant de manipuler les
données de votre BDD.

Les types de données géométriques

La géométrie d’un objet est constituée des coordonnées de chaque sommet composant l’objet.
Les coordonnées peuvent être en 2, 3 ou 4 dimensions (x, y, z et m).
Voici les principaux types de géométrie avec les coordonnées qui peuvent être associées (en 2D
et dans le format WKT (Well Know Text) qui a l’avantage d’être particulièrement lisible) :
Point : Point(X1 Y1)
Multipoint : MultiPoint((X1 Y1), (X2 Y2))
Ligne : Linestring(X1 Y1, X2 Y2, ..., Xn Yn)
Multiligne : MultiLinestring((X1 Y1, ..., Xn Yn), (X2 Y2, ..., Xm Ym))
Polygone : Polygon(X1 Y1, ..., Xn Yn, X1 Y1)
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Polygone Polygon((X1 Y1, ..., Xn Yn , X1 Y1), (X2 Y2, ..., Xm Ym , X2

troué : Y2))
Multipolygone MultiPolygon((X1 Y1, ..., Xn Yn , X1 Y1), (X2 Y2, ..., Xm Ym
, X2 Y2))
Multipolygone MultiPolygon((X1 Y1, ..., Xn Yn , X1 Y1), ((X2 Y2, ..., Xm Ym
troué : , X2 Y2), (X3 Y3, ..., Xo Yo , X3 Y3)))
Collection de GeometryCollection( Point(X1 Y1), Polygon(X2 Y2, ..., Xn Yn ,
géométrie : X2 Y2))

La géométrie des objets est stockée dans PostgreSQL comme n’importe quelle donnée : dans
une colonne.
Cette colonne est nommée geom ou the_geom par convention mais peut être nommée autrement
comme n’importe quelle colonne.
De même, il est tout à fait possible d’avoir plusieurs colonnes géométriques dans une seule
table afin de stocker plusieurs géométries sans rapport pour un seul objet (à ne pas confondre
avec les multigéométries qui sont stockées dans une seule et même colonne).
La donnée stockée dans cette colonne est de type geometry mais PostGIS permet de préciser le
type de géométrie ainsi que le système de projection utilisé par la donnée. Voici la requête de
création d’une table géométrique contenant plusieurs colonnes géométriques avec différentes
variantes géométrie :
geom_1 (Point,4326) : Géométrie de type point, projection = 4326
geom_2 (MultiPoint,4326) : Géométrie de type multipoint, projection = 4326
geom_3 (Linestring,4326) : Géométrie de type ligne, projection = 4326
geom_4 (MultiLinestring,4326) : Géométrie de type multiligne, projection = 4326
geom_5 (Polygon,4326) : Géométrie de type polygone, projection = 4326
geom_6 (MultiPolygon,4326) : Géométrie de type multipolygone, projection = 4326
geom_7 (GeometryCollection,4326) : Géométrie de n'importe quel type, projection = 4326

Les données géométriques sont rapidement volumineuses. Ainsi, il est nécessaire de toujours
ajouter un index sur ces colonnes afin de limiter les temps de traitement sur ces données.
Ces index sont de type GIST. Leur fonctionnement est simple, il compare le rectangle englobant
de chaque objet avant d’aller plus loin. Ceci permet de faire un premier tri dans les données ce
qui élimine beaucoup d’opérations inutiles.
Voici comment ajouter un index de type gist :
CREATE INDEX ma_table_geom_idx ON mon_schema.ma_table USING gist (geom);
Les fonctions spatiales
Il existe plusieurs catégories de fonctions spatiales, comme par exemple celles qui permettent
de passer du format WKT au WKB ou inversement. Voici quelques fonctions de départ :
ST_SRID (geom) : retourne le code du système de projection de l'objet
ST_Transform(geom, x) : Reprojeter la donnée avec le SIRD x
ST_IsValid (geom) : vérifie la géométrie des objets (pas d'erreur topologique) ... Ceci
concerne essentiellement les polygones

PostGIS ajoute d'autres fonctions de vérification de la géométrie

ST_IsValidReason (geom) : retourne un texte indiquant les raisons d'une éventuelle invalidité.
ST_IsValidDetail (geom) : retourne en plus un pointeur vers la partie non valide
ST_MakeValid (geom) : Tente de corriger les géométries invalides

ST_X (geom) : retourne la coordonnée X d'un point (et uniquement d'un point).
ST_Y (geom) : coordonnée Y d'un point
ST_Centroid (geom) : retourne le centroïde d'un polygone

ST_Area (geom) retourne la surface d'un objet

ST_Buffer (geom, X) retourne un nouvel objet tampon construit autour d'un objet de X mètre
ST_Length (geom) : retourne la longueur d'un objet de type ligne ou multi-ligne (attention à
ne pas utiliser length () qui retourne la longueur du champ, spatialite autorise aussi Glength()).
ST_Perimeter (geom) : retourne le périmètre d'un objet polygone ou multi-polygone
ST_Distance (geom_1, geom_2) : retourne la distance minimum entre deux géométries
ST_MaxDistance (geom_1, geom_2) : retourne la distance maximum entre deux géométries

ST_Disjoint(geom_A, geom_B) : retourne "Vrai" si A et B ne s'intersectent pas

ST_Touches(geom_A, geom_B) : retourne"Vrai" si A touche B sans intérieur en commun
ST_Intersects(geom_A, geom_B) : retourne "Vrai" si A intersecte B
ST_Crosses(geom_A, geom_B) : retourne "Vrai" si A et B ont un intérieur commun
ST_Overlaps(geom_A, geom_B) : retourne "Vrai" si A et B ont un intérieur en commun (mais
pas complètement)
ST_Equals(geom_A, geom_B) : retourne "Vrai" si A = B
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

ST_Within(geom_A, geom_B) : retourne "Vrai" si A est entièrement contenu dans B

ST_Contains(geom_A, geom_B) : retourne "Vrai" si B est entièrement contenu dans A
ST_ContainsProperly(geom_A, geom_B) : retourne "Vrai" si B est contenu dans A sans toucher
la bordure extérieure

ST_DWithin(geom_A, geom_B, x) : retourne "Vrai" si A et B distant de x mètres maximum

(plus petite distance)
ST_DFullyWithin(geom_A, geom_B, x) : retourne "Vrai" si A et B distant de x mètres
maximum (plus grande distance)

ST_LineMerge(geom_li_1, geom_li_2...) : retourne la différentes lignes (renvoie les lignes

originale si la fusion n'est pas possible)
ST_Split(geom_A, geom_B) : retourne un objet A (ligne ou polygone) par un autre (point ou
ligne)
ST_Difference(geom_A, geom_B) : retourne la partie de A qui n'intersecte pas B
ST_Intersection(geom_A, geom_B) : retourne l'intersection de A et de B
ST_SymDifference(geom_A, geom_B) : retourne la différence entre A et B
ST_Union(geom_A, geom_B) : retourne l'union de A et de B

ST_LineInterpolatePoint(geom_A, x) : retourne un point localisé à x % de la longueur de la

ligne A
ST_LineSubstring(geom_A, x, y) : retourne un extrait de la ligne A démarrant à x % et
finissant à y %
ST_LineLocatePoint(geom_A, geom_B) : retourne un nombre entre 0 et 1 représentant la
localisation de la projection C de B sur une ligne A
ST_AddMeasure(geom_A, x, y) : retourne la ligne A dont chaque point aura une mesure
interpolée entre la valeur de début : x et la valeur de fin : y
ST_InterpolatePoint(geom_A, geom_B) -- [num] - Afficher la valeur de la mesure
sur une ligne A de la projection d'un point B sur A
ST_LocateAlong(geom_A, x) : retourne les éléments (points) de la ligne A dont la mesure est
x
ST_LocateBetween(geom_A, x, y) : retourne les éléments (points) de la ligne A dont la mesure
est comprise entre x et y
NB : Prefixe ST_ : Il faut préfixer les commandes par ST_ (Spatial Temporal) pour être
conforme au standard SQL/MM.
Connecter une base de données PostGIS : Utiliser PostGIS depuis QGIS
Avant d'utiliser le DB Manager de QGIS, il faut au préalable créer une nouvelle connexion ou
utiliser une connexion déjà créée depuis le menu Couche / Ajouter une couche / Ajouter une
couche PostGIS puis configurer les paramètres de connexions de la base de données (adresse,
port, nom de la base, nom de l'utilisateur et mot de passe).

Cliquer sur l'icône Ajouter une couche PostGIS dans la barre d'outils de QGIS.

ou à partir du menu Couche puis Ajouter une couche PostGIS (Ctrl+Shift+D).

Cliquer sur Nouveau pour établir une nouvelle connexion avec une base PostGIS. Renseigner
les paramètres de connexion (identiques à ceux renseignés dans pgAdmin) :
• nom à choisir
• hôte : localhost
• port : 5432
• base de données : nom de la BD

Dans le volet Authentification, cliquer sur

De base :
Nom d’utilisateur : postgres
Mot de passe : entrer la votre

Tester ma connexion, puis cliquer sur OK.

M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Ajouter une couche PostGIS

L'utilisation du bouton connecter en ayant choisi la connexion donne accès à la liste des
schémas et tables de la base.

Choisir la table qu'on souhaite afficher dans QGIS et cliquer sur Ajouter.
La couche des données de PostGIS apparaît dans la liste des couches de QGIS et s'affiche dans
la fenêtre cartographique si on coche son affichage.

L'info-bulle dans le contrôle des couches affiche les informations sur l'origine de la couche
(base Sandbox,...)

Importer des données

Pour importer et exporter des données dans PostGIS, il existe plusieurs solutions avec chacune
leurs avantages et inconvénients. La plus simple est l'utilisation du DBManager de QGIS.
Accès à DBmanager : Depuis QGIS nous allons utiliser le plugin DBManager qui s'interface
bien avec PostGIS. C'est le plugin qui est le plus porté par la communauté QGIS. Cette
extension est installée par défaut, il convient tout de même de vérifier qu'elle est activée
(cochée), depuis le menu Extensions / Installer et gérer les extensions.

Le plugin est alors disponible dans le menu Bases de données de QGIS.

Pour se connecter la première fois à une base de données existante (PostGIS) dans DBManager,
il est nécessaire de le faire par l'intermédiaire de QGIS.

En lançant DB Manager vous devez maintenant pouvoir vous connecter à cette base.
Utilisation de DBmanager

Rappel : Pour accéder aux bases de données depuis DBManager, il faut au préalable créer une
nouvelle connexion ou utiliser une connexion déjà créée depuis le menu Couche / Ajouter une
couche / Ajouter une couche PostGIS puis configurer les paramètres de connexions de la base
de données (adresse, port, nom de la base, nom de l'utilisateur et mot de passe).

DBManager est une extension de QGIS disponible depuis le menu Bases de données /
Gestionnaire de bases de données. Il permet de charger des fichiers dans une base PostGIS
connectée sous QGIS, par glisser / lâché de fichiers à partir du navigateur QGIS, ou par le menu
Table > Import de couche / fichier pour les couches déjà chargée dans QGIS. Ce dernier menu
est également accessible par le bouton
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Avant d'importer une couche, il conviendra de parcourir l'arborescence du gestionnaire de

bases de données, jusqu'à la base formation.

La fenêtre d'import permet de saisir les paramètres nécessaires :

Mise en œuvre en pratique SQL dans le SGBD PostGIS (démonstration) :
Si la base de données PostGIS est protégée par un mot de passe, il faut le saisir dans la fenêtre
qui apparaît pour cela.

Nous rappelons ici les principales étapes...

La fenêtre suivante apparaît indiquant que la connexion avec la base de données PostGIS est
effective.

Nb : A noter que chaque utilisateur dispose de Privilèges qui sont indiqués. C'est
l'administrateur de la base de données qui règles les droits et rôles.

Un utilisateur doit pourvoir au minimum 'accéder aux objets', un producteur doit pouvoir 'créer
de nouveaux objets'.

Lorsqu'on se place sur un objet, on peut lire les privilèges de l'objet en question.

Cliquer sur le schéma utilisé ici public puis sur le nom du fichier souhaité : ici route_xy
(attention, il ne s'agit ici que d'une démonstration).

La fenêtre suivante apparaît indiquant tous les champs attributaires de cette table PostGIS :
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

L'onglet info fournit les informations sur les tables

On peut par exemple lire que la table route_xy contient 3818 enregistrements (rows), qu'il y a
une colonne de géométrie contenant des objets 'LINESTRING', que la projection est Lambert
93 et qu'il n'y a pas d'index spatial (nous verrons ce que cela signifie concrètement plus tard).

L'onglet table fournit une vision des données de la table et l'onglet preview une visualisation
de la géométrie.
Le bouton 'fenêtre SQL' (ou menu Base de données / Fenêtre SQL) ouvre une nouvelle boîte
de dialogue dans laquelle nous allons pouvoir exécuter des ordres SQL.

On se propose de sélectionner tous les tronçons de route de la table route_xy dont le champ
numéro comprend la valeur D104.

On se place alors n'importe où sur la base de données, et on lance la fenêtre SQL.

La requête est :
 SELECT * FROM route_xy where numero = 'D104'

Après avoir cliqué sur le bouton Exécuter (F5), la liste des enregistrements de la table
correspondant à la requête apparaît dans la partie Résultat de la fenêtre.
En cochant la case Charger en tant que nouvelle couche, on peut afficher dans QGIS les
données résultant de la requête.

Il faut désigner obligatoirement la colonne avec des valeurs entières et uniques (identifiant) et
la colonne géométrique.
La colonne Géométrique peut être trouvée dans l'onglet info de la table sous DBManager ou
l'on voit que le champ 37 est de type geometry(LineString, 2154). La colonne avec des 'valeurs
entières et uniques' est donnée par la rubrique Constraint dans le même onglet, il s'agit de la
colonne gid.

Pour rajouter un identifiant unique on aurait pu le créer en modifiant la requête SQL :

SELECT row_number() over() as ident, * FROM route_xy where numero = 'D104'
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Accès aux éléments de définition de la table

Le DB Manager de QGIS
Prenons l'exemple de la table des communes, pour laquelle l'index spatial a été créé.
Les informations disponibles selon le client, sont les même mais leur accessibilité est
différente.
Sous le DB manager de QGIS, tous les éléments de la table sont disponibles dans la partie
'Info'. Les éléments qui composent la table ne sont modifiables que par requête SQL.

Cela nécessite de connaître ou retrouver la syntaxe SQL des éléments à modifier, ajouter ou
supprimer, mais ils sont facilement compréhensibles à la lecture.
PgAdmin
Sous pgAdmin, lorsque l'on se positionne sur la table commune, l'ensemble de sa définition
est disponible au format SQL, au travers du panneau SQL.

Définition SQL d'une table vectorielle géométrique

Toujours d'après l'exemple de la table des communes, nous allons décomposer les éléments
qui la caractérisent.
Toute table dispose de ses informations de base :
CREATE TABLE commune (id integer NOT NULL, id_bdcarto integer, [...] );

A ces informations de bases sont ajoutés :

La séquence qui donnera une valeur automatiquement à id lors d'un nouvel enregistrement :
DEFAULT nextval('commune_id_seq'::regclass)
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Création de la séquence :
CREATE SEQUENCE public.commune_id_seq
INCREMENT 1
START 1
MINVALUE 1
MAXVALUE 2147483647
CACHE 1 ;

Ici, la valeur d'un nouvel enregistrement sera la dernière valeur (19), à laquelle on ajoutera 1
(suite arithmétique [u(n+1) = u(n) + 1]).

La clé primaire : CONSTRAINT "COMMUNE_pkey" PRIMARY KEY (id)

Création de la clé primaire :
ALTER TABLE [Link]
ADD CONSTRAINT commune_pkey PRIMARY KEY (id);

Les droits accordés à chaque utilisateur sur la table :

ALTER TABLE [Link]
OWNER to postgres;

Définition de la géométrie : Les tables géométriques possèdent les éléments supplémentaires

suivants :
La géométrie : geom geometry(MultiPolygon,2154),

Création de la géométrie : ALTER TABLE [Link]

ADD COLUMN geom geometry(MultiPolygon,2154) ;

La géométrie est définie par le type d'entité et la projection de la table. Ces deux éléments
sont dépendants des métadonnées : la table spatial_ref_sys et la vue geometry_columns
La table spatiale_ref_sys
Cette table contient l'ensemble des systèmes de références spatiales selon les spécifications de
l'OGC. L'identifiant unique (SRID) puise sa définition dans cette table.
La vue geometry_columns
Cette vue est l'objet interrogé par les clients tels que QGIS lorsque l'on se connecte à une base
PostGIS.
En se positionnant sur la vue, et en appuyant sur le bouton « afficher les données de l'objet
séléctionné », on obtient la liste et les caractéristiques de toutes les tables géométriques
présentes dans la base de données.

Les trois premières colonnes (f_table_catalog, f_table_schema, f_table_name) caractérisent

l'emplacement des tables géométriques.
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

La colonne f_geometry_column indique la colonne qui contient la géométrie des objets.

Les colonnes coord_dimension et srid correspondent à la dimension et au système de
référence spatiale (un SRID = '-1' indique que la table est spatiale mais que le SRID n'a pas
été indiqué).
La colonne f_geometry_column définit la géométrie (plus précisément le type) des objets de
la table considérée.
L'index spatial (éventuellement) :
CREATE INDEX sidx_commune_geom
ON [Link] USING gist (geom)
TABLESPACE pg_default;

Le système de projection
Modifier le système de projection (ou système de référence spatiale) d'une table, c'est
simplement corriger la projection lorsqu'elle a mal été renseignée à l'import de la table :
SELECT UpdateGeometrySrid ('commune','geom' ,4326);
L'observation du résultat dans geometry_columns indique que la table des communes est en
WGS84. La superposition de cette table dans QGIS avec les autres tables sont sans appel : La
superposition des tables n'existe plus.
Remodifier la géométrie en 2154 dans geometry_columns permet de rétablir la projection de
la table des communes, qui est son seul système de projection correct !
Reprojeter une table
Pour reprojeter une table, il faut en créer une copie et utiliser la
fonction ST_TRANSFORM(geometry,srid).
Cette fonction transforme les coordonnées d'un système de référence spatiale (SRID) vers un
autre.
Effacer la géométrie
SELECT DropGeometryColumn ('commune','geom');
La syntaxe ci-dessus a pour effet de supprimer la géométrie de la table commune, ainsi que
dans les métadonnées (dans la vue geometry_columns)
Effacer une table géométrique
Pour effacer la table commune, le plus courant est la phrase :
DROP TABLE commune;

Bilan pgAdmin et DB Manager de QGIS

Le DB Manageur et QGIS offrent les avantages suivants :
• visualisation directe des types d'entités d'une table,
• visualisation graphique des données,
• modification graphique des données,
• visualisation directe informations d'une table et des droits de l'utilisateur considéré,
• accès simplifié pour un utilisateur.

Le client pgAdmin offre les avantages suivants :

• meilleure structuration des éléments d'une donnée (index, contraintes, gestion des
utilisateurs, fonctions disponibles...),
• visibilité directe sur l'organisation des données (tables et vues),
• modification graphique des éléments/objets,
• création de bases de données,
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Le client DB manager sera donc utilisé préférentiellement pour les utilisateurs de quelques
tables et vues, alors que pgAdmin sera destiné à des utilisateurs plus avancés voire des
administrateurs de bases de données PostGIS.

Requêtes Spatiales
Dans les requêtes spatiales, vous pouvez utiliser la colonne de géométrie (geom) comme
n’importe quelle colonne de base de données.
Avec l’installation de PostGIS dans notre base de données, vous avons ajouté des fonctions
additionnelles pour requêter la base.
CREATE DATABASE postgis_demo
CREATE EXTENSION postgis;
Ensuite, importez les données fournies dans le répertoire exercise_data/postgis/.
Créer une nouvelle connexion PostGIS à la nouvelle base de données.
Importer via DB Manager. Importez les fichiers dans les tables suivantes de la base :

Sélection par localisation

-- Récupérer tous les bâtiments de la région KwaZulu:
SELECT [Link], [Link], st_astext([Link]) as point
FROM building a, region b
 WHERE st_within([Link], [Link])
AND [Link] = 'KwaZulu';

Ou, si nous créons une vue depuis la requête précédente :

CREATE VIEW vw_select_location AS
SELECT [Link], [Link], [Link]
FROM building a, region b
WHERE st_within([Link], [Link])
AND [Link] = 'KwaZulu';

Affichez la liste des tous les noms de régions qui touchent la région Hokkaido
SELECT [Link]
FROM region a, region b
WHERE st_touches([Link], [Link])
AND [Link] = 'Hokkaido';

Créer un tampon de 100 mètres autour de la région d’Hokkaido.

CREATE VIEW vw_hokkaido_buffer AS
SELECT gid, ST_BUFFER (geom, 100) as geom
FROM region
WHERE name = 'Hokkaido';

Dans cette requête, la vue originelle du tampon est utilisée comme n’importe quelle autre
table. Elle dispose d’un alias a et son champ de géométrie, [Link], est utilisé pour
sélectionner un polygone dans la table region (alias b) qui l’intersecte. Néanmoins, Hokkaido
est exclue de cette sélection car nous n’en voulons pas, nous voulons seulement les régions
qui la touche.
CREATE VIEW vw_hokkaido_buffer_select AS
SELECT [Link], [Link], [Link]
FROM (SELECT * FROM vw_hokkaido_buffer) a, region b
WHERE ST_INTERSECTS ([Link], [Link])
AND [Link] != 'Hokkaido' ;
M. SY Licence 2/ GC/USSEIN SBBD à cartouche spatiale

Il est également possible de sélectionner les objets à une certaine distance sans avoir besoin
de créer un tampon :
CREATE VIEW vw_hokkaido_distance_select AS
SELECT [Link], [Link], [Link]
FROM region a, region b
WHERE ST_DISTANCE ([Link], [Link]) < 100
AND [Link] = 'Hokkaido'
AND [Link] != 'Hokkaido';

Exercices pratiques :

Question 1 : Sélectionner toutes les communes (table commune) du département de la

SARTHE (colonne nom_dept)
Question 2 : Sélectionner les communes du département du Maine et Loire de plus de 500
habitants en affichant un tableau avec les noms de communes et leur population.
Question 3 : Sélectionner les communes de la table commune dont le statut n'est pas une
commune simple et afficher les colonnes nom_com en lui donnant comme alias NOM (en
majuscules) et les colonnes : statut, population et superficie.
Question 4 : Sélectionner les différents noms des tronçons comportant le nom 'rivière' dans la
colonne toponyme de la table troncon_hydrographique.
Question 5 : A partir de la table commune, calculer pour chaque département ; la population
totale, la densité moyenne de population des communes = moyenne (population commune
/superficie commune) arrondie à deux décimales, la population de la commune la plus
peuplée et celle de la moins peuplée, la superficie moyenne des communes.
Question 6 : Quels sont les surfaces (en km2) et périmètres (en km), arrondis à deux chiffres
après la virgule, de l'Iris 'Verron' ?
Question 7 : Compter le nombre de tronçons des rivières : 'rivière le loir' et 'rivière l'argance',
par classe de largeur (colonne largeur).
Attention, appeler la rivière l'argance dans une requête SQL nécessite d'échapper l'apostrophe
en la doublant : 'rivière l''argance'.
Question 8 : Quelle est la longueur des rivières : 'rivière le loir' et 'rivière l'argance', par type
de largeur sur ce jeu de données ?