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
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).
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.
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