Introduction
Cet article se veut une première introduction aux nouveaux types spatiaux de SQL Server 2008. Je vais vous les présenter et nous les manipulerons. Nous aborderons aussi leur utilisation avec du code .NET. Enfin, nous utiliserons Virtual Earth pour présenter les données géographiques mais nous ne rentrerons pas dans le détail de cette technologie.
L’aventure « Spatial »
SQL Server 2008 apporte dans son lot de nouveautés le support des données spatiales. Mais que cache-t-on derrière ce terme spatial. En fait, ce type de donnée va servir à représenter des informations géographiques. Dans cette catégorie, on inclut les données vectorielles comme des points (coordonnées GPS, points dans un plan, etc. [1]) mais aussi des lignes et des polygones.
Toutes ces données vectorielles respectent des standards, ceux définis par l’OGC (Open Geospatial Consortium) dont Microsoft est membre actif comme tous les autres grands du secteur. Le respect de ces standards est indispensable pour la compatibilité et l’interopérabilité avec les systèmes existants et Microsoft ne s’y trompe pas.
Alors à quoi ressemble une donnée spatiale ? On peut la représenter selon 3 formats :
- WKT : Well-Known Binary
- WKB : Well-Known Text
- GML : Geography Markup Language
Que l’on représente la donnée en binaire, en XML ou en texte, on retrouve toujours ce genre de langage de description :
- POINT (43.6042618680962 1.44367218017578)
- POLYGON((1 0, 0 1, 1 2, 2 1, 1 0), (2 0, 1 1, 2 2, 3 1, 2 0))
Vous trouverez plus loin des exemples avec toutes les possibilités de données.
Cependant, il faut noter que ces données doivent se classer dans 2 domaines bien distincts : la géométrie et la géodésique. Le premier s’utilise dans un plan avec des coordonnées X/Y alors que le second se base sur la forme de la terre (qui n’est pas tout à fait ronde), avec des coordonnées latitude/longitude.
Les nouveaux types
Du fait de ces 2 domaines de représentation, on distingue 2 types dans SQL Server 2008 :
- geometry : pour le système géométrique
- geography : pour le système géodésique
Néanmoins, ces 2 types se basent sur la même hiérarchie de classes.
Figure 1 - Les types spatiaux (source BOL)
A noter que tous ces types sont des types CLR.
Pour instancier le bon objet, il suffit d’appeler une des méthodes statiques d’un des 2 types et de lui passer les informations de construction au format binaire ou texte.
|
Objet géographique |
Méthode |
WKT |
|
Point |
STPointFromText |
POINT(x y) |
|
Ligne |
STLineFromText |
LINESTRING(x1 y1,…,xn yn) |
|
Polygone |
STPolyFromText |
POLYGON(x1 y1,…,xn yn, x1 y1) |
|
Collections |
STMxxxFromText |
MULTILINESTRING((x1 y1,…,xn yn), …)
MULTIPOINT((x y), (z w), …)
MULTIPOLYGON(…) |
Notez que toutes les méthodes ont un équivalent binaire de la forme STxxxFromWKB. Egalement, toutes ces créations d’objets peuvent être faites par une méthode générique : STGeomFromText.
Voici quelques exemples d’instanciation d’objets spatiaux :
DECLARE @geom geometry = geometry::STMPolyFromText('MULTIPOLYGON (((5 5, 10 5, 10 10, 5 5)), ((10 10, 100 10, 200 200, 30 30, 10 10)))', 0);
DECLARE @geom2 geometry = geometry::STPointFromText('POINT (0 0)', 0);
DECLARE @geog geography = geography::STGeomFromText('LINESTRING(43.14 3.108, 43.145 3.13)', 4326);
DECLARE @geog2 geography = geography::Parse('POINT(43.13043037365261 3.1371116638183527)');
Que ce soit un type geometry ou geography, vous noterez un second paramètre passé en plus du WKT. Ce paramètre est le SRID pour Spatial Reference ID. Il indique quel système de calcul est retenu. Pour un type geometry, il sera égal à 0 car nous somme dans un plan simple. Pour le type geography, on prendra la valeur 4326 qui correspond au système standard. A noter que la méthode Parse prend la valeur par défaut (0 pour geometry, 4326 pour geography).
On peut connaitre la liste des SRID disponibles grâce à la vue système suivante :
select * from sys.spatial_reference_systems
A l’inverse, pour obtenir une lecture plus lisible des valeurs on utilisera la méthode STAsText qui nous retourne le WKT. On notera aussi les méthodes STX et STY qui permettent de récupérer les coordonnées d’un point géométrique.
Select @geog.STAsText()
Select @geom2.STX
Après ce petit aperçu de ces nouveaux types, voyons leur utilisation dans une table. Il n’y a pas de manipulation particulière, les 2 nouveaux types s’utilisent comme tous les autres :
CREATE TABLE CustomerPlaces
( CustomerId int,
Localization geography,
LocalizationString AS Localization.STAsText()
);
CREATE TABLE Shapes
(
ShapeName varchar(50),
Layer int,
Color char(6),
Shape geometry
);
Pour l’insertion, on choisira indépendamment un simple parsing ou une instanciation bien qualifiée.
Premières requêtes
Les 2 nouveaux types proposent toute une série de méthodes pour manipuler des données spatiales. Aire de la surface, surface circonscrite, inscrite, périmètre, intersection, union, barycentre… Le but n’est pas de vous les décrire ici une à une. Nous allons toutefois en utiliser quelques unes dans des requêtes simples mais indispensables dans une application gérant des types spatiaux.
Tout d’abord, voyons comment retrouver les points contenus dans une zone donnée. Comme cas concret, on peut imaginer une recherche d’appartements ou d’hôtels dans une zone définie. Pour cela, nous avons plusieurs possibilités avec les méthodes STContains, STIntersects et STWithin. La méthode STIntersects serra utilisée pour le type geography car les 2 autres n’existent que sur le type geometry. Voici le code T-SQL permettant cette requête :
Declare @zone geography = geography::STPolyFromText('POLYGON((43 3.2, 43 3.5, 43.2 3.5, 43.2 3.2, 43 3.2))', 4326)
Select *
From CustomerPlaces
Where @zone.STIntersects(CustomerPlaces.Localization) = 1
Declare @outShape geometry = geometry::STPolyFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326)
Select *
From Shapes
Where @outShape.STContains(Shapes.Shape) = 1
Select *
From Shapes
Where Shape.STWithin(@outShape) = 1
La différence entre ces méthodes est l’inclusion complète ou partielle de l’élément dans la zone recherchée mais pour des points, cela ne posera pas de problèmes.
En second exemple, on veut retrouver le ou les points les plus proches d’un point donné. Le cas concret associé est de trouver un parking ou la station météo la plus proche. Pour atteindre ce résultat, nous allons utiliser la méthode STDistance.
DECLARE @currentPlace geography = geography::Parse('POINT(43.13043037365261 3.1371116638183527)');
Select top 1 CustomerID
From CustomerPlaces
Where Localization.STDistance(@currentPlace) < 300
Indexation
Les méthodes présentées ci-dessus permettent de retrouver facilement des points. Cependant, dans une table contenant des centaines de milliers de points, on pourrait s’inquiéter des performances. En effet, pour obtenir le résultat, le moteur de requête est obligé de lire toute la table, de charger tous les types dans la CLR et d’effectuer le calcul, comme on peut le voir sur le plan d’exécution suivant.
Figure 2 - Plan d'exécution d'une recherche sans index spatial
Pour améliorer les performances, ce type ne pouvait pas s’accompagner d’un système d’indexation. SQL Server s’enrichit donc d’un nouveau type d’index : les index spatiaux.
Pourquoi ne pas avoir utilisé les index classiques de SQL Server ? Tout simplement parce qu’on doit placer des points dans l’espace et que la manière dont est représentée une clé dans un B-Tree classique ne le permet pas.
La méthode d’indexation utilisée est la « tesselation » (ou maillage). La surface est découpée en zones égales, chaque zone est elle-même découpée et ainsi de suite sur plusieurs niveaux. Le principe du B-Tree est conservé puisqu’il permet de naviguer dans les différentes zones pour atteindre celle du point ou de la forme que l’on recherche. Le schéma suivant illustre ce principe.
Figure 3 - Index spatiaux (source BOL)
La création de l’index ressemble à la création d’un index classique. On ajoute juste le mot-clé SPATIAL et quelques options de tuning.
CREATE SPATIAL INDEX [IX_Places] ON [dbo].[CustomerPlaces]
(
[Localization]
) USING GEOGRAPHY_GRID
WITH (CELLS_PER_OBJECT = 16);
Toutes les méthodes des types spatiaux ne s’appuient pas sur les index spatiaux. Les méthodes suivantes en tirent partie, elles correspondent à la recherche de points (ou éléments) et aux questions de recoupage (contenance, intersection).
- geometry1.STContains( geometry2 ) = 1
- geometry1.STDistance ( geometry2 ) < number
- geometry1.STDistance ( geometry2 ) <= number
- geometry1.STEquals ( geometry2 ) = 1
- geometry1.STIntersects ( geometry2 ) = 1
- geometry1.STOverlaps (geometry2) = 1
- geometry1.STTouches ( geometry2 ) = 1
- geometry1.STWithin ( geometry2 ) = 1
Attention, contrairement à un index classique qui pourrait être utilisé sur un opérateur de type ORDER BY, l’index spatial ne s’utilisera que dans une clause WHERE.
Le résultat de l’utilisation d’un index spatial dans le plan d’exécution est le suivant :
Figure 4 - Plan d'exécution d'une recherche avec un index spatial
Ici je ne montre qu’une partie du plan d’exécution car il est bien plus complexe qu’un simple opérateur Index Seek. L’utilisation de la tesselation implique plusieurs opérations internes avant d’interroger l’index spatial.
Le résultat de l’indexation est encore plus flagrant avec le SQL Profiler :
Figure 5 - Impact sur les performances d'un index spatial
On gagne plus de 70% sur les lectures logiques. Mais le plus remarquable est le gain en temps CPU car c’est le calcul qui est optimisé. La conséquence, c’est que l’on passe d’une requête de près de 11s à une requête de 36ms.
ADO.NET
Après avoir vu un aperçu des types spatiaux et de leur utilisation dans SQL dans Management Studio, intéressons nous à la manipulation depuis une application .NET.
A ce jour, il n’existe pas d’update d’ADO.NET qui supporte nativement les types spatiaux. Vous avez néanmoins à disposition l’Assembly Microsoft.SqlSever.Types qui contient les types CLR de SQL Server 2008. Vous la trouverez dans le GAC en version 10.0.
Figure 6 - Assembly des nouveaux types SQL Server 2008
On trouve dans cette Assembly un type SqlGeography et un type SqlGeometry que vous pouvez utiliser dans vos applications en bénéficiant de l’instanciation d’éléments spatiaux et de leur manipulation via les méthodes décrites plus haut (STDistance, STIntersects, etc.). Ce sont les mêmes types que ceux de la SQLCLR.
Cependant, ces types ne peuvent pas être utilisés dans des paramètres d’une SqlCommand. Nous allons contourner cette limitation en utilisant des types existants : varchar et varbinary.
Nous avons vu que l’on pouvait représenter un type spatial via les formats WKT et WKB (Well-Known Text et Well-Known Binary). Pour ce qui est du texte, on pourrait construire le WKT par une manipulation de chaine, mais il est plus simple d’utiliser le type SqlGeography mis à notre disposition. Ce dernier dispose des méthodes STAsText et STAsBinary. Reste à passer ça à une SqlCommand classique.
/*
* On crée la structure spatiale
*/
SqlGeography g = SqlGeography.Point(Latitude, Longitude, 4326);
using (SqlConnection cnn = new SqlConnection(connectionStr))
{
using (SqlCommand cmd = cnn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = @"CustomerPlaces_I_WKT";
cmd.Parameters.Add("@WKT", SqlDbType.VarChar).Value = g.STAsText();
//on transforme en WKT (well-known type)
cnn.Open();
cmd.ExecuteNonQuery();
}
}
La variante utilisant le binaire est la suivante :
cmd.CommandText = @"CustomerPlaces_I_WKB";
cmd.Parameters.Add("@WKB", SqlDbType.VarBinary).Value = g.STAsBinary();
//on transforme en WKB (well-known binary)
Alors, quelle méthode préférer pour la manipulation des types spatiaux dans une application .NET ? Des tests de performances vont permettre de statuer sur le choix.
Premièrement, la méthode STAsBinary est 30% moins performante que STAsText dans le code .NET lors de mes essais (100.000 appels). Mais qu’en est-il de l’interprétation côté serveur ?
Coté serveur, j’ai créé 3 procédures stockées. Une pour le texte et une pour le binaire utilisant respectivement les méthodes STGeomFromWKB et STGeomFromText. Et une troisième n’utilisant aucune méthode mais faisant une insertion avec un CAST implicite.
Create Procedure CustomerPlaces_I_WKB
(
@WKB as varbinary(1024)
)
As
Begin
Declare @g geography = geography::STGeomFromWKB(@WKB, 4326)
Insert into Demo.dbo.CustomerPlaces(Localization) Values (@g)
End
go
Create Procedure CustomerPlaces_I_WKT
(
@WKT as varchar(1024)
)
As
Begin
Declare @g geography = geography::STGeomFromText(@WKT, 4326)
Insert into Demo.dbo.CustomerPlaces(Localization) Values (@g)
End
go
Create Procedure CustomerPlaces_I_Native
(
@WKT as varchar(1024)
)
As
Begin
Insert into Demo.dbo.CustomerPlaces(Localization) Values (@WKT)
End
go
La trace faite sur les différents appels montre que l’utilisation du binaire comme format pivot (3ème appel) est à exclure. On voit aussi qu’un transtypage implicite (2ème appel) permet de gagner encore un peu
Figure 7 - Performances ADO.NET
Affichage avec Virtual Earth
Reste un point à traiter, l’affichage des données spatiales. Bien sûr, en fonction de l’utilisation que vous souhaitez faire de ces nouveaux types, vos besoins en restitution peuvent être très différents. Toutefois, il est un mode de restitution incontournable, c’est l’affichage des points ou des zones sur une carte.
Virtual Earth nous permet de faire cette restitution. Cette technologie permet d’avoir accès aux cartes de l’ensemble du globe et de dessiner des points ou des formes dessus, correspondant à nos données spatiales. Cet article ne présente pas en profondeur cette technologie, je vous renvoie à la série d’articles de Bewise à venir sur le sujet écrite par Philippe Lonvaud qui détailleront Virtual Earth.
Ce qu’il faut retenir, c’est que Virtual Earth travaille avec des coordonnées de type longitude/latitude. On manipule les cartes avec du JavaScript. Pour plus de facilité, on peut l’encapsuler dans un extender Ajax pour manipuler le tout via contrôle ASP.NET.
[TargetControlType(typeof(Panel))]
public class VirtualEarthExtender : ExtenderControl
{
/// <summary>
/// Gets or sets the initial latitude.
/// </summary>
public double InitialLatitude {}
/// <summary>
/// Gets or sets the initial longitude.
/// </summary>
public double InitialLongitude {}
/// <summary>
/// Gets or sets the zoom level.
/// </summary>
public int ZoomLevel {}
}
Pour ajouter les points, on doit récupérer les valeurs. Pour cela, nous allons faire appel aux méthodes des types SqlGeography et SqlGeometry coté serveur ET côté client. Côté serveur, on exporte les données spatiales sous la forme WKT et on reconstruit un objet SqlGeography coté client.
string command = @"
select top 1 EmployeeID, Localization.STAsText() as LocalizationText, Comment, [When]
from dbo.Localizations
where EmployeeID = @empID
order by [When] desc";
...
SqlGeography gText = SqlGeography.STGeomFromText(dr.GetSqlChars(dr.GetOrdinal("LocalizationText")), 4326);
l.Latitude = gText.Lat.Value;
l.Longitude = gText.Long.Value;
Il n’y a plus qu’à passer ces coordonnées à Virtual Earth pour l’affichage via le code JavaScript :
var pushpinLocation = new VELatLong(pushpinData.Latitude, pushpinData.Longitude);
var shape = new VEShape(VEShapeType.Pushpin, pushpinLocation);
// Add pushpin to the map.
this._instance.AddShape(shape);
Figure 8 - Représentation de points dans Virtual Earth
Conclusion
Voila pour une première approche des types spatiaux dans SQL Server 2008. Certes le sujet mérite de nombreux articles qui viendront par la suite.
Notamment, pour aller plus loin, je creuserai dans les articles à venir l’utilisation des types spatiaux dans différents scénarios et avec les différents outils de l’écosystème Microsoft. Entre autre, j’aborderai l’utilisation du type spatial dans un projet décisionnel et sa manipulation dans Integration Services.
N’oubliez pas de consulter la série d’article sur Virtual Earth, écrite par Philippe Lonvaud, qui sera bientôt mise à disposition pour présenter et creuser cette technologie. Restez à l’écoute.
[1] Tous ces types d’informations géographiques seront détaillés dans un article consacré à Virtual Earth.