Après avoir introduit les différents types d’éléments géographiques disponibles au sein de SQL Server et en abordant leurs déclarations et les règles de définition associées, nous allons pouvoir enfin tirer partie du meilleur en utilisant les méthodes spatiales spécifiques à ce type de données.

SQL Server geography 2008 et 2012 et SQL Azure - Presentation des méthodes standards OGC telles que STUnion(), STBuffer(), STSymDifference()...

Cet article s’incrit dans la série dédiée à l’explication du type géographique au sein de SQL Server 2008, 2012 et Azure.

Retrouvez les anciens articles sur le même sujet :

 

Standards et systèmes d’information géographiques (SIG)

OGC logoLes standards sont établis par l’OGC (Open Geospatial Consortium) et définissent un comportement, un prototype et un résultat pour les méthodes que nous allons découvrir ici.

Afin d’obtenir davantage d’informations sur les standards et sur la définition de ces méthodes, n’hésitez pas à parcourir les liens suivants :

Il est important de rappeler qu’il existe de nombreux serveurs de base de données permettant de stocker ces éléments géographiques, on parle souvent de serveur de base de données spatiales.

Voici les principaux serveurs de base de données en question :

  • Oracle Spatial
  • IMB DB2
  • Microsoft SQL Server 2008 et supérieur
  • Postgre SQL

Attention, il est important de différencier le serveur de base de données ayant le support de types spatiaux et les systèmes d’information géographiques complets.

SQL Server 2008 et 2010 SIG Systeme d'information géographique

En effet, ces solutions intègrent la plupart du temps des services complémentaires ou des modèles d’exposition de données qui dépasse la simple restitution sous forme d’enregistrement de tables (ex : WMS, WMS avec cache, capacité de géocodage, calcul d’itinéraire avec contraintes…).

 

Présentation des méthodes

Il existe donc plusieurs méthodes standards que tout serveur qui se dit ‘spatial’ doit pouvoir executer en restituant des résultats cohérents.

Ces méthodes sont les suivantes :

Méthodes Description rapide
STArea() Calcul de l’aire
STAsBinary() Représentation binaire (WKB)
STAsText() Représentation textuelle (WKT)
STBuffer() Tampon autour des données
STDifference() Difference entre une technologie et une autre
STDimension() Nombre de dimension
STDisjoint() Liaison entres les géométries
STDistance() Distance entre les géométries
STEndPoint() Dernier point de la géométrie
STGeometryN() Point identifié (n) sur la géométrie
STGeometryType() Type de géométrie
STIntersection() Géométrie d’intersection entre géométries
STIntersects() Vérification de l’intersection entre géométries
STIsClosed() Fermeture de la géométrie
STIsEmpty() Possède une données
STLength() Longueur d’une géométrie
STNumGeometries() Nombre de géométries composants
STNumPoints() Nombre de points composants au total
STPointN() Point identifié (n) de la géométrie
STSrid() SRID de la géométrie
STStartPoint() Premier point de la géométrie
STSymDifference() Différence symétrique entre 2 géométries
STUnion() Union entre géométries

Il existe également plusieur méthodes statiques standards qui sont abordées dans cet article.

MSDN SQL Server Geography bonnes pratiques et utilisationsBien entendu, il est tout à fait possible d’utiliser ces méthodes qui sont implémentées dans SQL Server 2008 et supérieure (2012 ‘Denali’) avec les types géographiques ou géométriques :
http://msdn.microsoft.com/en-us/library/bb933914.aspx

 

Implémentation technique dans SQL Server

Afin de comprendre l’intérêt de ces méthodes, il faut se mettre dans la situation où nous souhaitons localiser des éléments, des zones ou des lignes référencée géographiquement et parmis lesquels nous souhaitons réaliser plusieurs opérations de tri, de sélection ou filtre ou bien même d’aggrégation.

Ici nous n’aborderons pas les précédentes solutions qu’il nous fallait réaliser en SQL Server 2005, mais nous pouvons très rapidement comprendre l’intérêt de l’utilisation de ces méthodes en termes de développement, mais aussi en termes de performances puisque le moteur SQL Server inclut de manière native ces méthodes et se révèle donc particulièrement rapide.

On remarque, dans l’implémentation SQL Server, le préfixe de ces méthodes “ST” différenciant ainsi ces méthodes de celles spécifiques que nous aborderons dans un prochain article.

 

  • Méthodes de calcul sur les éléments

    • Calcul de distance : STDistance

Description :

Cette méthode permet de calculer la distance la plus courte qui sépare deux éléments en tenant bien évidemment compte de la distance réelle en fonction du référentiel dans lequel on travaille. Il s’agit donc de la longueur de la ligne géodésique liant les éléments concernés renvoyée en mètre.

Schéma descriptif :

STDistance() Explication

Implémentation SQL Server :

Voici l’implémentation d’exemple utilisant 2 points comme éléments :

--STDistance
DECLARE @pointA AS GEOGRAPHY
SET @pointA = GEOGRAPHY::STGeomFromText('POINT(4 45)', 4326);
DECLARE @pointB AS GEOGRAPHY
SET @pointB = GEOGRAPHY::STGeomFromText('POINT(3 44)', 4326);
SELECT @pointA.STDistance(@pointB) as 'Distance'

Résultat :

STDistance resultat de l'execution SQL Server

 

    • Calcul de la longueur d’un élément : STLength

Description :

Cette méthode permet de calculer la longueur d’un élément défini et retourne le résultat en mètre.

Note : La longueur d’un point retourne 0.

Cas pratiques :

- Longueur du Tour de France : distance parcourue

Schéma descriptif :

STLength() sql geography description

 

Implémentation SQL Server :

--STLength
DECLARE @line AS GEOGRAPHY
SET @line = GEOGRAPHY::STGeomFromText('LINESTRING(3 45,4 45,4 44)', 4326);
SELECT @line as 'Line', @line.STLength() as 'Length'

Résultat :

STLength() resultat geography

STLength() resultat geography

 

    • Calcul de l’aire : STArea

Description :

Cette méthode permets de calculer l’aire d’un élément tel qu’un polygon ou un élément complexe.

Note : L’aire d’un point ou d’une ligne vaut 0.

Cas pratiques :

- Aire d’une parcelle cadastrale
- Surface boisée en France

Schéma descriptif :

STArea() sql geography

 

Implémentation SQL Server :

--STArea
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon as 'Polygon', @polygon.STArea() as 'Area'

Résultat :

STArea() resultat geography

STArea() resultat geography

 

  • Méthodes de parcours ou manipulation des propriétés des éléments

    • Détermination du point de départ de l’élément : STStartPoint

Description :

Cette méthode permet de retourner le premier point (géométrie) d’une géométrie définie.

Note : La méthode renvoie le point concerné dans le cas où la géométrie correspond à un simple point.

Schéma descriptif :

STStartPoint() description details

Implémentation SQL Server :

--STStartPoint
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon as 'Polygon', @polygon.STStartPoint().ToString() as 'StartPoint'

Résultat :

STStartPoint() geography resultat

On remarque donc qu’il s’agit bien du premier point défini dans la déclaration de la géométrie de type polygone.

 

    • Détermination du dernier point de l’élément : STEndPoint

Description :

Cette méthode retourne sur le même principe que la méthode précédente, le dernier point défini dans la géométrie.

Note : Dans le cas d’un point, cette méthode retounr le point correspondant à la géométrie. Dans le cas d’un polygone, on observera souvent le même résultat que la méthode STStartPoint() tout simplement parce que la géométrie d’un polygone doit être fermée et par conséquent le point de définition initial est le même que le point final.

Schéma descriptif :

STEndPoint() description sql geography

 

Implémentation SQL Server :

--STEndPoint
DECLARE @line AS GEOGRAPHY
SET @line = GEOGRAPHY::STGeomFromText('LINESTRING(3 45,4 45,4 44)', 4326);
SELECT @line as 'Line', @line.STEndPoint().ToString() as 'EndPoint'

Résultat :

STEndPoint() geography resultat

 

    • Récupération du nombre de points de l’élément : STNumPoints

Description :

Cette méthode retourne le nombre de point composant la géométrie définie.

Note : Dans le cas d’un point, la méthode retourne 1.

Schéma descriptif :

STNumPoints() sql geography

 

Implémentation SQL Server :

--STNumPoints
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon as 'Polygon', @polygon.STNumPoints() as 'NumPoints'

Résultat :

STNumPoints() resultat geography

 

    • Récupération d’un point spécifique : STPointN

Description :

Cette méthode permet de récupérer un point particulier déterminé par son index, composant la géométrie.

Note : l’index noté N est un entier et commence à 1. Par conséquent STPointN(1) équivaut à STStartPoint().

Schéma descriptif :

STPointN() sql geography

 

Implémentation SQL Server :

--STPointN
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon as 'Polygon', @polygon.STPointN(1).ToString() as 'STPointN'

Résultat :

STPointN() result geography

 

    • Récupération du type d’élément : STGeometryType

Description :

Cette méthode renvoie le type de géométrie qui est associé à l’élément défini. Ce type peut donc correspondre aux différents présentés dans les articles précédents de cette série : Point, linestring, polygon ou collections…

Schéma descriptif :

image

Implémentation SQL Server :

--STGeometryType
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon as 'Polygon', @polygon.STGeometryType() as 'Type'

Résultat :

STGeometryType() resultat geography

 

    • Récupération d’une géométrie spécifique de l’élément : STGeometryN

Description :

Cette méthode est très utile dans le cas de collection de géométrie car elle permet de renvoyer une instance géométrique de cette collection. Il devient par exemple possible de récupérer le N-ième élément d’une collection de géométrie et donc potentiellement de récupérer les inner-rings d’un polygone dit “à trous”.

Schéma descriptif :

STGeometryN()

Implémentation SQL Server :

--STGeometryN
DECLARE @multipolygon AS GEOGRAPHY
SET @multipolygon = GEOGRAPHY::STGeomFromText('MULTIPOLYGON(((3 45,4 45,4 46,3 45)),((5 46,6 47,5 47,5 46)))',4326)
SELECT @multipolygon, @multipolygon.STGeometryN(2) as 'SecondGeometry'

Résultat :

STGeometryN() resultat sql geography

 

    • Récupération du nombre de dimension associée à la représentation de l’élément (2D ou 3D) : STDimension

Description :

Cette méthode retourne le nombre de dimension associée à la représentation de l’élément, ainsi une surface s’incrivant dans un plan simple comportera logiquement 2 dimensions, tandis que si l’on définit cette surface avec des points indiquant l’altitude (la valeur Z) on obtiendra 3 dimensions.

Implémentation SQL Server :

--STDimension
DECLARE @line AS GEOGRAPHY
SET @line = GEOGRAPHY::STGeomFromText('LINESTRING(3 45,4 45,4 44)', 4326);
SELECT @line as 'Line', @line.STDimension() as 'Dimension'
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon as 'Polygon', @polygon.STDimension() as 'Dimension'

Résultat :

STDimension() resultat sql geography

 

    • Récupération du SRID associé : STSrid

Description :

Cette méthode retourne l’identifiant du référentiel spatial utilisé (SRID) et qui permet de définir l’élément.

Implémentation SQL Server :

--STSrid
DECLARE @line AS GEOGRAPHY
SET @line = GEOGRAPHY::STGeomFromText('LINESTRING(3 45,4 45,4 44)', 4326);
SELECT @line as 'Line', @line.STSrid as 'SRID'

Résultat :

STSrid resultat sql geography geometry

 

  • Méthodes de relations entre les éléments

    • Vérification d’intersection : STIntersects

Description :

Cette méthode vérifie l’intersection entre deux géométries utilisées. Elle retourne True si les géométries présentent une intersection.

Cas pratiques :

- Vérification de l’existence d’une intersection
- Détection de points communs entre deux géométries

Schéma descriptif :

STIntersects() sql server geography

 

Implémentation SQL Server :

--STIntersects
DECLARE @polygonA AS GEOGRAPHY, @polygonB AS GEOGRAPHY
SET @polygonA = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SET @polygonB = GEOGRAPHY::STGeomFromText('POLYGON((3 44,4 44,4 45,3 44))', 4326);
SELECT @polygonA.STIntersects(@polygonB) AS 'Intersects'

Résultat :

STIntersects sql geography

 

    • Détermination de l’intersection : STIntersection

Description :

La méthode STIntersection() retourne la ou les géométries correspondant à l’intersection entre deux géométries utilisées.

Cas pratiques :

- Recherche polygonale
- Zone concernée par un projet de construction ou d’urbanisme

Schéma descriptif :

STIntersection() sql geography precision et description

 

Implémentation SQL Server :

--STIntersection
DECLARE @polygonA AS GEOGRAPHY, @polygonB AS GEOGRAPHY
SET @polygonA = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SET @polygonB = GEOGRAPHY::STGeomFromText('POLYGON((3 44,4 44,4 45,3 44))', 4326);
SELECT @polygonA.STIntersection(@polygonB) AS 'Intersection'
UNION ALL SELECT @polygonA
UNION ALL SELECT @polygonB

Résultat :

STIntersection sql geography

 

    • Différence entre deux éléments : STDifference

Description :

Cette méthode permets d’obtenir la géométrie correspondant à la partie de la géométrie n’étant pas en intersection avec une seconde géométrie.

Cas pratiques :

- Surface non détruite par un événément climatique
- Tronçon de route non concerné par une zone travaux

Schéma descriptif :

STDifference() sql server geography

 

Implémentation SQL Server :

--STDifference
DECLARE @polygonA AS GEOGRAPHY, @polygonB AS GEOGRAPHY
SET @polygonA = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SET @polygonB = GEOGRAPHY::STGeomFromText('POLYGON((3 44,4 44,4 45,3 44))', 4326);
SELECT @polygonA.STDifference(@polygonB) AS 'Difference A from B'

Résultats :

image

 

    • Différence symétrique entre deux éléments : STSymDifference

Description :

Cette méthode permet de récupérer la différence symétrique entre deux géographies. En clair, la méthode permet de récupérer tout ce qui n’est pas commun aux deux géométries utilisées.

Cas pratiques :

- Zone couverte par un seul opérateur mobile
- Extraction de points distincts (non-communs entre 2 collections de points)

Schéma descriptif :

STSymDifference() sql server geography

Implémentation SQL Server :

--STSymDifference
DECLARE @polygonA AS GEOGRAPHY, @polygonB AS GEOGRAPHY
SET @polygonA = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SET @polygonB = GEOGRAPHY::STGeomFromText('POLYGON((3 44,4 44,4 45,3 44))', 4326);
SELECT @polygonA.STSymDifference(@polygonB) AS 'SymDifference'

Résultats :

STSymDifferenc() stsymdifference between a and b sql server geography (SQL Server 2012)

 

 

    • Combinaison avec un autre élément : STUnion

Description :

Cette méthode permet de récupérer la géométrie résultant de l’union des deux géographies.

Cas pratiques :

- Couverture géographique et aérienne cumulée
- Ensemble des batis d’un projet immobilier

Schéma descriptif :

STUnion() resultat sql geography

 

Implémentation SQL Server :

--STUnion
DECLARE @polygonA AS GEOGRAPHY, @polygonB AS GEOGRAPHY
SET @polygonA = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SET @polygonB = GEOGRAPHY::STGeomFromText('POLYGON((3 44,4 44,4 45,3 44))', 4326);
SELECT @polygonA.STUnion(@polygonB) AS 'Union'

Résultat :

STUnion() sql geography

 

  • Méthodes de transformation d’éléments

    • Transformation en représentation binaire WKB : STAsBinary

Description :

Cette méthode permet de renvoyer la valeur de la colonne sous forme de binaire sous forme de Well Known Binary (WKB)

Implémentation SQL Server :

--STAsBinary
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon.STAsBinary() AS 'WKB'

Résultat :

STAsBinary() sql geography

 

    • Transformation en représentation textuelle WKT : STAsText

Description :

Comme la précédente, cette méthode permet de renvoyer la valeur modifiée mais cette fois-ci sous forme textuelle Well Known Text (WKT).

Implémentation SQL Server :

--STAsText
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon.STAsText() AS 'WKT'

Résultat :

STAsText() sql geography

 

    • Création d’un buffer autour de l’élément : STBuffer

Description :

Cette méthode permet de composer une autre géométrie tampon autour de celle d’origine en utilisant une distance passée en paramètre.

Cas pratiques :

- Création d’une zone de recherche à vol d’oiseau autour d’un point spécifique 
- Création d’un corridor de recherche

Schéma descriptif :

STBuffer() sql server geography

 

Implémentation SQL Server :

--STBuffer
DECLARE @line AS GEOGRAPHY
SET @line = GEOGRAPHY::STGeomFromText('LINESTRING(3 45,4 45,4 44)', 4326);
SELECT @line.STBuffer(5000) AS 'Buffer'
DECLARE @polygon AS GEOGRAPHY
SET @polygon = GEOGRAPHY::STGeomFromText('POLYGON((3 45,4 44,4 45,3 45))', 4326);
SELECT @polygon.STBuffer(5000) AS 'Buffer'

Résultat :

STBuffer() sql geography buffer (tampon)

 

  • Méthodes statiques sur le type géographique

    • Création d’un élément à partir de la représentation textuelle connue – WKT : STGeomFromText

Description :

Cette méthode statique permet d’initialiser la valeur d’une colonne géographique à partir de la définition WKT.

Implémentation SQL Server :

--STGeomFromText
DECLARE @line AS GEOGRAPHY
SET @line = GEOGRAPHY::STGeomFromText('LINESTRING(3 45,4 45,4 44)', 4326);
SELECT @line.STAsBinary() AS 'Line WKB', @line as 'Line'
Résultat :

STGeomFromText() resultat sql geography geometry

 

    • Création d’un élément à partir de la représentation binaire connue – WKB : STGeomFromWKB

Description :

Dans le même esprit que la méthode précédente, cette méthode statique permet d’initialiser la valeur d’une colonne géographique ou géométrique à partir de la définition WKB.

Implémentation SQL Server :

--STGeomFromWKB
DECLARE @line AS GEOGRAPHY
SET @line = GEOGRAPHY::STGeomFromWKB(0x010200000………000004640, 4326);
SELECT @line AS 'Line', @line.STAsText() AS 'WKT'

Résultat :

STGeomFromWKB() resultat sql geography geometry

 

Conclusions

Ces méthodes et les possibilités offertes par ces opérations sur les données montrent une fois de plus l’intérêt de l’utilisation de ce type géographique (ou bien même le type géométrique) surtout dans leurs utilisations combinées.

En effet, en l’absence de type géographique ou géométrique ou en cas de non-utilisation de ces types, il devient nécessaire d’utiliser des formules de calcul coûteuses et certainement approximatives et délicates à écrire (ex : calcul de surface d’une géométrie multiples, génération du buffer d’une zone) pour obtenir des résultats exploitables et précis alors qu’ici en utilisant le moteur SQL Server, ces méthodes sont présentes et conformes aux standards imposés et directement utilisables sur ces types.

Dans un prochain article nous nous intéresserons aux méthodes spécifiques à SQL Server (méthodes non standards) et nous analyserons avec précision les performances de chacunes de ces opérations.