IGE100 – CONCEPTION ET
EXPLOITATION DES BASES DE
DONNÉES
COURS 6 : INTÉGRATION DES RÉFÉRENCES
SPATIALES DANS LES BASES DE DONNÉES –
PARTIE 2
Mickaël Germain
Références du cours
• PostGIS (extension de PostgreSQL) :
[Link]
foss4g/doc/[Link] (chap. 8 à 13)
• Référence des fonctions spatiales et des opérateurs spatiaux
[Link]
2
SQL : les fonctions spatiales
• Préparation du cours
– Pour la suite de ce cours, nous allons utiliser une table
générée par le script [Link] disponible sur Moodle
– Paramètre du SGDB utilisé dans le cours :
• Serveur : [Link] ; Port : 5432
• User : ige100 ; Password : ige100
• BD : ige100
• Interface Web : [Link]
SELECT id, name, st_astext(geom)
FROM exemple
3
QGIS
SQL : les fonctions spatiales
• Avant de commencer
– Les fonctions spatiales SQL présentées dans la suite de cours ont été
définies et normalisées par l’OGC (OpenGIS), et seront présentées pour
les données vectorielles.
– Quelques exemples de fonctions sont présentées dans le cours. Pour
une étude complète des fonctions spatiales, veuillez vous référer au
site suivant : [Link]
• Remarque importante : le préfixe ST_ ajouté devant une
fonction spatiale permet d’identifier les fonctions qui respectent
la norme OGC pour PostGIS
4
SQL : les fonctions spatiales
• Présentation des fonctions spatiales dans PostGIS
– Les fonctions spatiales du SQL peuvent être classées en quatre
catégories:
• Les fonctions d’information donnant de l’information sur une
forme géométrique.
• Les fonctions logiques permettant de faire des tests logiques entre
deux formes géométriques.
• Les fonctions géométriques créant des formes à partir d’autres
formes.
• Les fonctions de mesure sur des formes géométriques
5
SQL : les fonctions spatiales
• Les fonctions d’information
– st_dimension(geometry)
• Cette fonction retourne la dimension de l’objet géométrique étudié.
– 0 pour le point
– 1 pour le segment
– 2 pour le polygone ou une région
– Etc. exemple
– Exemple :
SELECT st_dimension(geom)
FROM exemple
6
SQL : les fonctions spatiales
• Les fonctions d’information
– st_asText(geometry)
• Cette fonction retourne la description sous la forme de texte de l’information
géométrique
exemple
– Exemple :
SELECT st_asText(geom)
FROM exemple
7
SQL : les fonctions spatiales
• Les fonctions d’information
– st_isSimple(geometry)
• Cette fonction retourne 1 (ou TRUE) si l’objet géométrique est d’une forme simple,
c’est-à-dire sans une auto-intersection ou une auto-tangente.
– Exemple : exemple
SELECT st_isSimple(geom)
FROM exemple
8
SQL : les fonctions spatiales
• Les fonctions d’information
– st_isEmpty(geometry)
• Cette fonction retourne 1 (ou TRUE) si l’objet géométrique est vide, 0 ( ou FALSE)
sinon.
exemple
– Exemple :
SELECT st_isEmpty(geom)
FROM exemple
9
SQL : les fonctions spatiales
• Les fonctions d’information
– st_geometryType(geometry)
• Cette fonction retourne le type de la forme géométrique (LINESTRING, POLYGON,
MULITPOINT, POINT, etc.)
exemple
– Exemple :
SELECT st_geometryType(geom)
FROM exemple
10
SQL : les fonctions spatiales
• Les fonctions d’information
– st_isClosed(geometry)
• Cette fonction retourne 1 (ou TRUE) si la forme géométrique est fermée (point de
départ=point d’arrivée). 0 (ou FALSE) sinon
exemple
– Exemple :
SELECT st_isClosed(geom)
FROM exemple
11
SQL : les fonctions spatiales
• Les fonctions d’information
– st_X(geometry), st_Y(geometry), st_Z(geometry)
• Ces fonctions retournent les coordonnées x, y ou z d’un point. Retourne
NULL si il n’y a aucun point dans l’objet.
– st_SRID(geometry)
• Retourne la projection spatiale utilisée
– st_endPoint(geometry) ( st_startPoint(geometry) )
• Retourne le premier (dernier) point de l’objet géométrique passé en
argument
– Etc. Voir la documentation PostGIS pour les autres fonctions
d’information 12
SQL : les fonctions spatiales
• Les fonctions logiques
– st_touches(geometry,geometry)
• Cette fonction retourne 1 (TRUE) si les formes géométriques se touchent,
c’est-à-dire qu’il faut que les frontières se touchent. 0 (FALSE) sinon.
– Exemples : exemple
SELECT st_touches([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=2;
SELECT [Link]
FROM exemple g1, exemple g2
WHERE [Link]=7
AND st_touches ([Link],[Link])=true;
13
SQL : les fonctions spatiales
• Les fonctions logiques
– st_overlaps(geometry,geometry)
• Cette fonction retourne 1 (TRUE) si une forme géométrique recouvre
spatialement l’autre. 0 (FALSE) sinon. Retourne 0 aussi si l’un des deux
objets est entièrement compris dans l’autre.
– Exemples : exemple
SELECT st_overlaps([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=2;
SELECT st_astext([Link])
FROM exemple g1, exemple g2
WHERE [Link]=7
AND st_overlaps ([Link],[Link])=true;
14
SQL : les fonctions spatiales
• Les fonctions logiques
– st_equals(geometry,geometry)
• Cette fonction retourne 1 (TRUE) si les formes géométriques sont
identiques. 0 (FALSE) sinon.
– Exemples : exemple
SELECT st_equals([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=6 and [Link]=1;
SELECT st_astext([Link])
FROM exemple g1, exemple g2
WHERE [Link]=6
AND st_equals([Link],[Link])=true;
15
SQL : les fonctions spatiales
• Les fonctions logiques
– st_disjoint(geometry,geometry)
• Cette fonction retourne 1 (TRUE) si les formes géométriques sont
disjointes. 0 (FALSE) sinon.
(deux formes sont disjointes si leur intersection est vide)
exemple
– Exemples :
SELECT st_disjoint([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=2;
SELECT st_disjoint([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=1;
16
SQL : les fonctions spatiales
• Les fonctions logiques
– st_intersects(geometry,geometry)
• Cette fonction retourne 1 (TRUE) si une intersection existe entre les 2
géométries. 0 (FALSE) sinon.
– Exemples : exemple
SELECT st_intersects([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=2;
SELECT st_intersects([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=1;
17
SQL : les fonctions spatiales
• Les fonctions logiques
– st_contains(geometry,geometry) (contraire de within(geometry,geometry))
• Cette fonction retourne 1 (TRUE) si la forme géométrique dans le premier
argument contient la forme géométrique du deuxième argument. 0 (FALSE)
sinon.
exemple
– Exemples :
SELECT st_contains([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=6 and [Link]=1;
SELECT st_asText([Link])
FROM exemple g1,exemple g2
WHERE [Link]=6
AND st_contains([Link],[Link])=true;
18
SQL : les fonctions spatiales
• Les fonctions logiques
– st_crosses(geometry,geometry)
• Cette fonction retourne 1 (TRUE) si une forme géométrique traverse
l’autre. 0 (FALSE) sinon.
– st_within(geometry,geometry)
• Cette fonction retourne 1 (TRUE) si la forme géométrique est à l’intérieur
de l’autre. 0 (FALSE) sinon.
– Etc.
19
SQL : les fonctions spatiales
• Les fonctions logiques – Règles spatiales OGC
Deux formes se touchent (touches) si elles ont des frontières communes :
Deux formes sont disjointes (disjoint) si leur intersection est vide :
20
SQL : les fonctions spatiales
• Les fonctions logiques – Règles spatiales OGC
Deux formes se croisent (crosses) si la dimension de leur intersection est plus
petite que la dimension maximale des géométries et qu’elles ne sont pas égales :
Deux formes se superposent (overlaps) :
21
SQL : les fonctions spatiales
• Les fonctions logiques – Règles spatiales OGC
Deux formes ont une intersection (intersects) si elles ne sont pas disjointes.
Une forme est contenue (within) dans une autre si tous ses points sont
contenus dans l’autre :
Une forme contient (contains) une autre forme si cette autre forme est
contenue dans cette forme.
22
SQL : les fonctions spatiales
• Les fonctions géométriques
– st_buffer(geometry,double)
• Cette fonction retourne une forme géométrique (polygone) tampon
autour de la forme géométrique en argument, avec une épaisseur
mentionnée en argument. L’unité dépend de la projection cartographique.
exemple
– Exemples :
SELECT st_asText(st_buffer(geom,2))
FROM exemple
WHERE id=5;
SELECT st_asText(st_buffer(geom,2))
FROM exemple
WHERE id=7;
23
SQL : les fonctions spatiales
• Les fonctions géométriques
– st_buffer(geometry,double)
• Cette fonction retourne une forme géométrique (polygone) tampon
autour de la forme géométrique en argument, avec une épaisseur
mentionnée en argument. L’unité dépend de la projection cartographique.
– Question : comment visualiser l’information dans QGIS ?
• Créer une table qui contient le résultat
CREATE TABLE questionBuffer as (
SELECT st_buffer(geom,2)
FROM exemple
WHERE id=5
)
24
QGIS
SQL : les fonctions spatiales
• Les fonctions géométriques
– st_intersection(geometry,geometry)
• Cette fonction retourne la forme géométrique obtenue à partir de
l’intersection des 2 formes géométriques en argument.
– Exemples : exemple
SELECT st_asText(st_intersection([Link],[Link]))
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=2;
SELECT st_asText(st_intersection([Link],[Link]))
FROM exemple g1,exemple g2
WHERE [Link]=6 and [Link]=1;
25
SQL : les fonctions spatiales
• Les fonctions géométriques
– st_difference(geometry,geometry)
• Cette fonction retourne la forme géométrique résultante de la différence
entre les formes géométriques en argument.
– Exemples : exemple
SELECT st_asText(st_difference([Link],[Link]))
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=2;
SELECT st_asText(st_difference([Link],[Link]))
FROM exemple g1,exemple g2
WHERE [Link]=6 and [Link]=1;
26
SQL : les fonctions spatiales
• Les fonctions géométriques
– st_boundary(geometry)
• Cette fonction retourne la frontière de l’objet géométrique
– Point -> frontière vide
– Segment -> plusieurs points
– Polygone -> plusieurs segments exemple
– Exemples :
SELECT st_asText(st_boundary(geom))
FROM exemple
27
SQL : les fonctions spatiales
• Les fonctions géométriques
– st_geomUnion(geometry,geometry)
• Cette fonction retourne une forme géométrique réunissant les 2 formes
géométriques en argument.
– st_union(geometry)
• Cette fonction retourne une forme géométrique réunissant la colonne
géométrique.
– st_symDifference(geometry,geometry)
• Cette fonction retourne la forme géométrique de la valeur absolue entre
deux formes.
– st_convexHull(geometry)
• Cette fonction retourne une forme géométrique correspondant à la forme
convexe de Hull de la géométrie en argument.
28
SQL : les fonctions spatiales
• Les fonctions géométriques – Les règles spatiales OGC
– Couleur jaune ci-dessous = la forme résultante de l’opération
geomUnion intersection difference
convexHull symDifference buffer 29
SQL : les fonctions spatiales
• Les fonctions de mesure
– st_area(geometry)
• Cette fonction retourne la valeur de la superficie pour la forme
géométrique en argument. La superficie dépend de l’unité utilisée pour les
coordonnées.
exemple
– Exemples :
SELECT st_area(geom)
FROM exemple
WHERE id=7;
SELECT st_area(geom)
FROM exemple
WHERE id=6;
30
SQL : les fonctions spatiales
• Les fonctions de mesure
– st_distance(geometry,geometry)
• Cette fonction calcule la distance euclidienne entre les 2 formes
géométriques en argument. La distance dépend de l’unité utilisée pour les
coordonnées.
exemple
– Exemples :
SELECT st_distance([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=7 and [Link]=2;
SELECT st_distance([Link],[Link])
FROM exemple g1,exemple g2
WHERE [Link]=6 and [Link]=4;
31
SQL : les fonctions spatiales
• Les fonctions de mesure
– st_length(geometry)
• Cette fonction retourne la longueur de la forme géométrique en argument
(longueur des lignes). La longueur dépend de l’unité utilisée pour les
coordonnées.
exemple
– Exemples :
SELECT st_length(geom)
FROM exemple
WHERE id=7;
SELECT st_length(geom)
FROM exemple
WHERE id=6;
32
SQL : les fonctions spatiales
• Les fonctions de mesure
– st_perimeter(geometry,geometry)
• Cette fonction retourne le périmètre de la forme géométrique en
argument (polygone). Le périmètre dépend de l’unité utilisée pour les
coordonnées.
exemple
– Exemples :
SELECT st_perimeter(geom)
FROM exemple
WHERE id=7;
SELECT st_perimeter(geom)
FROM exemple
WHERE id=6;
33
SQL : les fonctions spatiales
• Pour continuer …
[Link]
34
