significa - ¿Por qué usar el tipo de datos de geografía de SQL Server 2008?

tipos de datos sql pdf

Question

Estoy rediseñando una base de datos de clientes y una de las nuevas piezas de información que me gustaría almacenar junto con los campos de dirección estándar (Calle, Ciudad, etc.) es la ubicación geográfica de la dirección. El único caso de uso que tengo en mente es permitir que los usuarios localicen las coordenadas en los mapas de Google cuando la dirección no se puede encontrar de otra manera, lo que a menudo sucede cuando el área se desarrolla por última vez, o se encuentra en una ubicación remota / rural.

Mi primera inclinación fue almacenar la latitud y la longitud como valores decimales, pero luego recordé que SQL Server 2008 R2 tiene un tipo de datos geography . No tengo absolutamente ninguna experiencia en el uso de la geography , y desde mi investigación inicial, parece ser exagerado para mi escenario.

Por ejemplo, para trabajar con la latitud y la longitud almacenadas como decimal(7,4) , puedo hacer esto:

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

pero con la geography , haría esto:

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

Aunque no es mucho más complicado, ¿por qué agregar complejidad si no es necesario?

Antes de abandonar la idea de usar geography , ¿hay algo que deba considerar? ¿Sería más rápido buscar una ubicación usando un índice espacial vs. indexar los campos de Latitud y Longitud? ¿Hay ventajas en el uso de la geography que no tengo conocimiento? O, por el otro lado, ¿hay advertencias que debería saber que me desanimarían a usar la geography ?

Actualizar

@Erik Philips mencionó la posibilidad de hacer búsquedas de proximidad con geography , lo cual es genial.

Por otro lado, una prueba rápida muestra que una select simple para obtener la latitud y la longitud es significativamente más lenta cuando se usa la geography (detalles a continuación). , y un comentario sobre la respuesta aceptada a otra pregunta SO sobre geography me tiene receloso:

@SaphuA De nada. Como nota al margen, TENGA MUCHO cuidado al usar un índice espacial en una columna de tipo de datos GEOGRAPHY que admite nulos. Hay un problema de rendimiento grave, por lo que la columna GEOGRAPHY no admite nulos, incluso si tiene que remodelar su esquema. - Tomás, 18 de junio a las 11:18

En general, sopesando la probabilidad de realizar búsquedas de proximidad frente a la compensación en rendimiento y complejidad, he decidido renunciar al uso de la geography en este caso.

Detalles de la prueba que ejecuté:

Creé dos tablas, una usando geography y otra usando decimal(9,6) para latitud y longitud:

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

e insertó una sola fila usando los mismos valores de latitud y longitud en cada tabla:

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

Finalmente, ejecutar el siguiente código muestra que, en mi máquina, seleccionar la latitud y la longitud es aproximadamente 5 veces más lento cuando se usa la 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

Resultados:

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

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

Lo que es más sorprendente es que incluso cuando no se seleccionan filas, por ejemplo, seleccionando RowId = 2 , que no existe, la geography era aún más lenta:

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

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

Answer 1

Otra cosa a considerar es el espacio de almacenamiento ocupado por cada método. El tipo de geografía se almacena como VARBINARY(MAX) . Intenta ejecutar este 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''

Resultado:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   400 KB

El tipo de datos geográficos ocupa casi el doble de espacio.

Answer 2

Si planea hacer cualquier cálculo espacial, EF 5.0 permite expresiones LINQ como:

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();
}

Entonces hay una muy buena razón para usar Geografía.

Explicación de espacio dentro de Entity Framework .

Actualizado con la creación de bases de datos espaciales de alto rendimiento

Como noté en Noel Abrahams Respuesta :

Una nota sobre el espacio, cada coordenada se almacena como un número de coma flotante de doble precisión que tiene 64 bits (8 bytes) de largo, y el valor binario de 8 bytes es aproximadamente equivalente a 15 dígitos de precisión decimal, por lo que se compara un decimal (9 , 6) que tiene solo 5 bytes, no es exactamente una comparación justa. El decimal debería ser un mínimo de Decimal (15,12) (9 bytes) para cada LatLong (total de 18 bytes) para una comparación real.

Entonces comparando tipos de almacenamiento:

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''

Resultado:

name    rows    data     
Geo     20000   728 KB   
LatLon  20000   560 KB

El tipo de datos geográficos ocupa un 30% más de espacio.

Además, el tipo de datos de geografía no se limita a almacenar solo un Punto, sino que también puede almacenar LineString, CircularString, CompoundCurve, Polygon, CurvePolygon, GeometryCollection, MultiPoint, MultiLineString y MultiPolygon, entre otros . Cualquier intento de almacenar hasta el más simple de los tipos de Geografía (como Lat / Long) más allá de un Punto (por ejemplo LINESTRING (1 1, 2 2)) incurrirá en filas adicionales para cada punto, una columna para secuenciar el orden de cada punto y otra columna para agrupar líneas. SQL Server también tiene métodos para los tipos de datos de Geografía que incluyen el cálculo de Área, Límite, Longitud, Distancias y más .

Parece imprudente almacenar latitud y longitud como decimal en el servidor Sql.

Actualización 2

Si planeas hacer cálculos como distancia, área, etc., es difícil calcularlos correctamente sobre la superficie de la tierra. Cada tipo de Geografía almacenado en SQL Server también se almacena con una ID de Referencia Espacial . Estos identificadores pueden ser de diferentes esferas (la Tierra es 4326). Esto significa que los cálculos en SQL Server realmente se calcularán correctamente sobre la superficie de la tierra (en lugar de as-the-crow-flies que podría ser a través de la superficie de la tierra).

Answer 3

    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: [email protected]
Phone: [phone] 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