SGBD et extensions spatiales Présentation de PostGIS

SGBD et extensions spatiales
Présentation de PostGIS
Le modèle relationnel et les objets spatiaux
Stockage de l'information spatiale
Les opérateurs spatiaux
PostGIS, l'extension spatiale de PostgreSQL
Thérèse Libourel
Samuel Andrés
Nikolay Sirakov
SGBD et extensions spatiales
Les SGBD relationnels sont conçus d’abord pour manipuler des
données de type texte, numérique ou date
id culture
parcelle
Extension nécessaire pour :
1
blé
2
blé
3
maïs
4
vigne
• Gérer les systèmes de coordonnées
5
tournesol
• Créer des index spatiaux
Oracle
MySQL
Extension spatiale
SQLServer
PostgreSQL
• Stocker la géométrie
• Manipuler la géométrie
Oracle Spatial (depuis V7 en 1992)
MySQL GIS (intégré depuis V4.0.1 en 2003)
SQLServer Spatial (intégré depuis 2008)
PostGIS (première apparition en 2001)
2
Extensions spatiales et le modèle relationnel
Besoin : nécessité de supporter des données géographiques dans un SGBD
Contrainte : conserver la compatibilité avec l'existant par extension du modèle
relationnel
Solution : ajouter la possibilité de stocker dans les tables des objets
géographiques grâce à des nouveaux types d'attributs et des fonctions
SQL spécifiques pour les manipuler
Ces types complexes ne respectent pas la première forme normale car ils
peuvent contenir des attributs multivalués
3
Le modèle des SGBD Relationnels
Théorie formulée par Edgar F. Codd en 1970, basée sur les ensembles
{ 1 ,2 ,3 ,4 ,5 ,6 ,7 ,8 ,9 ,10,11,12,13,14,15,16 }
= nombres
{ blé, maïs, colza, tournesol, vigne, verger }
= cultures
{
,
,
,
,
,
,
,
{ Marie, Robert, Georges, John, Céline, Franck }
}
= polygones
= noms
4
Le modèle des SGBD Relationnels
1
2
3
4
5
6
7
8
blé
blé
John
maïs
John
vigne
Céline
tournesol
Franck
blé
Céline
vigne
John
verger
Georges
Robert
5
Manipulation de données en SQL
Projection
SELECT id , parcelle FROM occup_sol ;
occup_sol
id culture
parcelle
1
blé
1
2
blé
2
3
maïs
3
4
vigne
4
5
tournesol
5
6
blé
6
7
vigne
7
8
verger
8
6
Manipulation de données en SQL
Restriction
SELECT * FROM occup_sol WHERE culture = ‘blé’;
occup_sol
id culture
parcelle
1
blé
1
blé
2
blé
2
blé
3
maïs
6
blé
4
vigne
5
tournesol
6
blé
7
vigne
8
verger
7
Manipulation de données en SQL
Jointure
SELECT * FROM occup_sol,exploitant WHERE id = id_parc;
exploitant
occup_sol
id culture
parcelle
id_ex nom_ex
id_parc
1
blé
12
John
1
2
blé
7
Gérard
1
3
maïs
14
Céline
3
4
vigne
19
Franck
4
5
tournesol
15
Isabelle
4
id = id_parc
3
maïs
14
Céline
4
vigne
19
Franck
8
Manipulation de données en SQL
Projection et résultat dans une nouvelle relation
CREATE TABLE parc AS (SELECT id , parcelle FROM occup_sol);
occup_sol
id culture
parcelle
parc
id parcelle
1
blé
1
2
blé
2
3
maïs
3
4
vigne
4
5
tournesol
5
6
blé
6
7
vigne
7
8
verger
8
9
Stockage de l’information spatiale
Simple Feature Specification de l’Open Géospatial Consortium
Open Geospatial Consortium ( http://www.opengeospatial.org/ )
Simple Feature Specification.
10
Stockage de l’information spatiale
Éléments simples
Éléments ponctuels
POINT (44 855)
Éléments linéaires
LINESTRING (40 798, 72 808, 87 797, 122 812, 138 812)
Éléments surfaciques
POLYGON ((148 749, 147 735, 172 735, 174 750, 148 749))
11
Stockage de l’information spatiale
Éléments composés
Éléments ponctuels composés
MULTIPOINT (332.251 691.235, 333.256 682.546, ... ,
328.874 659.415)
Éléments linéaires composés
MULTILINESTRING (( 292.487 904.395, 298.529
906.226 ), ...,
( 316.104 906.226, 318.301
905.677, 321.414 905.677, 327.638 904.029))
Éléments surfaciques composés
MULTIPOLYGON ((( 294.096 872.146, ...
, 294.096 872.146), ..., (( 322.115 866.840,
... , 322.115 866.840 )))
12
Stockage de l’information spatiale
Formats de stockage
•
Well Known Text (WKT)
Représentation textuelle
de données géographiques
– Objets spatiaux
– Systèmes de coordonnées
Il s'agit en général du format utilisé par l'utilisateur pour interagir avec le SGBD
13
Stockage de l’information spatiale
Formats de stockage
•
Well Known Binary (WKB)
Représentation binaire compacte de données géographiques : chaque objet
spatial est représenté par un ensemble d'octets
Exemple : la valeur WKB correspondant à POINT(1 1) est la séquence
0101000000000000000000F03F000000000000F03F
où
01 = type de stockage (byte order)
01000000 = type de géométrie (Point, LineString, Polygon, …)
000000000000F03F = coordonnée X
000000000000F03F = coordonnée Y
Il s'agit du format utilisé en interne par le SGBD pour stocker les objets
14
Les opérateurs spatiaux
Opérations spatiales de comparaison
A et B sont Disjoint
A
A Touche B
A et B sont en
Intersection
A Contient B
B
Résultat = vrai ou faux
15
Les opérateurs spatiaux
Opérations spatiales produisant des valeurs ou des nouvelles géométries
Distance
Valeur numérique
Zone tampon
(buffer)
Intersection
Découpage
Union
Différence
Nouvelle entité géographique
16
PostGIS l'extension spatiale de PostgreSQL
PostGIS permet la manipulation d'informations spatiales sous forme de
géométries avec le SGBD PostgreSQL
Disponible sous licence libre GNU/GPL
Multi plateforme (Unix, Linux, Windows, Mac)
Première version en 2001, dernière version : 2.1.1 (8 novembre 2013)
Développement communautaire actif avec des mises à jour fréquentes (la
version 2 a apporté de nombreuses améliorations)
Propose des fonctions spécifiques pour faire des requêtes SQL spatiales
http://fr.wikipedia.org/wiki/PostGIS
17
PostGIS l'extension spatiale de PostgreSQL
Permet le stockage des objets géographiques conformément aux
standards de l'OGC (Point, Multipoint, Linestring, Multilinestring, Polygon,
Multipolygon et GeometryCollections) aux formats WKT/WKB
S'appuie sur des bibliothèques complémentaires : GDAL (formats raster),
OGR (formats vecteur), PROJ4 (projections), GEOS (opérations
géométriques)
Gestion des systèmes de projection grâce aux identifiants SRID (Spatial
Referencing IDentifier)
18
Prise en compte de PostGIS dans une base PostgreSQL
Ajout du langage plpgsql utilisé par les fonctions spatiales
createlang
OU
plpgsql
CREATE LANGUAGE
ma_base
plpgsql;
depuis une console (DOS ou unix)
après connexion à la base de données
Ajout des fonctions spatiales
psql -d ma_base -f chemin/postgis.sql
depuis une console (DOS ou unix)
Ajout des systèmes de coordonnées
psql -d ma_base -f chemin/spatial_ref_sys.sql
depuis une console (DOS ou unix)
Depuis PostgreSQL 9.1
psql -d ma_base -c 'CREATE EXTENSION postgis'
depuis une console
OU
CREATE EXTENSION postgis;
après connexion à la base de données
19
Stockage de l’information géographique dans PostGIS
•
La table de méta-données SPATIAL_REF_SYS
– Liste les systèmes de référence spatiale (SRS) et les systèmes de
projection cartographiques
– Schéma :
SRID = identifiant du SRS dans PostGIS
AUTH_NAME = nom de l'autorité gestionnaire du SRS (ex : EPSG)
AUTH_SRID = identifiant du SRS défini par l'autorité
SRTEXT = représentation WKT du SRS
PROJ4TEXT = définition des coordonnées PROJ4 du SRS
– Un grand nombre de systèmes de référence est déjà disponible dans
PostGIS
20
Stockage de l’information géographique dans PostGIS
•
La table vue de méta-données GEOMETRY_COLUMNS
– Liste les tables contenant des colonnes spatiales
– Schéma :
F TABLE CATALOG, F TABLE SCHEMA, F TABLE NAME = nom
totalement qualifié de la table contenant la colonne géométrique
F GEOMETRY COLUMN = nom de la colonne géométrique dans la table
COORD DIMENSION = dimension spatiale de la colonne (2, 3 ou 4)
SRID = identifiant du SRS de la colonne (clé étrangère vers
SPATIAL_REF_SYS)
TYPE = type d'objets géométriques (Point, Linestring, Polygon, ...)
Depuis PostGIS 2, ce n'est plus une table mais une vue branchée sur les
catalogues système de PostgreSQL
21
Ajout d'une colonne spatiale dans une table
Avec l'opérateur AddGeometryColumn
SELECT AddGeometryColumn
('nom_table','nom_colonne_spatiale',SRID,'type_objet_spatial',NB_DIM);
avec
SRID = identifiant du système de référence spatial (voir table spatial_ref_sys)
type_objet_spatial = POINT, LINESTRING, POLYGON, ...
NB_DIM = nombre de dimensions de la géométrie (2, 3 ou 4D)
En déclarant directement une colonne de type géométrique (depuis PostGIS 2)
ALTER TABLE nom_table ADD COLUMN the_geom geometry(Point,2154);
CREATE TABLE nom_table (
att1 integer not null PRIMARY KEY,
att2 varchar(50),
the_geom geometry(Point,2154)
);
22
Ajouts d'objets géométriques dans une table
La fonction ST_GeometryFromText permet de décrire un objet
géométrique à partir de sa définition en WKT
Ajout d'un enregistrement dans une table contenant colonne spatiale POINT
INSERT INTO nom_table
VALUES ('', 'Colonne1', 'Colonne2', ST_GeometryFromText ('POINT(25 10)', SRID));
Remplissage d'une colonne spatiale POINT à partir de colonnes numériques (X,Y)
UPDATE nom_table SET nom_colonne_spatiale = ST_GeometryFromText ('POINT(' || X || ' ' || Y || ')',
SRID);
23
Vues et index spatiaux
Création d’une vue contenant une colonne spatiale
CREATE VIEW prix_eau_commune AS (
SELECT pe.num_insee, pe.nom_commune, com.the_geom, com.gid
FROM prix_eau AS pe, commune AS com
WHERE pe.num_insee = com.insee_comm
);
INSERT INTO geometry_columns
VALUES ('','public','prix_eau_commune','the_geom',2,4326,'MULTIPOLYGON');
Pas nécessaire dans PostGIS 2
Création d’un index spatial
CREATE INDEX commune_gidx ON commune
USING GIST ( the_geom GIST_GEOMETRY_OPS_2D);
24
PostGIS et le format Shapefile
Importation de données à partir d’un fichier au format Shapefile
shp2pgsql –s srid shapefile nom_table > fichier.sql
shp2pgsql –s 2975 canne.shp canne > canne.sql
psql –U postgres –f canne.sql ma_base
OU
shp2pgsql –s 2975 canne.shp canne | psql -U postgres -d ma_base
Exportation de données vers un fichier au format Shapefile
pgsql2shp –f shapefile nom_base nom_table
pgsql2shp –f shapefile nom_base requête
pgsql2shp -f canne.shp ma_base canne
pgsql2shp -f canne.shp ma_base "SELECT * FROM canne"
25
Les opérateurs non spatiaux
•
Opérateurs de comparaison
Et : AND
Ou : OR
Ou exclusif : XOR
Non : NOT
SELECT nomCom FROM commune WHERE
numInsee < 100000 AND surfaceCom > 35000;
•
Opérateurs statistiques
Maximum : MAX
Minimum : MIN
Nombre d'entités : COUNT
Moyenne : AVG
Somme : SUM
SELECT MIN(longueur) FROM riviere;
26
Les opérateurs spatiaux dans PostGIS
Opérateurs spatiaux de comparaison
A et B sont Disjoint
A
A Touche B
A et B sont en
Intersection
A Contient B
B
ST_Disjoint (A,B)
ST_Touches (A,B)
ST_Intersects (A,B)
ST_Contains (A,B)
ST_Within (B,A)
Déterminer si une géométrie est vide = ST_IsEmpty(A)
http://postgis.net/docs/reference.html
27
Les opérateurs spatiaux dans PostGIS
Opérateurs spatiaux produisant des valeurs ou des nouvelles géométries
Zone tampon
(buffer)
Distance
Intersection
Découpage
ST_Intersection (A,B) ST_Difference (A,B)
Union
ST_Union (A,B)
ST_Distance (A,B)
Différence
ST_SymDifference (A,B)
ST_Buffer (A,rayon)
http://postgis.net/docs/reference.html
28
Les opérateurs spatiaux dans PostGIS
Opérateurs spatiaux produisant des valeurs ou des nouvelles géométries
Afficher le SRID d'une colonne spatiale = ST_SRID(geom)
Afficher la dimension d'une colonne spatiale = ST_Dimension(geom)
Afficher le type de géométrie d'une colonne spatiale = ST_GeometryType(geom)
Calcul d'aire = ST_Area(geom)
Calcul de périmètre = ST_Perimeter(geom)
Calcul de la longueur = ST_Length(geom)
Rectangle englobant d'une géométrie = ST_Extent(geom)
Coordonnées min et max d'une géométrie = ST_XMax(geom), ST_XMin(geom),
ST_YMax(geom), ST_YMin(geom)
http://postgis.net/docs/reference.html
29
Quelques exemples de requêtes spatiales
Fonctions de test sur un attribut spatial
SELECT r.numero FROM routes AS r, parc AS p WHERE ST_Intersects(r.the_geom, p.the_geom);
SELECT nom_commune, the_geom FROM commune
WHERE ST_Distance(the_geom, ST_GeometryFromText('POINT(750661 6277044)', 2154)) < 5000;
Fonctions produisant de nouvelles entités spatiales
SELECT ST_Buffer(the_geom,500) as buf FROM routes WHERE routeId = 'D 85';
UPDATE emprise SET the_geom = (SELECT ST_Buffer() …. ) ;
SELECT ST_Intersection(b.the_geom, c.the_geom) AS the_geom
FROM batiment b, commune c
WHERE ST_Intersects(b.the_geom, c.the_geom) AND c.nomCom = 'Montpellier';
30
Fonctions d’exportation
ST_AsEWKT(geometry)
ST_AsText(geometry)
ST_AsEWKB(geometry)
ST_AsBinary(geometry)
ST_AsSVG(geometry)
ST_AsGML(geometry)
ST_AsKML(geometry)
ST_AsGeoJson(geometry)
Well Known Text avec SRID (OGC)
Well Known Text sans SRID (OGC)
Well Known Binary avec SRID (OGC)
Well Known Binary sans SRID (OGC)
Scalable Vector Graphic (W3C)
Geographic Markup Language (OGC)
Keyhole Markup Language (Google)
Geo Javascript Object Notation
SELECT ST_AsGml(the_geom) FROM ma_table;
<gml:MultiPolygon srsName="EPSG:4326">
<gml:polygonMember>
<gml:Polygon>
<gml:outerBoundaryIs>
<gml:LinearRing>
<gml:coordinates>55.5172314264874,-20.9036305014517 55.5172432651491,-20.9036035094091 55.5172445661464,20.9036001790097 55.5172458671463,-20.9035968483398 </gml:coordinates>
</gml:LinearRing>
</gml:outerBoundaryIs>
</gml:Polygon>
</gml:polygonMember>
</gml:MultiPolygon>"
31
PostGIS installation sous Windows
Installation de PostgreSQL :
32
PostGIS installation sous Windows
Installation de PostGIS avec PostgreSQL 8.3 :
33
PostGIS installation sous Linux (Ubuntu)
sudo apt-get install postgresql-9.1-postgis postgis
34
Psql : interface d'administration en ligne de commande
35
PgAdmin : interface graphique d'administration
36
QuantumGIS : affichage et manipulation de couches PostGIS
37
Quelques ressources en ligne utiles sur PostGIS
Site de la communauté des utilisateurs francophones :
http://www.postgis.fr/
Site officiel (en anglais) :
http://postgis.refractions.net/
Documentation (en anglais):
http://postgis.net/documentation/
Wiki PostGIS (en anglais):
http://trac.osgeo.org/postgis/wiki/UsersWikiMain
Référence des fonctions et opérateurs PostGIS 2 (en anglais):
http://postgis.net/docs/reference.html
PgAdmin (interface graphique d'administration PostgreSQL/PostGIS) :
http://www.pgadmin.org/?locale=fr_FR
QuantumGIS (SIG permettant l'affichage de couches PostGIS) :
http://www.qgis.org
38