Más

¿Utiliza geometría en lugar de tipos de datos geográficos en SQL Server Spatial?

¿Utiliza geometría en lugar de tipos de datos geográficos en SQL Server Spatial?


Históricamente, he trabajado principalmente con coordenadas simples de latitud y longitud en un servidor SQL. Los almacené como un tipo de datos geográficos en SQL Server y los rendericé en una variedad de pantallas (principalmente Google Maps).

Recientemente comencé a trabajar con conjuntos de datos de shapefile de varias fuentes, y casi exclusivamente son geometría. Además, utilizan una variedad de sistemas de referencia de coordenadas.

Es bastante enloquecedor trabajar con él.

  • El USGS, para algunos de sus datos, usa un CRS muy raro y difícil de identificar.
  • El condado de Los Ángeles dice que usan el State Plane 5 (sin referencia específica a un CRS) y, por supuesto, hay varios "state plane 5" para elegir en QGIS.

¿Existen realmente algunas ventajas de estos sistemas de referencia espacial en particular que no se pueden lograr con WGS84, considerando que la mayoría de las personas VAST que ven y consumen lo querrán en ese formato?


Hay una buena respuesta en Stack Overflow, que es algo como esto:

El tipo de geografía es un poco más restrictivo que la geometría. No puede cruzar diferentes hemisferios y el anillo exterior debe dibujarse en sentido antihorario.

El resto se puede encontrar en el tipo de datos Geografía frente al tipo de datos Geometría en SQL Server.

Un artículo de Geometry vs Geography en el SQL del blog Trenches entra en más detalles:

Si está buscando la mayor diferencia entre los dos tipos de datos, verá que es la funcionalidad. Un objeto Geometry es solo un polígono plano 2D. Esto significa que no importa si toma un país en la parte superior de la tierra (por ejemplo, Canadá, que es "curvo"), o un país cerca del Ecuador (por ejemplo, Brasil, que es "plano").

Un objeto de Geografía, por otro lado, es un polígono 3D (o incluso 4D), que tiene la misma curva que la forma de la tierra. Eso significa que la diferencia entre 2 puntos no se calcula en línea recta, pero es necesario considerar la curvatura de la tierra.

Otra diferencia crucial es la capacidad de almacenar sus datos en sistemas de coordenadas estándar, como NAD_1983_StatePlane_California Zone 5, y utilizar todas las capacidades de la base de datos espacial, funciones espaciales, etc. y lo más importante, la precisión espacial de un sistema de coordenadas localizado, mientras se mantiene con geografía como tipo de datos, solo puede almacenar sus datos en WGS84.

Entonces, diría que si tiene la opción, vaya con la geometría, use EPSG: 102645/102245 (tendrá que verificar cuál es la 'zona de plano de estado 5' estándar para SoCal) y estará listo para cualquier análisis que desea realizar. Si desea compartir, exporte sus conjuntos de datos a WGS84 si lo prefiere para compartir.


¿Quizás verifique las propiedades de los sistemas de coordenadas proyectadas para ver su utilidad?

Hay 3 aspectos o propiedades que se presentan en los sistemas de coordenadas proyectadas que establecen su utilidad y razón de ser. Por supuesto, cualquier proyección de espacio 3D en una superficie 2D exhibirá distorsión frente a la realidad. Dependiendo de su aplicación, el uso de un sistema de coordenadas proyectadas específico puede garantizar una representación más precisa de la realidad y, en algunas aplicaciones, la precisión de grano fino es importante.

Tres propiedades de los sistemas de coordenadas proyectadas. Puedes tener perfección en algunos, pero nunca perfección en todos:

Equidistante - Las distancias (desde un lugar en particular) que se muestran en el mapa son fieles a la realidad.

Conformal - Cuando se amplía, los ángulos que se muestran en el mapa son fieles a la realidad.

Área igual - Las áreas registradas en el mapa son iguales a las áreas de los objetos en la realidad.


En California, las oficinas de los topógrafos utilizan una proyección cónica conformada de Lambert y dividen el estado en zonas para minimizar las distorsiones. State Plane 5 es una de esas zonas.

http://www.conservation.ca.gov/cgs/information/geologic_mapping/state_plane


Actualizar

@Erik Philips mencionó la capacidad de realizar búsquedas de proximidad con geografía, lo cual es muy bueno.

Por otro lado, una prueba rápida muestra que una simple selección para obtener la latitud y la longitud es significativamente más lenta cuando se usa la geografía (detalles a continuación). , y un comentario sobre la respuesta aceptada a otra pregunta SO sobre geografía 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 anulable. Hay algunos problemas de rendimiento graves, así que haga que la columna GEOGRAPHY no acepte nulos incluso si tiene que remodelar su esquema. - Tomas 18 de junio a las 11:18

Con todo, al sopesar la probabilidad de realizar búsquedas de proximidad frente a la compensación en rendimiento y complejidad, he decidido renunciar al uso de la geografía en este caso.

Detalles de la prueba que realicé:

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

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

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 geografía.

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


Conclusión

Para desarrollar una aplicación con reconocimiento de ubicación, debe tener una solución de almacenamiento de datos para datos espaciales. SQL Server 2008 brinda soporte para datos geográficos mediante la inclusión de nuevos tipos de datos espaciales, que puede usar para almacenar y manipular información basada en la ubicación. Estos tipos de datos nativos vienen en forma de dos nuevos tipos de datos, a saber. Tipo de datos GEOGRAPHY para datos espaciales geodésicos y tipo de datos GEOMETRY para datos espaciales planos.

Este artículo es el cuarto de una serie. Los otros artículos de la serie son:


Segmentos de arco circular

Tres tipos instanciables pueden tomar segmentos de arco circular: CircularString, CompuestoCurva, y CurvePolygon. Un segmento de arco circular está definido por tres puntos en un plano bidimensional y el tercer punto no puede ser el mismo que el primer punto. Pocos ejemplos de segmentos de arco circular:

Los primeros dos ejemplos muestran segmentos de arco circular típicos. Observe cómo cada uno de los tres puntos se encuentra en el perímetro de un círculo.

Otros dos ejemplos muestran cómo un segmento de línea se puede definir como un segmento de arco circular. Todavía se necesitan tres puntos para definir el segmento de arco circular, a diferencia de un segmento de línea regular, que se puede definir con solo dos puntos.

Los métodos que operan en tipos de segmento de arco circular utilizan segmentos de línea recta para aproximar el arco circular. El número de segmentos de línea utilizados para aproximar el arco dependerá de la longitud y curvatura del arco. Los valores Z se pueden almacenar para cada uno de los tipos de segmento de arco circular, pero no se utilizarán en los cálculos.

Si se dan valores Z para segmentos de arco circular, entonces deben ser los mismos para todos los puntos en el segmento de arco circular para que se acepte como entrada. Por ejemplo: CIRCULARSTRING (0 0 1, 2 2 1, 4 0 1) se acepta, pero CIRCULARSTRING (0 0 1, 2 2 2, 4 0 1) no se acepta.

Comparación de LineString y CircularString

Este ejemplo muestra cómo almacenar triángulos isósceles idénticos usando un LineString instancia y CircularString ejemplo:

Note que un CircularString La instancia requiere siete puntos para definir el triángulo. LineString La instancia requiere solo cuatro puntos para definir el triángulo. La razón de esto es que un CircularString instancia almacena segmentos de arco circular y no segmentos de línea. Los lados del triángulo almacenados en el CircularString ejemplo son ABC, CDE y EFA. Los lados del triángulo almacenados en el LineString ejemplo son AC, CE y EA.

Considere el siguiente ejemplo:

CircularString instancias usan menos puntos para almacenar los límites de la curva con mayor precisión que LineString instancias. CircularString Las instancias son útiles para almacenar límites circulares como un radio de búsqueda de 20 millas desde un punto específico. LineString Las instancias son buenas para almacenar límites que son lineales como una manzana cuadrada.

Comparación de LineString y CompoundCurve

Los siguientes ejemplos de código muestran cómo almacenar la misma figura usando LineString y CompuestoCurva instancias:

En los ejemplos anteriores, un LineString instancia o una CompuestoCurva instancia podría almacenar la figura. Este siguiente ejemplo usa un CompuestoCurva para almacenar una porción de pastel:

A CompuestoCurva instancia puede almacenar el segmento de arco circular (2 2, 1 3, 0 2) directamente, pero un LineString instancia tendría que convertir la curva en varios segmentos de línea más pequeños.

Comparación de CircularString y CompoundCurve

El siguiente ejemplo de código muestra cómo el sector circular se puede almacenar en un CircularString ejemplo:

Almacenar la porción de pastel usando un CircularString La instancia requiere que se utilicen tres puntos para cada segmento de línea. Si no se conoce un punto intermedio, debe calcularse o el punto final del segmento de línea debe duplicarse como muestra el siguiente fragmento:

CompuestoCurva instancias permiten ambos LineString y CircularString componentes de modo que solo sea necesario conocer dos puntos de los segmentos de línea del sector circular. Este ejemplo de código muestra cómo usar un CompuestoCurva para almacenar la misma figura:

Comparación de polígonos y curvas de polígonos

CurvePolygon las instancias pueden usar CircularString y CompuestoCurva instancias al definir sus anillos exteriores e interiores. Polígono las instancias no pueden.


¿Cuál es la diferencia entre los tipos de datos espaciales?

¿Cuál es la diferencia entre los dos tipos de datos espaciales en SQL Server 2008? Sé que son geografía y geometría, pero ¿en qué se diferencian?

Этим пользователям нравится это

4 Ответов

Ответ от Bob Hovious · 22/10/09 00:04

Los datos geométricos tratan con puntos, líneas, curvas y áreas en superficies planas, mientras que los datos geográficos tratan con estos mismos "objetos" mapeados en la superficie de la tierra. Digo la superficie de la tierra, en lugar de una superficie curva, porque la tierra no es una esfera perfecta. Se abulta en el medio.

Los objetos en un sistema geométrico se definen con pares de coordenadas que son simplemente puntos X e Y en una cuadrícula plana. Los objetos de un sistema geográfico se definen mediante pares de coordenadas que representan la latitud y la longitud. La distinción absolutamente CRÍTICA está en términos de tamaño, distancia y distorsión.

La distancia es el ejemplo más sencillo. En un sistema de coordenadas geométricas, la distancia entre dos puntos se puede calcular usando el teorema de Pitágoras y las coordenadas x, y de los puntos involucrados.

Eso no funciona para pares lat / long en un sistema geográfico debido a la curvatura de la Tierra. Cuando viajas entre dos puntos del globo, estás viajando en una curva. (Las aerolíneas vuelan rutinariamente rutas que se verían como grandes curvas si se trazaran en un mapa de pared, pero que son la distancia más corta cuando se trazan en un globo).

Tampoco funciona debido a la naturaleza de las coordenadas lat / long. ¿Alguna vez notó cómo en un globo terráqueo las líneas horizontales (latitud) corren paralelas, mientras que las líneas verticales (longitud) se acercan a medida que se acercan a los polos? Si dos puntos en el ecuador están exactamente separados por un grado de longitud, están MUCHO más separados que dos puntos en Alaska o Sudáfrica que están exactamente separados por un grado. Recuerde que todas las líneas de longitud convergen en los polos, por lo que si están cerca del polo norte o sur, un grado de separación se traduciría en solo unas pocas pulgadas.

Obviamente, lo que afecta la distancia también distorsiona el tamaño y la forma. Pero cuando utiliza tipos de datos geográficos, las funciones integradas para la distancia y el área representan todos los caprichos de una tierra rotunda.

Los sistemas de cartografía geográfica se han vuelto cada vez más precisos con el tiempo, y el tipo de datos geográficos se puede definir en términos de varios estándares diferentes, cada uno de los cuales tiene un número único en MS-SQL. Es importante ser consciente de esto, porque es posible que esté cargando datos de una fuente que usa un estándar diferente al que está usando como predeterminado en su base de datos. Las rutinas espaciales en SQL pueden traducir eso por usted, siempre que lo identifique correctamente.


Tipos de datos de texto

Si crea un campo VARCHAR o VARCHAR (MAX) en una base de datos de SQL Server, se asignará al tipo de datos de ArcGIS TEXT cuando se visualice en ArcGIS. Si crea un campo TEXT en una base de datos de SQL Server desde ArcGIS, se utiliza NVARCHAR o NVARCHAR (MAX).

En una geodatabase corporativa, si el parámetro de configuración UNICODE_STRING está establecido en FALSE y el campo de texto tiene 7999 caracteres o menos, se usa VARCHAR.

Si el parámetro de configuración UNICODE_STRING se establece en FALSE y el campo de texto tiene 8.000 caracteres o más, se utiliza VARCHAR (MAX).

Si el parámetro de configuración UNICODE_STRING se establece en TRUE y el campo de texto tiene 3999 caracteres, se utiliza NVARCHAR.

Si el parámetro de configuración UNICODE_STRING se establece en TRUE y el campo de texto tiene 4.000 caracteres o más, se utiliza NVARCHAR (MAX).


Impacto

Si todavía está conmigo, vayamos un paso más allá: digamos que desea actualizar su ensamblaje de objetos "Core" a .NET Standard. No puedes. Porque EF Core aún no es compatible con DbGeography. Y no puede usar Microsoft.Spatial (o cualquiera de las otras bibliotecas mencionadas), porque no se asignan directamente a SQL Server.

Su única opción (al menos, la única que pude encontrar) es eliminar la propiedad DbGeography de su modelo central y usar un activador de base de datos para analizar el "WellKnownText" (una representación de texto basada en estándares de los datos geográficos) a su Columna "Geografía" para poder ejecutar consultas como "muéstrame todos los apartamentos en un radio de 50 millas de Phoenix, AZ".

Sé que fue largo, pero espero que proporcione una explicación de la profundidad del problema.


Índice único

Un índice único se utiliza para mantener la integridad de los datos de las columnas en las que se crea, asegurándose de que no haya valores duplicados en la clave de índice y las filas de la tabla en las que se crea ese índice. Esto asegura que los datos serán únicos según la clave de índice, según las características de los datos que se almacenan en la columna de clave de índice o en la lista de columnas. Si la clave de índice única consta de una columna, SQL Server garantizará que cada valor de la clave de índice sea único. Por otro lado, si la clave de índice única consta de varias columnas, cada combinación de valores en esa clave de índice debe ser única. Puede definir que los índices agrupados y no agrupados sean únicos, siempre que los datos de estas claves de índice sean únicos.

Se creará un índice único automáticamente cuando defina restricciones PRIMARY KEY o UNIQUE KEY en las columnas especificadas. En todos los casos, es muy recomendable crear un índice único en los datos únicos, en lugar de crear un índice no único en los mismos datos, ya que ayudará al Optimizador de consultas de SQL Server a generar el plan de ejecución más eficiente en función de los datos adicionales. información útil proporcionada por ese índice.

Suponga que necesitamos crear la siguiente tabla, utilizando la instrucción CREATE TABLE T-SQL a continuación, sin especificar ninguna instrucción CREATE INDEX, como se muestra a continuación:

Verá que se creará automáticamente un índice agrupado único en la columna ID de esa tabla, para hacer cumplir la restricción PRIMARY KEY, y se creará automáticamente un índice no agrupado único en la columna Nombre para hacer cumplir la restricción ÚNICA, como se muestra debajo:

Tenga en cuenta que el índice que se crea automáticamente para hacer cumplir cualquier restricción no se puede eliminar mediante una instrucción DROP INDEX T-SQL. Si intentamos eliminar el índice Único creado previamente para hacer cumplir la restricción ÚNICA usando la instrucción DROP INDEX T-SQL a continuación:

la declaración fallará, lo que muestra que no podemos eliminar explícitamente ningún índice que se cree automáticamente para hacer cumplir una restricción, como se muestra en el mensaje de error a continuación:

Para eliminar ese índice, debemos eliminar la restricción que creó el índice, usando la instrucción ALTER TABLE… DROP CONSTRAINT T-SQL a continuación:

El índice único también se puede crear manualmente, fuera de la restricción, especificando la palabra clave UNIQUE en la declaración de creación de índice agrupado o no agrupado, como en la declaración CREATE INDEX T-SQL a continuación:

La declaración CREATE INDEX anterior se puede utilizar para crear un índice único no agrupado en la columna Nombre, como se muestra a continuación:

Un índice único se utiliza para imponer la unicidad de los valores clave del índice. Por ejemplo, el índice anterior se utiliza para asegurarse de que no haya ningún valor duplicado para la columna Nombre disponible en esa tabla. Si intentamos ejecutar la siguiente instrucción INSERT INTO que inserta dos nuevos registros con los mismos valores de Nombre en esa tabla:

La declaración fallará, mostrando que no está permitido insertar valores duplicados para la columna Nombre, que es impuesta por el índice único creado, proporcionando los valores duplicados impedidos, como se muestra en el mensaje de error a continuación:

Si intentamos eliminar el índice único, usando la instrucción DROP INDEX T-SQL a continuación:

luego ejecute la misma instrucción INSERT INTO, verá que los valores de Nombre duplicados se insertarán correctamente, sin ninguna restricción o índice que imponga la unicidad de los valores de esa columna, como se muestra claramente a continuación:

Ahora, si intentamos crear el índice Único nuevamente en esa tabla, la instrucción CREATE INDEX fallará, ya que la tabla ya tiene valores duplicados en la columna Nombre como se muestra a continuación:

Tampoco el uso de la opción de creación de índice IGNORE_DUP_KEY no funcionará con el índice ÚNICO. Si intentamos habilitar esa opción, mientras creamos el índice Único, para ignorar los valores duplicados existentes, la declaración fallará nuevamente, mostrando que no podemos crear un índice Único con valores clave de índice duplicados disponibles en la tabla, como se muestra en el mensaje de error a continuación:

Para poder crear el índice Único en la columna Nombre, debemos eliminar o actualizar los valores duplicados. En nuestro caso, actualizaremos el segundo nombre duplicado usando la instrucción UPDATE a continuación:

Con las filas de la tabla antes y después de la operación UPDATE se muestra a continuación:

Al intentar crear el índice único después de resolver el problema de duplicados, el índice único se creará correctamente como se muestra a continuación:

Podemos incluir otra columna en la clave de índice Único para hacer cumplir la unicidad de la combinación de las dos columnas, en lugar de aplicarla solo en la columna Nombre. El siguiente CREATE INDEX se utilizará para crear un índice único que refuerza la unicidad de la combinación de columnas ID y Nombre:

Si intentamos ejecutar la siguiente instrucción INSERT INTO, que inserta dos registros con el mismo nombre, los registros se insertarán correctamente, ya que la columna ID es la columna IDENTITY que asignará valores diferentes para cada fila insertada de la siguiente:


Comparación de tipos de datos

¿Cómo se comparan todos los tipos de datos entre cada proveedor de bases de datos? ¿Y qué tipos de datos forman parte del estándar SQL?

Eche un vistazo a esta tabla para descubrir cómo se comparan los tipos de datos entre los proveedores. Algunos tipos de datos son comunes en todas las bases de datos mencionadas. Otros solo están presentes en una base de datos, o todos menos uno.

Tipos de datos numéricos para cada base de datos

Tipo de datos Oráculo servidor SQL MySQL PostgreSQL
ENTERO Y Y Y Y
EN T Y Y Y
TINYINT Y Y
PEQUEÑO Y Y Y Y
MEDIUMINT Y
EMPEZANDO Y Y Y
NUMÉRICO Y Y Y Y
DECIMAL Y Y Y Y
DIC Y Y
NÚMERO Y
FLOTADOR Y Y Y
DOBLE Y
PRECISIÓN DOBLE Y Y Y Y
VERDADERO Y Y Y Y
BINARY_FLOAT Y
BINARY_DOUBLE Y
UN POCO Y Y
DINERO Y Y
POCO DINERO Y
REPARADO Y
PEQUEÑA SERIE Y
DE SERIE Y
GRANDE Y

Tipos de datos de caracteres para cada base de datos

Tipo de datos Oráculo servidor SQL MySQL PostgreSQL
CARBONIZARSE Y Y Y Y
PERSONAJE Y Y
NCHAR Y Y
VARCHAR Y Y Y Y
CARÁCTER VARIANTE Y Y Y
VARCHAR2 Y
NVARCHAR Y
NVARCHAR2 Y
GOTA Y Y
BYTEA Y
CLOB Y
NCLOB Y
TEXTO Y Y Y
NTEXT Y
TINYBLOB Y
TINYTEXT Y
MEDIUMBLOB Y
MEDIUMTEXT Y
LONGBLOB Y
TEXTO LARGO Y
BINARIO Y Y
VARIANTE BINARIO Y
VARBINARIO Y
ENUM Y Y
COLOCAR Y
LARGO Y
CRUDO Y
LARGO CRUDO Y

Tipos de datos de fecha para cada base de datos

Tipo de datos Oráculo servidor SQL MySQL PostgreSQL
FECHA Y Y Y Y
HORA Y Y Y
HORA CON ZONA HORARIA Y
TIMESTAMP Y Y Y
TIMESTAMP CON HUSO HORARIO Y Y
HORARIO CON HUSO HORARIO LOCAL Y
FECHA Y HORA Y Y
DATETIME2 Y
INTERVALO AÑO A MES Y
INTERVALO DÍA AL SEGUNDO Y
INTERVALO Y
DATETIMEOFFSET Y
PEQUEÑA FECHA Y
AÑO Y

Otros tipos de datos para cada base de datos

Tipo de datos Oráculo servidor SQL MySQL PostgreSQL
BFILE Y
BOOL Y
Booleano Y Y
CAJA Y
CIDR Y
CIRCULO Y
CURSOR Y
GEOGRAFÍA Y
GEOMETRÍA Y Y
COLECCIÓN DE GEOMETRÍA Y
HEIRARQUÍIDA Y
IMAGEN Y
INET Y
JSON Y Y
JSONB Y
LÍNEA Y
LINESTRING Y
LSEG Y
MACADDR Y
MACADDR8 Y
MULTILINESTRING Y
MULTIPUNTO Y
MULTIPOLÍGONO Y
CAMINO Y
PG_LSN Y
PUNTO Y Y
POLÍGONO Y Y
ROWID Y
ROWVERSION Y
SQL_VARIANT Y
MESA Y
TSQUERY Y
TSVECTOR Y
TXID_SNAPSHOT Y
IDENTIFICADOR ÚNICO Y
UROWID Y
UUID Y
XML Y Y