SQL Server 2008 Spatial
Calculer la distance entre deux points sur le globe avec SQL Server 2008
Quoi de neuf ?
Il y a deux ans, dans le cadre d'un de mes projets, je devais retrouver les arrêts de bus situé à proximité d'une position fournie par un GPS. J'avais donc importé les longitudes et latitudes de toutes les stations dans des champs de type "double" d'une base SQL Server 2005.
J'avais ensuite adapté en C# une routine de calcul de distance dite de 'Haversine' (http://www.movable-type.co.uk/scripts/latlong.htm).
Avec SQL Server 2008, Microsoft a intégré dans son SGBD le support natif des coordonnées spatiales. En clair, deux nouveaux types de données on fait leur apparition : geometry et geography.
Geometry représente une position de type x, y sur un plan à deux dimensions. Ce type de coordonnées est suffisant pour le calcul de distances à l'intérieur de surfaces réduites (le plan d'un bâtiment par exemple).
Geography permet de définir une position à la surface d'une sphère par sa latitude et sa longitude.
Enfin, avant de commencer à taper quelques requêtes, une petite explication sur le SRID (Spatial Reference Identifier) s'impose. Chaque unité de type spatiale doit être taggée avec un SRID, qui indique le système de représentation utilisé. Cette information est importante car les opérations sur les données spatiales ne fonctionnent qu'avec le même SRID.
Quelques exemples...
A titre d'exemple, j'ai choisi de créer une table pour stocker les coordonnées d'aéroports. La structure de mon exemple est très simple :
Pour la petite histoire, le code ICAO (International Civil Aviation Organization) est un identifiant internationnal de 4 lettres pour les aéroports civils. Il servira donc de clef primaire dans la table Aiports.
Nous allons commencer par insérer les coordonnées des aéroports de Luxembourg et de Brussels National :
La méthode STGeomFromText() du namespace geography va nous permettre de spécifier des coordonnées géographiques dans le format de notre choix. Dans ce cas, 4326 correspond au standard WGS84, où longitude et latitude sont exprimés en degrés décimaux.
Si nous faisons maintenant un SELECT sur la table, nous pouvons voir que les coordonnées spatiales ne sont pas directement lisibles :
Le type de données geography nous offre heureusement la méthode STAsText() pour convertir la valeur native du champ en quelque chose de lisible :
Nous allons maintenant très facilement pouvoir calculer la distance entre deux aéroports, à l'aide de la méthode STDistance() du type geography :
Le résultat est exprimé en mètres. Un petit coup d'oeil sur une carte de la Belgique nous permet de confirmer que la distance réelle est bien d'environ 188 km à vol d'oiseau. CQFD...
Quoi de neuf ?
Il y a deux ans, dans le cadre d'un de mes projets, je devais retrouver les arrêts de bus situé à proximité d'une position fournie par un GPS. J'avais donc importé les longitudes et latitudes de toutes les stations dans des champs de type "double" d'une base SQL Server 2005.
J'avais ensuite adapté en C# une routine de calcul de distance dite de 'Haversine' (http://www.movable-type.co.uk/scripts/latlong.htm).
Avec SQL Server 2008, Microsoft a intégré dans son SGBD le support natif des coordonnées spatiales. En clair, deux nouveaux types de données on fait leur apparition : geometry et geography.
Geometry représente une position de type x, y sur un plan à deux dimensions. Ce type de coordonnées est suffisant pour le calcul de distances à l'intérieur de surfaces réduites (le plan d'un bâtiment par exemple).
Geography permet de définir une position à la surface d'une sphère par sa latitude et sa longitude.
Enfin, avant de commencer à taper quelques requêtes, une petite explication sur le SRID (Spatial Reference Identifier) s'impose. Chaque unité de type spatiale doit être taggée avec un SRID, qui indique le système de représentation utilisé. Cette information est importante car les opérations sur les données spatiales ne fonctionnent qu'avec le même SRID.
Quelques exemples...
A titre d'exemple, j'ai choisi de créer une table pour stocker les coordonnées d'aéroports. La structure de mon exemple est très simple :
CREATE TABLE Airports(
ICAO char(4) NOT NULL,
Name varchar(64) NOT NULL,
Location geography NULL,
CONSTRAINT PK_Airports PRIMARY KEY CLUSTERED ( ICAO ASC )
)
go
Pour la petite histoire, le code ICAO (International Civil Aviation Organization) est un identifiant internationnal de 4 lettres pour les aéroports civils. Il servira donc de clef primaire dans la table Aiports.
Nous allons commencer par insérer les coordonnées des aéroports de Luxembourg et de Brussels National :
insert into Airports
values(
'ELLX',
'Luxembourg',
geography::STGeomFromText('POINT(49.626599 6.211506)',4326)
)
insert into Airports
values(
'EBBR',
'Brussels Nat',
geography::STGeomFromText('POINT(50.905749 4.479145)',4326)
)
La méthode STGeomFromText() du namespace geography va nous permettre de spécifier des coordonnées géographiques dans le format de notre choix. Dans ce cas, 4326 correspond au standard WGS84, où longitude et latitude sont exprimés en degrés décimaux.
Si nous faisons maintenant un SELECT sur la table, nous pouvons voir que les coordonnées spatiales ne sont pas directement lisibles :
select Location from Airports
Location
----------------------------------------------
0xE6100000010C3EB14E95EF734940ED2AA4FCA4EA1140
0xE6100000010C685A626534D0484090A1630795D81840
Le type de données geography nous offre heureusement la méthode STAsText() pour convertir la valeur native du champ en quelque chose de lisible :
select Location.STAsText() Location from Airports
Location
--------------------------
POINT (50.905749 4.479145)
POINT (49.626599 6.211506)
Nous allons maintenant très facilement pouvoir calculer la distance entre deux aéroports, à l'aide de la méthode STDistance() du type geography :
select
a1.Location.STDistance(a2.Location) [Distance (m)]
from
Airports a1, Airports a2
where
a1.ICAO = 'ELLX' and a2.ICAO = 'EBBR'
Distance (m)
----------------------
188406.913414101
Le résultat est exprimé en mètres. Un petit coup d'oeil sur une carte de la Belgique nous permet de confirmer que la distance réelle est bien d'environ 188 km à vol d'oiseau. CQFD...
Commentaires