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

je suis en train de redessiner une base de données client et l'un des nouveaux éléments d'information que je voudrais stocker avec les champs d'adresse standard (rue, ville, etc.) est l'emplacement géographique de l'adresse. Le seul cas d'utilisation que j'ai à l'esprit est de permettre aux utilisateurs de cartographier les coordonnées sur Google maps lorsque l'adresse ne peut pas être trouvée autrement, ce qui se produit souvent lorsque la zone est nouvellement développée, ou est dans un endroit éloigné/rural.

stocker la latitude et la longitude comme valeurs décimales, mais ensuite je me suis souvenu que SQL Server 2008 R2 a un type de données geography . Je n'ai absolument aucune expérience de l'utilisation de geography , et de mes recherches initiales, il semble être exagéré pour mon scénario.

par exemple, pour travailler avec la latitude et la longitude stockées comme 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 qu'il ne soit pas que beaucoup plus compliqué, pourquoi ajouter complexité si je n'ai pas à le faire?

avant d'abandonner l'idée d'utiliser geography , y a-t-il quelque chose que je devrais considérer? Serait-il plus rapide de chercher un emplacement à l'aide d'un index spatial que d'indexer les champs de Latitude et de Longitude? Y a-t-il des avantages à utiliser geography dont je ne suis pas au courant? Ou, de l'autre côté, y a-t-il des mises en garde que je devrais connaître qui pourraient 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.

par contre, un test rapide montre qu'un simple select pour obtenir la latitude et la longitude est significativement plus lent en utilisant geography (détails ci-dessous). , et un commentaire sur la réponse acceptée à l'autre AFIN que la question sur geography a me méfie:

@SaphuA vous êtes les bienvenus. Comme un sidenote être très prudent d'utiliser un index spatial sur une colonne de type de données géographiques nulles. Il y a quelques problème de performance sérieux, donc rendre cette colonne de géographie non-nullable même si tu dois remodeler ton schéma. - Tomas Jun 18 à 11: 18

en somme, soupesant la probabilité de faire des recherches de proximité par rapport au compromis entre performance et complexité, j'ai décidé de renoncer à l'utilisation de geography dans ce cas.


détails de l'essai que j'ai effectué:

j'ai créé deux tableaux, l'un utilisant geography et l'autre utilisant decimal(9,6) pour latitude et 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 a inséré une seule ligne en utilisant les mêmes valeurs de latitude et de longitude dans chaque tableau:

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, sélectionner la latitude et la longitude est environ 5 fois plus lent en utilisant 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 est plus surprenant, c'est que même si aucune ligne n'est sélectionnée , par exemple en sélectionnant où RowId = 2 , qui n'existe pas, geography était encore plus lent:

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

AvgGeographyDuration AvgLatLongDuration
-------------------- ------------------
1608                 947
100
demandé sur Community 2011-09-14 01:55:58

3 réponses

si vous prévoyez de faire un calcul spatial, EF 5.0 autorise des Expressions LINQ 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();
}

alors il y a une très bonne raison D'utiliser la géographie.

Explication de la répartition spatiale au sein de l'Entité Cadre .

mis à Jour avec la Création de la Haute Performance des Bases de données Spatiales

comme je l'ai noté sur réponse de Noel Abrahams :

une note sur l'Espace, chaque coordonnée est stockée comme un nombre de double-précision à virgule flottante qui est 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, donc comparer une décimale(9,6) qui est seulement 5 octets, n'est pas exactement une comparaison juste. Virgule devrait être un minimum de Décimales(15,12) (9 octets) pour chaque LatLong (total de 18 octets) pour une réelle comparaison.

So 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

le type de données géographiques occupe 30% de plus d'espace.

en outre, le type de données de géographie ne se limite pas à stocker un Point, vous pouvez également stocker LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString, et MultiPolygon et plus . Toute tentative pour stocker même le plus simple des types de géographie (comme Lat/Long) au-delà d'un Point(par exemple LINESTRING (1 1, 2 2) instance) entraînera 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 des lignes. SQL Server a également des méthodes pour les types de données géographiques qui comprennent le calcul de zone, Limite, Longueur, Distances, et plus .

il semble imprudent de stocker la Latitude et la Longitude comme décimale dans Sql Server.

Update 2

si vous prévoyez de faire des calculs comme la distance, la superficie, etc., il est difficile de les calculer correctement sur la surface de la terre. Chaque type de géographie stocké dans SQL Server est également stocké avec un Spatial Reference ID . Ces id peuvent être de différentes sphères (la terre est 4326). Cela signifie que les calculs dans SQL Server seront effectivement 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).

enter image description here

64
répondu Erik Philips 2017-05-23 12:18:14

une Autre chose à considérer est l'espace de stockage pris par chaque méthode. Le type de géographie est stocké sous la forme d'un VARBINARY(MAX) . Essayez d'exécuter ce script:

CREATE TABLE dbo.Geo
(
    geo geography

)

GO

CREATE TABLE dbo.LatLon
(
    lat decimal(9, 6)
,   lon decimal(9, 6)

)

GO

INSERT dbo.Geo
SELECT geography::Point(36.204824, 138.252924, 4326) UNION ALL
SELECT geography::Point(51.5220066, -0.0717512, 4326) 

GO 10000

INSERT dbo.LatLon
SELECT  36.204824, 138.252924 UNION
SELECT 51.5220066, -0.0717512

GO 10000

EXEC sp_spaceused 'dbo.Geo'
EXEC sp_spaceused 'dbo.LatLon'

résultat:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   400 KB

le type de données géographiques occupe presque deux fois plus d'espace.

6
répondu Noel Abrahams 2012-06-08 14:59:29
    CREATE FUNCTION [dbo].[fn_GreatCircleDistance]
(@Latitude1 As Decimal(38, 19), @Longitude1 As Decimal(38, 19), 
            @Latitude2 As Decimal(38, 19), @Longitude2 As Decimal(38, 19), 
            @ValuesAsDecimalDegrees As bit = 1, 
            @ResultAsMiles As bit = 0)
RETURNS decimal(38,19)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @ResultVar  decimal(38,19)

    -- Add the T-SQL statements to compute the return value here
/*
Credit for conversion algorithm to Chip Pearson
Web Page: www.cpearson.com/excel/latlong.aspx
Email: chip@cpearson.com
Phone: (816) 214-6957 USA Central Time (-6:00 UTC)
Between 9:00 AM and 7:00 PM

Ported to Transact SQL by Paul Burrows BCIS
*/
DECLARE  @C_RADIUS_EARTH_KM As Decimal(38, 19)
SET @C_RADIUS_EARTH_KM = 6370.97327862
DECLARE  @C_RADIUS_EARTH_MI As Decimal(38, 19)
SET @C_RADIUS_EARTH_MI = 3958.73926185
DECLARE  @C_PI As Decimal(38, 19)
SET @C_PI =  pi()

DECLARE @Lat1 As Decimal(38, 19)
DECLARE @Lat2 As Decimal(38, 19)
DECLARE @Long1 As Decimal(38, 19)
DECLARE @Long2 As Decimal(38, 19)
DECLARE @X As bigint
DECLARE @Delta As Decimal(38, 19)

If @ValuesAsDecimalDegrees = 1 
Begin
    set @X = 1
END
Else
Begin
    set @X = 24
End 

-- convert to decimal degrees
set @Lat1 = @Latitude1 * @X
set @Long1 = @Longitude1 * @X
set @Lat2 = @Latitude2 * @X
set @Long2 = @Longitude2 * @X

-- convert to radians: radians = (degrees/180) * PI
set @Lat1 = (@Lat1 / 180) * @C_PI
set @Lat2 = (@Lat2 / 180) * @C_PI
set @Long1 = (@Long1 / 180) * @C_PI
set @Long2 = (@Long2 / 180) * @C_PI

-- get the central spherical angle
set @Delta = ((2 * ASin(Sqrt((power(Sin((@Lat1 - @Lat2) / 2) ,2)) + 
    Cos(@Lat1) * Cos(@Lat2) * (power(Sin((@Long1 - @Long2) / 2) ,2))))))

If @ResultAsMiles = 1 
Begin
    set @ResultVar = @Delta * @C_RADIUS_EARTH_MI
End
Else
Begin
    set @ResultVar = @Delta * @C_RADIUS_EARTH_KM
End

    -- Return the result of the function
    RETURN @ResultVar

END
-1
répondu Paul Burrows 2017-03-29 12:59:39