La raison d'être des bases de données spatiales est de réaliser à l'intérieur de la base de données des requêtes qui normalement nécessiteraient des fonctionnalités d'un SIG Desktop. Utiliser PostGIS requiert en effet la connaissance de quelles fonctions spatiales sont disponibles, comment les utiliser dans une requête, et de s'assurer de la mise en place adéquate des index, pour une bonne performance.
Les relations spatiales indiquent comment deux géométries interagissent l'une avec l'autre. Elles constituent une fonctionnalité fondamentale pour effectuer des requêtes sur des géométries.
Selon le OpenGIS Simple Features Implementation Specification for SQL, "l'approche de base pour comparer deux géométries consiste à effectuer des tests par paire des intersections entre les intérieurs, les limites et les extérieurs des deux géométries et à classer la relation entre les deux géométries sur la base des entrées de la matrice "intersection" résultante."
Dans la théorie de la topologie des ensembles de points, les points d'une géométrie intégrée dans un espace à deux dimensions sont classés en trois ensembles :
La limite d'une géométrie est l'ensemble des géométries de la dimension immédiatement inférieure. Pour les POINT, qui ont une dimension de 0, la limite est l'ensemble vide. La limite d'une LINESTRING est constituée par ses deux extrémités. Pour les POLYGON, la limite est le réseau de lignes des anneaux extérieur et intérieur.
L'intérieur d'une géométrie est constitué des points de la géométrie qui ne se trouvent pas dans la limite. Pour les POINT, l'intérieur est le point lui-même. L'intérieur d'une LINESTRING est l'ensemble des points situés entre les extrémités. Pour les POLYGON, l'intérieur est la surface aréale à l'intérieur du polygone.
L'extérieur d'une géométrie est le reste de l'espace dans lequel la géométrie est intégrée ; en d'autres termes, tous les points qui ne se trouvent pas à l'intérieur ou sur la limite de la géométrie. Il s'agit d'une surface non fermée à 2 dimensions.
Le Dimensionally Extended 9-Intersection Model (DE-9IM) décrit la relation spatiale entre deux géométries en spécifiant les dimensions des 9 intersections entre les ensembles ci-dessus pour chaque géométrie. Les dimensions des intersections peuvent être représentées formellement dans une matrice d'intersections de 3x3.
Pour une géométrie g, les Limites, Intérieures, et Extérieures sont désignés par la notation I(g), B(g), et E(g). En outre, dim(s) désigne la dimension d'un ensemble s dont le domaine est {0,1,2,F} :
0 => point
1 => ligne
2 => area
F => ensemble vide
En utilisant cette notation, la matrice d'intersection pour deux géométries a et b est :
| Intérieur | Limite | Extérieur | |
|---|---|---|---|
| Intérieur | dim( I(a) ∩ I(b) ) | dim( I(a) ∩ B(b) ) | dim( I(a) ∩ E(b) ) |
| Limite | dim( B(a) ∩ I(b) ) | dim( B(a) ∩ B(b) ) | dim( B(a) ∩ E(b) ) |
| Extérieur | dim( E(a) ∩ I(b) ) | dim( E(a) ∩ B(b) ) | dim( E(a) ∩ E(b) ) |
The following overlapping polygons provide a concrete example. ST_Relate computes the matrix 212101212; it matches the overlap pattern T*T***T** used by ST_Overlaps.
WITH example AS (
SELECT
'POLYGON ((140 140,140 122,135 105,126 100,118 99,110 94,100 86,97 73,102 59,98 49,87 38,70 30,55 29,40 30,28 38,20 50,14 66,10 84,6 100,4 119,4 143,6 166,10 180,18 191,28 195,40 190,55 189,67 186,86 179,112 165,124 158,133 148,140 140),(48 177,40 177,30 174,24 166,22 159,25 155,30 153,40 154,45 157,52 162,53 169,51 174,48 177))'::geometry AS a,
'POLYGON ((75 63,79 50,87 38,95 31,108 25,124 22,140 18,154 11,166 6,176 10,184 21,188 35,190 58,190 82,193 104,190 121,185 139,178 154,166 163,154 171,139 172,124 171,112 165,96 152,92 142,92 126,86 116,79 110,75 104,72 94,73 86,75 76,75 63))'::geometry AS b
), relation AS (
SELECT a, b, ST_Relate(a, b) AS matrix
FROM example
)
SELECT matrix,
ST_RelateMatch(matrix, 'T*T***T**') AS matches_overlap_pattern,
ST_Overlaps(a, b) AS overlaps
FROM relation;
matrix | matches_overlap_pattern | overlaps -----------+-------------------------+---------- 212101212 | t | t (1 row)
The executable query and generated overlay above are the visual source for the relation. The query below renders the nine cells of the same kind of matrix as a 3-by-3 set of examples. The final exterior/exterior cell is clipped to a finite viewport, since the true exterior/exterior intersection is unbounded.
WITH example AS (
SELECT
ST_MakeEnvelope(0, 0, 6, 4) AS a,
ST_MakeEnvelope(3, -1, 8, 3) AS b,
ST_MakeEnvelope(-1, -2, 9, 5) AS viewport
), parts AS (
SELECT
a,
b,
ST_Boundary(a) AS ba,
ST_Boundary(b) AS bb,
viewport
FROM example
)
SELECT
ST_Relate(a, b) AS matrix,
ST_AsText(ST_Normalize(a)) AS input_a,
ST_AsText(ST_Normalize(b)) AS input_b,
ST_AsText(ST_Normalize(ST_Intersection(a, b))) AS "2 I(a) ∩ I(b)",
ST_AsText(ST_Normalize(ST_Intersection(a, bb))) AS "1 I(a) ∩ B(b)",
ST_AsText(ST_Normalize(ST_Difference(a, b))) AS "2 I(a) ∩ E(b)",
ST_AsText(ST_Normalize(ST_Intersection(ba, b))) AS "1 B(a) ∩ I(b)",
ST_AsText(ST_Normalize(ST_Intersection(ba, bb))) AS "0 B(a) ∩ B(b)",
ST_AsText(ST_Normalize(ST_Collect(
'LINESTRING(0 0,0 4,6 4,6 3)'::geometry,
'LINESTRING(0 0,3 0)'::geometry
))) AS "1 B(a) ∩ E(b)",
ST_AsText(ST_Normalize(ST_Difference(b, a))) AS "2 E(a) ∩ I(b)",
ST_AsText(ST_Normalize(ST_Collect(
'LINESTRING(3 -1,8 -1,8 3,6 3)'::geometry,
'LINESTRING(3 -1,3 0)'::geometry
))) AS "1 E(a) ∩ B(b)",
ST_AsText(ST_Normalize(ST_Difference(viewport, ST_Union(a, b))))
AS "2 E(a) ∩ E(b) clipped"
FROM parts;
En lisant de gauche à droite et de haut en bas, la matrice d'intersection est représentée par la chaîne de texte "212101212".
Pour plus d'informations, voir :
Pour faciliter la détermination des relations spatiales communes, l'OGC SFS définit un ensemble de prédicats de relations spatiales. PostGIS fournit ces prédicats sous la forme des fonctions ST_Contains, ST_Crosses, ST_Disjoint, ST_Equals, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within. Il définit également les prédicats de relation non standard ST_Covers, ST_CoveredBy, et ST_ContainsProperly.
Les prédicats spatiaux sont généralement utilisés comme conditions dans les clauses SQL WHERE ou JOIN. Les prédicats spatiaux nommés utilisent automatiquement un index spatial s'il existe, de sorte qu'il n'est pas nécessaire d'utiliser également l'opérateur de boîte de délimitation &&. Par exemple :
SELECT city.name, state.name, city.geom FROM city JOIN state ON ST_Intersects(city.geom, state.geom);
Pour plus de détails et d'illustrations, voir l' Atelier PostGIS.
In some cases the named spatial relationships are insufficient to provide a desired spatial filter condition. These requirements can be expressed by computing the full DE-9IM intersection matrix with ST_Relate.
Pour tester une relation spatiale particulière, un modèle de matrice d'intersection est utilisé. Il s'agit de la représentation matricielle augmentée des symboles supplémentaires {T,*} :
T => la dimension d'intersection est non vide ; c'est-à-dire qu'elle se trouve dans {0,1,2}
* => indifférent
Using intersection matrix patterns, specific spatial relationships can be evaluated succinctly. The ST_Relate and ST_RelateMatch functions can both test these patterns.
These road segments share a line segment. ST_Crosses does not find this relationship for linear features, because it only returns true when their interiors intersect at a point. The pattern 1*1***1** tests for a one-dimensional intersection instead.
WITH roads AS (
SELECT
'LINESTRING(10 10,40 90,70 110,140 110,170 130,190 190)'::geometry AS "road A",
'LINESTRING(10 190,50 130,90 110,130 110,160 70,180 10)'::geometry AS "road B"
), relation AS (
SELECT "road A", "road B", ST_Relate("road A", "road B") AS matrix
FROM roads
)
SELECT
ST_Intersection("road A", "road B") AS overlap,
matrix,
ST_Crosses("road A", "road B") AS "ST_Crosses",
ST_RelateMatch(matrix, '1*1***1**') AS "matches 1*1***1**"
FROM relation;
overlap | matrix | ST_Crosses | matches 1*1***1** ----------------------------+-----------+------------+------------------- LINESTRING(90 110,130 110) | 1F1FF0102 | f | t (1 row)
The next example finds a wharf which runs from the lake interior onto its shoreline, with one endpoint on the shoreline. The other wharves provide nearby counterexamples in the figure. The pattern 102101FF2 expresses the complete required relationship in one test.
WITH data AS (
SELECT
'POLYGON((-20 10,30 30,80 70,110 80,145 85,190 110,300 220,230 220,-20 220,-20 10))'::geometry AS lake,
'MULTILINESTRING((120 180,145 85,110 80),(10 130,30 70),(90 140,110 80,94.81118881118876 75.83496503496498),(150 160,180 80))'::geometry AS wharves
), wharf AS (
SELECT lake, (item).path[1] AS id, (item).geom
FROM data CROSS JOIN LATERAL ST_Dump(wharves) AS item
), relation AS (
SELECT id, geom, ST_Relate(lake, geom) AS matrix
FROM wharf
)
SELECT
id AS wharf,
geom AS "matching wharf",
matrix
FROM relation
WHERE ST_RelateMatch(matrix, '102101FF2')
ORDER BY id;
wharf | matching wharf | matrix
-------+-----------------------------------+-----------
1 | LINESTRING(120 180,145 85,110 80) | 102101FF2
(1 row)
Lors de la construction de requêtes utilisant des conditions spatiales, il est important, pour de meilleures performances, de s'assurer qu'un index spatial est utilisé, s'il existe (voir Section 4.9, « Index spatiaux »). Pour ce faire, un opérateur spatial ou une fonction sensible à l'index doit être utilisé dans une clause WHERE ou ON de la requête.
Les opérateurs spatiaux comprennent les opérateurs de boîte de délimitation (dont le plus couramment utilisé est && ; voir Section 7.10.1, « Opérateurs de Bounding Box » pour la liste complète) et les opérateurs de distance utilisés dans les requêtes sur les plus proches voisins (le plus courant étant <-> ; voir Section 7.10.2, « Opérateurs de distance » pour la liste complète.)
Les fonctions tenant compte de l'index ajoutent automatiquement un opérateur de boîte de délimitation à la condition spatiale. Les fonctions tenant compte de l'index comprennent les prédicats de relation spatiale nommés ST_Contains, ST_ContainsProperly, ST_CoveredBy, ST_Covers, ST_Crosses, ST_Intersects, ST_Overlaps, ST_Touches, ST_Within, ST_Within, et ST_3DIntersects, et les prédicats de distance ST_DWithin, ST_DFullyWithin, ST_3DDFullyWithin, et ST_3DDWithin . )
Les fonctions telles que ST_Distance n'utilisent pas les index pour optimiser leur fonctionnement. Par exemple, la requête suivante serait assez lente sur une grande table :
SELECT geom FROM geom_table WHERE ST_Distance(geom, 'SRID=312;POINT(100000 200000)') < 100
Cette requête sélectionne toutes les géométries de la geom_table qui se trouvent à moins de 100 unités du point (100000, 200000). Elle sera lente car elle calcule la distance entre chaque point du tableau et le point spécifié, c'est-à-dire qu'un calcul ST_Distance() est effectué pour chaque ligne du tableau.
Le nombre de lignes traitées peut être considérablement réduit en utilisant la fonction d'indexation ST_DWithin :
SELECT geom FROM geom_table WHERE ST_DWithin(geom, 'SRID=312;POINT(100000 200000)', 100)
Cette requête sélectionne les mêmes géométries, mais de manière plus efficace. Ceci est possible en ST_DWithin() utilisant l'opérateur && en interne sur une boîte de délimitation élargie de la géométrie de la requête. S'il existe un index spatial sur geom, le planificateur de requêtes reconnaîtra qu'il peut utiliser l'index pour réduire le nombre de lignes analysées avant de calculer la distance. L'index spatial permet d'extraire uniquement les enregistrements dont les boîtes de délimitation chevauchent l'étendue élargie et qui, par conséquent, pourraient se trouver à l'intérieur de la distance requise. La distance réelle est ensuite calculée pour confirmer l'inclusion de l'enregistrement dans l'ensemble des résultats.
Pour plus d'informations et d'exemples, voir l' atelier de travail PostGIS.
Les exemples de cette section utilisent une table de routes linéaires et une table de limites de communes polygonales. La définition de la table bc_roads est la suivante :
Column | Type | Description ----------+-------------------+------------------- gid | integer | Unique ID name | character varying | Road Name geom | geometry | Location Geometry (Linestring)
La définition de la table bc_municipality est la suivante :
Column | Type | Description ---------+-------------------+------------------- gid | integer | Unique ID code | integer | Unique ID name | character varying | City / Town Name geom | geometry | Location Geometry (Polygon)
|
5.3.1. |
Quelle est la longueur totale de toutes les routes, exprimée en kilomètres ? |
|
Vous pouvez répondre à cette question à l'aide d'un code SQL très simple : Code
SELECT sum(ST_Length(geom))/1000 AS km_roads FROM bc_roads; Export de raster
km_roads ------------------ 70842.1243039643 |
|
|
5.3.2. |
Quelle est la superficie de la ville de Prince George, en hectares ? |
|
Cette requête combine une condition d'attribut (sur le nom de la municipalité) avec un calcul spatial (de la surface du polygone) : Code
SELECT ST_Area(geom)/10000 AS hectares FROM bc_municipality WHERE name = 'PRINCE GEORGE'; Export de raster
hectares ------------------ 32657.9103824927 |
|
|
5.3.3. |
Quelle est la plus grande municipalité de la province, en termes de superficie ? |
|
Cette requête utilise une mesure spatiale comme valeur de référence. Il existe plusieurs façons d'aborder ce problème, mais la plus efficace est la suivante : Code
SELECT name, ST_Area(geom)/10000 AS hectares FROM bc_municipality ORDER BY hectares DESC LIMIT 1; Export de raster
name | hectares ---------------+----------------- TUMBLER RIDGE | 155020.02556131 Notez que pour répondre à cette question, nous devons calculer la surface de chaque polygone. Si nous faisions cela souvent, il serait logique d'ajouter une colonne de surface à la table qui pourrait être indexée pour des raisons de performance. En ordonnant les résultats dans une direction décroissante et en utilisant la commande "LIMIT" de PostgreSQL, nous pouvons facilement sélectionner la plus grande valeur sans utiliser une fonction d'agrégation comme MAX(). |
|
|
5.3.4. |
Quelle est la longueur des routes entièrement comprises dans chaque municipalité ? |
|
Il s'agit d'un exemple de "jointure spatiale", qui rassemble les données de deux tables (avec une jointure) en utilisant une interaction spatiale ("contenu") comme condition de jointure (plutôt que l'approche relationnelle habituelle de jointure sur une clé commune) : Code
SELECT m.name, sum(ST_Length(r.geom))/1000 as roads_km FROM bc_roads AS r JOIN bc_municipality AS m ON ST_Contains(m.geom, r.geom) GROUP BY m.name ORDER BY roads_km; Export de raster
name | roads_km ----------------------------+---------- SURREY | 1539.476 VANCOUVER | 1450.331 LANGLEY DISTRICT | 833.793 BURNABY | 773.769 PRINCE GEORGE | 694.376 ... Cette requête prend un certain temps, car chaque route du tableau est résumée dans le résultat final (environ 250 000 routes pour le tableau de l'exemple). Pour des ensembles de données plus petits (plusieurs milliers d'enregistrements sur plusieurs centaines), la réponse peut être très rapide. |
|
|
5.3.5. |
Créez un nouveau tableau avec toutes les routes de la ville de Prince George. |
|
Il s'agit d'un exemple de "superposition", qui prend en compte deux tableaux et produit un nouveau tableau composé de résultats coupés dans l'espace. Contrairement à la "jointure spatiale" présentée ci-dessus, cette requête crée de nouvelles géométries. Une superposition est comme une jointure spatiale turbocompressée, et est utile pour des travaux d'analyse plus précis : Code
CREATE TABLE pg_roads as SELECT ST_Intersection(r.geom, m.geom) AS intersection_geom, ST_Length(r.geom) AS rd_orig_length, r.* FROM bc_roads AS r JOIN bc_municipality AS m ON ST_Intersects(r.geom, m.geom) WHERE m.name = 'PRINCE GEORGE'; |
|
|
5.3.6. |
Quelle est la longueur en kilomètres de "Douglas St" à Victoria ? |
|
Code
SELECT sum(ST_Length(r.geom))/1000 AS kilometers FROM bc_roads r JOIN bc_municipality m ON ST_Intersects(m.geom, r.geom WHERE r.name = 'Douglas St' AND m.name = 'VICTORIA'; Export de raster
kilometers ------------------ 4.89151904172838 |
|
|
5.3.7. |
Quel est le plus grand polygone municipal comportant un trou ? |
|
Code
SELECT gid, name, ST_Area(geom) AS area FROM bc_municipality WHERE ST_NRings(geom) > 1 ORDER BY area DESC LIMIT 1; Export de raster
gid | name | area -----+--------------+------------------ 12 | SPALLUMCHEEN | 257374619.430216 |