SQL Server 2008 a introduit de nombreux nouveaux types de données, dont le type de données géographiques GEOGRAPHY.
Ce n’est pas un type habituel, puisque c’est un type CLR.NET intégré à SQL Server.
L’avantage présenté par l’intégration de ce type est l’ensemble des méthodes standard « livrées » avec ce type, qui permettent d’extraire très simplement une latitude (attribut Lat), une longitude (attribut Long), ou encore de connaître la distance entre deux points géographiques avec la méthode STDistance.
Mais il devient alors plus complexe de garantir l’unicité de positions dans une table où l’on stocke la celle de plusieurs villes.
En effet, si une colonne de ce type est spécifiée comme clé d’une contrainte d’unicité, le moteur de base de données SQL Server lève une exception.
Est-il possible de contourner ce problème ?
Si nous tentons de créer la table suivante :
1 2 3 4 5 6 | CREATE TABLE dbo.TbCoordonneeVille ( IDCoordonneeVille INT NOT NULL IDENTITY CONSTRAINT PK_TbCoordonneeVille PRIMARY KEY, nomVille VARCHAR(45) NOT NULL CONSTRAINT UQ_TbCoordonneeVille_nomVille UNIQUE, geoPositionVille GEOGRAPHY NOT NULL CONSTRAINT UQ_TbCoordonneeVille_geoPositionVille UNIQUE ) |
Nous obtenons l’erreur :
Msg 1919, Niveau 16, État 1, Ligne 1
La colonne ‘geoPositionVille’ dans la table ‘TbCoordonneeVille’ n’est pas d’un type valide lui permettant d’être utilisée en tant que colonne clé dans un index.
Msg 1750, Niveau 16, État 0, Ligne 1
Impossible de créer la contrainte. Voir les erreurs précédentes.
Nous sommes donc contraints de créer la table sans la contrainte d’unicité sur la colonne geoPositionVille :
1 2 3 4 5 6 | CREATE TABLE dbo.TbCoordonneeVille ( IDCoordonneeVille INT NOT NULL IDENTITY CONSTRAINT PK_TbCoordonneeVille PRIMARY KEY, nomVille VARCHAR(45) NOT NULL CONSTRAINT UQ_TbCoordonneeVille_nomVille UNIQUE, geoPositionVille GEOGRAPHY NOT NULL ) |
Comment faire pour garantir l’unicité des positions des villes sans perdre les avantages offerts par le type GEOGRAPHY ?
Il est possible de créer une contrainte d’unicité sur des colonnes calculées persistantes.
Ajoutons donc deux colonnes calculées, une pour la latitude, et une autre pour la longitude.
Attention, la casse sur les méthodes CLR est importante.
1 2 3 4 5 6 | ------------------------------- -- Nicolas SOUQUET - 29/10/2009 ------------------------------- ALTER TABLE dbo.TbCoordonneeVille ADD latitudeVille AS (geoPositionVille.Lat) PERSISTED NOT NULL, longitudeVille AS (geoPositionVille.Long) PERSISTED NOT NULL |
Il nous suffit ensuite de créer une contrainte d’unicité sur ces deux colonnes :
1 2 3 4 5 6 | ------------------------------- -- Nicolas SOUQUET - 29/10/2009 ------------------------------- ALTER TABLE dbo.TbCoordonneeVille ADD CONSTRAINT UQ_TbCoordonneeVille_latitudeVille_longitudeVille UNIQUE (latitudeVille, longitudeVille) |
Essayons maintenant d’insérer deux villes dont le nom est différent, mais dont les coordonnées sont identiques :
1 2 3 4 5 6 7 | INSERT INTO dbo.TbCoordonneeVille (nomVille, geoPositionVille) VALUES ('Toulouse', GEOGRAPHY::STPointFromText('POINT(43.604503 1.444026)', 4326)) GO INSERT INTO dbo.TbCoordonneeVille (nomVille, geoPositionVille) VALUES ('Lyon', GEOGRAPHY::STPointFromText('POINT(43.604503 1.444026)', 4326)) GO |
La première instruction se passe sans problèmes.
En revanche la seconde échoue, et nous obtenons l’erreur suivante :
Msg 2627, Niveau 14, État 1, Ligne 1
Violation de la contrainte UNIQUE KEY ‘UQ_TbCoordonneeVille_latitudeVille_longitudeVille’. Impossible d’insérer une clé en double dans l’objet ‘dbo.TbCoordonneeVille’.
L’instruction a été arrêtée.
Vérifions le contenu de la table :
Une solution alternative est de créer une vue indexée, ce qui présentera un bon avantage si la table est souvent lue.
Supprimons la table puis recréons-la :
1 2 3 4 5 6 7 8 9 | DROP TABLE dbo.TbCoordonneeVille GO CREATE TABLE dbo.TbCoordonneeVille ( IDCoordonneeVille INT NOT NULL IDENTITY CONSTRAINT PK_TbCoordonneeVille PRIMARY KEY, nomVille VARCHAR(45) NOT NULL CONSTRAINT UQ_TbCoordonneeVille_nomVille UNIQUE, geoPositionVille GEOGRAPHY NOT NULL ) |
Créons ensuite la vue :
1 2 3 4 5 6 7 8 9 10 | ------------------------------- -- Nicolas SOUQUET - 29/10/2009 ------------------------------- CREATE VIEW dbo.VwCoordonneeVille WITH SCHEMABINDING AS SELECT nomVille, geoPositionVille.Lat AS latitudeVille, geoPositionVille.Long AS longitudeVille FROM dbo.TbCoordonneeVille |
Puis indexons-la :
1 2 3 4 5 | ------------------------------- -- Nicolas SOUQUET - 29/10/2009 ------------------------------- CREATE UNIQUE CLUSTERED INDEX IXUQC_VwCoordonneeVille_latitudeVille_longitudeVille ON dbo.VwCoordonneeVille(latitudeVille, longitudeVille) |
Et tentons les mêmes insertions que celles que nous avons faites pour tester la contrainte d’unicité sur les colonnes calculées persistantes :
1 2 3 4 5 6 7 | INSERT INTO dbo.TbCoordonneeVille (nomVille, geoPositionVille) VALUES ('Toulouse', GEOGRAPHY::STPointFromText('POINT(43.604503 1.444026)', 4326)) GO INSERT INTO dbo.TbCoordonneeVille (nomVille, geoPositionVille) VALUES ('Lyon', GEOGRAPHY::STPointFromText('POINT(43.604503 1.444026)', 4326)) GO |
Là encore, la première instruction se passe sans problème, mais la seconde échoue, provoquant l’erreur suivante :
Msg 2601, Niveau 14, État 1, Ligne 1
Impossible d’insérer une ligne de clé en double dans l’objet ‘dbo.VwCoordonneeVille’ avec un index unique ‘IXUQC_VwCoordonneeVille_latitudeVille_longitudeVille’.
L’instruction a été arrêtée.
Interrogeons néanmoins la vue :
ElSuket