Pourquoi utiliser le type de données géographiques SQL Server 2008?

Question

Je suis la refonte d'un client de base de données et l'un des nouveaux éléments d'information, je voudrais stocker avec la norme champs d'adresse (Rue, Ville, etc.) est l'emplacement géographique de l'adresse. Le seul cas d'utilisation j'ai à l'esprit est de permettre aux utilisateurs de la carte les coordonnées sur Google maps lorsque l'adresse ne peut pas être trouvé, ce qui arrive souvent lorsque la zone est nouvellement développé, ou est dans une distance/zone rurale.

Mon premier réflexe a été de stocker la latitude et la longitude des valeurs décimales, mais ensuite je me suis souvenu que SQL Server 2008 R2 a un geography type de données. Je n'ai absolument aucune expérience geography, et de ma recherche initiale, il semble être trop pour mon scénario.

Par exemple, pour travailler avec la latitude et la longitude stockées en tant que decimal(7,4), je peux faire ceci:

insert into Geotest(Latitude, Longitude) values (47.6475, -122.1393)
select Latitude, Longitude from Geotest

mais avec geography, je ferais ceci:

insert into Geotest(Geolocation) values (geography::Point(47.6475, -122.1393, 4326))
select Geolocation.Lat, Geolocation.Long from Geotest

Bien que ce n'est pas que beaucoup plus compliqué, pourquoi ajouter de la complexité si je n'ai pas?

Avant de m'abandonner l'idée d'utiliser geography, est-il quelque chose que je dois prendre en compte? Serait-il plus rapide pour rechercher un emplacement, à l'aide d'un index spatial contre l'indexation de la Latitude et la Longitude des champs? Existe-il des avantages à utiliser le geography que je ne suis pas au courant? Ou, sur le revers de la médaille, il y a les mises en garde que je devrais savoir au sujet de ce qui allait me décourager d'utiliser geography?

---

Mise à jour

@Erik Philips a évoqué la possibilité de faire des recherches de proximité avec geography, ce qui est très cool.

D'autre part, un test rapide montre qu'un simple select pour obtenir la latitude et la longitude est significativement plus lente lors de l'utilisation d' geography (détails ci-dessous). et un commentaire sur la accepté de répondre à l'autre question sur geography a me méfier:

@SaphuA Vous êtes les bienvenus. Au passage être TRÈS prudent de à l'aide d'un index spatial sur une nullable GÉOGRAPHIE colonne de type de données. Il y a quelques grave problème de performance, afin de faire de la GÉOGRAPHIE de la colonne non nullable même si vous devez rénover votre schéma. – Tomas 18 Juin à 11:18

Dans l'ensemble, peser la probabilité de faire des recherches de proximité contre le commerce de la performance et de la complexité, j'ai décidé de renoncer à l'utilisation de l' geography dans ce cas.

---

Détails de l'épreuve j'ai couru:

J'ai créé deux tables, l'une utilisant geography et un autre à l'aide de decimal(9,6) de la latitude et de la longitude:

CREATE TABLE [dbo].[GeographyTest]
(
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [Location] [geography] NOT NULL,
    CONSTRAINT [PK_GeographyTest] PRIMARY KEY CLUSTERED ( [RowId] ASC )
) 

CREATE TABLE [dbo].[LatLongTest]
(
    [RowId] [int] IDENTITY(1,1) NOT NULL,
    [Latitude] [decimal](9, 6) NULL,
    [Longitude] [decimal](9, 6) NULL,
    CONSTRAINT [PK_LatLongTest] PRIMARY KEY CLUSTERED ([RowId] ASC)
) 

et d'insérer une seule ligne, en utilisant les mêmes valeurs de latitude et longitude dans chaque table:

insert into GeographyTest(Location) values (geography::Point(47.6475, -122.1393, 4326))
insert into LatLongTest(Latitude, Longitude) values (47.6475, -122.1393)

Enfin, l'exécution du code suivant montre que, sur ma machine, la sélection de la latitude et de longitude est environ 5 fois plus lente lors de l'utilisation d' geography.

declare @lat float, @long float,
        @d datetime2, @repCount int, @trialCount int, 
        @geographyDuration int, @latlongDuration int,
        @trials int = 3, @reps int = 100000

create table #results 
(
    GeographyDuration int,
    LatLongDuration int
)

set @trialCount = 0

while @trialCount < @trials
begin

    set @repCount = 0
    set @d = sysdatetime()

    while @repCount < @reps
    begin
        select @lat = Location.Lat,  @long = Location.Long from GeographyTest where RowId = 1
        set @repCount = @repCount + 1
    end

    set @geographyDuration = datediff(ms, @d, sysdatetime())

    set @repCount = 0
    set @d = sysdatetime()

    while @repCount < @reps
    begin
        select @lat = Latitude,  @long = Longitude from LatLongTest where RowId = 1
        set @repCount = @repCount + 1
    end

    set @latlongDuration = datediff(ms, @d, sysdatetime())

    insert into #results values(@geographyDuration, @latlongDuration)

    set @trialCount = @trialCount + 1

end

select * 
from #results

select avg(GeographyDuration) as AvgGeographyDuration, avg(LatLongDuration) as AvgLatLongDuration
from #results

drop table #results

Résultats:

GeographyDuration LatLongDuration
----------------- ---------------
5146              1020
5143              1016
5169              1030

AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
5152                 1022

Ce qui était plus surprenant, c'est que même lorsque aucune ligne n'est sélectionnée, par exemple en sélectionnant où RowId = 2, ce qui n'existe pas, geography a été encore plus lente:

GeographyDuration LatLongDuration
----------------- ---------------
1607              948
1610              946
1607              947

AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
1608                 947

Answer 1

Si vous projetez de faire tout calcul spatial, EF 5.0 permet LINQ des Expressions comme:

private Facility GetNearestFacilityToJobsite(DbGeography jobsite)
{   
    var q1 = from f in context.Facilities            
             let distance = f.Geocode.Distance(jobsite)
             where distance < 500 * 1609.344     
             orderby distance 
             select f;   
    return q1.FirstOrDefault();
}

Ensuite, il ya une très bonne raison d'utiliser la Géographie.

Explication de la répartition spatiale au sein de l'Entity Framework.

Mis à jour avec la Création de la Haute Performance des Bases de données Spatiales

Comme je l'ai noté sur Noel Abrahams Réponse:

Une remarque sur l'espace, chaque coordonnée est stocké comme un double-nombre à virgule flottante 64 bits (8 octets) de long, et la valeur binaire de 8 octets est à peu près équivalent à 15 chiffres de précision décimale, de sorte qu'en comparant un nombre décimal(9,6) qui est à seulement 5 octets, ce n'est pas exactement une comparaison équitable. Virgule devrait être un minimum de Décimales(15,12) (9 octets) pour chaque LatLong (total de 18 octets) pour une réelle comparaison.

De sorte qu'en comparant les types de stockage:

CREATE TABLE dbo.Geo
(    
geo geography
)
GO

CREATE TABLE dbo.LatLng
(    
    lat decimal(15, 12),   
    lng decimal(15, 12)
)
GO

INSERT dbo.Geo
SELECT geography::Point(12.3456789012345, 12.3456789012345, 4326) 
UNION ALL
SELECT geography::Point(87.6543210987654, 87.6543210987654, 4326) 

GO 10000

INSERT dbo.LatLng
SELECT  12.3456789012345, 12.3456789012345 
UNION
SELECT 87.6543210987654, 87.6543210987654

GO 10000

EXEC sp_spaceused 'dbo.Geo'

EXEC sp_spaceused 'dbo.LatLng'

Résultat:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   560 KB

Les données de la géographie-type prend jusqu'à 30% d'espace en plus.

En outre, la géographie type de données n'est pas limitée uniquement à stocker un Point, vous pouvez également stocker LineString, CircularString, CompoundCurve, Polygone, CurvePolygon GeometryCollection, MultiPoint, MultiLineString, et MultiPolygon et plus. Toute tentative pour stocker même le plus simple des types géographiques (Lat/Long) au-delà d'un Point (par exemple LINESTRING(1 1, 2 2) de l'instance de), vous obtiendrez des lignes supplémentaires pour chaque point, une colonne pour le séquençage de l'ordre de chaque point et une autre colonne pour le groupement de lignes. SQL Server a aussi des méthodes de la Géographie des types de données qui comprennent les calculs de la Zone, à la Limite, la Durée, la Distance, et plus encore.

Il semble judicieux de stocker de Latitude et de Longitude Décimale dans Sql Server.

Mise à jour 2

Si vous projetez de faire des calculs comme la distance, zone, etc, calcul juste de la ces sur la surface de la terre est difficile. Chaque type de Géographie stockées dans SQL Server est également stockée avec un ID de Référence Spatiale. Ces id peuvent être de différentes sphères (la terre est 4326). Cela signifie que les calculs dans SQL Server en fait de calculer correctement sur la surface de la terre (au lieu de comme-le-corbeau-mouches qui pourrait être par le biais de la surface de la terre).

Answer 2

Une autre chose à considérer est l’espace de stockage, repris par chaque méthode. Le type geography est stocké comme un `` . Essayez d’exécuter ce script :

Résultat :

Le type de données géographie prend presque deux fois plus de place.