PostgreSQL – Vade-mecum SQL – 5/8 – Le cas PostGIS

Si vous souhaitez tout connaitre sur PostGIS, n’hésitez pas à jetez un œil à mon livre

Les données spatiales dans PostgreSQL

Extension PostGIS

PostGIS est une extension qui permet d’ajouter les fonctionnalités suivantes à PostgreSQL :

• Les types de données spatiales
• 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) :

-- Ajout de l'extension PostGIS
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éographique 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.

Données spatiales

Géométrie

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) :

Géométrie	Type	Exemple	Coordonnées
Point	Point(X1 Y1)		Point(30 10)
Multipoint	MultiPoint((X1 Y1), (X2 Y2))		MultiPoint((10 40), (40 30), (20 20), (30 10))
Ligne	Linestring(X1 Y1, X2 Y2, ... , Xn Yn)		Linestring(30 10, 10 30, 40 40)
Multiligne	MultiLinestring((X1 Y1, ... , Xn Yn), (X2 Y2, ... , Xm Ym))		MultiLinestring((10 10, 20 20, 10 40), (40 40, 30 30, 40 20, 30 10))
Polygone	Polygon(X1 Y1, ... , Xn Yn , X1 Y1)		Polygon((30 10, 40 40, 20 40, 10 20, 30 10))
Polygone troué	Polygon((X1 Y1, ... , Xn Yn , X1 Y1), (X2 Y2, ... , Xm Ym , X2 Y2))		Polygon((35 10, 45 45, 15 40, 10 20, 35 10), (20 30, 35 35, 30 20, 20 30))
Multipolygone	MultiPolygon((X1 Y1, ... , Xn Yn , X1 Y1), (X2 Y2, ... , Xm Ym , X2 Y2))		MultiPolygon(((30 20, 45 40, 10 40, 30 20)), ((15 5, 40 10, 10 20, 5 10, 15 5)))
Multipolygone troué	MultiPolygon((X1 Y1, ... , Xn Yn , X1 Y1), ((X2 Y2, ... , Xm Ym , X2 Y2), (X3 Y3, ... , Xo Yo , X3 Y3)))		MultiPolygon(((40 40, 20 45, 45 30, 40 40)), ((20 35, 10 30, 10 10, 30 5, 45 20, 20 35), (30 20, 20 15, 20 25, 30 20)))
Collection de géométrie	GeometryCollection( Point(X1 Y1), Polygon(X2 Y2, ... , Xn Yn , X2 Y2))		GeometryCollection(Point(40 10), LINESTRING(10 10, 20 20, 10 40), Polygon((40 40, 20 45, 45 30, 40 40)))

Les exemples proviennent de l’article Wipipédia suivant : Well-known text representation of geometry

Stockage

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 :

CREATE TABLE mon_schema.ma_table
(
	identifiant serial,
	
	geom_1 geometry(Point,2154),              -- Géométrie de type point, projection = 2154
	geom_2 geometry(MultiPoint,2154),         -- Géométrie de type multipoint, projection = 2154
	geom_3 geometry(Linestring,2154),         -- Géométrie de type ligne, projection = 2154
	geom_4 geometry(MultiLinestring,2154),    -- Géométrie de type multiligne, projection = 2154
	geom_5 geometry(Polygon,2154),            -- Géométrie de type polygone, projection = 2154
	geom_6 geometry(MultiPolygon,2154),       -- Géométrie de type multipolygone, projection = 2154
	geom_7 geometry(GeometryCollection,2154), -- Géométrie de n'importe quel type, projection = 2154
)
;

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 une index :

-- Création d'un index de type gist
CREATE INDEX ma_table_geom_idx ON mon_schema.ma_table USING gist (geom);

Les fonctions usuelles

Sans pour autant être exhaustive, cette liste présente les fonctions que j’utilise le plus souvent.

Les abréviations suivantes sont utilisés :

• x, y, n … : la donnée à utiliser est une valeur numérique
• 'x', 'y', 'n' … : la donnée à utiliser est une valeur textuelle
• geom : la donnée à utiliser est une géométrie
• geom_m : la donnée à utiliser est une multigéométrie
• geo_pt : la donnée à utiliser est une géométrie de type point
• geom_li : la donnée à utiliser est une géométrie de type ligne
• geom_su : la donnée à utiliser est une géométrie de type surfacique
• geom_mpt : la donnée à utiliser est une multigéométrie de type point
• geom_mli : la donnée à utiliser est une multigéométrie de type ligne
• geom_msu : la donnée à utiliser est une multigéométrie de type surfacique
• [geom] : le résultat est une géométrie
• [text] : le résultat est au format texte
• [num] : le résultat est au format numérique
• [bool] : le résultat est au format booléen (vrai/faux)

Créer des géométries

Brut

ST_MakePoint(x, y)				-- [geom] - Créer un point à partir de coordonnées x et y
ST_MakeLine(geom_pt_1, geom_li_2, ...)		-- [geom] - Créer une ligne à partir de points où de lignes
ST_LineFromMultiPoint(geom_mpt)			-- [geom] - Créer une ligne à partir de multipoints
ST_MakePolygon(geom_li) 			-- [geom] - Créer un polygone à partir d'une ligne fermée (fin = début)
ST_MakePolygon(geom_li_1, geom_li_2, ...) 	-- [geom] - Créer un polygone troué à partir de plusieurs lignes fermées (fin = début)
ST_MakeEnvelope(x_min, y_min, x_max, y_max) 	-- [geom] - Créer un rectangle à partir des valeurs min et max en x et y
ST_Collect(geom_1, geom_2,...) 			-- [geom] - Créer une multigéométrie à partir d'une ou plusieurs géométries

Import

ST_GeomFromText('text') 	-- [geom] - Créer une géométrie à partir d'un WKT
ST_GeomFromGeoJSON('json')	-- [geom] - Créer une géométrie à partir d'un GeoJSON
ST_GeomFromKML('kml')		-- [geom] - Créer une géométrie à partir d'un KML

Export

ST_AsText(geom)			-- [text] - Afficher une géométrie au format WKT
ST_AsEWKT(geom)			-- [text] - Afficher une géométrie au format EWKT (WKT + SRID)
ST_AsGeoJSON(geom)		-- [text] - Afficher une géométrie au format geoJSON 
ST_AsKML(geom)			-- [text] - Afficher une géométrie au format KML
ST_AsLatLonText(geom)		-- [text] - Afficher une géométrie au format degré, minute, seconde
ST_AsSVG(geom)			-- [text] - Afficher une géométrie au format SVG

Information sur la géométrie

Généralités

ST_IsValid(geom)		-- [bool] - "Vrai" si la géométrie est valide
ST_IsValidReason(geom)		-- [text] - Afficher la raison de l'invalidité d'une géométrie

ST_MemSize(geom)			-- [num] - Afficher la taille en octet de la géométrie

Géométrie

ST_SRID(geom)			-- [text] - Afficher le SRID de la géométrie
ST_GeometryType(geom)		-- [text] - Afficher le type de géométrie
ST_Dimension(geom)		-- [text] - Afficher la dimension de l'objet (ligne : 1 dimension)

ST_NPoints(geom)		-- [text] - Afficher le nombre de point d'une géométrie
ST_NRings(geom)			-- [text] - Afficher le nombre de contours (normalement 1, mais plus s'il y a des trous)
ST_NumGeometries(geom)		-- [text] - Afficher le nombre de géométrie d'une collection

ST_IsRing(geom_li)		-- [bool] - "Vrai" si une ligne est fermée (début=fin) et ne s'intersecte pas

Coordonnées

ST_CoordDim(geom) 		-- [text] - Afficher la dimension des coordonnées (x, y : 2 dimensions)

ST_X(geom)			-- [text] - Afficher la coordonnée X d'un point
ST_Y(geom)			-- [text] - Afficher la coordonnée Y d'un point
ST_XMax(geom)			-- [text] - Afficher la valeur maximum de X
ST_YMax(geom)			-- [text] - Afficher la valeur maximum de Y
ST_XMin(geom)			-- [text] - Afficher la valeur minimum de X
ST_YMin(geom)			-- [text] - Afficher la valeur minimum de Y

Mesures

ST_Length(geom)				-- [num] - Afficher la longueur d'une géométrie
ST_Perimeter(geom)			-- [num] - Afficher le périmètre d'une géométrie
ST_Area(geom)				-- [text] - Afficher l'aire d'une géométrie

ST_Distance(geom_1, geom_2)		-- [num] - Afficher la distance minimum entre deux géométrie
ST_MaxDistance(geom_1, geom_2)		-- [num] - Afficher la distance maximum entre deux géométries

Relations

ST_Disjoint(geom_A, geom_B)		-- [bool] - "Vrai" si A et B ne s'intersectent pas
ST_Touches(geom_A, geom_B)		-- [bool] - "Vrai" si A touche B sans intérieur en commun
ST_Intersects(geom_A, geom_B)		-- [bool] - "Vrai" si A intersecte B
ST_Crosses(geom_A, geom_B)		-- [bool] - "Vrai" si A et B ont un intérieur commun
ST_Overlaps(geom_A, geom_B)		-- [bool] - "Vrai" si A et B on un intérieur en commun (mais pas complètement)
ST_Equals(geom_A, geom_B)		-- [bool] - "Vrai" si A = B
ST_Within(geom_A, geom_B)		-- [bool] - "Vrai" si A est entièrement contenu dans B
ST_Contains(geom_A, geom_B)		-- [bool] - "Vrai" si B est entièrement contenu dans A
ST_ContainsProperly(geom_A, geom_B)	-- [bool] - "Vrai" si B est contenu dans A sans toucher la bordure extérieur

ST_DWithin(geom_A, geom_B, x)		-- [bool] - "Vrai" si A et B distant de x mètres maximum (plus petite distance)
ST_DFullyWithin(geom_A, geom_B, x)	-- [bool] - "Vrai" si A et B distant de x mètres maximum (plus grande distance)

Créer de nouvelles géométries

Contour

ST_Extent(geom)				-- [geom] - Récupérer le rectangle englobant de la géométrie
ST_Boundary(geom)			-- [geom] - Récupérer le contour de la géométrie
ST_ExteriorRing(geom_su)		-- [geom] - Récupérer le contour extérieur
ST_InteriorRingN(geom_su, n)		-- [geom] - Récupérer le contour intérieur (trou) numéro n

Sommets

ST_Centroid(geom)			-- [geom] - Récupérer le centroïde
ST_StartPoint(geom_li)			-- [geom] - Récupérer le premier Sommet
ST_PointN(geom_li, n)			-- [geom] - Récupérer le n-ième sommet d'une géométrie
ST_EndPoint(geom_li)			-- [geom] - Récupérer le dernier sommet
ST_AddPoint(geom_li, geom_pt) 		-- [geom] - Ajouter un sommet à une ligne
ST_RemovePoint(geom_li, n)		-- [geom] - Supprimer un sommet d'une ligne
ST_ClosestPoint(geom_1, geom_2)		-- [geom] - Récupérer un point sur geom_1 au plus proche de geom_2
ST_Project(geom, a, b)			-- [geom] - Récupérer un point projeté à une distance a et à un angle azimutal b par rapport à geom
ST_SetPoint(geom_li, n, geom_pt)	-- [geom] - Remplacer le point n d'une ligne par un autre

ST_ForcePolygonCW(geom_su)		-- [geom] - Récupérer un polygone dont les sommets extérieurs sont ordonnés dans le sens des aiguilles d'une montre et les sommets intérieur (trou) dans le sens inverse des aiguilles d'une montre
ST_Reverse(geom)			-- [geom] - Inverser le sens des sommets

Géométrie

ST_Buffer(geom, x)			-- [geom] - Récupérer une zone tampon de x mètre
ST_Segmentize(geom, n)			-- [geom] - Récupérer une géométrie constituée de segments de taille inférieur ou égale à n

ST_Simplify(geom, x, true)		-- [geom] - Simplifier la géométrie, x = tolérance en mètre, true pour préserver les objets plus petits que la tolérance
ST_SimplifyPreserveTopology(geom, x)	-- [geom] - Simplifier la géométrie tout en respectant la topologie (évite les géométries invalides), x = tolérance en mètre

ST_MakeValid(geom)			-- [geom] - Récupérer la géométrie valide (ST_Buffer peut également être utilisé)

Coordonnées

ST_SetSRID(geom, x) 			-- [geom] - Définir le SRID de la donnée comme étant x
ST_Transform(geom, x)			-- [geom] - Reprojetter la donnée avec le SIRD x
ST_FlipCoordinates(geom)		-- [geom] - Inverser les coordonnées x et y
ST_Force2D(geom) 			-- [geom] - Récupérer une géométrie en 2D (x, y)

Transformations

ST_Rotate(geom, z, x, y) 		-- [geom] - Faire une rotation de l'objet d'un angle de z radian en prenant le point (x,y) comme point d'origine
ST_Rotate(geom, z, geom_pt) 		-- [geom] - Faire une rotation de l'objet d'un angle de zz radian en prenant geom_pt comme point d'origine
ST_Scale(geom, x, y)			-- [geom] - Faire une transformation d'échelle x, y
ST_Translate(geom, x, y)		-- [geom] - Faire une translation de x et y mètres

Multigéométrie

ST_Multi(geom)				-- [geom] - Convertir une géométrie en multi-géométrie
ST_Collect(geom_1, geom_2, ...)		-- [geom] - Agréger des géométries dans une multigéométrie ou une collection de géométrie
ST_Collect(geom)			-- [geom] - Agréger des géométries dans une multigéométrie ou une collection de géométrie à partir d'une clause GROUP BY
ST_Dump(geom_m).geom			-- [geom] - Exploser les multigéométries en lignes qui représentent chacune une géométrie simple
ST_DumpPoints(geom_m).geom		-- [geom] - Comme ST_Dump pour les points
ST_DumpRings(geom_m).geom		-- [geom] - Comme ST_Dump mais pour extraire le contour extérieur et intérieur des polygones
ST_GeometryN(geom, n)			-- [geom] - Récupérer la n-ème géométrie lorsqu'on a une multigéométrie
ST_CollectionExtract(geom_m, n)		-- [geom] - Récupérer les géométries du type spécifié par n : 1 = POINT, 2 = LINESTRING, 3 = POLYGON

Relation spatiale

ST_LineMerge(geom_li_1, geom_li_2...)	-- [geom] - Fusionner différentes lignes (renvoie les lignes originale si la fusion n'est pas possible)
ST_Split(geom_A, geom_B)		-- [geom] - Couper un objet A (ligne ou polygone) par un autre (point ou ligne)
ST_Difference(geom_A, geom_B)		-- [geom] - Récupérer la partie de A qui n'intersecte pas B
ST_Intersection(geom_A, geom_B)		-- [geom] - Récupérer l'intersection de A et de B
ST_SymDifference(geom_A, geom_B)	-- [geom] - Récupérer la différence entre A et B
ST_Union(geom_A, geom_B)		-- [geom] - Récupérer l'union de A et de B

Référencement linéaire

ST_LineInterpolatePoint(geom_A, x)		-- [geom] - Récupérer un point localisé à x % de la longueur de la ligne A
ST_LineSubstring(geom_A, x, y)			-- [geom] - Récupérer un extrait de la ligne A démarrant à x % et finissant à y %
ST_LineLocatePoint(geom_A, geom_B)		-- [num] - Afficher 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)			-- [geom] - Récupérer 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)			-- [geom] - Récupérer les éléments (points) de la ligne A dont la mesure est x
ST_LocateBetween(geom_A, x, y)			-- [geom] - Récupérer les éléments (points) de la ligne A dont la mesure est comprise entre x et y

Les concepts clés :

Les variables spécifiques

PostGIS repose sur des librairies (GEOS, GDAL…) indépendantes. Certains de leurs paramètres peuvent être contrôlés par l’intermédiaire de variables spécifiques.

Ces variables doivent être définies dans les paramètres de configuration de PostgreSQL.

Pour conniatre la liste de ces variables et comment les configurer, lisez l’article dédié :

Configurez PostgreSQL

La bounding box ou rectangle englobant

La bounding box est tout simplement le rectangle englobant un objet. Ses coordonnées sont les x min, x max, y min et y max de l’objet en question.

Importer et exporter des données géométriques : QGIS

On s’éloigne un peu de PostgreSQL mais il faut bien que vous appreniez à importer et à exporter des données SIG dans votre base… Pour ce faire, un plugin est livré avec PostGIS : shapefile and dbf loader mais il n’est pas forcément pratique à utiliser, je vous recommande donc d’utiliser le logiciel QGIS.

Si vous vous intéressez à PostGIS c’est que vous êtes un peu sigiste. Alors, comme tout bon sigiste qui se respecte, vous devez avoir QGIS sur votre poste. Je vous passe le fonctionnement du logiciel pour me concentrer sur le gestionnaire de BDD.

Dans un premier temps, vous devez déclarer une connexion à votre base PostgreSQL :

Couche > Ajouter une couche > Ajouter une couche PostGIS > Nouveau

Vous retrouverez un peu l’interface de connexion des autres clients pour PostgreSQL. Entrez vos informations de connexion et cliquez sur « ok ».

Votre connexion est maintenant enregistrée dans QGIS.

Pour ajouter des couches de votre BDD dans QGIS, il vous suffit de cliquer sur « connexion », de choisir vos couches puis de cliquer sur « ajouter ». Mais il existe un autre moyen de gérer vos couches : le gestionnaire de base de données.

Base de données > Gestionnaire de base de données > Gestionnaire de base de données

Vous retrouverez ici une arborescence vous permettant de sélectionner votre BDD. En dépliant les éléments, vous pouvez voir vos schémas puis vos tables.

Cliquez sur une table, vous verrez toutes ses informations s’afficher dans le volet de droite. Pour l’ajouter dans QGIS, glissez-la dans la fenêtre de QGIS.

A partir de cette interface vous pouvez gérer votre BDD : créer/supprimer des schémas, ajouter/supprimer des tables, créer des contraintes et des index.

Deux boutons sont à votre disposition :

• Importer une couche ou un fichier : pour importer une couche QGIS ou un fichier plat dans votre base PostgreSQL.
• Exporter vers un fichier : pour exporter une table PostgreSQL vers un fichier plat.

Les options de la fenêtre d’import parlent d’elles-mêmes mais n’hésitez pas à utiliser le bouton « Mettre à jour les options » pour rapidement reprendre les infos de votre couche dans les paramètres d’import.

• Clé primaire : une clé primaire sur la colonne « id » est automatiquement créée. Vous pouvez ici modifier le nom de la colonne.
• Colonne de géométrie : par défaut « geom » mais vous pouvez changer ce nom.
• SCR source : SRID source, changez le s’il est mal détecté.
• SCR cible : SRID de la table dans PostgreSQL, changez le si vous souhaitez reprojeter vos données lors de l’import.
• Encodage : par défaut UTF-8 changez le si vous souhaitez un autre encodage pour vos caractères.
• Remplacer la table de destination (si existante) : …
• Créer des géométries simples au lieu de multiparties : …
• Convert field names to lowercase : convertir le nom des colonnes en minuscule.
• Créer un index spatial : toujours cocher cette case.

L’import concerne toutes les colonnes et créera automatiquement une colonne supplémentaire contenant la géométrie de vos objets.

---

Il est maintenant temps de comprendre le fonctionnement de toutes ces fonctions que nous avons vu.

Sommaire général

Voici le sommaire général du cours :

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDPostGISPostgreSQLProgrammationSIGSQL tuto