Post Gis Intro IN Frenche

Introduction to PostGIS
Version 1.0
Mark Leslie, Paul Ramsey
January 28, 2017
Table des matires
Bienvenue
1.1 Conventions dcriture . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
1
Partie 1 : Introduction
2.1 Quest-ce quune base de donnes spatiales ? . . . . . . . . . . . . . . . . . . . . . . .
2.2 Quest-ce que PostGIS ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3
3
7
Partie 2 : Installation
11
Partie 3 : Crer une base de donnes spatiales

4.1 Le Dashboard et PgAdmin . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2 Crer une base de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3 Liste des fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
17
17
19
23
Partie 4 : Charger des donnes spatiales

5.1 Shapefile ? Quest-ce que cest ? . . . . . . . . . . . . . . . . . . .
5.2 SRID 26918 ? Quest que cest ? . . . . . . . . . . . . . . . . . . .
5.3 Les choses essayer : rendre spatiale une base de donnes existante
5.4 Les choses essayer : visualiser des donnes avec uDig . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
25
27
28
30
30
Partie 5 : A propos de nos donnes

6.1 nyc_census_blocks . . . . . .
6.2 nyc_neighborhoods . . . . . .
6.3 nyc_streets . . . . . . . . . .
6.4 nyc_subway_stations . . . . .
6.5 nyc_census_sociodata . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
31
31
33
34
34
35
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
Partie 6 : Requtes SQL simples

37
7.1 Requte de type SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 38
7.2 Liste de fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Partie 7 : Exercices simples de SQL

41
8.1 Liste des fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
Partie 8 : Les gometries

9.1 Introduction . . . . . . . . . . . . .
9.2 Les tables de mtadonnes . . . . . .
9.3 Rprsenter des objets du monde rel
9.4 Entr / Sortie des gomtries . . . . .
9.5 Liste des fonctions . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
43
43
44
46
52
55
10 Partie 9 : Exercices sur les gomtries

57
10.1 Exercices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
11 Partie 10 : Les relations spatiales
11.1 ST_Equals . . . . . . . . . . . . . . . . . . . . . . . .
11.2 ST_Intersects, ST_Disjoint, ST_Crosses et ST_Overlaps
11.3 ST_Touches . . . . . . . . . . . . . . . . . . . . . . .
11.4 ST_Within et ST_Contains . . . . . . . . . . . . . . . .
11.5 ST_Distance et ST_DWithin . . . . . . . . . . . . . . .
11.6 Liste des fonctions . . . . . . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
61
61
63
65
65
69
70
12 Partie 11 : Exercices sur les relations spatiales

73
12.1 Exercices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
13 Partie 12 : Les jointures spatiales
77
13.1 Jointure et regroupement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 77
13.2 Jointures avances . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
13.3 Liste de fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
14 Partie 13 : Exercices sur jointures spatiales
83
14.1 Exercices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
15 Partie 14 : Lindexation spatiale
15.1 Comment les index spatiaux fonctionnent
15.2 Requte avec seulement des index . . . .
15.3 Analyse . . . . . . . . . . . . . . . . . .
15.4 Nttoyage . . . . . . . . . . . . . . . . .
15.5 Liste des fonctions . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
87
88
89
90
91
91
16 Partie 15 : Projections des donnes

93
16.1 Comparaison de donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
16.2 Transformer les donnes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 94
16.3 Liste des fonctions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
17 Partie 16 : Exercices sur les projections
97
17.1 Exercices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
18 Partie 17 : Coordonnes gographiques
18.1 Utiliser le type Geography . . . . . . . . . .
18.2 Cration dune table stockant des gographies
18.3 Conversion de type . . . . . . . . . . . . . . .
18.4 Pourquoi (ne pas) utiliser les gographies . . .
18.5 Liste des fonctions . . . . . . . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
99
102
103
103
104
104
19 Partie 18 : Fonctions de construction de gomtries

107
19.1 ST_Centroid / ST_PointOnSurface . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
ii
19.2
19.3
19.4
19.5
ST_Buffer . . . .
ST_Intersection . .
ST_Union . . . .
Liste des fonctions
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
108
111
111
114
20 Partie 19 : Plus de jointures spatiales

20.1 Cration de la table de traage des recensements . . . . . . . . . . . . . . . . . . . . .
20.2 Polygones/Jointures de polygones . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
20.3 Jointures utilisant un large rayon de distance . . . . . . . . . . . . . . . . . . . . . . .
115
115
117
118
21 Partie 20 : Validit
21.1 Quest-ce que la validit ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
21.2 Dtecter la validit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
21.3 Rparer les invalides . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
121
121
122
123
22 Partie 21 : Paramtrer PostgreSQL pour le spatial

22.1 shared_buffers . . . . . . . . . . . . . . . . .
22.2 work_mem . . . . . . . . . . . . . . . . . . .
22.3 maintenance_work_mem . . . . . . . . . . . .
22.4 wal_buffers . . . . . . . . . . . . . . . . . . .
22.5 checkpoint_segments . . . . . . . . . . . . . .
22.6 random_page_cost . . . . . . . . . . . . . . .
22.7 seq_page_cost . . . . . . . . . . . . . . . . .
22.8 Recharger la configuration . . . . . . . . . . .
125
128
129
129
130
131
132
133
134
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
23 Partie 22 : galit
135
23.1 galit . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 135
24 Annexes A : Fonctions PostGIS
24.1 Constructeurs . . . . . . .
24.2 Sorties . . . . . . . . . .
24.3 Mesures . . . . . . . . . .
24.4 Relations . . . . . . . . .
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
141
141
141
141
142
25 Annexes B : Glossaire
143
26 Annexes C : License
145
Index
147
iii
iv
CHAPITRE 1
Bienvenue
1.1 Conventions dcriture

Cette section prsente les diffrentes conventions dcriture qui seront utilises dans ce document afin
den faciliter la lecture.
1.1.1 Indications
Les indications pour vous, lecteurs de ce document, seront notes en gras.
Par exemple :
Cliquez sur Suivant pour continuer.
1.1.2 Code
Les exemples de requtes SQL seront affichs de la manire suivante :
SELECT postgis_full_version();
Cet exemple peut tre saisi dans la fentre de requtage ou depuis linterface en ligne de commande.
1.1.3 Notes
Les notes sont utilises pour fournir une information utile mais non critique pour la comprhension
globale du sujet trait.
Note : Si vous navez pas mang une pomme aujourdhui, le docteur devrait se mettre en route.
1.1.4 Fonctions
Lorsque les noms de fonctions sont contenus dans une phrase, ils sont affichs en gras.
Par exemple :
Introduction to PostGIS, Version 1.0
ST_Touches(geometry A, geometry B) retourne vrai si un des contours de gomtrie

touche lautre contour de gomtrie
1.1.5 Fichiers, Tables et nom de colonne

Les noms de fichiers, les chemins, le noms de tables et les noms de colonnes seront affichs comme suit
Select the name column in the nyc_streets table.
1.1.6 Menus et formulaires

Les menus et les lments de formulaire comme les champs ou les botes cocher ainsi que les autre
objets sont affichs en italique.
Par exemple :
Cliquez sur Fichier > Nouveau. Cochez la case qui contient Confirmer.
1.1.7 Organisation
Les diffrentes sections de ce document permettent dvoluer progressivement. Chaque section suppose
que vous ayez termin et compris les sections prcdentes.
Certaines sections fournissent des exemples fonctionnels ainsi que des exercices. Dans certains cas, il y
a aussi des sections Les choses essayer pour les curieux. Ces tches contiennent des problmes plus
complexes que dans les exercices.
Chapitre 1. Bienvenue
CHAPITRE 2
Partie 1 : Introduction
2.1 Quest-ce quune base de donnes spatiales ?

PostGIS est une base de donnes spatiale. Oracle Spatial et SQL Server 2008 sont aussi des bases de
donnes spatiales. Mais quest-ce que cela signifie ? Quest-ce qui diffrencie un serveur de base de
donnes spatiales dun serveur de base de donnes non spatiale ?
La rponse courte, est ...
Les bases de donnes spatiales permettent le stockage et la manipulation des objets spatiaux
comme les autres objets de la base de donnes.
Ce qui suit prsente brivement lvolution des bases de donnes spatiales, puis les liens entre les donnes spatiales et la base de donnes (types de donnes, index et fonctions).
1. Types de donnes spatiales fait rfrence aux gomtries de type point, ligne et polygone ;
2. Lindexation spatiale est utilise pour amliorer les performances dexcution des oprations
spatiales ;
3. Les fonctions spatiales, au sens SQL, sont utilises pour accder des proprits ou des relations
spatiales.
Utiliss de manire combine, les types de donnes spatiales, les index et les fonctions fournissent une
structure flexible pour optimiser les performances et les analyses.
2.1.1 Au commencement
Dans les premires implmentations SIG, toutes les donnes spatiales taient stockes sous la forme
de fichiers plats et certaines applications SIG spcifiques taient ncessaires pour les interprter et les
manipuler. Ces outils de gestion de premire gnration avaient t conus pour rpondre aux besoins
des utilisateurs pour lesquels toutes les donnes taient localises au sein de leur agence. Ces outils
propritaires taient des systmes specifiquement crs pour grer les donnes spatiales.
La seconde gnration des systmes de gestion de donnes spatiales stockait certaines donnes dans
une base de donnes relationelle (habituellement les attributs ou autres parties non spatiales) mais ne
fournissaient pas encore la flexibilit offerte par une intgration complte des donnes spatiales.
Effectivement, les bases de donnes spatiales sont nes lorsque les gens ont commenc considrer
les objet spatiaux comme les autres objets dune base de donnes .
Les bases de donnes spatiales intgrent les donnes spatiales sous forme dobjets de la base de donnes
relationnelle. Le changement opr passe dune vision centre sur le SIG une vision centre sur les
bases de donnes.
Note : Un systme de gestion de base de donnes peut tre utilis dans dautres cadres que celui
des SIG. Les bases de donnes spatiales sont utilises dans divers domaines : lanatomie humaine, les
circuits intgrs de grandes envergures, les structures molculaires, les champs electro-magntiques et
bien dautres encore.
2.1.2 Les types de donnes spatiales

Une base de donnes classique propose par exemple les types chanes de caractres et date. Une base
de donnes spatiales ajoute les types de donnes (spatiales) pour reprsenter les entits gographiques.
Ces types de donnes spatiales permettent daccder des proprits de lentit gographique comme
ses contours ou sa dimension. Pour bien des aspects, les types de donnes spatiales peuvent tre vus
simplement comme des formes.
Chapitre 2. Partie 1 : Introduction
Les types de donnes spatiales sont organiss par une hirarchie de type. Chaque sous-type hrite de la
structure (les attributs) et du comportement (les mthodes et fonctions) de son type suprieur dans la
hierarchie.
2.1.3 Index spatiaux et tendue

Une base de donnes ordinaire fournit des mthodes daccs connues sous le nom dindex pour
permettre un accs efficace et non squentiel un sous ensemble de donnes. Lindexation des types
non gographiques (nombre, chanes de caractres, dates) est habituellement faite laide des index de
type arbres binaires. Un arbre binaire est un partitionnement des donnes utilisant lordre naturel pour
stocker les donnes hirarchiquement.
Lordre naturel des nombres, des chanes de caractres et des dates est assez simple dterminer
chaque valeur est infrieure, plus grande ou gale toutes les autres valeurs. Mais, tant donn que les
polygones peuvent se chevaucher, peuvent tre contenus dans un autre et sont reprsents par un tableau
en deux dimensions (ou plus), un arbre binaire ne convient pas pour indexer les valeurs. Les vraies bases
de donnes spatiales fournissent un index spatial qui rpond plutt la question : quel objet se trouve
dans une tendue spcifique ?
Une tendue correspond au rectangle de plus petite taille capable de contenir un objet gographique.
2.1. Quest-ce quune base de donnes spatiales ?
Les tendues sont utilises car rpondre la question : est-ce que A se trouve lintrieur de B ? est
une opration coteuse pour les polygones mais rapide dans le cas ou ce sont des rectangles. Mme des
polygones et des lignes complexes peuvent tre reprsents par une simple tendue.
Les index spatiaux doivent raliser leur ordonnancement rapidement afin dtre utiles. Donc au lieu de
fournir des rsultats exacts, comme le font les arbres binaires, les index spatiaux fournissent des rsultats
approximatifs. La question quelles lignes sont lintrieur de ce polygone sera interprte par un index
spatial comme : quelles lignes ont une tendue qui est contenue dans ltendue de ce polygone ?
Les incrments spatiaux rels mis en application par diverses bases de donnes varient considrablement.
Les index spatiaux actuellement utiliss par les diffrents systmes de gestion de bases de donnes
varient aussi considrablement. Limplmentation la plus commune est larbre R (utilis dans PostGIS),
mais il existe aussi des implmentations de type Quadtrees, et des index bass sur une grille.
2.1.4 Les fonctions spatiales

Pour manipuler les donnes lors dune requte, une base de donnes classique fournit des fonctions
comme la concatnation de chanes de caractres, le calcul de la clef md5 dune chane, la ralisation
doprations mathmatiques sur les nombres ou lextraction dinformations spcifiques sur une date.
Une base de donnes spatiales fournit un ensemble complet de fonctions pour analyser les composants
gographiques, dterminer les relations spatiales et manipuler les objets gographiques. Ces fonctions
spatiales sont utilises comme des pices de Lego pour de nombreux projets SIG.
La majorit des fonctions spatiales peuvent tre regroupes dans lune des cinq catgories suivantes :
1. Conversion : fonctions qui convertissent les donnes gographiques dans un format externe.
2. Gestion : fonctions qui permettent de grer les informations relatives aux tables spatiales et ladministration de PostGIS.
3. Rcupration : fonctions qui permettent de rcuprer les proprits et les mesures dune
gomtrie.
4. Comparaison : fonctions qui permettent de comparer deux gomtries en respectant leurs relations spatiales.
5. Contruction : fonctions qui permettent de construire de nouvelles gomtries partir dautres.
La liste des fonctions possibles est trs vaste, mais un ensemble commun lensemble des implmentations est dfini par la spcification term :OGC SFSQL. Cet ensemble commun (avec dautres fonctions
supplmentaires) est implment dans PostGIS.
2.2 Quest-ce que PostGIS ?

PostGIS confre au systme de gestion de base de donnes PostgreSQL le statut de base de donnes
spatiales en ajoutant les trois supports suivants : les types de donnes spatiales, les index et les fonctions.
tant donn quil est bas sur PostgreSQL, PostGIS bnficie automatiquement des capacits orientes
entreprise ainsi que le respect des standards de cette implmentation.
2.2.1 Mais quest-ce que PostgreSQL ?

PostgreSQL est un puissant systme de gestion de donnes relationnel objets (SGBDRO). Il a t
publi sous la licence de style BSD et est donc un logiciel libre. Comme avec beaucoup de logiciels
libres, PostgreSQL nest pas contrl par une socit unique mais par une communaut de dveloppeurs
et de socits qui le dveloppe.
PostgreSQL a t conu depuis le dbut en conservant lesprit quil serait potentiellement ncessaire
de ltendre laide dextensions particulires la possibilit dajouter de nouveaux types, des nouvelles fonctions et des mthodes daccs chaud. Grce cela, une extension de PostgreSQL peut tre
dveloppe par une quipe de dveloppement indpendante, bien que le lien soit trs fortement li au
coeur de la base de donnes PostgreSQL.
Pourquoi choisir PostgreSQL ?
Une question que se posent souvent les gens dja familiariss avec les bases de donnes libres est :
Pourquoi PostGIS na pas t bas sur MySQL ?
PostgreSQL a :
prouv sa fiabilit et son respect de lintgrit des donnes (proprits ACID)
un support soigneux des standard SQL (respecte la norme SQL92)
un support pour le dveloppement dextensions et de nouvelles fonctions
un modle de dveloppement communautaire
pas de limite sur la taille des colonne (les tuples peuvent tre TOASTs) pour supporter des objets
gographiques
un structure dindex gnrique (GiST) permettant lindexation laide darbres R
une facilit dajout de fonctions personalises
Tout ceci combin, PostgreSQL permet un cheminement simple du dveloppement ncessaire lajout
des types spatiaux. Dans le monde propritaire, seul Illustra (maintenant Informix Universal Server)
permet une extension aussi simple. Ceci nest pas une concidence, Illustra est une version propritaire
modifie du code original de PostgreSQL publi dans les annes 1980.
Puisque le cheminement du dveloppement ncessaire lajout de types PostgreSQL est direct, il
semblait naturel de commencer par l. Lorsque MySQL a publi des types de donnes spatiaux de base
dans sa version 4.1, lquipe de PostGIS a jet un coup doeil dans leur code source et cela a confirm le
choix initial dutiliser PostgreSQL. Puisque les objets gographiques de MySQL doivent tre considrs
2.2. Quest-ce que PostGIS ?
comme un cas particulier de chanes de caractres, le code de MySQL a t diffus dans lintgralit du
code de base. Le dveloppement de PostGIS version 0.1 a pris un mois. Raliser un projet MyGIS 0.1
aurait pris beaucoup plus de temps, cest sans doute pourquoi il na jamais vu le jour.
2.2.2 Pourquoi pas des fichiers Shapefile ?

Les fichiers shapefile (et les autres formats) ont t la manire standard de stocker et dinteragir avec
les donnes spatiales depuis lorigine des SIG. Nanmoins, ces fichiers plats ont les inconvnients
suivants :
Les fichier au formats SIG requirent un logiciel spcifique pour les lire et les crire. Le langage
SQL est une abstraction de laccs alatoire aux donnes et leur analyse. Sans cette abstraction, vous
devrez dvelopper laccs et lanalyse par vos propre moyens.
Laccs concurrent aux donnes peut parfois entraner un stockage de donnes corrompues.
Alors quil est possible dcrire du code supplmentaire afin de garantir la cohrence des donnes,
une fois ce problme solutionn et celui de la performance associe, vous aurez re-crit la partie la
plus importante dun systme de base de donnes. Pourquoi ne pas simplement utiliser une base de
donnes standard dans ce cas ?
Les questions compliques ncessitent des logiciels compliqus pour y rpondre. Les question
intressantes et compliques (jointures spatiales, aggrgations, etc) qui sont exprimables en une ligne
de SQL grce la base de donnes, ncessitent une centaine de lignes de code spcifiques pour y
rpondre dans le cas de fichiers.
La plupart des utilisateurs de PostGIS ont mis en place des systmes o diverses applications sont susceptibles daccder aux donnes, et donc davoir les mthodes daccs SQL standard, qui simplifient le
dploiement et le dveloppement. Certains utilisateurs travaillent avec de grands jeux de donnes sous
forme de fichiers, qui peuvent tre segments en plusieurs fichiers, mais dans une base de donnes ces
donnes peuvent tre stockes dans une seule grande table.
En rsum, la combinaison du support de laccs concurrent, des requtes complexes spcifiques et de
la performance sur de grands jeux de donnes diffrencient les bases de donnes spatiales des systmes
utilisant des fichiers.
2.2.3 Un bref historique de PostGIS

En mai 2001, la socit Refractions Research publie la premire version de PostGIS. PostGIS 0.1 fournissait les objets, les index et des fonctions utiles. Le rsultat tait une base de donnes permettant le
stockage et laccs mais pas encore lanalyse.
Comme le nombre de fonctions augmentait, le besoin dun principe dorganisation devint clair. La spcification Simple Features for SQL (SFSQL) publie par lOpen Geospatial Consortium fournit une
telle structure avec des indications pour le nommage des fonctions et les pr-requis.
Avec le support dans PostGIS de simples fonctions danalyses et de jointures spatiales, Mapserver devint
la premire application externe permettant de visualiser les donnes de la base de donnes.
Au cours de ces dernires annes, le nombre de fonctions fournies par PostGIS grandissait, mais leur
puissance restait limite. La plupart des fonctions intressantes (ex : ST_Intersects(), ST_Buffer(),
ST_Union()) taient difficiles implmenter. Les crire en repartant du dbut promettait des annes
de travail.
Heureusement un second projet, nomm Geometry Engine, Open Source ou GEOS vit le jour. Cette
librairie fournit lensemble des algorithmes ncessaires limplmentation de la spcification SFSQL .
En se liant GEOS, PostGIS fournit alors le support complet de la SFSQL depuis la version 0.8.
Alors que les capacits de PostGIS grandissaient, un autre problme fit surface : la reprsentation utilise
pour stocker les gomtries ntait pas assez efficace. Pour de petits objets comme les points ou de
courtes lignes, les mtadonnes dans la reprsentation occupaient plus de 300% supplmentaires. Pour
des raisons de performances, il fut ncessaire de faire faire un rgime la reprsentation. En rduisant
lentte des mtadonnes et les dimensions requises, lespace supplmentaire fut rduit drastiquement.
Dans PostGIS 1.0, cette nouvelle reprsentation plus rapide et plus lgre devint la reprsentation par
dfaut.
Les mises jour rcentes de PostGIS ont permis dtendre la compatibilit avec les standards, dajouter
les gomtries courbes et les signatures de fonctions spcifies dans la norme ISO SQL/MM. Dans
un soucis de performance, PostGIS 1.4 a aussi augment considrablement la rapidit dexcution des
fonctions de tests sur les gomtries.
2.2.4 Qui utilise PostGIS ?

Pour une liste complte des cas dutilisation, consultez la page web : Cas dutilisations de PostGIS (en
anglais).
Institut Gographique National, France
LIGN utilise PostGIS pour stocker des cartes topographiques de grande rsolution de la France : la
BDUni. La BDUni a plus de 100 millions dentits, et est maintenue par une quipe de 100 personnes
qui vrifie les observations et ajoute quotidiennement de nouvelles donnes la base. Linstallation de
lIGN utilise le systme transactionnel de la base de donnes pour assurer la consistance durant les
phases de mises jour et utilise un serveur de warm-standby par transfert de journaux afin de conserver
un tat cohrent en cas de dfaillance du systme.
GlobeXplorer
GlobeXplorer est un service web fournissant un accs en ligne une imagerie satellite et photos ariennes de plusieurs petabytes. GlobeXplorer utilise PostGIS pour grer les mtadonnes associes avec
le catalogue dimages. Les requtes pour accder aux images recherchent dabord dans le catalogue
PostGIS pour rcuprer la localisation des images demandes, puis rcuprent ces images et les retournent au client. Lors du proccessus de mise en place de leur systme, GlobeXplorer a essay dautres
systmes de base de donnes spatiales mais a conserv PostGIS cause de la combinaison du prix et de
la performance quil offre.
2.2.5 Quest-ce quune application qui supporte PostGIS ?

PostGIS est devenu une base de donnes spatiale communment utilise, et le nombre dapplications
tierces qui supportent le stockage ou la rcupration des donnes na cess daugmenter. Les application qui supportent PostGIS contiennent la fois des applications libres et des application propritaires
tournant sur un serveur ou localement depuis votre bureau.
La table suivante propose une liste des logiciels qui tirent profit de PostGIS :
2.2. Quest-ce que PostGIS ?
Libre/Gratuit
Ferm/Propritaire
Chargement/Extraction
Shp2Pgsql
ogr2ogr
Dxf2PostGIS
Bas sur le web
Mapserver
GeoServer (Java-based WFS / WMS -server
)
SharpMap SDK - for ASP.NET 2.0
MapGuide Open Source (using FDO)
Logiciels bureautiques
uDig
QGIS
mezoGIS
OpenJUMP
OpenEV
SharpMap SDK for Microsoft.NET 2.0
ZigGIS for ArcGIS/ArcObjects.NET
GvSIG
GRASS
Chargement/Extraction
Safe FME Desktop Translator/Converter
Bas sur le web
Ionic Red Spider (now ERDAS)
Cadcorp GeognoSIS
Iwan Mapserver
MapDotNet Server
MapGuide Enterprise (using FDO)
ESRI ArcGIS Server 9.3+
Logiciels bureautiques
Cadcorp SIS
Microimages TNTmips GIS
ESRI ArcGIS 9.3+
Manifold
GeoConcept
MapInfo (v10)
AutoCAD Map 3D (using FDO)
10
CHAPITRE 3
Partie 2 : Installation
Nous utiliserons OpenGeo Suite comme application dinstallation, car celle-ci contient PostGIS/PostgreSQL dans un seul outil dinstallation pour Windows, Apple OS/X et Linux. La suite contient
aussi GeoServer, OpenLayers et dautres outils de visualisations sur le web.
Note : Si vous souhaitez installer simplement PostgreSQL, cela peut se faire en tlchargeant directement le code source ou les binaires de PostgreSQL sur le site du projet http ://postgresql.org/download/.
Aprs avoir install PostgreSQL, utilisez loutil StackBuilder pour ajouter lextension PostGIS votre
installation.
Note : Les indications prcises de ce document sont propre Windows, mais linstallation sous OS/X
est largement similaire. Une fois la Suite installe, les instructions relatives au systme dexploitation
devraient tre identiques.
1. Dans le rpertoire postgisintro\software\ vous trouverez linstalleur de OpenGeo Suite

nomm : opengeosuite-2.4.3.exe (sur OS/X, opengeosuite-2.4.3.dmg). Double
cliquez sur cet excutable pour le lancer.
2. Apprciez le message de courtoisie dOpenGeo, puis cliquez sur Next.
11
3. OpenGeo Suite est publie sous licence GPL, ce qui est prcis dans la fentre de license. Cliquez
sur I Agree.
12
Chapitre 3. Partie 2 : Installation
4. Le rpertoire o OpenGeo Suite sera install est gnralement le rpertoire C:\Program

Files\. Les donnes seront places dans le rpertoire personnel de votre utilisateur, dans le
rpertoire .opengeo. Cliquez sur Next.
5. Linstalleur crera un certain nombre de raccourcis dans le rpertoire OpenGeo du menu Dmarrer.
Cliquez sur Next.
13
6. Tous les composants de la Suite sont obligatoires ce niveau. Cliquez sur Next.
7. Prt installer ! Cliquez sur Install.

14
8. Le processus dinstallation prendra quelques minutes.
9. Lorsque linstallation est termine, lancez le Dashboard pour commencer la partie suivante de ces
15
travaux pratiques ! Cliquez sur Finish.
16
CHAPITRE 4
Partie 3 : Crer une base de donnes

spatiales
4.1 Le Dashboard et PgAdmin

Le Dashboard est une application centralisant les accs aux diffrentes parties de lopenGeo Suite.
Lorsque vous dmarrez le dashboard pour la premire fois, il vous fournit une indication quand au mot
de passe par dfaut pour accder GeoServer.
Note : La base de donnes PostGIS a t installe sans la moindre restriction daccs pour les utilisateurs
locaux (les utilisateurs se connectant sur la mme machine que celle faisant tourner le serveur de base
de donnes). Cela signifie quil acceptera tout les mots de passe que vous fournirez. Si vous devez vous
17
connecter depuis un ordinateur distant, le mot de passe pour lutilisateur postgres a utiliser est :
postgres.
Pour ces travaux pratiques, nous nutilserons que les parties de la section PostGIS du Dashboard.
1. Premirement, nous devons dmarrer le serveur de base de donnes PostGIS. Cliquez sur le bouton
vert Start en haut droite de la fentre du Dashboard.
2. La premire fois que la Suite se dmarre, elle initialise un espace de donnes et met en place des
modles de bases de donnes. Ceci peut prendre quelques minutes. Une fois la Suite lance, vous
pouvez cliquer sur loption Manage dans le composant PostGIS pour lancer loutil pgAdmin.
Note : PostgreSQL dispose de nombreux outils dadministration diffrents. Le premier

est psql un outil en ligne de commande permettant de saisir des requtes SQL. Un autre
outil dadministation populaire est loutil graphique libre et gratuit pgAdmin. Toutes
les requtes excutes depuis pgAdmin peuvent aussi tre utilises depuis la ligne de
commande avec psql.
3. Si cest la premire fois que vous lancez pgAdmin, vous devriez avoir une entre du type PostGIS
(localhost :54321) dj configure dans pgAdmin. Double cliquez sur cet lment, et entrez le
mot de passe de votre choix pour vous connecter au serveur.
18
Chapitre 4. Partie 3 : Crer une base de donnes spatiales
Note : Si vous aviez dj une installation pgAdmin sur votre ordinateur, vous naurez
pas lentre (localhost :54321). Vous devrez donc crer une nouvelle connexion. Allez
dans File > Add Server, puis enregistrez un nouveau serveur pour localhost avec le
port 54321 (notez que numro de port nest pas standard) afin de vous connecter au
serveur PostGIS install laide de lOpenGeo Suite.
4.2 Crer une base de donnes

PostgreSQL fournit ce que lon appelle des modles de bases de donnes qui peuvent tre utiliss lors de
la cration dune nouvelle base. Cette nouvelle base contiendra alors une copie de tout ce qui est prsent
dans le modle. Lorsque vous installez PostGIS, une base de donnes appele template_postgis a
t cre. Si nous utilisons template_postgis comme modle lors de la cration de notre nouvelle
base, la nouvelle base sera une base de donnes spatiales.
1. Ouvrez larbre des bases de donnes et regardez quelles sont les bases de donnes disponibles.
La base postgres est la base de lutilisateur (par dfaut lutilisateur postgres, donc pas trs
intressante pour nous). La base template_postgis est celle que nous utiliserons pour crer
des bases de donnes spatiales.
2. Cliquez avec le clic droit sur llment Databases et slectionnez New Database.
4.2. Crer une base de donnes
19
Note :
Si vous recevez un message derreur indiquant que la base de donnes
(template_postgis) est utilise par dautre utilisateurs, cela signifie que vous lavez activ
par inadvertance. Utilisez alors le clic droit sur llment PostGIS (localhost:54321)
puis slectionnez Disconnect. Double cliquez sur le mme lment pour vous reconnecter et
essayez nouveau.
3. Remplissez le formulaire New Database puis cliquez sur OK.
Name
nyc
Owner
postgres
Encoding UTF8
Template template_postgis
20
4. Slectionnez la nouvelle base de donnes nyc et ouvrez-la pour consulter son contenu. Vous
verrez le schma public, et sous cela un ensemble de tables de mtadonnes spcifiques
PostGIS geometry_columns et spatial_ref_sys.
4.2. Crer une base de donnes
21
5. Cliquez sur le bouton SQL query comme prsent ci-dessous (ou allez dans Tools > Query Tool).
6. Saisissez la requte suivante dans le champ prvu cet effet :

Note : Cest notre premire requte SQL. postgis_full_version() est une fonction dadministration qui renvoie le numro de version et les options de configuration utilises lors de la
compilation.
7. Cliquez sur le bouton Play dans la barre doutils (ou utilisez la touche F5) pour excuter la
requte. La requte retournera la chane de caractres suivante, confirmant que PostGIS est correctement activ dans la base de donnes.
22
Vous venez de crer une base de donnes PostGIS avec succs !
4.3 Liste des fonctions

PostGIS_Full_Version : Retourne les informations compltes relatives la version et aux options de
compilation de PostGIS.
4.3. Liste des fonctions
23
24
CHAPITRE 5
Partie 4 : Charger des donnes

spatiales
Support par une grande varit de librairies et dapplications, PostGIS fournit de nombreux outils pour
charger des donnes. Cette partie traitera uniquement du chargement basique de donnes, cest dire le
chargement de fichiers Shapefile (.shp) en utilisant loutil ddi de PostGIS.
1. Premirement, retournez sur le Dashboard et cliquez sur le lien Import shapefiles de la section
PostGIS. Linterface dimport de donnes Shapefile pgShapeLoader se lance.
25
2. Ensuite, ouvrez le navigateur de fichier Shape File puis dans le rpertoire file :\postgisintro\data
slectionnez le fichier nyc_census_blocks.shp.
3. Saisissez les dtails de la section connexion PostGIS et cliquez sur le bouton Test Connection....
Username
postgres
Password
postgres
Server Host localhost 54321
Database
nyc
Note : Affecter le numro de port 54321 est trs important ! Le serveur PostGIS dOpenGeo
utilise ce port et non le port par dfaut (5432).
1. Saisissez les dtails de la section Configuration.
26
Chapitre 5. Partie 4 : Charger des donnes spatiales
Destination Schema
SRID
Destination Table
Geometry Column
public
26918
nyc_census_blocks
the_geom
2. Cliquez sur le bouton Options et slectionnez Load data using COPY rather than INSERT. Ce
qui implique que le chargement des donnes sera plus rapide.
3. Pour finir, cliquez sur le bouton Import et regardez limportation sexcuter. Cela peut prendre
plusieurs minutes pour charger, mais ce fichier est le plus gros que nous aurons charger.
4. Reptez la mthode afin dimporter les autres donnes prsentes dans le rpertoire data. Hormis
le nom du fichier et le nom de la table de sortie, les autres paramtres de pgShapeLoader devrait
rester les mmes :
nyc_streets.shp
nyc_neighborhoods.shp
nyc_subway_stations.shp
5. Lorsque tous les fichiers sont chargs, cliquez sur le bouton Refresh de pgAdmin pour mettre
jour larbre affich. Vous devriez voir vos quatre nouvellles tables affiches dans la section Tables
de larbre.
5.1 Shapefile ? Quest-ce que cest ?

Il est possible que vous vous demandiez Quest-ce que cest ce shapefile ? On utilise communment
le terme Shapefile pour parler dun ensemble de fichiers dextension .shp, .shx, .dbf, ou autre
ayant un nom commun (ex : nyc_census_blocks). Le fichier Shapefile est en ralit le fichier dextension
.shp, mais ce fichier seul nest pas complet sans ses fichiers associs.
Fichiers obligatoires :
5.1. Shapefile ? Quest-ce que cest ?
27
.shp les formes ; les entits gographiques elle-mmes

.shx lindex de formes ; un index bas sur les positions des entits gographiques
.dbf les attributs ; les donnes attributaires associes chaque forme, au format dBase III
Les fichiers optionnels possibles :
.prj la projection ; le systme de coordonnes et linformation de projection, un fichier texte
dcrivant la projection utilisant le format texte bien connu (WKT)
Afin dutiliser un fichier Shapefile dans PostGIS, vous devez le convertir en une srie de requtes SQL.
En utilisant pgShapeLoader, un Shapefile est converti en une table que PostgreSQL peut comprendre.
5.2 SRID 26918 ? Quest que cest ?

La plupart des paramtres de limportation de donnes sont explicites mais mme les professionnels du
SIG peuvent rencontrer des difficult propos du SRID.
SRID signifie IDentifiant de Rfrence Spatiale. Il dfinit tous les paramtres de nos donnes, telles
les coordonnes gographiques et la projection. Un SRID est pratique car il encapsule sous la forme dun
nombre toutes les informations propos de la projection de la carte (ce qui peut tre trs compliqu).
Vou pouvez consulter la dfinition de la projection de la carte en consultant la base de donnes en ligne
suivante :
http ://spatialreference.org/ref/epsg/26918/
ou directement depuis PostGIS en interrogeant la table spatial_ref_sys.
SELECT srtext FROM spatial_ref_sys WHERE srid = 26918;
Note : La table spatial_ref_sys de PostGIS est une table standard OGC qui dfinit tous les
systmes de rfrence spatiale connus par la base de donnes. Les donnes livres avec PostGIS, contiennent 3000 systmes de rfrence spatiale et prcisent les informations ncessaires la transformation
ou la reprojection.
Dans les deux cas, vous obtiendrez une reprsentation du systme de rfrence spatiale 26918 (affiche
sur plusieurs lignes ici pour plus de clart).
PROJCS["NAD83 / UTM zone 18N",
GEOGCS["NAD83",
DATUM["North_American_Datum_1983",
SPHEROID["GRS 1980",6378137,298.257222101,AUTHORITY["EPSG","7019"]],
AUTHORITY["EPSG","6269"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],
UNIT["metre",1,AUTHORITY["EPSG","9001"]],
PROJECTION["Transverse_Mercator"],
PARAMETER["latitude_of_origin",0],
PARAMETER["central_meridian",-75],
PARAMETER["scale_factor",0.9996],
PARAMETER["false_easting",500000],
PARAMETER["false_northing",0],
AUTHORITY["EPSG","26918"],
AXIS["Easting",EAST],
AXIS["Northing",NORTH]]
28
Si vous ouvrez le fichier nyc_neighborhoods.prj du rpertoire data, vous verrez la mme dfinition.
Un problme auquel se confronte la plupart des dbutants en PostGIS est de savoir quel SRID il doit
utiliser pour ses donnes. Tout ce quils ont cest un fichier .prj. Mais comment un humain peut-il
reconnaitre le numro de SRID correct en lisant le contenu du fichier .prj ?
La rponse simple est dutiliser un ordinateur. Copiez le contenu du fichier .prj dans le formulaire du
site http ://prj2epsg.org. Cela vous donnera le nombre (ou la liste de nombres) qui correspond le plus
votre dfinition de projection. Il ny a pas de nombre pour toutes les projections de cartes existantes
dans le monde, mais les plus courants sont disponibles dans la base de donnes de prj2epsg.
Les donnes que vous recevez des agences locales de lEtat - comme la ville de New York - utilisent la
plupart du temps des projections locales notes state plane ou UTM. Dans notre cas, la projection
est Universal Transverse Mercator (UTM) Zone 18 North soit EPSG :26918.
5.2. SRID 26918 ? Quest que cest ?
29
5.3 Les choses essayer : rendre spatiale une base de donnes

existante
Vous avez dj vu comment crer une base de donnes en utilisant le modle postgis_template
depuis pgAdmin. Nanmoins, lorsque vous install depuis les sources ou que vous ajoutez le module
PostGIS une base existante, il nest pas toujours appropri de crer une nouvelle base de donnes en
utilisant le modle PostGIS.
Votre tche consiste dans cette section crer une base de donnes et ajouter les types et les fonctions
PostGIS ensuite. Les script SQL ncessaires - postgis.sql et spatial_ref_sys.sql - se trouvent dans le rpertoire contrib de votre installation de PostgreSQL. Pour vous guider, vous pouvez
consulter la documentation PostGIS expliquant comment installer PostGIS 1 .
Note : Noubliez pas saisir le nom de lutilisateur et le numro de port lorsque vous crez une base de
donnes en ligne de commande.
5.4 Les choses essayer : visualiser des donnes avec uDig

uDig, (User-friendly Desktop Internet GIS) est un outil bureautique de visualisation/dition SIG permettant de visualiser rapidement ses donnes. Vous pouvez visualiser un grand nombre de formats diffrents
dont les Shapefiles et les bases de donnes PostGIS. Son interface graphique vous permet dexplorer vos
donnes facilement mais aussi de les tester et les styler rapidement.
Utilisez cette application pour vous connecter votre base de donnes PostGIS. Lapplication est contenue dans le rpertoire software.
1. Chapter 2.5. Installation PostGIS Documentation. Mai 2010 <http ://postgis.org/documentation/manual1.5/ch02.html#id2786223>
30
CHAPITRE 6
Partie 5 : A propos de nos donnes
Les donnes utilises dans ces travaux pratiques sont quatre shapefiles de la ville de New York, et une
table attributaire des variables socio-dmographiques de la ville. Nous les avons chargs sous forme de
tables PostGIS et nous ajouterons les donnes socio-dmographiques plus tard.
Cette partie fournit le nombre denregistrements et les attributs de chacun de nos ensembles de donnes.
Ces valeurs attributaires et les relations sont essentielles pour nos futures analyses.
Pour visualiser la nature de vos tables depuis pgAdmin, cliquez avec le bouton droit sur une table et
slectionnez Properties. Vous trouverez un rsum des proprits de la table, incluant la liste des attributs dune table dans longlet Columns.
6.1 nyc_census_blocks
Un bloc recens est la plus petite entit gographique pour laquelle un recensement est rapport. Toutes
les couches reprsentant les niveaux suprieurs (rgions, zones de mtro, comts) peuvent tre contruites
partir de ces blocs. Nous avons attach des donnes dmographiques aux blocs.
Nombre denregistrements : 36592
blkid
Un code 15 chiffres qui permet didentifier de manire unique chaque bloc block.
Eg : 360050001009000
popn_total Nombre total de personnes dans le bloc
popn_white Nombre de personnes se dclarant comme de couleur blanche
popn_black Nombre de personnes se dclarant comme de couleur noire
popn_nativ Nombre de personnes se dclarant comme natif dAmrique du nord
popn_asian Nombre de personnes se dclarant comme asiatique
popn_other Nombre de personnes se dclarant comme faisant partie dune autre catgorie
hous_total Nombre de pices dans le bloc
hous_own Nombre de propritaires occupant le bloc
hous_rent Nombre de locataires occupant le bloc
boronNom du quartier (Manhattan, The Bronx, Brooklyn, Staten Island, Queens)
ame
the_geom Polygone reprsentant les contours dun bloc
Note : Pour disposer des donnes dun recensement dans votre SIG, vous avez besoin de joindre deux
informations : Les donnes socio-dmographiques et les limites gographiques des blocs/quartiers. Il
31
F IGURE 6.1 Pourcentage de la population qui est de couleur noire
32
Chapitre 6. Partie 5 : A propos de nos donnes
existe plusieurs moyen de se les procurer, dans notre cas, elles ont t rcupres sur le site Internet du
Census Bureaus American FactFinder.
6.2 nyc_neighborhoods
Les quartiers de New York
name
boroname
the_geom
Nom du quartier
Nom de la section dans New York (Manhattan, The Bronx, Brooklyn, Staten Island,
Queens)
Limite polygonale du quartier
F IGURE 6.2 Les quartiers de New York
6.2. nyc_neighborhoods
33
6.3 nyc_streets
Les rues de New York
name
oneway
type
the_geom
Nom de la rue
Est-ce que la rue est sens unique ? yes = yes, = no
Type de voie (Cf : primary, secondary, residential, motorway)
Ligne du centre de la rue.
F IGURE 6.3 Les rues de New York (les rues principales apparaissent en rouge)
6.4 nyc_subway_stations
Les stations de mtro de New York
34

name
borough
routes
transfers
express
the_geom
Nom de la station
Nom de la section dans New York (Manhattan, The Bronx, Brooklyn, Staten Island, Queens)
Lignes de mtro passant par cette station
Lignes de mtro accessibles depuis cette station
Stations ou le train express sarrte, express = yes, = no
Localisation ponctuelle de la station
F IGURE 6.4 Localisation ponctuelle des stations de mtro de New York
6.5 nyc_census_sociodata
Donnes socio-dmographiques de la ville de New York
6.5. nyc_census_sociodata
35
Note : La donne nyc_census_sociodata est une table attributaire. Nous devrons nous connecter
aux gomtries correspondant la zone du recensement avant de conduire toute analyse spatiale .
tractid
transit_total
transit_public
transit_private
transit_other
transit_time_mins
Un code 11 chiffre qui identifie chaque secteur de recensement. tract. Eg :

36005000100
Nombre de travailleurs dans le secteur
Nombre de travailleurs dans le secteur utilisant les transports en commun
Nombre de travailleurs dans le secteur utilisant un vhicule priv
Nombre de travailleurs dans le secteur utilisant un autre moyen de transport
Nombre total de minutes passes dans les transports par lensemble des
travailleurs du secteur (minutes)
Nombre de familles dans le secteur
Revenu mdian par famille du secteur (dollars)
family_count
family_income_median
famRevenu total de toutes les familles du secteur (dollars)
ily_income_aggregate
edu_total
Nombre de personnes ayant un parcours scolaire
edu_no_highschool_dipl
Nombre de personnes nayant pas de diplme dducation secondaire
edu_highschool_dipl Nombre de personnes ayant un diplme dducation secondaire
edu_college_dipl
Nombre de personnes ayant un diplme de lyce
edu_graduate_dipl Nombre de personnes ayant un diplme de collge
36
CHAPITRE 7
Partie 6 : Requtes SQL simples
SQL, pour Structured Query Language, dfinit la manire dimporter et dinterroger des donnes dans
une base. Vous avez dj rdig du SQL lorsque nous avons cr notre premire base de donnes.
Rappel :
Maintenant que nous avons charg des donnes dans notre base, essayons dutiliser SQL pour les interroger. Par exemple,
Quels sont les noms des quartiers de la ville de New York ?
Ouvrez une fentre SQL depuis pgAdmin en cliquant sur le bouton SQL
Puis saisissez la requte suivante dans la fentre

SELECT name FROM nyc_neighborhoods;
et cliquez sur le bouton Execute Query (le triangle vert).
La requte sexcutera pendant quelques (mili)secondes et retournera 129 rsultats.
37
Mais que sest-il exactement pass ici ? Pour le comprendre, commenons par prsenter les quatre types
de requtes du SQL :
SELECT, retourne des lignes en rponse une requte
INSERT, ajoute des lignes dans une table
UPDATE, modifie des lignes existantes dune table
DELETE, supprime des lignes dune table
Nous travaillerons principalement avec des requtes de type SELECT afin dinterroger les tables en
utilisant des fonctions spatiales.
7.1 Requte de type SELECT

Une requte de type Select est gnralement de la forme :
SELECT colonnes FROM donnes WHERE conditions ;
Note : Pour une description exhaustive des paramtres possible dune requte SELECT, consultez la
documentation de PostgresSQL.
Les colonnes sont soit des noms de colonnes, soit des fonctions utilisant les valeurs des colonnes.
Les donnes sont soit une table seule, soit plusieurs tables relies ensemble en ralisant une jointure
sur une clef ou une autre condition. Les conditions reprsentent le filtre qui restreint le nombre de
lignes retourner.
Quel sont les noms des quartiers de Brooklyn ?
Nous retournons notre table nyc_neighborhoods avec le filtre en main. La table contient tous les
quartiers de New York et nous voulons uniquement ceux de Brooklyn.
SELECT name
FROM nyc_neighborhoods
WHERE boroname = Brooklyn;
38
Chapitre 7. Partie 6 : Requtes SQL simples
La requte prendra nouveau quelque (milli)secondes et retournera les 23 lments rsultants.

Parfois, nous aurons besoin dappliquer des fonctions sur le rsultat dune de nos requtes. Par exemple,
Quel est le nombre de lettres dans les noms des quartiers de Brooklyn ?
Heureusement PostgreSQL fournit une fonction calculant la longueur dune chane de caractres :
char_length(string).
SELECT char_length(name)
Bien souvent, nous sommes moins interesss par une ligne particulire que par un calcul statistique sur
lensemble rsultant. Donc, connatre la longueur des noms de quartiers est moins intressant que de
calculer la moyenne de ces longueurs. Les fonctions qui renvoient un rsultat unique en utilisant un
ensemble de valeurs sont appeles des fonctions daggrgations.
PostgreSQL fournit un ensemble de fonctions daggrgations, parmi lesquelles avg() pour calculer la
moyenne, and stddev() pour lcart type.
Quel est le nombre moyen et lcart type du nombre de lettres dans le nom des quartier de
Brooklyn ?
SELECT avg(char_length(name)), stddev(char_length(name))
avg
|
stddev
---------------------+-------------------11.7391304347826087 | 3.9105613559407395
Les fonctions dagrgation dans notre dernier exemple sont appliques chaque ligne de lensemble des
rsultats. Comment faire si nous voulons rassembler des donnes ? Pour cela, nous utilisons la clause
GROUP BY. Les fonctions dagrgation ont souvent besoin dune clause GROUP BY pour regrouper les
lments en utilisant une ou plusieurs colonnes.
Quel est la moyenne du nombre de caractres des noms de quartiers et lcart-type du
nombre de caractres des noms de quartiers, renvoy par section de New York ?
SELECT boroname, avg(char_length(name)), stddev(char_length(name))
GROUP BY boroname;
Nous ajoutons la colonne boroname dans le rsultat afin de pouvoir dterminer quelle valeur statistique
sapplique quelle section. Dans une requte agrge, vous pouvez seulement retourner les colonnes qui
sont (a) membre de la clause de regroupement ou (b) des fonctions dagrgation.
boroname
|
avg
|
stddev
---------------+---------------------+-------------------Brooklyn
| 11.7391304347826087 | 3.9105613559407395
Manhattan
| 11.8214285714285714 | 4.3123729948325257
The Bronx
| 12.0416666666666667 | 3.6651017740975152
Queens
| 11.6666666666666667 | 5.0057438272815975
Staten Island | 12.2916666666666667 | 5.2043390480959474
7.1. Requte de type SELECT
39
7.2 Liste de fonctions

avg(expression) : fonction dagrgation de PostgreSQL qui retourne la valeur moyenne dune colonne.
char_length(string) : fonction sappliquant aux chanes de caractre de PostgreSQL qui retourne le nombre de lettres dans une chane.
stddev(expression) : fonction daggrgation de PostgreSQL qui retourne lcart type dun ensemble de
valeurs.
40
Chapitre 7. Partie 6 : Requtes SQL simples
CHAPITRE 8
Partie 7 : Exercices simples de SQL
En utilisant la table nyc_census_blocks, rpondez au questions suivantes (et nallez pas directement aux rponses ! ).
Vous trouverez ci-dessous des informations utiles pour commencer. Rfrez-vous la partie A propos
des nos donnes pour la dfinition de notre table nyc_census_blocks.
blkid
popn_total
popn_white
popn_black
popn_nativ
popn_asian
popn_other
hous_total
hous_own
hous_rent
boroname
the_geom
Un code 15 chiffres qui dfinit de manire unique chaque bloc ressenc . Ex :

360050001009000
Nombre total de personnes dans un bloc ressens
Nombre de personnes se dclarant blancs
Nombre de personnes se dclarant noirs
Nombre de personnes se dclarant comme ns aux tats-unis
Nombre de personne se dclarant comme asiatiques
Nombre de personne se dclarant dune autre catgorie
Nombre de pices appartements
Nombre de propritaires occupant les appartements
Nombre de locations disponibles
Nom du quartier de New York. Manhattan, The Bronx, Brooklyn, Staten Island,
Queens
Polygone dlimitant le bloc
Ici se trouvent certaines des fonctions daggrgation qui vous seront utiles pour rpondre aux questions :
avg() - la moyenne des valeurs dans un ensemble denregistrements
sum() - la somme des valeurs dun ensemble denregistrements
count() - le nombre dlments contenus dans un ensemble denregistrements.
Maintenant les questions :
Quelle est la population de la ville de New York ?
SELECT Sum(popn_total) AS population
FROM nyc_census_blocks;
8008278
Note : Quest-ce que ce AS dans la requte ? vous pouvez donner un nom une table ou des
colonnes en utilisant un alias. Les alias permettent de rendre les requtes plus simple crire et lire.
Donc au lieu que notre colonne rsultat soit nomme sum nous utilisons le AS pour la renommer en
population.
41
Quelle est la population du Bronx ?

SELECT Sum(popn_total) AS population
FROM nyc_census_blocks
WHERE boroname = The Bronx;
1332650
Quelle est en moyenne le nombre de personnes vivant dans chaque appartement de la ville de
New York ?
SELECT Sum(popn_total)/Sum(hous_total) AS popn_per_house
2.6503540522400804
Pour chaque quartier, quel est le pourcentage de population blanche ?

SELECT
boroname,
100 * Sum(popn_white)/Sum(popn_total) AS white_pct
GROUP BY boroname;
boroname
|
white_pct
---------------+--------------------Brooklyn
| 41.2005552206888663
The Bronx
| 29.8655310846808990
Manhattan
| 54.3594013771837665
Queens
| 44.0806610271290794
Staten Island | 77.5968611401579346

avg(expression) : fonction daggrgation de PostgreSQL qui renvoie la moyenne dun ensemble de nombres.
count(expression) : une fonction daggrgation de PostgreSQL qui retourne le nombre dlments dans
un ensemble.
sum(expression) : une fonction daggrgation de PostgreSQL qui retourne la somme des valeurs
numriques dun ensemble.
42
Chapitre 8. Partie 7 : Exercices simples de SQL
CHAPITRE 9
Partie 8 : Les gometries
9.1 Introduction
Dans une partie prcdente nous avons charg diffrentes donnes. Avant de commencer jouer avec,
commenons par regarder quelques exemples simples. Depuis pgAdmin, choisissez de nouveau la base
de donne nyc et ouvrez loutil de requtage SQL. Copiez cette exemple de code SQL (aprs avoir
supprim le contenu prsent par dfaut si ncessaire) puis excutez-le.
CREATE TABLE geometries (name varchar, geom geometry);
INSERT INTO geometries VALUES

(Point, POINT(0 0)),
(Linestring, LINESTRING(0 0, 1 1, 2 1, 2 2)),
(Polygon, POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))),
(PolygonWithHole, POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))
(Collection, GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))));
SELECT Populate_Geometry_Columns();
SELECT name, ST_AsText(geom) FROM geometries;
43
Lexemple ci-dessus cr une table (geometries) puis y insre cinq gomtries : un point, une ligne, un
polygone, un polygone avec un trou, et une collection. Les lignes insres sont slectionnes et affiches
dans le tableau de sortie.
9.2 Les tables de mtadonnes

Dans le respect de la spcification Simple Features for SQL (SFSQL), PostGIS fournit deux tables pour
rcuprer et sinformer sur les types de gomtries disponibles dans une base de donnes spcifique.
La premire table, spatial_ref_sys, dfinit tous les systmes de projection connus de la base de
donnes et sera dcrite plus en dtails plus tard.
La seconde table, geometry_columns, fournit une liste de toutes les entits (dfinit comme un
objet avec un attribut gomtrique) et les dtails de base relatives ces entits.
44
Chapitre 9. Partie 8 : Les gometries
Dans lexemple fournit en introduction, la fonction Populate_Geometry_Columns() dtecte toutes

les colonnes de la base de donnes qui contiennent des gomtries et met jour la table
geometry_columns pour y inclure leurs rfrences.
Regardons maintenant la table geometry_columns de notre base de donnes. Copiez cette commande dans la fentre de requtage :
SELECT * FROM geometry_columns;
9.2. Les tables de mtadonnes
45
f_table_catalog, f_table_schema, et f_table_name fournissent le nom complet de

la table contenant une gomtrie donne. tant donn que PostgreSQL nutilise pas de catalogues,
f_table_catalog est toujours vide.
f_geometry_column est le nom de la colonne qui contient la gomtrie pour les tables ayant
plusieurs colonnes gomtriques, il y a un enregistrement dans cette table pour chacune.
coord_dimension et srid dfinissent respectivement la dimension de la gomtrie (en 2-, 3- or
4-dimensions) et le systme de rfrence spatiale qui fait rfrence la table spatial_ref_sys.
La colonne type dfinit le type de gomtrie comme dcrit plus tt, nous avons dj vu les points et
les lignes.
En interrogeant cette table, les clients SIG et les libraires peuvent dterminer quoi attendre lors de la
rcupration des donnes et peuvent raliser les opration de reprojection, transformation ou rendu sans
avoir inspecter chaque gomtrie.
9.3 Rprsenter des objets du monde rel

La spcification Simple Features for SQL (SFSQL), le standard ayant guid le dveloppement de PostGIS, dfinit comment les objets du monde rel doivent tre reprsents. En considrant une forme continue une seule rsolution fixe, nous obtenons une pitre reprsentation des objets. SFSQL considre
uniquement les reprsentations en 2 dimensions. PostGIS a tendu cela en ajoutant les reprsentation en
3 et 4 dimensions. Plus rcemment, la spcification SQL-Multimedia Part 3 (SQL/MM) a officiellement
dfinit sa propre reprsentation.
Notre table exemple contient diffrents types de gomtries. Nous pouvons rcuprer les informations
de chaque objet en utilisant les fonctions qui lisent les mtadonnes de la gomtrie.
ST_GeometryType(geometry) retourne le type de la gomtrie
ST_NDims(geometry) retourne le nombre de dimensions dune gomtrie
ST_SRID(geometry) retourne lidentifiant de rfrence spatiale de la gomtrie
SELECT name, ST_GeometryType(geom), ST_NDims(geom), ST_SRID(geom)
FROM geometries;
name
|
st_geometrytype
| st_ndims | st_srid
-----------------+-----------------------+----------+--------Point
| ST_Point
|
2 |
-1
Polygon
| ST_Polygon
|
2 |
-1
PolygonWithHole | ST_Polygon
|
2 |
-1
Collection
| ST_GeometryCollection |
2 |
-1
Linestring
| ST_LineString
|
2 |
-1
9.3.1 Les points
Un point reprsente une localisation unique sur la Terre. Ce point est reprsent par une seule coordonne (incluant soit 2, 3 ou 4 dimensions). Les points sont utiliss pour reprsenter des objets lorsque
46
le dtail exact du contour nest pas important une chelle donne. Par exemple, les villes sur une
carte du monde peuvent tre dcrites sous la forme de points alors quune carte rgionale utiliserait une
reprsentation polygonale des villes.
SELECT ST_AsText(geom)
FROM geometries
WHERE name = Point;
POINT(0 0)
Certaines des fonctions spcifiques pour travailler avec les points sont :
ST_X(geometry) retourne la composante X
ST_Y(geometry) retourne la composante Y
Donc, nous pouvons lire les coordonnes dun point de la manire suivante :
SELECT ST_X(geom), ST_Y(geom)
FROM geometries
WHERE name = Point;
La table des stations de mtro de la ville de New York (nyc_subway_stations) est un ensemble
de donnes reprsent sous la forme de points. La requte SQL suivante renverra la gomtrie associe
un point (dans la colonne ST_AsText).
SELECT name, ST_AsText(the_geom)
FROM nyc_subway_stations
LIMIT 1;
9.3.2 Les lignes
Une ligne est un chemin entre plusieurs points. Elle prend la forme dun tableau ordonn compos de
deux (ou plusieurs) points. Les routes et les rivires sont typiquement reprsentes sous la forme de
lignes. Une ligne est dite ferme si elle commence et finit en un mme point. Elle est dite simple si elle
ne se coupe pas ou ne se touche pas elle-mme (sauf ses extrmits si elle est ferme). Une ligne peut
tre la fois ferme et simple.
Le rseau des rues de New York (nyc_streets) a t charg auparavant. Cet ensemble de donnes
contient les dtails comme le nom et le type des rues. Une rue du monde rel pourrait tre constitue de
plusieurs lignes, chacune reprsentant une segment de routes avec ses diffrents attributs.
La requte SQL suivante retourne la gomtrie associe une ligne (dans la colonne ST_AsText) :
FROM geometries
WHERE name = Linestring;
9.3. Rprsenter des objets du monde rel
47
LINESTRING(0 0, 1 1, 2 1, 2 2)
Les fonctions spatiales permettant de travailler avec les lignes sont les suivantes :
ST_Length(geometry) retourne la longueur dune ligne
ST_StartPoint(geometry) retourne le premier point dune ligne
ST_EndPoint(geometry) retourne le dernier point dune ligne
ST_NPoints(geometry) retourne le nombre de points dans une ligne
Donc, la longueur de notre ligne est :
SELECT ST_Length(geom)
FROM geometries
WHERE name = Linestring;
3.41421356237309
9.3.3 Les polygones
Un polygone est reprsent comme une zone. Le contour externe du polygone est reprsent par une
ligne simple et ferme. Les trous sont reprsents de la mme manire.
Les polygones sont utiliss pour reprsenter les objets dont les tailles et la forme sont importants. Les
limites de villes, les parcs, les btiments ou les cours deau sont habituellement reprsents par des
polygones lorsque lchelle est suffisament leve pour pouvoir distinguer leurs zones. Les routes et les
rivires peuvent parfois tre reprsentes comme des polygones.
La requte SQL suivante retournera la gomtrie associe un polygone (dans la colonne ST_AsText).
FROM geometries
WHERE name LIKE Polygon%;
Note : Plutt que dutiliser le signe = dans notre clause WHERE, nous avons utilis loprateur LIKE
pour pouvoir dfinir notre comparaison. Vous auriez sans doute voulu utiliser le symbole * pour exprimer
nimporte quelle valeur mais en SQL vous devez utiliser : % et loprateur LIKE pour informer le
systme que cette comparaison doit tre possible.
POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))
POLYGON((0 0, 10 0, 10 10, 0 10, 0 0),(1 1, 1 2, 2 2, 2 1, 1 1))
48
Le premier polygone a seulement une ligne. Le second a un trou. La plupart des systmes de rendu
graphique supportent le concept de polygone, mais les systmes SIG sont les seuls accepter que les
polygones puissent contenir des trous.
Certaines des fonctions spatiales spcifiques de traitement des polygones sont :

ST_Area(geometry) retourne laire dun polygone
ST_NRings(geometry) retourne le nombre de contours (habituellement 1, plus lorsquil y a des trous)
ST_ExteriorRing(geometry) retourne le contour extrieur
ST_InteriorRingN(geometry,n) retourne le contour intrieur numro n
ST_Perimeter(geometry) retourne la longueur de tous les contours
Nous pouvons calculer laire de nos polygones en utilisant la fonction area :
SELECT name, ST_Area(geom)
FROM geometries
WHERE name LIKE Polygon%;
Polygon
PolygonWithHole
1
99
Remarquez que le polygone contenant un trou a une aire gale laire du contour externe (un carr de
10 sur 10) moins laire du trou (un carr de 1 sur 1).
9.3.4 Les collections

Il y a quatre types de collections, qui regroupent ensemble plusieurs gomtries simples.
MultiPoint, une collection de points
49
MultiLineString, une collection de lignes

MultiPolygon, une collection de polygones
GeometryCollection, une collection htrogne de nimporte quelle gomtrie (dont dautre collections)
Les collections sont un concept prsents dans les logiciels SIG plus que dans les applications de rendu
graphique gnriques. Elles sont utiles pour directement modliser les objets du monde rel comme des
objet spatiaux. Par exemple, comment modliser une parcelle qui a t coupe par un chemin ? Comme
un MultiPolygon, ayant une partie de chaque cot du chemin.
50
Notre collection exemple contient un polygone et un point :
51
SELECT name, ST_AsText(geom)

FROM geometries
WHERE name = Collection;
GEOMETRYCOLLECTION(POINT(2 0),POLYGON((0 0, 1 0, 1 1, 0 1, 0 0)))
Certaines des fonctions spatiales spcifiques la manipulation des collections sont :

ST_NumGeometries(geometry) retourne le nombre de composantes dune collection
ST_GeometryN(geometry,n) retourne une composante spcifique
ST_Area(geometry) retourne laire totale des composantes de type polygone
ST_Length(geometry) retourne la longueur totale des composantes de type ligne
9.4 Entr / Sortie des gomtries

Dans la base de donnes, les gomtries sont stockes dans un format utilis uniquement par le programme PostGIS. Afin que des programmes externes puissent insrer et rcuprer les donnes utiles,
elles ont besoin dtre converties dans un format compris par lapplication. Heureusement, PostGIS
supporte un grand nombre de formats en entre et en sortie :
Format texte bien connu (Well-known text WKT)
ST_GeomFromText(text) retourne une geometry
ST_AsText(geometry) retourne le texte
ST_AsEWKT(geometry) retourne le texte
Format binaire bien connu (Well-known binary WKB)
ST_GeomFromWKB(bytea) retourne geometry
ST_AsBinary(geometry) retourne bytea
ST_AsEWKB(geometry) retourne bytea
Geographic Mark-up Language (GML)
ST_GeomFromGML(text) retourne geometry
ST_AsGML(geometry) retourne text
Keyhole Mark-up Language (KML)
ST_GeomFromKML(text) retourne geometry
ST_AsKML(geometry) retourne text
GeoJSON
ST_AsGeoJSON(geometry) retourne text
Scalable Vector Graphics (SVG)
ST_AsSVG(geometry) retourne text
La requte SQL suivante montre un exemple de reprsentation en WKB (lappel encode() est requis
pour convertir le format binaire en ASCII pour lafficher) :
SELECT encode(
ST_AsBinary(ST_GeometryFromText(LINESTRING(0 0 0,1 0 0,1 1 2))),
hex);
52
Dans le reste de ces travaux pratiques, nous utiliserons principalement le format WKT pour que vous
puissiez lire et comprendre les gomtries que nous voyons. Nanmoins, pour la plupart des traitement
actuels, comme la visualisation des donnes dans une application SIG, le transfert de donnes des
services web, ou lexcution distante de traitements, le format WKB est un format de choix.
Puisque les formats WKT et le WKB sont dfinis dans la spcification SFSQL, ils ne prennent pas en
compte les gomtries 3 ou 4 dimensions. Cest pour cette raison que PostGIS dfinit les formats
Extended Well Known Text (EWKT) et Extended Well Known Binary (EWKB). Cela permet de grer
de faon similaire aux formats WKT et WKB les dimensions ajoutes.
Voici un exemple de ligne 3D au format WKT :
SELECT ST_AsEWKT(ST_GeometryFromText(LINESTRING(0 0 0,1 0 0,1 1 2)));
9.4. Entr / Sortie des gomtries
53
SELECT encode(ST_AsEWKB(ST_GeometryFromText(
LINESTRING(0 0 0,1 0 0,1 1 2))), hex);
En plus de pouvoir gnrer les diffrents formats en sortie (WKT, WKB, GML, KML, JSON, SVG),
PostGIS permet aussi de lire 4 de ces formats (WKT, WKB, GML, KML). La plupart des applications
utilisent des fonctions crant des gomtries laide du format WKT ou WKB, mais les autres marchent
aussi. Voici un exemple qui lit du GML et retourne du JSON :
54
SELECT ST_AsGeoJSON(ST_GeomFromGML(<gml:Point><gml:coordinates>1,1</gml:coordinates></g

Populate_Geometry_Columns : sassure que les colonnes gomtriques ont les contraintes spatiales appropries et quelles sont prsentes dans la table geometry_columns.
ST_Area : retourne laire de la surface si cest un polygone ou un multi-polygone. Pour le type geometry laire est dans lunit du SRID. Pour les geography laire est en mtres carrs.
ST_AsText : retourne la reprsentation de la geometry/geography au format Well-Known Text (WKT)
sans mtadonne correspondant au SRID.
ST_AsBinary : retourne la reprsentation de la geometry/geography au format Well-Known Binary
(WKB) sans mtadonne correspondant u SRID.
ST_EndPoint : retourne le dernier point dune ligne.
ST_AsEWKB : retourne la reprsentation de la gometrie au format Well-Known Binary (WKB) avec
la mtadonne SRID.
ST_AsEWKT : retourne la reprsentation de la gometrie au format Well-Known Text (WKT) avec la
mtadonne SRID.
ST_AsGeoJSON : retourne la gomtrie au format GeoJSON.
ST_AsGML : retourne la gomtrie au format GML version 2 ou 3.
ST_AsKML : retourne la gomtrie au format KML. Nombreuses variantes. Par dfaut : version=2 et
precision=15.
ST_AsSVG : retourne la gomtrie au format SVG.
ST_ExteriorRing : retourne une ligne reprsentant le contour extrieur du polygone. Retourne NULL si
la gomtrie nest pas un polygone. Ne fonctionne pas avec les multi-polygones.
ST_GeometryN : retourne la nime composante si la gomtrie est du type GEOMETRYCOLLECTION, MULTIPOINT, MULTILINESTRING, MULTICURVE ou MULTIPOLYGON. Sinon, retourne
NULL.
55
ST_GeomFromGML : prend en entre une reprsentation GML de la gomtrie et retourne un object

PostGIS de type geometry.
ST_GeomFromKML : prend en entre une reprsentation KML de la gomtrie et retourne un object
PostGIS de type geometry.
ST_GeomFromText : retourne une valeur de type ST_Geometry partir dune reprsentation au format
Well-Known Text (WKT).
ST_GeomFromWKB : retourne une valeur de type ST_Geometry partir dune reprsentation au format
Well-Known Binary (WKB).
ST_GeometryType : retourne le type de gomtrie de la valeur de type ST_Geometry.
ST_InteriorRingN : retourne le nime contour intrieur dun polygone. Retourne NULL si la gomtrie
nest pas un polygone ou si N est hors des limites.
ST_Length : retourne la longueur en 2-dimensions si cest une ligne ou une multi-lignes. Les objets de
type geometry sont dans lunit du systme de rfrence spatiale et les objets de type geography sont en
mtres (sphrode par dfaut).
ST_NDims : retourne le nombre de dimensions dune gomtrie. Les valeurs possibles sont : 2,3 ou 4.
ST_NPoints : retourne le nombre de points dans une gomtrie.
ST_NRings : si la gomtrie est un polygone ou un multi-polygone, retourne le nombre de contours.
ST_NumGeometries : si la gomtrie est du type GEOMETRYCOLLECTION (ou MULTI*) retourne
le nombre de gomtries, sinon retourne NULL.
ST_Perimeter : retourne la longueur du contour extrieur dune valeur de type ST_Surface ou
ST_MultiSurface (polygone, multi-polygone).
ST_SRID : retourne lidentifiant du systme de rfrence spatiale dfinit dans la table spatial_ref_sys
dun objet de type ST_Geometry.
ST_StartPoint : retourne le premier point dune ligne.
ST_X : retourne la coordonne X dun point, ou NULL si non prsent. Largument pass doit tre un
point.
ST_Y : retourne la coordonne Y dun point, ou NULL si non prsent. Largument pass doit tre un
point.
56
CHAPITRE 10
Partie 9 : Exercices sur les gomtries
Voici un petit rappel de toutes les fonction que nous avons abord jusqu prsent. Elles devraient tre
utiles pour les exercices !
sum(expression) agrgation retournant la somme dun ensemble
count(expression) agrgation retournant le nombre dlments dun ensemble
ST_GeometryType(geometry) retourne le type de la gomtrie
ST_NDims(geometry) retourne le nombre de dimensions
ST_SRID(geometry) retourne lidentifiant du systme de rfrence spatiale
ST_X(point) retourne la coordonne X
ST_Y(point) retourne la coordonne Y
ST_Length(linestring) retourne la longueur dune ligne
ST_StartPoint(geometry) retourne le premier point dune ligne
ST_EndPoint(geometry) retourne le dernier point dune ligne
ST_NPoints(geometry) retourne le nombre de points dune ligne
ST_NRings(geometry) retourne le nombre de contours (1 ou plus si il y a des trous)
ST_ExteriorRing(polygon) retourne le contour extrieur (ligne) dun polygone
ST_InteriorRingN(polygon, integer) retourne le contour intrieur (ligne) dun polygone
ST_Perimeter(geometry) retourne la longueur de tous les contours
ST_NumGeometries(multi/geomcollection) retourne le nombre de composantes dans une collection
ST_GeometryN(geometry, integer) retourne la nime entit de la collection
ST_GeomFromText(text) retourne geometry
ST_AsText(geometry) retourne WKT text
ST_AsEWKT(geometry) retourne EWKT text
ST_GeomFromWKB(bytea) retourne geometry
ST_AsBinary(geometry) retourne WKB bytea
ST_AsEWKB(geometry) retourne EWKB bytea
ST_GeomFromGML(text) retourne geometry
ST_AsGML(geometry) retourne GML text
ST_GeomFromKML(text) retourne geometry
ST_AsKML(geometry) retourne KML text
ST_AsGeoJSON(geometry) retourne JSON text
ST_AsSVG(geometry) retourne SVG text
Souvenez-vous aussi des tables disponibles :
nyc_census_blocks
name, popn_total, boroname, the_geom
57
nyc_streets
name, type, the_geom
nyc_subway_stations
name, the_geom
nyc_neighborhoods
name, boroname, the_geom
10.1 Exercices
Quelle est laire du quartier West Village ?
SELECT ST_Area(the_geom)
WHERE name = West Village;
1044614.53027344
Note : Laire est donne en mtres carrs. Pour obtenir laire en hectare, divisez par 10000. Pour
obtenir laire en acres, divisez par 4047.
Quelle est laire de Manhattan en acres ? (Astuce : nyc_census_blocks et
nyc_neighborhoods ont toutes les deux le champ boroname.)
SELECT Sum(ST_Area(the_geom)) / 4047
WHERE boroname = Manhattan;
13965.3201224118
or...
SELECT Sum(ST_Area(the_geom)) / 4047
WHERE boroname = Manhattan;
14572.1575543757
Combien de blocs de la ville de New York ont des trous ?

SELECT Count(*)
WHERE ST_NRings(the_geom) > 1;
66
Quel est la longueur totale des rues (en kilomtres) dans la ville de New York ? (Astuce : lunit
de mesure des donnes spatiales est le mtre, il y a 1000 mtres dans un kilomtre.)
SELECT Sum(ST_Length(the_geom)) / 1000
FROM nyc_streets;
10418.9047172
Quelle est la longueur de Columbus Cir (Columbus Circle) ?
58
Chapitre 10. Partie 9 : Exercices sur les gomtries
SELECT ST_Length(the_geom)
FROM nyc_streets
WHERE name = Columbus Cir;
308.34199
Quelle est le contour de West Village au format JSON ?

SELECT ST_AsGeoJSON(the_geom)
{"type":"MultiPolygon","coordinates":
[[[[583263.2776595836,4509242.6260239873],
[583276.81990686338,4509378.825446927], ...
[583263.2776595836,4509242.6260239873]]]]}
La gomtrie de type MultiPolygon, intressant !

Combien de polygones sont dans le multi-polygone West Village ?
SELECT ST_NumGeometries(the_geom)
1
Note : Il nest pas rare de trouver des lments de type multi-polygone ne contenant quun seul
polygone dans des tables. Lutilisation du type multi-polygone permet dutiliser une seule table pour
y stocker des gomtries simples et multiples sans mlanger les types.
Quel est la longueur des rues de la ville de New York, suivant leur type ?
SELECT type, Sum(ST_Length(the_geom)) AS length
FROM nyc_streets
GROUP BY type
ORDER BY length DESC;
type
|
length
--------------------------------------------------+-----------------residential
| 8629870.33786606
motorway
| 403622.478126363
tertiary
| 360394.879051303
motorway_link
| 294261.419479668
secondary
| 276264.303897926
unclassified
| 166936.371604458
primary
| 135034.233017947
footway
| 71798.4878378096
service
| 28337.635038596
trunk
| 20353.5819826076
cycleway
| 8863.75144825929
pedestrian
| 4867.05032825026
construction
| 4803.08162103562
residential; motorway_link
| 3661.57506293745
trunk_link
| 3202.18981240201
primary_link
| 2492.57457083536
living_street
| 1894.63905457332
10.1. Exercices
59
primary; residential; motorway_link; residential | 1367.76576941335

undefined
| 380.53861910346
steps
| 282.745221342127
motorway_link; residential
| 215.07778911517
Note : La clause ORDER BY length DESC ordonne le rsultat par la valeur des longueurs dans
lordre dcroissant. Le rsultat avec la plus grande valeur se retrouve au dbut la liste de rsultats.
60
Chapitre 10. Partie 9 : Exercices sur les gomtries
CHAPITRE 11
Partie 10 : Les relations spatiales
Jusqu prsent, nous avons utilis uniquement des fonctions qui permettent de mesurer (ST_Area,
ST_Length), de srialiser (ST_GeomFromText) ou dsrialiser (ST_AsGML) des gomtries. Ces
fonctions sont toutes utilises sur une gomtrie la fois.
Les base de donnes spatiales sont puissantes car elle ne se contentent pas de stocker les gomtries, elle
peuvent aussi vrifier les relations entre les gomtries.
Pour les questions comme Quel est le plus proche garage vlo prs du parc ? ou Ou est lintersection
du mtro avec telle rue ?, nous devrons comparer les gomtries reprsentant les garages vlo, les rues
et les lignes de mtro.
Le standard de lOGC dfinit lensemble de fonctions suivantes pour comparer les gomtries.
11.1 ST_Equals
ST_Equals(geometry A, geometry B) teste lgalit spatiale de deux gomtries.
ST_Equals retourne TRUE si les deux gomtries sont du mme type et ont des coordonnes x.y identiques.
Premirement, essayons de rcuprer la reprsentation dun
nyc_subway_stations. Nous ne prendrons que lentre : Broad St.
point
de
notre
table
SELECT name, the_geom, ST_AsText(the_geom)

WHERE name = Broad St;
name
|
the_geom
|
st_astext
----------+----------------------------------------------------+----------------------Broad St | 0101000020266900000EEBD4CF27CF2141BC17D69516315141 | POINT(583571 4506714)
Maintenant, copiez / collez la valeur affiche pour tester la fonction ST_Equals :

SELECT name
WHERE ST_Equals(the_geom, 0101000020266900000EEBD4CF27CF2141BC17D69516315141);
61
62
Chapitre 11. Partie 10 : Les relations spatiales
Broad St
Note
:
La
reprsentation
du
point
nest
pas
vraiment
comprhensible
(0101000020266900000EEBD4CF27CF2141BC17D69516315141) mais cest exactement la
reprsentation des coordonnes. Pour tester lgalit, lutilisation de ce format est ncessaire.
11.2 ST_Intersects, ST_Disjoint, ST_Crosses et ST_Overlaps

ST_Intersects, ST_Crosses, et ST_Overlaps teste si lintrieur des gomtries sintersecte, se croise
ou se chevauche.
11.2. ST_Intersects, ST_Disjoint, ST_Crosses et ST_Overlaps
63
ST_Intersects(geometry A, geometry B) retourne t (TRUE) si lintersection ne renvoie pas un ensemble vide de rsultats. Intersects retourne le rsultat exactement inverse de la fonction disjoint.
Loppos de ST_Intersects est ST_Disjoint(geometry A , geometry B). Si deux gomtries sont disjointes, elle ne sintersectent pas et vice-versa. En fait, il est souvent plus efficace de tester si deux
gomtries ne sintersectent pas que de tester si elles sont disjointes du fait que le test dintersection
peut tre spatialement index alors que le test disjoint ne le peut pas.
Pour les comparaisons de couples de types multipoint/polygon, multipoint/linestring,
linestring/linestring, linestring/polygon, et linestring/multipolygon, ST_Crosses(geometry A, geometry B) retourne t (TRUE) si les rsultats de lintersection sont lintrieur des deux gomtries.
ST_Overlaps(geometry A, geometry B) compare deux gomtries de mme dimension et retourne
TRUE si leur intersection est une gomtrie diffrente des deux fournies mais de mme dimension.
64
Essayons de prendre la station de mtro de Broad Street et de dterminer son voisinage en utilisant la
fonction ST_Intersects :
SELECT name, boroname
WHERE ST_Intersects(the_geom, 0101000020266900000EEBD4CF27CF2141BC17D69516315141);
name
| boroname
--------------------+----------Financial District | Manhattan
11.3 ST_Touches
ST_Touches teste si deux gomtries se touchent en leur contours extrieurs, mais leur contours intrieurs ne sintersectent pas
ST_Touches(geometry A, geometry B) retourne TRUE soit si les contours des gomtries sintersectent ou si lun des contours intrieurs de lune intersecte le contour extrieur de lautre.
11.4 ST_Within et ST_Contains

ST_Within et ST_Contains teste si une gomtrie est totalement incluse dans lautre.
11.3. ST_Touches
65
66
11.4. ST_Within et ST_Contains
67
68
ST_Within(geometry A , geometry B) retourne TRUE si la premire gomtrie est compltement

contenue dans lautre. ST_Within teste lexact oppos au rsultat de ST_Contains.
ST_Contains(geometry A, geometry B) retourne TRUE si la seconde gomtrie est compltement
contenue dans la premire gomtrie.
11.5 ST_Distance et ST_DWithin

Une question frquente dans le domaine du SIG est trouver tous les lments qui se trouvent une
distance X de cet autre lment.
La fonction ST_Distance(geometry A, geometry B) calcule la plus courte distance entre deux
gomtries. Cela est pratique pour rcuprer la distance entre les objets.
SELECT ST_Distance(
ST_GeometryFromText(POINT(0 5)),
ST_GeometryFromText(LINESTRING(-2 2, 2 2)));
3
Pour tester si deux objets sont la mme distance dun autre, la fonction ST_DWithin fournit un test
tirant profit des index. Cela est trs utile pour rpondre a une question telle que : Combien darbres se
situent dans un buffer de 500 mtres autour de cette route ?. Vous navez pas calculer le buffer, vous
avez simplement besoin de tester la distance entre les gomtries.
11.5. ST_Distance et ST_DWithin
69
En utilisant de nouveau notre station de mtro Broad Street, nous pouvons trouver les rues voisines (
10 mtres de) de la station :
SELECT name
FROM nyc_streets
WHERE ST_DWithin(
the_geom,
0101000020266900000EEBD4CF27CF2141BC17D69516315141,
10
);
name
-------------Wall St
Broad St
Nassau St
Nous pouvons vrifier la rponse sur une carte. La station Broad St est actuellement lintersection des
rues Wall, Broad et Nassau.

ST_Contains(geometry A, geometry B) : retourne TRUE si aucun des points de B nest lextrieur de
70
A, et au moins un point de lintrieur de B est lintrieur de A.

ST_Crosses(geometry A, geometry B) : retourne TRUE si la gomtrie A a certains, mais pas la totalit,
de ses points lintrieur de B.
ST_Disjoint(geometry A , geometry B) : retourne TRUE si les gomtries ne sintersectent pas - elles
nont aucun point en commun.
ST_Distance(geometry A, geometry B) : retourne la distance cartsienne en 2 dimensions minimum
entre deux gomtries dans lunit de la projection.
ST_DWithin(geometry A, geometry B, radius) : retourne TRUE si les gomtries sont distante (radius)
lune de lautre.
ST_Equals(geometry A, geometry B) : retourne TRUE si les gomtries fournies reprsentent la mme
gomtrie. Lordre des entits nest pas pris en compte.
ST_Intersects(geometry A, geometry B) : retourne TRUE si les gomtries sintersectent - (ont un espace
en commun) et FALSE si elles nen ont pas (elles sont disjointes).
ST_Overlaps(geometry A, geometry B) : retourne TRUE si les gomtries ont un espace en commun,
sont de la mme dimension, mais ne sont pas compltement contenues lune dans lautre.
ST_Touches(geometry A, geometry B) : retourne TRUE si les gomtries ont au moins un point en
commun, mais leur intrieurs ne sintersectent pas.
ST_Within(geometry A , geometry B) : retourne TRUE si la gomtrie A est compltement lintrieur
de B
71
72
CHAPITRE 12
Partie 11 : Exercices sur les relations

spatiales
Voici un rappel des fonctions que nous avons vu dans les parties prcdentes. Elles seront utiles pour les
exercices !
ST_Contains(geometry A, geometry B) retourne TRUE si la gomtrie A contient la gomtrie B
ST_Crosses(geometry A, geometry B) retourne TRUE si la gomtrie A croise la gomtrie B
ST_Disjoint(geometry A , geometry B) retourne TRUE si les gomtries ne sintersectent pas
ST_Distance(geometry A, geometry B) retourne la distance minimum entre deux gomtries
ST_DWithin(geometry A, geometry B, radius) retourne TRUE si la A est distante dau plus radius
de B
ST_Equals(geometry A, geometry B) retourne TRUE si A est la mme gomtrie que B
ST_Intersects(geometry A, geometry B) retourne TRUE si A intersecte B
ST_Overlaps(geometry A, geometry B) retourne TRUE si A et B on un espace en commun, mais
ne sont pas compltement incluses lun dans lautre.
ST_Touches(geometry A, geometry B) retourne TRUE si le contour extrieur de A touche B
ST_Within(geometry A, geometry B) retourne TRUE si A est hors de B
Souvenez-vous les tables votre disposition :
nyc_census_blocks
nyc_streets
nyc_subway_stations
name, the_geom
nyc_neighborhoods
12.1 Exercices
Quelle est la valeur gomtrique de la rue nomme Atlantic Commons ?
73
SELECT the_geom
FROM nyc_streets
WHERE name = Atlantic Commons;
01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0ADF
Quels sont les quartiers et villes qui sont dans Atlantic Commons ?
SELECT name, boroname

WHERE ST_Intersects(
the_geom,
01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0
);
name
| boroname
------------+---------Fort Green | Brooklyn
Quelles rues touchent Atlantic Commons ?
SELECT name
FROM nyc_streets
WHERE ST_Touches(
the_geom,
01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E0
);
name
--------------S Oxford St
Cumberland St
Approximativement combien de personnes vivent dans (ou dans une zone de 50 mtres autour
d) Atlantic Commons ?
74
Chapitre 12. Partie 11 : Exercices sur les relations spatiales
SELECT Sum(popn_total)
WHERE ST_DWithin(
the_geom,
01050000202669000001000000010200000002000000093235673BE82141F319CD89A22E514170E30E
50
);
1186
12.1. Exercices
75
76
Chapitre 12. Partie 11 : Exercices sur les relations spatiales
CHAPITRE 13
Partie 12 : Les jointures spatiales
Les jointures spatiales sont la cerise sur le gteau des base de donnes spatiales. Elles vous pemettent de
combiner les informations de plusieurs tables en utilisant une relation spatiale comme clause de jointure.
La plupart des analyses SIG standards peuvent tre exprimes laide de jointures spatiales.
Dans la partie prcdente, nous avons utilis les relations spatiales en utilisant deux tapes dans nos
requtes : nous avons dans un premier temps extrait la station de mtro Broad St puis nous avons
utilis ce rsultat dans nos autres requtes pour rpondre aux questions comme dans quel quartier se
situe la station Broad St ?
En utilisant les jointures spatiales, nous pouvons rpondre aux questions en une seule tape, rcuprant
les informations relatives la station de mtro et le quartier la contenant :
SELECT
subways.name AS subway_name,
neighborhoods.name AS neighborhood_name,
neighborhoods.boroname AS borough
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_subway_stations AS subways
ON ST_Contains(neighborhoods.the_geom, subways.the_geom)
WHERE subways.name = Broad St;
subway_name | neighborhood_name | borough
-------------+--------------------+----------Broad St
| Financial District | Manhattan
Nous avons pu regrouper chaque station de mtro avec le quartier auquel elle appartient, mais dans
ce cas nous nen voulions quune. Chaque fonction qui envoit un rsultat du type vrai/faux peut tre
utilise pour joindre spatialement deux tables, mais la plupart du temps on utilise : ST_Intersects,
ST_Contains, et ST_DWithin.
13.1 Jointure et regroupement

La combinaison de JOIN avec GROUP BY fournit le type danalyse qui est couramment utilis dans les
systmes SIG.
77
Par exemple : Quelle est la population et la rpartition raciale du quartier de Manhattan ? Ici nous
avons une question qui combine les informations relatives la population recense et les contours des
quartiers, or nous ne voulons quun seul quartier, celui de Manhattan.
SELECT
neighborhoods.name AS neighborhood_name,
Sum(census.popn_total) AS population,
Round(100.0 * Sum(census.popn_white) / Sum(census.popn_total),1) AS white_pct,
Round(100.0 * Sum(census.popn_black) / Sum(census.popn_total),1) AS black_pct
FROM nyc_neighborhoods AS neighborhoods
JOIN nyc_census_blocks AS census
ON ST_Intersects(neighborhoods.the_geom, census.the_geom)
WHERE neighborhoods.boroname = Manhattan
GROUP BY neighborhoods.name
ORDER BY white_pct DESC;
neighborhood_name | population | white_pct | black_pct
---------------------+------------+-----------+----------Carnegie Hill
|
19909 |
91.6 |
1.5
North Sutton Area
|
21413 |
90.3 |
1.2
West Village
|
27141 |
88.1 |
2.7
Upper East Side
|
201301 |
87.8 |
2.5
Greenwich Village
|
57047 |
84.1 |
3.3
Soho
|
15371 |
84.1 |
3.3
Murray Hill
|
27669 |
79.2 |
2.3
Gramercy
|
97264 |
77.8 |
5.6
Central Park
|
49284 |
77.8 |
10.4
Tribeca
|
13601 |
77.2 |
5.5
Midtown
|
70412 |
75.9 |
5.1
Chelsea
|
51773 |
74.7 |
7.4
Battery Park
|
9928 |
74.1 |
4.9
Upper West Side
|
212499 |
73.3 |
10.4
Financial District |
17279 |
71.3 |
5.3
Clinton
|
26347 |
64.6 |
10.3
East Village
|
77448 |
61.4 |
9.7
Garment District
|
6900 |
51.1 |
8.6
Morningside Heights |
41499 |
50.2 |
24.8
Little Italy
|
14178 |
39.4 |
1.2
Yorkville
|
57800 |
31.2 |
33.3
Inwood
|
50922 |
29.3 |
14.9
Lower East Side
|
104690 |
28.3 |
9.0
Washington Heights |
187198 |
26.9 |
16.3
East Harlem
|
62279 |
20.2 |
46.2
Hamilton Heights
|
71133 |
14.6 |
41.1
Chinatown
|
18195 |
10.3 |
4.2
Harlem
|
125501 |
5.7 |
80.5
Que ce passe-t-il ici ? Voici ce qui se passe (lordre dvaluation est optimis par la base de donnes) :
1. La clause JOIN cre une table virtuelle qui contient les colonnes la fois des quartiers et des
recensements (tables neighborhoods et census).
2. La clause WHERE filtre la table virtuelle pour ne conserver que la ligne correspondant Manhattan.
3. Les lignes restantes sont regroupes par le nom du quartier et sont utilises par la fonction dagrgation : Sum() pour raliser la somme des valeurs de la population.
4. Aprs un peu darithmtique et de formatage (ex : GROUP BY, ORDER BY)) sur le nombres
finaux, notre requte calcule les pourcentages.
78
Chapitre 13. Partie 12 : Les jointures spatiales
Note : La clause JOIN combine deux parties FROM. Par dfaut, nous utilisons un jointure du
type :INNER JOIN, mais il existe quatres autres types de jointures. Pour de plus amples informations
ce sujet, consultez la partie type_jointure de la page de la documentation officielle de PostgreSQL.
Nous pouvons aussi utiliser le test de la distance dans notre clef de jointure, pour crer une regroupement
de tous les lments dans un certain rayon. Essayons danalyser la gographie raciale de New York en
utilisant les requtes de distance.
Premirement, essayons dobtenir la rpartition raciale de la ville.
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
Sum(popn_total) AS popn_total
white_pct
|
black_pct
| popn_total
---------------------+---------------------+-----------44.6586020115685295 | 26.5945063345703034 |
8008278
Donc, 8M de personnes dans New York, environ 44% sont blancs et 26% sont noirs.
Duke Ellington chantait que You / must take the A-train / To / go to Sugar Hill way up in Harlem.
Comme nous lavons vu prcdemment, Harlem est de trs loin le quartier ou se trouve la plus grande
concentration dafricains-amricains de Manhattan (80.5%). Est-il toujours vrai quil faut prendre le
train A dont Duke parlait dans sa chanson ?
Premirement, le contenu du champ routes de la table nyc_subway_stations va nous servir
rcuprer le train A. Les valeurs de ce champs sont un peu complexes.
SELECT DISTINCT routes FROM nyc_subway_stations;
A,C,G
4,5
D,F,N,Q
5
E,F
E,J,Z
R,W
Note : Le mot clef DISTINCT permet dliminer les rptitions de lignes de notre rsultat. Dans ce
mot clef, notre requte renverrait 491 rsultats au lieu de 73.
Donc pour trouver le train A, nous allons demander toutes les lignes ayant pour routes la valeur
A. Nous pouvons faire cela de diffrentes manires, mais nous utiliserons aujourdhui le fait que la
fonction strpos(routes,A) retourne un entier diffrent de 0 si la lettre A se trouve dans la valeur du
champ route.
SELECT DISTINCT routes
FROM nyc_subway_stations AS subways
WHERE strpos(subways.routes,A) > 0;
13.1. Jointure et regroupement
79
A,B,C
A,C
A
A,C,G
A,C,E,L
A,S
A,C,F
A,B,C,D
A,C,E
Essayons de regrouper la rpartition raciale dans un rayon de 200 mtres de la ligne du train A.
SELECT
100.0 * Sum(popn_white) / Sum(popn_total) AS white_pct,
100.0 * Sum(popn_black) / Sum(popn_total) AS black_pct,
FROM nyc_census_blocks AS census
ON ST_DWithin(census.the_geom, subways.the_geom, 200)
WHERE strpos(subways.routes,A) > 0;
white_pct
|
black_pct
| popn_total
---------------------+---------------------+-----------42.0805466940877366 | 23.0936148851067964 |
185259
La rpartition raciale le long de la ligne du train A nest pas radicalement diffrente de la rpartition
gnrale de la ville de New York.
13.2 Jointures avances

Dans la dernire partie nous avons vu que le train A nest pas utilis par des populations si loignes de
la rpartition totale du reste de la ville. Y-a-t-il des trains qui passent par des parties de la ville qui ne
sont pas dans la moyenne de la rpartition raciale ?
Pour rpondre cette question, nous ajouterons une nouvelle jointure notre requte, de telle manire
que nous puissions calculer simultanment la rpartition raciale de plusieurs lignes de mtro la fois.
Pour faire ceci, nous crerons une table qui permettra dnumrer toutes les lignes que nous voulons
regrouper.
CREATE TABLE subway_lines ( route char(1) );
INSERT INTO subway_lines (route) VALUES
(A),(B),(C),(D),(E),(F),(G),
(J),(L),(M),(N),(Q),(R),(S),
(Z),(1),(2),(3),(4),(5),(6),
(7);
Maintenant nous pouvons joindre les tables des lignes de mtro notre requte prcdente.
SELECT
lines.route,
Round(100.0 * Sum(popn_white) / Sum(popn_total), 1) AS white_pct,
Round(100.0 * Sum(popn_black) / Sum(popn_total), 1) AS black_pct,
FROM nyc_census_blocks AS census
80
ON ST_DWithin(census.the_geom, subways.the_geom, 200)

JOIN subway_lines AS lines
ON strpos(subways.routes, lines.route) > 0
GROUP BY lines.route
ORDER BY black_pct DESC;
route | white_pct | black_pct | popn_total
-------+-----------+-----------+-----------S
|
30.1 |
59.5 |
32730
3
|
34.3 |
51.8 |
201888
2
|
33.6 |
45.5 |
535414
5
|
32.1 |
45.1 |
407324
C
|
41.3 |
35.9 |
430194
4
|
34.7 |
30.9 |
328292
B
|
36.1 |
30.6 |
261186
Q
|
52.9 |
26.3 |
259820
J
|
29.5 |
23.6 |
126764
A
|
42.1 |
23.1 |
370518
Z
|
29.5 |
21.5 |
81493
D
|
39.8 |
20.9 |
233855
G
|
44.8 |
20.0 |
138602
L
|
53.9 |
17.1 |
104140
6
|
52.7 |
16.3 |
257769
1
|
54.8 |
12.6 |
659028
F
|
60.0 |
8.6 |
438212
M
|
50.0 |
7.8 |
166721
E
|
69.4 |
5.3 |
86118
R
|
57.7 |
4.8 |
389124
7
|
42.4 |
3.8 |
107543
Comme prcdemment, les jointures crent une table virtuelle de toutes les combinaisons possibles et disponibles laide des contraintes de type JOIN ON. Ces lignes sont ensuite
utilises dans le regroupement GROUP. La magie spatiale tient dans lutilisation de
la fonction ST_DWithin qui sassure que les blocs sont suffisamment proches des lignes de mtros
incluses dans le calcul.
13.3 Liste de fonctions

ST_Contains(geometry A, geometry B) : retourne TRUE si et seulement si aucun point de B est
lextrieur de A, et si au moins un point lintrieur de B est lintrieur de A.
ST_DWithin(geometry A, geometry B, radius) : retourne TRUE si les gomtries sont distantes du rayon
donn.
ST_Intersects(geometry A, geometry B) : retourne TRUE si les gomtries/gographies sintersectent
spatialement (partage une portion de lespace) et FALSE sinon (elles sont disjointes).
round(v numeric, s integer) : fonction de PostgreSQL qui arrondit s dcimales.
strpos(chane, sous-chane) : fonction de chane de caractres de PostgreSQL qui retourne la position de
la sous-chaine.
sum(expression) : fonction dagrgation de PostgreSQL qui retourne la somme dun ensemble de
valeurs.
13.3. Liste de fonctions
81
82
CHAPITRE 14
Partie 13 : Exercices sur jointures

spatiales
Voici un petit rappel de certaines des fonctions vues prcdemment. Elles seront utiles pour les exercices !
ST_AsText(geometry) retourne un texte WKT
ST_Contains(geometry A, geometry B) retourne TRUE si la gomtrie A contient la gomtrie B
ST_Distance(geometry A, geometry B) retourne la distance minimum entre deux gomtries
ST_DWithin(geometry A, geometry B, radius) retourne TRUE si la A est distante dau plus radius
de B
ST_GeomFromText(text) retourne une gomtrie
ST_Intersects(geometry A, geometry B) retourne TRUE si la gomtrie A intersecte la gomtrie B
ST_Touches(geometry A, geometry B) retourne TRUE si le contour extrieur de A touche B
ST_Within(geometry A, geometry B) retourne TRUE si A est hors de B
Souvenez-vous aussi des tables votre disposition :
nyc_census_blocks
nyc_streets
nyc_subway_stations
name, routes, the_geom
nyc_neighborhoods
14.1 Exercices
Quelle station de mtro se situe dans le quartier Little Italy ? Quelle est litinraire de mtro
emprunter ?
SELECT s.name, s.routes
FROM nyc_subway_stations AS s
83
JOIN nyc_neighborhoods AS n
ON ST_Contains(n.the_geom, s.the_geom)
WHERE n.name = Little Italy;
name
| routes
-----------+-------Spring St | 6
Quels sont les quartiers desservis pas le train numro 6 ? (Astuce : la colonne routes de la
table nyc_subway_stations dispose des valeurs suivantes : B,D,6,V et C,6)
SELECT DISTINCT n.name, n.boroname
FROM nyc_subway_stations AS s
ON ST_Contains(n.the_geom, s.the_geom)
WHERE strpos(s.routes,6) > 0;
name
| boroname
--------------------+----------Midtown
| Manhattan
Hunts Point
| The Bronx
Gramercy
| Manhattan
Little Italy
| Manhattan
Financial District | Manhattan
South Bronx
| The Bronx
Yorkville
| Manhattan
Murray Hill
| Manhattan
Mott Haven
| The Bronx
Upper East Side
| Manhattan
Chinatown
| Manhattan
East Harlem
| Manhattan
Greenwich Village | Manhattan
Parkchester
| The Bronx
Soundview
| The Bronx
Note : Nous avons utilis le mot clef DISTINCT pour supprimer les rptitions dans notre ensemble
de rsultats o il y avait plus dune seule station de mtro dans le quartier.
Aprs le 11 septembre, le quartier de Battery Park tait interdit daccs pendant plusieurs
jours. Combien de personnes ont d tre vacues ?
FROM nyc_neighborhoods AS n
JOIN nyc_census_blocks AS c
ON ST_Intersects(n.the_geom, c.the_geom)
WHERE n.name = Battery Park;
9928
Quelle est la densit de population (personne / km^2) des quartiers de Upper West Side et de
Upper East Side ? (Astuce : il y a 1000000 m^2 dans un km^2.)
SELECT
n.name,
Sum(c.popn_total) / (ST_Area(n.the_geom) / 1000000.0) AS popn_per_sqkm
FROM nyc_census_blocks AS c
84
Chapitre 14. Partie 13 : Exercices sur jointures spatiales
ON ST_Intersects(c.the_geom, n.the_geom)
WHERE n.name = Upper West Side
OR n.name = Upper East Side
GROUP BY n.name, n.the_geom;
name
| popn_per_sqkm
-----------------+-----------------Upper East Side | 47943.3590089405
Upper West Side | 39729.5779474286
14.1. Exercices
85
86
Chapitre 14. Partie 13 : Exercices sur jointures spatiales
CHAPITRE 15
Partie 14 : Lindexation spatiale
Rapellez-vous que lindexation spatiale est lune des trois fonctionnalits cls dune base de donnes
spatiales. Les index permettent lutilisation de grandes quantits de donnes dans une base. Sans lindexation, chaque recherche dentit ncessitera daccder squentiellement tous les enregistrements
de la base de donnes. Lindexation acclre les recherches en organisant les donnes dans des arbres de
recherche qui peuvent tre parcourus efficacement pour retrouver une entit particulire.
Lindexation spatiale lun des plus grands atouts de PostGIS. Dans les exemples prcdents, nous avons
construit nos jointures spatiales en comparant la totalit des tables. Ceci peut parfois savrer trs coteux : raliser la jointure de deux tables de 10000 enregistrements sans indexation ncessitera de comparer 100000000 valeurs, les comparaisons requises ne seront plus que 20000 avec lindexation.
Lorsque nous avons charg la table nyc_census_blocks, loutil pgShapeLoader cre automatiquement un index spatial appel nyc_census_blocks_the_geom_gist.
Pour dmontrer combien il est important dindexer ses donnes pour la performance des requtes, essayons de requter notre table nyc_census_blocks sans utiliser notre index.
La premire tape consiste supprimer lindex.
DROP INDEX nyc_census_blocks_the_geom_gist;
Note : La commande DROP INDEX supprime un index existant de la base de donnes. Pour de plus
amples informations ce sujet, consultez la documentation officielle de PostgreSQL.
Maintenant, regardons le temps dexcution dans le coin en bas droite de linterface de requtage de
pgAdmin, puis lanons la commande suivante. Notre requte recherche les blocs de la rue Broad.
SELECT blocks.blkid
FROM nyc_census_blocks blocks
JOIN nyc_subway_stations subways
ON ST_Contains(blocks.the_geom, subways.the_geom)
WHERE subways.name = Broad St;
blkid
----------------360610007003006
87
La table nyc_census_blocks est trs petite (seulement quelque milliers denregistrements) donc
mme sans lindex, la requte prends 55 ms sur lordinateur de test.
Maintenant remettons en place lindex et lanons de nouveau la requte.
CREATE INDEX nyc_census_blocks_the_geom_gist ON nyc_census_blocks USING GIST (the_geom);
Note : lutilisation de la clause USING GIST spcifie PostgreSQL de crer une structure (GIST)
pour cet index. Si vous recevez un message derreur ressemblant ERROR: index row requires
11340 bytes, maximum size is 8191 lors de la cration, cela signifie sans doute que vous
avez omis la clause USING GIST.
Sur lordinateur de test le temps dexcution se rduit 9 ms. Plus votre table est grande, plus la diffrence de temps dexcution pour une requte utilisant les index augmentera.
15.1 Comment les index spatiaux fonctionnent

Les index des bases de donnes standards crent des arbres hirarchiques bass sur les valeurs des
colonnes indexer. Les index spatiaux sont un peu diffrents - ils ne sont pas capables dindexer des
entits gomtriques elles-mme mais ils indexent leur tendues.
Dans la figure ci-dessus, le nombre de lignes qui intersectent ltoile jaune est unique, la ligne rouge.
Mais ltendue des entits qui intersectent la bote jaune sont deux, la bote rouge et la bote bleue.
La manire dont les bases de donnes rpondent de manire efficace la question Quelles lignes intersectent ltoile jaune ? correspond premirement rpondre la question Quelle tendue intersecte
ltendue jaune en utilisant les index (ce qui est trs rapide) puis calculer le rsultat exact de la question Quelles lignes intersectent ltoile jaune ? seulement en utilisant les entits retournes par le
premier test.
Pour de grandes tables, il y a un systme en deux tapes dvaluation en utilisant dans un premier
temps lapproximation laide dindex, puis en ralisant le test exact sur une quantit bien moins importante de donnes ce qui rduit drastiquement le temps de calcul ncessaire cette deuxime tape.
PotGIS et Oracle Spatial partage la mme notion dindex structur sous la forme darbres R 1 . Les
arbres R classent les donnes sous forme de rectangles, de sous-rectangles etc. Cette structure dindex
gre automatiquement la densit et la taille des objets.
1. http ://postgis.org/support/rtree.pdf
88
Chapitre 15. Partie 14 : Lindexation spatiale
15.2 Requte avec seulement des index

La plupart des fonctions utilises par PostGIS (ST_Contains, ST_Intersects, ST_DWithin, etc) prennent en compte les index automatiquement. Mais certaines fonctions (comme par exemple : ST_Relate)
ne les utilisent pas.
Pour utiliser une recherche par tendue utilisant les index (et pas de filtres), vous pouvez utiliser loprateur &&. Pour les gomtries, loprateur && signifie ltendue recouvre ou touche de la mme
15.2. Requte avec seulement des index
89
manire que loprateur = sur des entiers signifie que les valeurs sont gales.
Essayons de comparer une requte avec seulement un index pour la population du quartier West Village. En utilisant la commande && notre requte ressemble cela :
FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON neighborhoods.the_geom && blocks.the_geom
WHERE neighborhoods.name = West Village;
50325
Maintenant essayons la mme requte en utilisant la fonction plus prcise ST_Intersects.

FROM nyc_neighborhoods neighborhoods
JOIN nyc_census_blocks blocks
ON ST_Intersects(neighborhoods.the_geom, blocks.the_geom)
WHERE neighborhoods.name = West Village;
27141
Un plus faible nombre de rsultats ! La premire requte nous renvoie tous les blocs qui intersectent
ltendue du quartier, la seconde nous renvoie seulement les blocs qui intersectent le quartier lui-mme.
15.3 Analyse
Le planificateur de requte de PostgreSQL choisit intelligemment dutiliser ou non les index pour raliser
une requte. Il nest pas toujours plus rapide dutiliser un index pour raliser une recherche : si la
recherche doit renvoyer lensemble des enregistrements dune table, parcourir lindex pour rcuprer
chaque valeur sera plus lent que de parcourir linairement lensemble de la table.
Afin de savoir dans quelle situation il est ncessaire dutiliser les index (lire une petite partie de la
table plutt quune grande partie), PostgreSQL conserve des statistiques relatives la distribution des
donnes dans chaque colonne indexe. Par dfaut, PostgreSQL rassemble les statistiques sur une base
rgulire. Nanmoins, si vous changez dramatiquement le contenu de vos tables dans une priode courte,
les statistiques ne seront alors plus jour.
Pour vous assurez que les statistiques correspondent bien au contenu de la table actuelle, il est courant
dutiliser la commande ANALYZE aprs un grand nombre de modifications ou de suppression de vos
donnes. Cela force le systme de gestion des statistiques rcuprer lensemble des donnes des
colonnes indexes.
La commande ANALYZE demande PostgreSQL de parcourir la table et de mettre jour les statistiques
utilises par le planificateur de requtes (la planification des requtes sera trait ultrieurement).
ANALYZE nyc_census_blocks;
90
15.4 Nttoyage
Il est souvent stressant de constater que la simple cration dun index nest pas suffisant pour que PostgreSQL lutilise efficacement. Le nettoyage doit tre ralis aprs quun index soit cr ou aprs un
grand nombre de requtes UDATE, INSERT ou DELETE ait t ralis sur une table. La commande
VACUUM demande PostgreSQL de rcuprer chaque espace non utilis dans les pages de la table qui
sont laisses en ltat lors des requtes UPDATE ou DELETE cause du modle destampillage multiversions.
Le nettoyage des donnes est tellement important pour une utilisation efficace du serveur de base de
donnes PostgreSQL quil existe maintenant une option autovacuum.
Active par dfaut, le processus autovacuum nettoie (rcupre lespace libre) et analyse (met jour les
statistiques) vos tables suivant un intervalle donn dtermin par lactivit des bases de donnes. Bien
que cela fonctionne avec les bases de donnes hautement transactionnelles, il nest pas supportable de
devoir attendre que le processus autovacuum se lance lors de la mise jour ou la suppression massive
de donnes. Dans ce cas, il faut lancer la commande VACUUM manuellement.
Le nettoyage et lanalyse de la base de donnes peuvent tre raliss sparment si ncessaire. Utiliser
la commande VACUUM ne mettra pas jour les statistiques alors que lancer la commande ANALYZE ne
rcuprera pas lespace libre des lignes dune table. Chacune de ces commandes peut tre lance sur
lintgralit de la base de donnes, sur une table ou sur une seule colonne.
VACUUM ANALYZE nyc_census_blocks;

geometry_a && geometry_b : retourne TRUE si ltendue de A chevauche celle de B.
geometry_a = geometry_b : retourne TRUE si ltendue de A est la mme que celle de B.
ST_Intersects(geometry_a, geometry_b) : retourne TRUE si la gomtrie a intersecte spatialement la
gomtrie b- (si elles ont une partie en commun) et FALSE sinon (elles sont disjointes).
15.4. Nttoyage
91
92
CHAPITRE 16
Partie 15 : Projections des donnes
La Terre nest pas plate et il ny a pas de moyen simple de la poser plat sur une carte en papier
(ou lcran dun ordinateur). Certaines projections prservent les aires, donc tous les objets ont des
tailles relatives aux autres, dautre projections conservent les angles (conformes) comme la projection
Mercator. Certaines projections tentent de minimiser la distorsion des diffrents paramtres. Le point
commun entre toutes les projections est quelles transforment le monde (sphrique) en un systme plat
de coordonnes cartsiennes, et le choix de la projection dpend de ce que vous souhaitez faire avec vos
donnes.
Nous avons dj rencontr des projections, lorsque nous avons charg les donnes de la ville de Ney
York .Rappelez-vous quelles utilisaient le SRID 26918. Parfois, vous aurez malgr tout besoin de
transformer et de reprojeter vos donnes dun systme de projection un autre, en utilisant la fonction ST_Transform(geometry, srid). Pour manipuler les identifiants de systme de rfrence spatiale
partir dune gomtrie, PostGIS fournit les fonctions ST_SRID(geometry) et ST_SetSRID(geometry,
srid).
Nous pouvons vrifier le SRID de nos donnes avec la commande ST_SRID :
SELECT ST_SRID(the_geom) FROM nyc_streets LIMIT 1;
26918
Et quelle est la dfinition du 26918 ? Comme nous lavons vu lors de la partie chargement des
donnes, la dfinition se trouve dans la table spatial_ref_sys. En fait, deux dfinitions sont
prsentes. La dfinition au format WKT dans la colonne srtext
SELECT * FROM spatial_ref_sys WHERE srid = 26918;
En fait, pour les calculs internes de re-projection, cest le contenu de la colonne proj4text qui est
utilis. Pour notre projection 26918, voici la dfinition au format proj.4 :
SELECT proj4text FROM spatial_ref_sys WHERE srid = 26918;
+proj=utm +zone=18 +ellps=GRS80 +datum=NAD83 +units=m +no_defs
En pratique, les deux colonnes srtext et proj4text sont importantes : la colonne srtext est
utilise par les applications externes comme GeoServer, uDig <udig.refractions.net>_, FME et autres,
alors que la colonne proj4text est principalement utilise par PostGIS en interne.
93
16.1 Comparaison de donnes

Combins, une coordonne et un SRID dfinissent une position sur le globe. Sans le SRID, une coordonne est juste une notion abstraite. Un systme de coordonnes cartsiennes est dfinit comme un
systme de coordonnes plat sur la surface de la Terre. Puisque les fonctions de PostGIS utilisent cette
surface plane, les oprations de comparaison ncessitent que lensemble des objets gomtriques soient
reprsents dans le mme systme, ayant le mme SRID.
Si vous utilis des gomtries avec diffrents SRID vous obtiendrez une erreur comme celle-ci :
SELECT ST_Equals(
ST_GeomFromText(POINT(0 0), 4326),
ST_GeomFromText(POINT(0 0), 26918)
);
ERROR: Operation on two geometries with different SRIDs
CONTEXT: SQL function "st_equals" statement 1
Note : Faites attention de pas utiliser la transformation la vole laide de ST_Transform trop
souvent. Les index spatiaux sont construits en utilisant le SRID inclus dans les gomtries. Si la comparaison est faite avec un SRID diffrent, les index spatiaux ne seront pas (la plupart du temps) utiliss.
Il est reconnu quil vaut mieux choisir un SRID pour toutes les tables de votre base de donnes. Nutilisez la fonction de tranformation que lorsque vous lisez ou crivez les donnes depuis une application
externe.
16.2 Transformer les donnes

Si vous retournez la dfinition au format proj4 du SRID 26918, vous pouvez voir que notre projection
actuelle est de type UTM zone 18 (Universal Transvers Mercator), avec le mtre comme unit de mesure.
+proj=utm +zone=18 +ellps=GRS80 +datum=NAD83 +units=m +no_defs
Essayons de convertir certaines donnes de notre systme de projection dans un systme de coordonnes
gographiques connu comme longitude/latitude.
Pour convertir les donnes dun SRID lautre, nous devons dans un premier temps vrifier que nos
gomtries ont un SRID valide. Une fois que nous avons vrifi cela, nous devons ensuite trouver le
SRID dans lequel nous souhaitons re-projeter. En dautre terme, quel est le SRID des coordonnes
gographiques ?
Le SRID le plus connu pour les coordonnes gographiques est le 4326, qui correspond au couple
longitude/latitude sur la sphrode WGS84. Vous pouvez voir sa dfinition sur le site http ://spatialreference.org.
http ://spatialreference.org/ref/epsg/4326/
Vous pouvez aussi rcuprer les dfinitions dans la table spatial_ref_sys :
SELECT srtext FROM spatial_ref_sys WHERE srid = 4326;
94
Chapitre 16. Partie 15 : Projections des donnes
GEOGCS["WGS 84",
DATUM["WGS_1984",
SPHEROID["WGS 84",6378137,298.257223563,AUTHORITY["EPSG","7030"]],
PRIMEM["Greenwich",0,AUTHORITY["EPSG","8901"]],
UNIT["degree",0.01745329251994328,AUTHORITY["EPSG","9122"]],
AUTHORITY["EPSG","4326"]]
Essayons de convertir les cordonnes de la station Broad St :

SELECT ST_AsText(ST_Transform(the_geom,4326))
POINT(-74.0106714688735 40.7071048155841)
Si vous chargez les donnes ou crez une nouvelle gomtrie sans spcifier de SRID, la valeur du SRID
prendra alors la valeur -1. Rappelez-vous que dans les Partie 8 : Les gometries, lorsque nous avons
cr nos tables gomtriques nous navions pas spcifi un SRID. Si nous interrogeons la base, nous
devons nous attendre ce que toutes les tables prfixes par nyc_ aient le SRID 26918, alors que la
table geometries aura la valeur -1 par dfaut.
Pour visualiser la table dassignation des SRID, interrogez la table geometry_columns de la base
de donnes.
SELECT f_table_name AS name, srid
FROM geometry_columns;
name
| srid
---------------------+------nyc_census_blocks
| 26918
nyc_neighborhoods
| 26918
nyc_streets
| 26918
nyc_subway_stations | 26918
geometries
|
-1
Nanmoins, si vous connaissez le SRID de vos donnes, vous pouvez laffecter par la suite en utilisant la
fonction ST_SetSRID sur les gomtries. Ensuite vous pourrez les transformer dans dautres systmes
de projections.
SELECT ST_AsText(
ST_Transform(
ST_SetSRID(geom,26918),
4326)
)
FROM geometries;

ST_AsText : retourne la reprsentation au format Well-Known Text (WKT) sans la mtadonne SRID.
ST_SetSRID(geometry, srid) : affecte une valeur au SRID dune gomtrie.
ST_SRID(geometry) : retourne lidentifiant du systme de rfrence spatiale dun objet ST_Geometry
comme dfini dans la table spatial_ref_sys.
95
ST_Transform(geometry, srid) : retourne une nouvelle gomtrie aprs avoir re-projet les donnes dans
le systme correspondant au SRID pass en paramtre.
96
Chapitre 16. Partie 15 : Projections des donnes
CHAPITRE 17
Partie 16 : Exercices sur les

projections
Voici un rappel de certaines fonctions que nous avons vu. Elles seront utiles pour les exercices !
sum(expression) agrgation qui retourne la somme dun ensemble de valeurs
ST_SRID(geometry, srid) retourne le SRID dune gomtrie
ST_Transform(geometry, srid) reprojette des gomtries dans un autre systme de rfrence spatiale
ST_GeomFromText(text) retourne un objet geometry
ST_AsText(geometry) retourne un WKT (texte)
ST_AsGML(geometry) retourne un GML (texte)
Rappelez-vous les ressources en ligne :
http ://spatialreference.org
http ://prj2epsg.org
Et les tables disponibles :
nyc_census_blocks
nyc_streets
nyc_subway_stations
name, the_geom
nyc_neighborhoods
17.1 Exercices
Quelle est la longueur des rue de New York, mesure en UTM 18 ?
SELECT Sum(ST_Length(the_geom))
FROM nyc_streets;
10418904.7172
Quelle est la dfinition du SRID 2831 ?
97
SELECT srtext FROM spatial_ref_sys

WHERE SRID = 2831;
Ou, via prj2epsg
PROJCS["NAD83(HARN) / New York Long Island",

GEOGCS["NAD83(HARN)",
DATUM["NAD83 (High Accuracy Regional Network)",
SPHEROID["GRS 1980", 6378137.0, 298.257222101, AUTHORITY["EPSG","7019"]],
TOWGS84[-0.991, 1.9072, 0.5129, 0.0257899075194932, -0.009650098960270402, -0.0
PRIMEM["Greenwich", 0.0, AUTHORITY["EPSG","8901"]],
UNIT["degree", 0.017453292519943295],
AXIS["Geodetic longitude", EAST],
AXIS["Geodetic latitude", NORTH],
PROJECTION["Lambert Conic Conformal (2SP)", AUTHORITY["EPSG","9802"]],
PARAMETER["central_meridian", -74.0],
PARAMETER["latitude_of_origin", 40.166666666666664],
PARAMETER["standard_parallel_1", 41.03333333333333],
PARAMETER["false_easting", 300000.0],
PARAMETER["false_northing", 0.0],
PARAMETER["scale_factor", 1.0],
PARAMETER["standard_parallel_2", 40.666666666666664],
UNIT["m", 1.0],
AXIS["Easting", EAST],
AXIS["Northing", NORTH],
AUTHORITY["EPSG","2831"]]
Quelle est la longueur des rues de New York, mesure en utilisant le SRID 2831 ?
SELECT Sum(ST_Length(ST_Transform(the_geom,2831)))
FROM nyc_streets;
10421993.706374
Note : La diffrence entre les mesure en UTM 18 et en Stateplane Long Island est de
(10421993 - 10418904)/10418904, soit 0.02%. Calcul sur la sphrode en utilissant en
Partie 17 : Coordonnes gographiques, le total des longueurs des routes est 10421999,
ce qui est proche de la valeur dans lautre systme de projection (Stateplane Long Island).
Ce dernier est prcisment calibr pour une petite zone gographique (la ville de New
York) alors que le systme UTM 18 doit fournir un rsultat raisonnable pour une zone
rgionale beaucoup plus large.
Quelle est la reprsentation KML du point de la station de mtris Broad St ?
SELECT ST_AsKML(the_geom)
<Point><coordinates>-74.010671468873468,40.707104815584088</coordinates></Point>
H ! les coordonnes sont gographiques bien que nous nayons pas fait appel la fonction
ST_Transform, mais pourquoi ? Parce que le standard KML spcifie que toutes les coordonnes doivent
tre gographiques (en fait, dans le systme EPSG :4326), donc la fonction ST_AsKML ralise la transformation automatiquement.
98
Chapitre 17. Partie 16 : Exercices sur les projections
CHAPITRE 18
Partie 17 : Coordonnes
gographiques
Il est trs frquent de manipuler des donnes coordonnes gographiques ou de longitude/latitude.

Au contraire des coordonnes de type Mercator, UTM ou Stateplane, les coordonnes gographiques ne
reprsentent pas une distance linaire depuis une origine, tel que dans un plan. Elles dcrivent la distance
angulaire entre lquateur et les ples. Dans les sytmes de coordonnes sphriques, un point est spcifi
par son rayon (distance lorigine), son angle de rotation par rapport au mridien plan, et son angle par
rapport laxe plaire.
Vous pouvez continuer utiliser des coordonnes gographiques comme des coordonnes cartsiennes
approximatives pour vos analyses spatiales. Par contre les mesures de distances, daires et de longueurs
seront errones. Etant donn que les coordonnes sphriques mesurent des angles, lunit est le degr.
Par exemple, les rsultats cartsien approximatifs de tests tels que intersects et contains peuvent
savrer terriblement faux. Par ailleurs, plus une zone est situe prs du ple ou de la ligne de date
internationale, plus la distance entre les points est agrandie.
Voici par exemple les coordonnes des villes de Los Angeles et Paris.
Los Angeles : POINT(-118.4079 33.9434)
Paris : POINT(2.3490 48.8533)
99
La requte suivante calcule la distance entre Los Angeles et Paris en utilisant le systme cartsien standard de PostGIS ST_Distance(geometry, geometry). Notez que le SRID 4326 dclare un systme de
rfrence spatiale gographique.
SELECT ST_Distance(
ST_GeometryFromText(POINT(-118.4079 33.9434), 4326), -- Los Angeles (LAX)
ST_GeometryFromText(POINT(2.5559 49.0083), 4326)
-- Paris (CDG)
);
121.898285970107
Aha ! 121 ! Mais, que veut dire cela ?

Lunit pour SRID 4326 est le degr. Donc la rponse signifie 121 degrs. Sur une sphre, la taille dun
degr au carr est assez variable. Elle devient plus petite au fur et mesure que lon sloigne de
lquateur. Pensez par exemple aux mridiens sur le globe qui se resserrent entre eux au niveau des
ples. Donc une distance de 121 degrs ne veut rien dire !
Pour calculer une distance ayant du sens, nous devons traiter les coordonnes gographiques non
pas comme des coordonnes cartsiennes approximatives, mais plutt comme de relles coordonnes
sphriques. Nous devons mesurer les distances entre les points comme de vrais chemins par dessus une
sphre, comme une portion dun grand cercle.
Depuis sa version 1.5, PostGIS fournit cette fonctionnalit avec le type geography.
Note : Diffrentes bases de donnes spatiales dveloppent diffrentes approches pour manipuler les
coordonnes gographiques.
Oracle essaye de mettre jour la diffrence de manire transparente en lanant des calculs lorsque le
SRID est gographique.
SQL Server utilise deux types spatiaux, STGeometry pour les coordonnes cartsiens et STGeography pour les coordonnes gographqiues.
Informix Spatial est une pure extension cartsienne dInformix, alors quInformix Geodetic est une
pure extension gographique.
Comme SQL Server, PostGIS utilise deux types : geometry et geography.
En utilisant le type geography plutot que geometry, essayons s nouveau de mesurer la distance
entre Los Angeles et Paris. Au lieu de la commande ST_GeometryFromText(text), nous utiliserons
cette fois ST_GeographyFromText(text).
SELECT ST_Distance(
ST_GeographyFromText(POINT(-118.4079 33.9434)), -- Los Angeles (LAX)
ST_GeographyFromText(POINT(2.5559 49.0083))
-- Paris (CDG)
);
9124665.26917268
Toutes les valeurs retournes tant en mtres, notre rponse est donc 9124 kilomtres.
Les versions plus anciennes de PostGIS supportaient uniquement des calculs sur sphre trs basiques
comme la fonction ST_Distance_Spheroid(point, point, measurement). Celle-ci est trs limite et ne
fonctionne uniquement sur des points. Elle ne supporte pas non plus lindexation au niveau des ples ou
de la ligne de date internationale.
Le besoin du support des autres types de gomtries se fit ressentir lorsquil sagissait de rpondre
des questions du type A quelle distance la ligne de vol dun avion Los Angeles/Paris passe-t-elle de
lIslande ?
100
Chapitre 18. Partie 17 : Coordonnes gographiques
Rpondre cette question en travaillant avec un plan cartsien fournit une trs mauvaise rponse en
effet ! En utilisant la ligne rouge, nous obtenons une bien meilleure rponse. Si nous convertissons notre
vol LAX-CDG en une ligne et que nous calculons la distance un point en Islande, nous obtiendrons la
rponse exacte, en mtres.
SELECT ST_Distance(
ST_GeographyFromText(LINESTRING(-118.4079 33.9434, 2.5559 49.0083)), -- LAX-CDG
ST_GeographyFromText(POINT(-21.8628 64.1286))
-- Iceland
);
531773.757079116
Donc le point le plus proche de lIslande pendant le vol LAX-CDG est de 532 kilomtres.
Lapproche cartsienne pour manipuler les coordonnes gographiques perd tout son sens pour les objets
situs au dessus de la ligne de date internationale. La route sphrique la plus courte entre Los-Angeles
et Tokyo traverse locan Pacifique. La route cartsienne la plus courte traverse quant elle les ocans
Atlantique et Indien.
SELECT ST_Distance(
ST_GeometryFromText(Point(-118.4079 33.9434)),
-- LAX
101
ST_GeometryFromText(Point(139.733 35.567)))
-- NRT (Tokyo/Narita)
AS geometry_distance,
ST_Distance(
ST_GeographyFromText(Point(-118.4079 33.9434)), -- LAX
ST_GeographyFromText(Point(139.733 35.567)))
-- NRT (Tokyo/Narita)
AS geography_distance;
geometry_distance | geography_distance
-------------------+-------------------258.146005837336 |
8833954.76996256
18.1 Utiliser le type Geography

Afin dimporter des donnes dans une table de type geography, les objets gographiques doivent
dabord tre projets dans le systme EPSG :4326 (longitude/latitude), ensuite ils doivent tre convertis
en objets de type geography. La fonction ST_Transform(geometry,srid) convertit les coordonnes
en geography et la fonction Geography(geometry) change le type (cast) de gomtrie gographie.
CREATE TABLE nyc_subway_stations_geog AS
SELECT
Geography(ST_Transform(the_geom,4326)) AS geog,
name,
routes
FROM nyc_subway_stations;
La construction dune indexation spatiale sur une table stockant des objets de type geography est
exactement identique la mthode employe pour les gomtries :
CREATE INDEX nyc_subway_stations_geog_gix
ON nyc_subway_stations_geog USING GIST (geog);
La diffrence est camoufle : lindexation des objets de type geography gre correctement les requtes
qui recouvrent les ples ou traversent les fuseaux horaires, alors que les gomtries ne le supporteront
pas.
Il ny a quun petit nombre de fonctions disponibles pour le type geography :
ST_AsText(geography) retourne la reprsentation textuelle
ST_GeographyFromText(text) retourne un objet de type geography
ST_AsBinary(geography) retourne la reprsentation binaire bytea
ST_GeogFromWKB(bytea) retourne un objet de type geography
ST_AsSVG(geography) retourne text
ST_AsGML(geography) retourne text
ST_AsKML(geography) retourne text
ST_AsGeoJson(geography) retourne text
ST_Distance(geography, geography) retourne double
ST_DWithin(geography, geography, float8) retourne boolean
ST_Area(geography) retourne double
ST_Length(geography) retourne double
ST_Covers(geography, geography) retourne boolean
ST_CoveredBy(geography, geography) retourne boolean
ST_Intersects(geography, geography) retourne boolean
102
ST_Buffer(geography, float8) retourne geography 1

ST_Intersection(geography, geography) retourne geography 1
18.2 Cration dune table stockant des gographies

Le code SQL permettant la cration dune nouvelle table avec une colonne de type geography ressemble la cration dune table stockant des gomtries. Cependant, les objets de type geography permettent de spcifier directement le type dobjet gographique la cration de la table. Par exemple :
CREATE TABLE airports (
code VARCHAR(3),
geog GEOGRAPHY(Point)
);
INSERT INTO airports VALUES (LAX, POINT(-118.4079 33.9434));
INSERT INTO airports VALUES (CDG, POINT(2.5559 49.0083));
INSERT INTO airports VALUES (REK, POINT(-21.8628 64.1286));
Lors de la dfinition le type GEOGRAPHY(Point) spcifie que nos aroports sont des points.
Le nouveau champ gographie nest pas rfrenc dans la table geometry_columns. Le stockage des mtadonnes relatives aux donnes de type geography seffectue dans une vue appele
geography_columns qui est maintenue jour automatiquement sans avoir besoin dutiliser des
fonctions comme geography_columns.
SELECT * FROM geography_columns;
f_table_name
| f_geography_column | srid |
type
-------------------------------+--------------------+------+---------nyc_subway_stations_geography | geog
|
0 | Geometry
airports
| geog
| 4326 | Point
Note : La possibilit de dfinir les types et le SRID lors de la cration de la table (requte CREATE), et
la mise jour automatique des mtadonnes geometry_columns sont des fonctionalits qui seront
adaptes pour le type gomtrie pour la version 2.0 de PostGIS.
18.3 Conversion de type

Bien que les fonctions de base qui sappliquent au type geography puissent tre utilises dans un
grand nombre de cas dutilisation, il est parfois ncessaire daccder aux autres fonctions qui ne supportent que le type gomtrie. Heureusement, il est possible de convertir des objets de type gomtrie en
des objets de types gographie et inversement.
1. Les fonctions buffer et intersection sont actuellement construites sur le principe de conversion de type en gomtries, et
ne sont pas actuellement capable de grer des coordonnes sphriques. Il en rsulte quelles peuvent ne pas parvenir retourner
un rsultat correcte pour des objets ayant une grande tendue qui ne peut tre reprsent correctement avec une reprsentation
planaire.
Par exemple, la fonction ST_Buffer(geography,distance) transforme les objets gographiques dans la meilleure projection, cre la zone tampon, puis les transforme nouveau en des gographies. Sil ny a pas de meilleure projection (lobjet
est trop vaste), lopration peut ne pas russir retourner une valeur correcte ou retourner un tampon mal form.
18.2. Cration dune table stockant des gographies
103
La syntaxe habituelle de PostgreSQL pour les conversion de type consiste ajouter la valeur la chane
suivante ::typename. Donc, 2::text convertit la valeur numrique deux en une chane de caractres 2. La commande : POINT(0 0)::geometry convertira la reprsentation textuelle dun
point en une point gomtrique.
La fonction ST_X(point) supporte seulement le type gomtrique. Comment lire la coordonne X dune
de nos gographie ?
SELECT code, ST_X(geog::geometry) AS longitude FROM airports;
code | longitude
------+----------LAX | -118.4079
CDG |
2.5559
REK | -21.8628
En ajoutant la chane ::geometry notre valeur gographique, nous la convertissons en une gographie ayant le SRID : 4326. partir de maintenant, nous pouvons utiliser autant de fonctions sappliquant aux gomtries que nous le souhaitons. Mais, souvenez-vous - maintenant que nos objets sont
des gomtries, leur coordonnes seront interprtes comme des coordonnes cartsiennes, non pas
sphriques.
18.4 Pourquoi (ne pas) utiliser les gographies

Les gographies ont des coordonnes universellement acceptes - chacun peut comprendre que
reprsente la latitude et la longitude, mais peu de personne comprennent ce que les coordonnes UTM
signifient. Pourquoi ne pas tout le temps utiliser des gographies ?
Premirement, comme indiqu prcdemment, il ny a que quelques fonctions qui supportent ce type
de donnes. Vous risquez de perdre beaucoup de temps contourner les problmes lis la nondisponibilit de certaines fonctions.
Deuximement, les calculs sur une sphre sont plus consomateurs en ressource que les mmes calculs
dans un systme cartsien. Par exemple, la formule de calcul de distance (Pythagore) entrane un seul
appel la fonction racine carr (sqrt()). La formule de calcul de distance sphrique (Haversine) utilise
deux appels la fonction racine carr, et un appel arctan(), quatre appels sin() et deux cos(). Les
fonctions trigonomtriques sont trs coteuses, et les calculs sphriques les utilisent massivement.
Quel conclusion en tirer ?
Si vos donnes sont gographiquement compactes (contenu lintrieur dun tat, dun pays ou dune
ville), utilisez le type geometry avec une projection cartsienne qui est pertinente pour votre localisation. Consultez le site http ://spatialreference.org et tapez le nom de votre rgion pour visualiser la liste
des systmes de projection applicables dans votre cas.
Si, dun autre cot, vous avez besoin de calculer des distances qui sont gographiquement parses (recouvrant la plupart du monde), utilisez le type geography. La complexit de lapplication vite en
travaillant avec des objets de type geography dpassera les problmes de performances. La conversion
de type en gomtrie permettra de dpasser les limites des fonctionnalits proposes pour ce type.

ST_Distance(geometry, geometry) : Pour le type gomtrie, renvoie la distance cartsienne, pour les
gographies la distance sphrique en mtres.
104
ST_GeographyFromText(text) : Retourne la valeur gographique partir dune reprsentation en WKT

ou EWKT.
ST_Transform(geometry, srid) : Retourne une nouvelle gomtrie avec ses coordonnes reprojetes dans
le systme de rfrence spatial rfrenc par le SRID fourni.
ST_X(point) : Retourne la coordonne X dun point, ou NULL si non disponible. La valeur passe doit
tre un point.
105
106
CHAPITRE 19
Partie 18 : Fonctions de construction

de gomtries
Toute les fonctions que nous avons vu jusqu prsent traitent les gomtries comme elles sont et
retournent :
une analyse des objets (ST_Length(geometry), ST_Area(geometry)),
une srialisation des objets (ST_AsText(geometry), ST_AsGML(geometry)),
une partie de lobjet (ST_RingN(geometry,n)) ou
un rsultat vrai/faux (ST_Contains(geometry,geometry), ST_Intersects(geometry,geometry)).
Les fonctions de construction de gomtries prennent des gomtries en entre et retourne de nouvelles
formes.
19.1 ST_Centroid / ST_PointOnSurface

Un besoin commun lors de la cration de requtes spatiales est de remplacer une entit polygonale par
un point reprsentant cette entit. Cela est utile pour les jointures spatiales (comme indiqu ici : Polygones/Jointures de polygones) car utiliser ST_Intersects(geometry,geometry) avec deux polygones
impliquera un double comptage : un polygone pour le contour externe intersectera dans les deux sens ;
le remplacer par un point le forcera tre dans un seul sens, pas les deux.
ST_Centroid(geometry) retourne le point qui est approximativement au centre de la masse de la
gomtrie passe en paramtre. Cest un calcul simple et rapide, mais parfois non profitable, car
le point retourn peut se trouver lextrieur de lentit elle-mme. Si lentit fournie est convexe
(imaginez la lettre C) le centrode renvoy pourrait ne pas tre lintrieur du polygone.
ST_PointOnSurface(geometry) retourne un point qui est obligatoirement dans lentit passe en
paramtre. Cette fonction cote plus cher en ressource que le calcul du centrode.
107
19.2 ST_Buffer
Lopration de zone tampon est souvent disponible dans les outils SIG, il est aussi disponible dans
PostGIS. La fonction ST_Buffer(geometry,distance) prend en paramtre une gomtrie et une distance
et retourne une zone tampon dont le contour est une distance donne de la gomtrie dorigine.
108
Chapitre 19. Partie 18 : Fonctions de construction de gomtries
Par exemple, si les services des parcs amricains souhaitaient renforcer la zone du traffic maritime
autour de lle Liberty, ils pourraient construire une zone tampon de 500 mtres autour de lle. Lle
de Liberty est reprsente par un seul bloc dans notre table nyc_census_blocks, nous pouvons
donc facilement raliser ce calcul.
-- Cration dune nouvelle table avec une zone tampon de 500 m autour de Liberty Island
CREATE TABLE libery_island_zone AS
SELECT ST_Buffer(the_geom,500) AS the_geom
WHERE blkid = 360610001009000;
-- Mise jour de la table geometry_columns
19.2. ST_Buffer
109
La fonction ST_Buffer permet aussi dutiliser des valeurs ngatives pour le paramtre distance et construit un polygone inclus dans celui pass en paramtre. Pour les points et les lignes vous obtiendrez
simplement un rsultat vide.
110
19.3 ST_Intersection
Une autre opration classique prsente dans les SIG - le chevauchement - cre une nouvelle entit en
calculant la zone correspondant lintersection de deux polygones superposs. Le rsultat la proprit
de permettre de reconstruire les entits de base laide de ce rsultat.
La fonction ST_Intersection(geometry A, geometry B) retourne la zone gographique (ou une ligne,
ou un point) que les deux gomtries ont en commun. Si les gomtries sont disjointes, la fonction
retourne une gomtrie vide.
-- Quelle est laire que ces deux cercles ont en commun ?
-- Utilisons la fonction ST_Buffer pour crer ces cercles !
SELECT ST_AsText(ST_Intersection(
ST_Buffer(POINT(0 0), 2),
ST_Buffer(POINT(3 0), 2)
));
19.4 ST_Union
Dans lexemple prcdent, nous intersections des gomtries, crant une nouvelle gomtrie unique
partir de deux entits. La commande ST_Union fait linverse, elle prend en paramtre des gomtries et
supprime les parties communes. Il y a deux versions possibles de la fonction ST_Union :
ST_Union(geometry, geometry) : une version avec deux paramtres qui prend les gomtries et
retourne lunion des deux. Par exemple, nos deux cercles ressemblent ce qui suit si nous utilisons
lopration union plutt que lintersection.
-- Quelle est laire totale de ces deux cercles ?
-- Utilisons ST_Buffer pour crer les cercles !
SELECT ST_AsText(ST_Union(
ST_Buffer(POINT(0 0), 2),
ST_Buffer(POINT(3 0), 2)
));
19.3. ST_Intersection
111
ST_Union([geometry]) : une version agrge qui prendre un ensemble de gomtries et retourne

une gomtrie contenant lensemble des gomtries rassembles. La fonction agrge ST_Union peut
tre utilis grce au SQL GROUP BY pour crer un ensemble rassemblant des sous-ensembles de
gomtries basiques. Cela est trs puissant.
Comme exemple pour la fonction dagrgation ST_Union, considrons notre table
nyc_census_blocks. Les gographie du recensement sont construites de manire ce quon
puisse crer dautres gographies partir des premires. ainsi, nous pouvons crer une carte des secteurs
de recensement en fusionnant les blocs que forme chaque secteur (comme nous le ferons aprs dans la
cration des tables secteurs). Ou, nous pouvons crer une carte du comt en fusionnant les blocs qui
relvent de chaque comt.
Pour effectuer la fusion, notez que la cl unique blkid incorpore des informations sur les gographies
de niveau suprieur. Voici les parties de la cl pour Liberty Island que nous avons utilis prcdemment.
360610001009000 = 36 061 00100 9000
36
061
000100
9
000
=
=
=
=
=
State of New York

New York County (Manhattan)
Census Tract
Census Block Group
Census Block
Ainsi, nous pouvons crer une carte du comt en fusionnant toutes les gomtries qui partagent les 5
premiers chiffres de blkid.
-- Cration dune table nyc_census_counties en regroupant les blocs
CREATE TABLE nyc_census_counties AS
SELECT
ST_Union(the_geom) AS the_geom,
SubStr(blkid,1,5) AS countyid
GROUP BY countyid;
112
Un test de surface peut confirmer que notre opration dunion na pas perdu de gomtries. Tout dabord,
nous calculons la surface de chacun des blocs de recensement et faisons la somme de ces surfaces en les
groupant par lidentifiant de recensement des comts.
SELECT SubStr(blkid,1,5) AS countyid, Sum(ST_Area(the_geom)) AS area
GROUP BY countyid;
countyid |
area
----------+-----------------36005
| 109807439.720947
36047
| 184906575.839355
36061
| 58973521.6225586
36081
| 283764734.207275
36085
| 149806077.958252
Ensuite nous calculons laire de chaque zone de nos nouveaux polygones de rgion de la table count :
SELECT countyid, ST_Area(the_geom) AS area
FROM nyc_census_counties;
countyid |
area
----------+-----------------36005
| 109807439.720947
36047
| 184906575.839355
36061
| 58973521.6225586
36081
| 283764734.207275
36085
| 149806077.958252
La mme rponse ! Nous avons construit avec succs une table des rgions de NYC partir de nos
donnes initiales.
19.4. ST_Union
113

ST_AsText(text) : retourne la reprsentation Well-Known Text (WKT) de la gomtrie/gographie sans
mtadonne SRID.
ST_Buffer(geometry, distance) : Pour les gomtries : retourne une gomtrie qui reprsente tous les
points dont la distance depuis cette gomtrie est infrieure ou gale la distance utilise. Les calculs se
font dans le systme de rfrence spatial de cette gomtrie. Pour les gographies : utilise une fonction
de transformation planaire pour effectuer le calcul.
ST_Intersection(geometry A, geometry B) : retourne une gomtrie qui reprsente la portion commune
des gomtries A et B. Limplmentation du type gographie fait une transformation vers une gomtrie
pour faire lintersection puis reprojette le rsultat en WGS84.
ST_Union() : Renvoie un objet gomtrique qui reprsente lensemble dunion des objets gomtriques
dsigns.
substring(string [from int] [for int]) : Fonction de chane PostgreSQL pour extraire une sous-chane de
caractres.
sum(expression) : Fonction dagrgation PostgreSQL qui retourne la somme des valeurs dune colonne
dans un ensemble denregistrements.
114
CHAPITRE 20
Partie 19 : Plus de jointures spatiales
Dans la partie prcdente nous avons vu les fonctions ST_Centroid(geometry) et

ST_Union(geometry) ainsi que quelques exemples simples. Dans cette partie nous raliserons
des choses plus labores.
20.1 Cration de la table de traage des recensements

Dans le rpertoire \data\ des travaux pratiques, il y a un fichier qui contient des donnes attributaires,
mais pas de gomtries, ce fichier est nomm nyc_census_sociodata.sql. La table contient des
donnes sociaux-conomiques intressantes propos de New York : revenus financiers, ducation .... Il
y a juste un problme, les donnes sont rassembles en trace de recensement et nous navons pas de
donnes spatiales associes !
Dans cette partie nous allons
Charger la table nyc_census_sociodata.sql
Crer une table spatiale pour les traces de recensement
Joindre les donnes attributaires nos donnes spatiales
Raliser certaines analyses sur nos nouvelles donnes
20.1.1 Chargement du fichier nyc_census_sociodata.sql

1. Ouvrez la fentre de requtage SQL depuis PgAdmin
2. Slectionnez
File->Open
depuis
nyc_census_sociodata.sql
le
menu
et
naviguez
jusquau
fichier
3. Cliquez sur le bouton Run Query

4. Si vous cliquez sur le bouton Refresh depuis PgAdmin, la liste des tables devrait contenir votre
nouvelle table nyc_census_sociodata
20.1.2 Cration de la table traces de recensement

Comme nous lavons fait dans la partie prcdente, nous pouvons construire des gomtries de niveau
supprieur en utilisant nos blocs de base en utilisant une partie de la clef blkid. Afin de calculer les
traces de recensement, nous avons besoin de regrouper les blocs en uitlisant les 11 premiers caractres
de la colonne blkid.
115
360610001009000 = 36 061 00100 9000

36
061
000100
9
000
=
=
=
=
=
State of New York

New York County (Manhattan)
Census Tract
Census Block Group
Census Block
Cration de la nouvelle table en utilisant la fonction dagrgation ST_Union :

-- Cration de la table
CREATE TABLE nyc_census_tract_geoms AS
SELECT
ST_Union(the_geom) AS the_geom,
SubStr(blkid,1,11) AS tractid
GROUP BY tractid;
-- Indexation du champ tractid
CREATE INDEX nyc_census_tract_geoms_tractid_idx ON nyc_census_tract_geoms (tractid);
20.1.3 Regrouper les donnes attributaires et spatiales

Lobjectif est ici de regrouper les donnes spatiales que nous avons cr avec les donnes attributaires
que nous avions charg initialement.
-- Cration de la table
CREATE TABLE nyc_census_tracts AS
SELECT
g.the_geom,
a.*
FROM nyc_census_tract_geoms g
JOIN nyc_census_sociodata a
ON g.tractid = a.tractid;
-- Indexation des gomtries
CREATE INDEX nyc_census_tract_gidx ON nyc_census_tracts USING GIST (the_geom);
20.1.4 Rpondre une question intressante

Rpondre une question intressante ! Lister les 10 meilleurs quartiers ordonns par la proportion de
personnes ayant acquis un diplme.
SELECT
Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct,
n.name, n.boroname
FROM nyc_neighborhoods n
JOIN nyc_census_tracts t
116
Chapitre 20. Partie 19 : Plus de jointures spatiales
ON ST_Intersects(n.the_geom, t.the_geom)
WHERE t.edu_total > 0
GROUP BY n.name, n.boroname
ORDER BY graduate_pct DESC
LIMIT 10;
Nous sommons les statistiques qui nous intressent, nous les divisons ensuite la fin. Afin dviter
lerreur de non-division par zro, nous ne prenons pas en compte les quartiers qui nont aucune personne
ayant obtenu un diplme.
graduate_pct |
name
| boroname
--------------+-------------------+----------40.4 | Carnegie Hill
| Manhattan
40.2 | Flatbush
| Brooklyn
34.8 | Battery Park
| Manhattan
33.9 | North Sutton Area | Manhattan
33.4 | Upper West Side
| Manhattan
33.3 | Upper East Side
| Manhattan
32.0 | Tribeca
| Manhattan
31.8 | Greenwich Village | Manhattan
29.8 | West Village
| Manhattan
29.7 | Central Park
| Manhattan
20.2 Polygones/Jointures de polygones

Dans notre requte intressante (dans Rpondre une question intressante) nous avons utilis la fonction ST_Intersects(geometry_a, geometry_b) pour dterminer quelle entit polygonale inclure dans
chaque groupe de quartier. Ce qui nous conduit la question : que ce passe-t-il si une entit tombe entre
deux quartiers ? Il intersectera chacun dentre eux et ainsi sera inclut dans chacun des rsultats.
20.2. Polygones/Jointures de polygones
117
Pour viter ce cas de double comptage il existe trois mthodes :

La mthode simple consiste a sassurer que chaque entit ne se retrouve que dans un seul groupe
gographique (en utilisant ST_Centroid(geometry))
La mthode complexe consiste disviser les parties qui se croisent en utilisant les bordures (en utilisant ST_Intersection(geometry,geometry))
Voici un exemple dutilisation de la mthode simple pour viter le double comptage dans notre requte
prcdente :
SELECT
Round(100.0 * Sum(t.edu_graduate_dipl) / Sum(t.edu_total), 1) AS graduate_pct,
n.name, n.boroname
FROM nyc_neighborhoods n
JOIN nyc_census_tracts t
ON ST_Contains(n.the_geom, ST_Centroid(t.the_geom))
WHERE t.edu_total > 0
GROUP BY n.name, n.boroname
ORDER BY graduate_pct DESC
LIMIT 10;
Remarquez que la requte prend plus de temps sexcuter, puisque la fonction ST_Centroid doit tre
effectue pour chaque entit.
graduate_pct |
name
| boroname
--------------+-------------------+----------49.2 | Carnegie Hill
| Manhattan
39.5 | Battery Park
| Manhattan
34.3 | Upper East Side
| Manhattan
33.6 | Upper West Side
| Manhattan
32.5 | Greenwich Village | Manhattan
32.2 | Tribeca
| Manhattan
31.3 | North Sutton Area | Manhattan
30.8 | West Village
| Manhattan
30.1 | Downtown
| Brooklyn
28.4 | Cobble Hill
| Brooklyn
viter le double comptage change le rsultat !
20.3 Jointures utilisant un large rayon de distance

Une requte quil est sympa de demander est : Comment les temps de permutation des gens proches
(dans un rayon de 500 mtres ) des stations de mtro diffrent de ceux qui en vivent loin ?
Nanmoins, la question rencontre les mmes problmes de double comptage : plusieurs personnes seront
dans un rayon de 500 mtres de plusieurs stations de mtro diffrentes. Comparons la population de New
York :
8008278
Avec la population des gens de New York dans un rayon de 500 mtres dune station de mtro :
118
FROM nyc_census_blocks census
JOIN nyc_subway_stations subway
ON ST_DWithin(census.the_geom, subway.the_geom, 500);
10556898
Il y a plus de personnes proches du mtro quil y a de personnes ! Clairement, notre requte SQL simple
rencontre un gros problme de double comptage. Vous pouvez voir le problme en regardant limage
des zones tampons cres pour les stations.
La solution est de sassurer que nous avons seulement des blocs distincts avant de les regrouper. Nous
pouvons raliser cela en cassant notre requte en sous-requtes qui rcuprent les blocs distincts, les
regroupent pour ensuite retourner notre rponse :
FROM (
SELECT DISTINCT ON (blkid) popn_total
FROM nyc_census_blocks census
JOIN nyc_subway_stations subway
ON ST_DWithin(census.the_geom, subway.the_geom, 500)
) AS distinct_blocks;
4953599
Cest mieux ! Donc un peu plus de 50 % de la population de New York vit proximit (500m, environ 5
7 minutes de marche) du mtro.
20.3. Jointures utilisant un large rayon de distance
119
120
CHAPITRE 21
Partie 20 : Validit
Dans 90% des cas la rponse la question pourquoi mes requtes me renvoient un message derreur
du type TopologyException error est : un ou plusieurs des arguments passs sont invalides. Ce qui
nous conduit nous demander : que signifie invalide et pourquoi est-ce important ?
21.1 Quest-ce que la validit ?

La validit est surtout importante pour les polygones, qui dfinissent des aires et requirent une bonne
structuration. Les lignes sont vraiment simples et ne peuvent pas tre invalides ainsi que les points.
Certaines des rgles de validation des polygones semble videntes, et dautre semblent arbitraires (et le
sont vraiment).
Les contours des polygones doivent tre ferms.
Les contours qui dfinissent des trous doivent tre inclus dans la zone dfinie par le contour extrieur.
Les contours ne doivent pas sintersecter (ils ne doivent ni se croiser ni se toucher).
Les contours ne doivent pas toucher les autres contours, sauf en un point unique.
Les deux dernires rgles font partie de la catgorie arbitraire. Il y a dautres moyens de dfinir des
polygones qui sont consistants mais les rgles ci-dessus sont celles utilises dans le standard OGC
SFSQL que respecte PostGIS.
La raison pour laquelle ces rgles sont importantes est que les algorithmes de calcul dpendent de cette
structuration consistante des arguments. Il est possible de construire des algorithmes qui nutilisent
pas cette structuration, mais ces fonctions tendent tre trs lentes, tant donn que la premire tape
consiste analyser et construire des structures lintrieur des donnes.
Voici un exemple de pourquoi cette structuration est importante. Ce polygone nest pas valide :
POLYGON((0 0, 0 1, 2 1, 2 2, 1 2, 1 0, 0 0));
Vous pouvez comprendre ce qui nest pas valide en regardant cette figure :
121
Le contour externe est exactement en forme en 8 avec une intersection au milieu. Notez que la fonction
de rendu graphique est tout de mme capable den afficher lintrieur, donc visuellement cela ressemble
bien une aire : deux units carr, donc une aire couplant ces deux units.
Essayons maintenant de voir ce que pense la base de donnes de notre polygone :
SELECT ST_Area(ST_GeometryFromText(POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0, 0 0
st_area
--------0
Que ce passe-t-il ici ? Lalgorithme qui calcule laire suppose que les contours ne sintersectent pas. Un
contour normal devra toujours avoir une aire qui est borne (lintrieur) dans un sens de la ligne du
contour (peu importe quelle sens, juste un sens). Nanmoins, dans notre figure en 8, le contour externe
est droite de la ligne pour un lobe et gauche pour lautre. Cela entraine que les aires qui sont calcules
pour chaque lobe annulent la prcdente (lune vaut 1 et lautre -1) donc le rsultat est une aire de zro.
21.2 Dtecter la validit

Dans lexemple prcdent nous avions un polygone que nous savions non-valide. Comment dterminer les gomtries non valides dans une tables dun million denregistrements ? Avec la fonction
ST_IsValid(geometry) utilise avec notre polygone prcdent, nous obtenons rapidement la rponse :
SELECT ST_IsValid(ST_GeometryFromText(POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1, 1 0,
122
Chapitre 21. Partie 20 : Validit
Maintenant nous savons que lentit est non-valide mais nous ne savons pas pourquoi. Nous pouvons
utiliser la fonction ST_IsValidReason(geometry) pour trouver la cause de non validit :
SELECT ST_IsValidReason(ST_GeometryFromText(POLYGON((0 0, 0 1, 1 1, 2 1, 2 2, 1 2, 1 1,
Self-intersection[1 1]
Vous remarquerez quen plus de la raison (intersection) la localisation de la non validit (coordonne (1
1)) est aussi renvoye.
Nous pouvons aussi utiiliser la fonction ST_IsValid(geometry) pour tester nos tables :
-- Trouver tous les polygones non valides et leur problme
SELECT name, boroname, ST_IsValidReason(the_geom)
WHERE NOT ST_IsValid(the_geom);
name
|
boroname
|
st_isvalidreason
-------------------------+---------------+---------------------------------------------Howard Beach
| Queens
| Self-intersection[597264.083368305 4499924.54
Corona
| Queens
Steinway
| Queens
Red Hook
| Brooklyn
21.3 Rparer les invalides

Commenons par la mauvaise nouvelle : il ny a aucune garantie de pouvoir corriger une gomtrie non
valide. Dans le pire des scnarios, vous pouvez utiliser la fonction ST_IsValid(geometry) pour identifier
les entits non valides, les dplacer dans une autre table, exporter cette table et les rparer laide dun
outil extrieur.
Voici un exemple de requte SQL qui dplace les gomtries non valides hors de la table principale dans
une table part pour les exporter vers un programme de rparation.
-- Table part des gomtries non-valides
CREATE TABLE nyc_neighborhoods_invalid AS
SELECT * FROM nyc_neighborhoods
-- Suppression de la table principale
DELETE FROM nyc_neighborhoods
Un bon outil pour rparer visuellement des gomtries non valide est OpenJump (http ://openjump.org)
qui contient un outils de validation depuis le menu Tools->QA->Validate Selected Layers.
Maintenant, la bonne nouvelle : un grand nombre de non-validits peut tre rsolu dans la base de
donnes en utilisant la fonction : ST_Buffer.
Le coup du Buffer tire avantage de la manire dont les buffers sont construits : une gomtrie bufferise
est une nouvelle gomtrie, construite en dplaant les lignes de la gomtrie dorigine. Si vous dplacez
21.3. Rparer les invalides
123
les lignes originales par rien (zero) alors la nouvelle gomtrie aura une structure identique loriginale,
mais puisquelle utilise les rgles topologiques de lOGC, elle sera valide.
Par exemple, voici un cas classique de non-validit - le polygone de la banane - un seul contour que
cre une zone mais se touche, laissant un trou qui nen est pas un.
POLYGON((0 0, 2 0, 1 1, 2 2, 3 1, 2 0, 4 0, 4 4, 0 4, 0 0))
En crant un buffer de zero sur le polygone retourne un polygone OGC valide, le contour externe et un
contour interne qui touche lautre en un seul point.
SELECT ST_AsText(
ST_Buffer(
ST_GeometryFromText(POLYGON((0 0, 2 0, 1 1, 2 2, 3 1, 2 0, 4 0, 4 4, 0 4, 0
0.0
)
);
POLYGON((0 0,0 4,4 4,4 0,2 0,0 0),(2 0,3 1,2 2,1 1,2 0))
Note : Le polygone banane (ou coquillage invers) est un cas o le modle topologique de lOGC
et de ESRI diffrent. Le modle ESRI considre que les contours qui se touchent sont non valides et
prfre la forme de banane pour ce cas de figure. Le modle de lOGC est linverse.
124
Chapitre 21. Partie 20 : Validit
CHAPITRE 22
Partie 21 : Paramtrer PostgreSQL

pour le spatial
PostgreSQL est une base de donnes trs versatile, capable de tourner dans des environnements ayant
des ressources trs limites et partageant ces ressources avec un grand nombre dautres applications.
Afin dassurer quelle tournera convenablement dans ces environnements, la configuration par dfaut est
trs peu consommatrice de ressources mais terriblement inadapte pour des bases de donnes hautesperformances en production. Ajoutez cela le fait que les bases de donnes spatiales ont diffrents types
dutilisation, et que les donnes sont gnralement plus grandes que les autres types de donnes, vous
en arriverez la conclusion que les paramtres par dfaut ne sont pas appropris pour notre utilisation.
Tous ces paramtres de configuration peuvent tre dits dans le fichier de configuration de la base de
donnes : C:\Documents and Settings\%USER\.opengeo\pgdata\%USER. Le contenu
du fichier est du texte et il peut donc tre ouvert avec loutil ddition de fichiers de votre choix (Notepad
par exemple). Les modifications apportes ce fichier ne seront effectives que lors du redmarrage du
serveur.
125
Une faon simple dditer ce fichier de configuration est dutiliser loutil nomm : Backend Configuration Editor. Depuis pgAdmin, allez dans File > Open postgresql.conf.... Il vous sera demand le chemin du fichier, naviguez dans votre arborescence jusquau fichier C:\Documents and
Settings\%USER\.opengeo\pgdata\%USER.
126
Chapitre 22. Partie 21 : Paramtrer PostgreSQL pour le spatial
Cette partie dcrit certains des paramtres de configuration qui doivent tre modifis pour la mise ne
127
place dune base de donnes spatiale en production. Pour chaque partie, trouvez le bon paramtre dans
la liste et double cliquez dessus pour lditer. Changez le champ Value par la valeur que nous recommandons, assurez-vous que le champ est bien activ puis cliquez sur OK.
Note : Ces valeurs sont seulement celles que nous recommandons, chaque environnement diffrera
et tester les diffrents paramtrages est toujours ncessaire pour sassurer dutiliser la configuration
optimale. Mais dans cette partie nous vous fournissons un bon point de dpart.
22.1 shared_buffers
Alloue la quantit de mmoire que le serveur de bases de donnes utilise pour ses segments de mmoires
partages. Cela est partag par tous les processus serveur, comme son nom lindique. La valeur par dfaut
est affligeante et inadapte pour une base de donnes en production.
Valeur par dfaut : typiquement 32MB
Valeur recommande : 75% de la mmoire de la base de donnes (500MB)
128
22.2 work_mem
Dfinit la quantit de mmoire que les opration internes dordonnancement et les tables de hachages
peuvent consommer avec le serveur sur le disque. Cette valeur dfinit la mmoire disponible pour chaque
opration complexe, les requtes complexes peuvent avoir plusieurs ordres ou opration de hachage
tournant en parallle, et chaque client connect peut excuter une requte.
Vous devez donc considrer combien de connexions et quelle complexit est attendue dans les requtes
avant daugmenter cette valeur. Le bnfice acquis par laugmentation de cette valeur est que la plupart
des opration de classification, dont les clause ORDER BY et DISTINCT, les jointures, les agrgation
bases sur les hachages et lexcution de requte imbriques, pourront tre ralises sans avoir passer
par un stockage sur disque.
Valeur par dfaut : 1MB
Valeur recommande : 16MB
22.3 maintenance_work_mem
Dfinit la quantit de mmoire utilise pour les opration de maintenance, dont le nettoyage (VACUUM),
les index et la cration de clefs trangres. Comme ces opration sont couramment utilises, la valeur par
dfaut devrait tre acceptable. Ce paramtre peut tre augment dynamiquement lexcution depuis une
22.2. work_mem
129
connexion au serveur avant lexcution dun grand nombre dappels CREATE INDEX ou VACUUM
comme le montre la commande suivante.
SET maintenance_work_mem TO 128MB;
VACUUM ANALYZE;
SET maintenance_work_mem TO 16MB;
Valeur par dfaut : 16MB

22.4 wal_buffers
Dfinit la quantit de mmoire utilise pour lcriture des donnes dans le journal respectant la rgle
du defer (WAL). Elle indique que les informations pour annuler les effets dune opration sur un objet
doivent tre crites dans le journal en mmoire stable avant que lobjet modifi ne migre sur le disque.
Cette rgle permet dassurer lintgrit des donnes lors dune reprise aprs dfaillance. En effet, il
suffira de lire le journal pour retrouver ltat de la base lors de son arrt brutal.
La taille de ce tampon ncessite simplement dtre suffisament grand pour stocker les donnes WAL pour
une seule transaction. Alors que la valeur par dfaut est gnralement suffisante, les donnes spatiales
tendent tre plus larges. Il est donc recommand daugmenter la taille spcifie dans ce paramtre.
Valeur par dfaut : 64kB
130
22.5 checkpoint_segments
Cette valeur dfinit le nombre maximum de segments des journaux (typiquement 16MB) qui doit tre
remplit entre chaque point de reprise WAL. Un point de reprise WAL est une partie dune squence de
transactions pour lequel on garantit que les fichiers de donnes ont t mis jour avec toutes les requtes
prcdant ce point. ce moment-l toutes les pages sont punaises sur le disque et les points de reprise
sont crits dans le fichier de journal. Cela permet au processus de reprise aprs dfaillance de trouver les
derniers points de reprise et applique toute les lignes suivantes pour rcuprer ltat des donnes avant
la dfaillance.
tant donn que les points de reprise ncessitent un punaisage de toutes le pages ayant t modifies sur
le disque, cela va crer une charge dentres/sorties significative. Le mme argument que prcdemment
sapplique ici, les donnes spatiales sont assez grandes pour contrebalancer loptimisation de donnes
non spatiales. Augmenter cette valeur limitera le nombre de points de reprise, mais impliquera un redmarrage plus lent en cas de dfaillance.
Valeur par dfaut : 3
Valeur recommande : 6
22.5. checkpoint_segments
131
22.6 random_page_cost
Cette valeur sans unit reprsente le cot daccs alatoire une page du disque. Cette valeur est
relative aux autres paramtres de cot notamment laccs squentiel aux pages, et le cot des oprations processeur. Bien quil ny ait pas de valeur magique ici, la valeur par dfaut est gnralement
trop faible. Cette valeur peut tre affecte dynamiquement par session en utilisant la commande SET
random_page_cost TO 2.0.
Valeur par dfaut : 4.0
Valeur recommande : 2.0
132
22.7 seq_page_cost
Cest une paramtre qui contrle le cot des accs squentiels aux pages. Il nest gnralement pas ncessaire de modifier cette valeur mais la diffrence entre cette valeur et la valeur random_page_cost
affecte drastiquement le choix fait par le planificateur de requtes. Cette valeur peut aussi tre affecte
depuis une session.
Valeur par dfaut : 1.0
Valeur recommande : 1.0
22.7. seq_page_cost
133
22.8 Recharger la configuration

Aprs avoir ralis les changements mentionns dans cette partie sauvez-les puis rechargez la configuration.
Ceci se fait en cliquant avec le bouton droit sur le nom du serveur (PostgreSQL 8.4 on
localhost:54321) depuis pgAdmin, selectionnez Disconnect.
Cliquez sur le bouton Shutdown depuis le Dashboard OpenGeo, puis cliquez sur Start.
Pour finir reconnectez-vous au serveur depuis pgAdmin (cliquez avec le bouton droit sur le serveur
puis slectionnez Connect).
134
CHAPITRE 23
Partie 22 : galit
23.1 galit
tre en mesure de dterminer si deux geomtries sont gales peut tre compliqu. PostGIS met votre
disposition diffrentes fonctions permettant de juger de lgalit diffrents niveaux, bien que pour
des raison de simplicit nous nous contenterons ici de la dfinition fournie plus bas. Pour illustrer ces
fonctions, nous utiliserons les polygones suivants.
Ces polygones sont charger laide des commandes suivantes.

CREATE TABLE polygons (name varchar, poly geometry);
INSERT INTO polygons VALUES
135
(Polygon 1, POLYGON((-1 1.732,1 1.732,2 0,1 -1.732,

-1 -1.732,-2 0,-1 1.732))),
(Polygon 2, POLYGON((-1 1.732,-2 0,-1 -1.732,1 -1.732,
2 0,1 1.732,-1 1.732))),
(Polygon 3, POLYGON((1 -1.732,2 0,1 1.732,-1 1.732,
-2 0,-1 -1.732,1 -1.732))),
(Polygon 4, POLYGON((-1 1.732,0 1.732, 1 1.732,1.5 0.866,
2 0,1.5 -0.866,1 -1.732,0 -1.732,-1 -1.732,-1.5 -0.866,
-2 0,-1.5 0.866,-1 1.732))),
(Polygon 5, POLYGON((-2 -1.732,2 -1.732,2 1.732,
-2 1.732,-2 -1.732)));
23.1.1 Exactement gaux

Lgalit exacte est dtermine en comparant deux gomtries, sommets par sommets, dans lordre,
pour sassurer que chacun est une position identique. Les exemples suivant montrent comment cette
mthode peut tre limite dans son efficacit.
SELECT a.name, b.name, CASE WHEN ST_OrderingEquals(a.poly, b.poly)
THEN Exactly Equal ELSE Not Exactly Equal end
FROM polygons as a, polygons as b;
136
Chapitre 23. Partie 22 : galit
Dans cette exemple, les polygones sont seulement gaux eux-mme, mais jamais avec un des autres
polygones (dans notre exemple les polygones de 1 3). Dans le cas des polygones 1, 2 et 3, les sommets
sont des positions identiques mais sont dfinis dans un ordre diffrent. Le polygone 4 a des sommets
en double causant la non-galit avec le polygone 1.
23.1.2 Spatialement gaux

Comme nous lavons prcdemment montr, lgalit exacte ne prend pas en compte la nature spatiale des gomtries. Il y a une fonction, nomme ST_Equals, permettant de tester lgalit spatiale ou
lquivalence des gomtries.
23.1. galit
137
SELECT a.name, b.name, CASE WHEN ST_Equals(a.poly, b.poly)

THEN Spatially Equal ELSE Not Equal end
Ces rsultats sont plus proches de notre comprhension intuitive de lgalit. Les polygones de 1 4
sont considrs comme gaux, puisque quils recouvrent la mme zone. Notez que ni la direction des
polygones nest considre, ni le point de dpart pour la dfinition du polygone, ni le nombre de points.
Ce qui importe cest que la zone gographique reprsente soit la mme.
23.1.3 galit des tendues

Lgalit exacte ncessite, dans le pire des cas, de comparer chacun des sommets dune gomtrie pour
dterminer lgalit. Ceci peut tre trs lent, et savrer innapropri pour comparer un grand nombre de
138
gomtries. Pour permettre de rendre plus rapide ces comparaison, lopration dgalit des tendue est
fournit : =. Cet oprateur utilise uniquement les tendues (cadre limite rectangulaire), assurant que les
gomtries occupent le mme espace dans un repre cartsien en deux dimensions, mais ne reprsente
pas ncessairement le mme espace.
SELECT a.name, b.name, CASE WHEN a.poly = b.poly
THEN Equal Bounds ELSE Non-equal Bounds end
Comme vous pouvez le constater, toutes les gomtries gales ont aussi une tendue gale. Malheureusement, le polygone 5 est aussi retourn comme tant gal avec ce test, puisquil partage la mme tendue
que les autres gomtries. Mais alors, pourquoi est-ce utile ? Bien que cela soit trait en dtail plus tard,
la rponse courte est que cela permet lutilisation dindexations spatiales qui peuvent rduire drastiquement les ensembles de gomtries comparer en utilisant des filtres utilisant cette galit dtendue.
23.1. galit
139
140
CHAPITRE 24
Annexes A : Fonctions PostGIS
24.1 Constructeurs
ST_MakePoint(Longitude, Latitude) Retourne un nouveau point. Note : ordre des coordonnes (longitude puis latitude).
ST_GeomFromText(WellKnownText, srid) Retourne une nouvelle gomtrie partir dun reprsentation au format WKT et un SRID.
ST_SetSRID(geometry, srid) Met jour le SRID dune gomtrie. Retourne la mme gomtrie. Cela
ne modifie pas les coordonnes de la gomtrie, cela met simplement jour le SRID. Cette fonction est utile pour reconditionner les gomtries sans SRID.
ST_Expand(geometry, Radius) Retourne une nouvelle gomtrie qui est une extension de ltendue
de la gomtrie passe en argument. Cette fonction est utile pour crer des enveloppes pour des
recherches utilisant les indexations.
24.2 Sorties
ST_AsText(geometry) Retourne une gomtrie au format WKT.
ST_AsGML(geometry) Retourne la gomtrie au format standard OGC GML.
ST_AsGeoJSON(geometry) Retourne une gomtrie au format standard GeoJSON.
24.3 Mesures
ST_Area(geometry) Retourne laire dune gomtrie dans lunit du systme de rfrence spatiale.
ST_Length(geometry) Retourne la longueur de la gomtrie dans lunit du systme de rfrence spatiale.
ST_Perimeter(geometry) Retourne le primtre de la gomtrie dans lunit du systme de rfrence
spatiale.
ST_NumPoints(linestring) Retourne le nombre de sommets dans une ligne.
ST_NumRings(polygon) Retourne le nombre de contours dans un polygone.
ST_NumGeometries(geometry) Retourne le nombre de gomtries dans une collection de gomtries.
141
24.4 Relations
ST_Distance(geometry, geometry) Retourne la distance entre deux gomtries dans lunit du systme
de rfrence spatiale.
ST_DWithin(geometry, geometry, radius) Retourne TRUE si les gomtries sont distantes dun rayon
de lautre, sinon FALSE.
ST_Intersects(geometry, geometry) Retourne TRUE si les gomtries sont disjointes, sinon FALSE.
ST_Contains(geometry, geometry) Retourne TRUE si la premire gomtrie est totalement contenue
dans la seconde, sinon FALSE.
ST_Crosses(geometry, geometry) Retourne TRUE si une ligne ou les contours dun polygone croisent
une ligne ou un contour de polygone, sinon FALSE.
142
Chapitre 24. Annexes A : Fonctions PostGIS
CHAPITRE 25
Annexes B : Glossaire
CRS Un systme de rfrence spatiale. La combinaison dun systme de coordonnee gographiques

et un systme de projection.
GDAL Geospatial Data Abstraction Library, prononc GDAL, une bibliothque open source permettant daccder aux donnes rasters supportant un grand nombre de formats, utilis largement
la fois dans les applications open source et propritaires.
GeoJSON Javascript Object Notation, un format texte qui est trs rapide et qui permet de reprsenter
des objets JavaScript. En spatial, la spcification tendue GeoJSON est couramment utilise.
SIG Systme dInformation Gographique capture, stocke, analyse, gre, et prsente les donnes qui
sont relies la zone gographique.
GML Geography Markup Language. Le GML est un format standard XML OGC pour reprsenter les
donnes gographiques.
JSON Javascript Object Notation, un format texte qui est trs rapide et permet de stocker des objets
JavaScript. Au niveau spatial, la spcification tendue GeoJSON est couramment utilis.
JSTL JavaServer Page Template Library, est une bibliothque pour JSP qui encapsule plusieurs
fonctionnalits de bases gres en JSP (requte de bases de donnes, itration, conditionnel) dans
un syntaxe tierce.
JSP JavaServer Pages est un systme de script pour les serveur dapplications Java qui permet de
mixer du code XML et du code Java.
KML Keyhole Markup Language, le format XML utilis par Google Earth. Google Earth. Il ft
lorigine dvelopp par la socit Keyhole, ce qui explique sa prsence (maintenant obscure)
dans le nom du format.
OGC
Open Geospatial Consortium http ://opengeospatial.org/ (OGC) est une organisation qui
dveloppe des spcifications pour les services spatiaux.
OSGeo Open Source Geospatial Foundation http ://osgeo.org (OSGeo) est une association but non
lucratif ddie la promotion et au support des logiciels cartographiques open source.
SFSQL La spcification Simple Features for SQL (SFSQL) de lOGC dfinit les types et les fonctions
qui doivent tre disponibles dans une base de donnes spatiale.
SLD Les spcifications Styled Layer Descriptor (SLD) de lOGC dfinissent un format permettant de
dcrire la manire dafficher des donnes vectorielles.
SRID Spatial reference ID est un identifiant unique assign un systme de coordonnes gographiques particulier. La table PostGIS spatial_ref_sys contient une large collection de valeurs
de SRID connus.
143
SQL Structured query language est un standard permettant de requter les bases de donnes relationnelles. Rfrence http ://en.wikipedia.org/wiki/SQL.
SQL/MM SQL Multimedia ; spcification contenant diffrentes sections sur les types tendues. Elle
inclue une section substantielle sur les types spatiaux.
SVG Scalable vector graphics est une famille de spcifications bas sur le format XML pour dcrire
des objet graphiques en 2 dimensions, aussi bien statiques que dynamiques (par exemple interactif
ou anim). Rference : http ://en.wikipedia.org/wiki/Scalable_Vector_Graphics.
WFS La spcification Web Feature Service (WFS) de lOGC dfinit une interface pour lire et crire
des donnes gographiques travers internet.
WMS La spcification Web Map Service (WMS) de lOGC dfinit une interface pour requter une
carte travers internet.
WKB Well-known binary. Fait rfrence la reprsentation binaire des gomtries comme dcrit
dans les spcifications Simple Features for SQL (SFSQL).
WKT Well-known text. Fait rfrence la reprsentation textuelle de gomtries, avec des chanes
commenant par POINT, LINESTRING, POLYGON, etc. Il peut aussi faire rfrence
la reprsentation textuelle dun CRS, avec une chane commenant par PROJCS, GEOGCS,
etc. Les reprsentations au format Well-known text sont des standards de lOGC, mais nont pas
leur propres documents de spcifications. La premire description du WKT (pour les gomtries
et pour les CRS) apparaissent dans les spcifications SFSQL 1.0.
144
Chapitre 25. Annexes B : Glossaire
CHAPITRE 26
Annexes C : License
Ce contenu est publi sous licence Creative Commons share alike with attribution, et est librement
redistribuable en respectant les termes de cette license.
Vous devez conserver lensemble des copyrights prsents dans ce document.
145
146
Chapitre 26. Annexes C : License
Index
C
CRS, 143
G
GDAL, 143
GeoJSON, 143
GML, 143
J
JSON, 143
JSP, 143
JSTL, 143
K
KML, 143
O
OGC, 143
OSGeo, 143
S
SFSQL, 143
SIG, 143
SLD, 143
SQL, 143
SQL/MM, 144
SRID, 143
SVG, 144
W
WFS, 144
WKB, 144
WKT, 144
WMS, 144
147

Post Gis Intro IN Frenche

Transféré par

Droits d'auteur :

Formats disponibles

Post Gis Intro IN Frenche

Transféré par

Informations du document

Copyright

Formats disponibles

Partager ce document

Partager ou intégrer le document

Options de partage

Avez-vous trouvé ce document utile ?

Ce contenu est-il inapproprié ?

Droits d'auteur :

Formats disponibles

Post Gis Intro IN Frenche

Transféré par

Droits d'auteur :

Formats disponibles

Introduction to PostGIS

Mark Leslie, Paul Ramsey

January 28, 2017

Table des matires

Partie 3 : Crer une base de donnes spatiales

Partie 4 : Charger des donnes spatiales

Partie 5 : A propos de nos donnes

Partie 6 : Requtes SQL simples

Partie 7 : Exercices simples de SQL

Partie 8 : Les gometries

10 Partie 9 : Exercices sur les gomtries

12 Partie 11 : Exercices sur les relations spatiales

16 Partie 15 : Projections des donnes

19 Partie 18 : Fonctions de construction de gomtries

20 Partie 19 : Plus de jointures spatiales

22 Partie 21 : Paramtrer PostgreSQL pour le spatial

1.1 Conventions dcriture

Introduction to PostGIS, Version 1.0

ST_Touches(geometry A, geometry B) retourne vrai si un des contours de gomtrie

1.1.5 Fichiers, Tables et nom de colonne

1.1.6 Menus et formulaires

2.1 Quest-ce quune base de donnes spatiales ?

Introduction to PostGIS, Version 1.0

2.1.2 Les types de donnes spatiales

Chapitre 2. Partie 1 : Introduction

Introduction to PostGIS, Version 1.0

2.1.3 Index spatiaux et tendue

2.1. Quest-ce quune base de donnes spatiales ?

Introduction to PostGIS, Version 1.0

2.1.4 Les fonctions spatiales

Chapitre 2. Partie 1 : Introduction

Introduction to PostGIS, Version 1.0

2.2 Quest-ce que PostGIS ?

2.2.1 Mais quest-ce que PostgreSQL ?

Introduction to PostGIS, Version 1.0

2.2.2 Pourquoi pas des fichiers Shapefile ?

2.2.3 Un bref historique de PostGIS

Chapitre 2. Partie 1 : Introduction

Introduction to PostGIS, Version 1.0

2.2.4 Qui utilise PostGIS ?

2.2.5 Quest-ce quune application qui supporte PostGIS ?

2.2. Quest-ce que PostGIS ?

Introduction to PostGIS, Version 1.0

Chapitre 2. Partie 1 : Introduction

1. Dans le rpertoire postgisintro\software\ vous trouverez linstalleur de OpenGeo Suite

Introduction to PostGIS, Version 1.0

Chapitre 3. Partie 2 : Installation

Introduction to PostGIS, Version 1.0

4. Le rpertoire o OpenGeo Suite sera install est gnralement le rpertoire C:\Program

Introduction to PostGIS, Version 1.0

7. Prt installer ! Cliquez sur Install.

Chapitre 3. Partie 2 : Installation

Introduction to PostGIS, Version 1.0

8. Le processus dinstallation prendra quelques minutes.

Introduction to PostGIS, Version 1.0

travaux pratiques ! Cliquez sur Finish.

Chapitre 3. Partie 2 : Installation

Partie 3 : Crer une base de donnes

4.1 Le Dashboard et PgAdmin

Introduction to PostGIS, Version 1.0