Tema 5 SQL Server Bases de datos Máster

Tema 5: SQL Server Bases de datos Máster en Tecnologías de Información Geográfica

Contenido 1. Introducción 2. Soporte de datos espaciales 3. Tipos de datos espaciales en SQL Server 4. SQL para tipos de datos espaciales 5. Rendimiento 6. Vistas espaciales gráficas integradas 7. Aplicaciones 8. Management Studio 9. Recuperación y disponibilidad 10. Soporte nativo de XML 11. Gestión de geodatabases en Arc. GIS 2

1. Introducción u Historia: n n u Nace en Sybase (década de 1980) para Unix Microsoft lo traduce posteriormente a Windows NT Es un SGBDR completo: n Propiedades ACID l l n n n u Atomicity Consistency Isolation Durability Disparadores Restricciones de integridad Réplicas de bases de datos (duplicación) Copias de seguridad Ajuste de rendimiento Funciona en plataformas: n Pocket PC -> Mainframes 3

1. Introducción u Extensiones: n n XML OLAP (Online Analytical Processing) Data Mining (Minería de datos) Extensiones espaciales para GIS 4

2. Soporte de datos espaciales u Modelos espaciales geodésicos: n Superficie de la Tierra: l l n Aproximada por un elipsoide (esfera achatada) Datum Coordenadas: l l Latitud y Longitud (generalmente) Aunque hay otros sistemas… 5

2. Soporte de datos espaciales u Modelos espaciales planos: n Proyecciones bidimensionales del esferoide n Proyección UTM l Desarrollo cilíndrico conforme de Gauss 6

3. Tipos de datos espaciales en SQL Server u Datos espaciales geodésicos: n n n Tipo de datos geography Coordenadas latitud-longitud Usado en mapas y aplicaciones globales l u Datos espaciales planos: n n n Tipo de datos geometry Coordenadas en un plano arbitrario Usado en mapas regionales l n Ej: Mapas de carreteras Conforme al estándar OGC (Open Geospatial Consortium) l u Ej: Geolocalización con GPS Simple Features Specification for SQL Implementados como tipos de datos CLR (Common Language Runtime) n Accesibles en. NET 7

3. Tipos de datos espaciales en SQL Server 8

3. Tipos de datos espaciales en SQL Server 9

3. Tipos de datos espaciales en SQL Server u WKT (Well-Known Text) n n u Lenguaje de marcas de texto. Para representar objetos geométricos vectoriales. WKB (Well-Known Binary) n n Equivalente en formato binario. Almacenamiento en bases de datos. 10

Jerarquía de tipos: herencia 11

Ejemplos u En WKT: n n n u Punto simple definido por coordenadas X Y: n u LINESTRING(0 0, 10 10) Polígono: n u POINT(10 10 10 1) Camino de líneas: n u POINT(10 10) Punto con coordenadas X Y Z (elevación) M (medida): n u Los espacios separan las coordenadas de un punto. Las comas separan los puntos en un tipo más complejo. Los paréntesis agrupan puntos en una única figura. POLYGON((0 0, 0 10, 10 0, 0 0)) Polígono con un hueco en forma de triángulo: n POLYGON((0 0, 0 10, 10 0, 0 0), (1 1, 4 5, 4 1, 1 1)) 12

Métodos para los tipos espaciales Gris: Tipos Geometry que no están en Geography 13

Métodos para los tipos espaciales u STGeom. From. Text() n n u STLine. From. Text() n u INSERT INTO Spatial. Table (Geom. Col 1) VALUES (geometry: : STLine. From. Text( 'LINESTRING (100 100, 20 180, 180)', 0)); Parse(). Se puede usar en lugar de los anteriores. n u INSERT INTO Spatial. Table (Geom. Col 1) VALUES (geometry: : STGeom. From. Text( 'LINESTRING (100 100, 20 180, 180)', 0)); INSERT INTO Spatial. Table (Geom. Col 1) VALUES (geometry: : STGeom. From. Text( 'POLYGON ((0 0, 150 150, 0 0))', 0)); INSERT INTO Spatial. Table (Geom. Col 1) VALUES (geometry: : Parse( 'LINESTRING (100 100, 20 180, 180)', )); To. String(). Para escribir datos geográficos. n SELECT Geom. Col 1. To. String() FROM Spatial. Table 14

Métodos para los tipos espaciales u Ejemplo a usar en las descripciones de los métodos (Transact-SQL): n n DECLARE @g geometry = 'POLYGON((10 10, 40 40, 10 10))' DECLARE @h geometry = 'POLYGON((30 30, 50 50, 30 30))' 15

Transact-SQL (T-SQL) u u u Lenguaje de cuarta generación de bases de datos. Desarrollado por Microsoft/Sybase. Añade a SQL mecanismos para lograr un lenguaje de propósito general: n n n Lenguaje de control de flujo. Variables. Funciones. IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1 PRINT '¡Es fin de semana!' ELSE PRINT 'Es laborable… : -(' DECLARE @i NVARCHAR(50) SET @i = 0 WHILE @i < 5 BEGIN PRINT 'Número: ' + @i SET @i = @i + 1 END Número: 0 Número: 1 Número: 2 Número: 3 Número: 4 16

Métodos para los tipos espaciales u STDifference() n Diferencia de conjuntos. Devuelve los puntos de la columna geométrica a la que se aplica tal que no están en los de la columna geométrica parámetro del método. SELECT @g. STDifference(@h). To. String(); n Resultado: POLYGON ((10 10, 40 30, 30 40, 10 10)) 17

Métodos para los tipos espaciales u STDifference() n También sobre tipos distintos. DECLARE @g geometry = 'LINESTRING(9 9, 40 40)' DECLARE @h geometry = 'POLYGON((15 15, 15 30, 30 15, 15 15))' SELECT @g. STDifference(@h). To. String(); n Resultado: MULTILINESTRING ((40 40, 30 30), (15 15, 9 9)) 18

Métodos para los tipos espaciales u STIntersection() n Intersección de formas. Devuelve la parte común de dos formas. SELECT @g. STIntersection(@h). To. String(); n Resultado: POLYGON ((30 30, 40 40, 30 30)) 19

Métodos para los tipos espaciales u STIntersection() n También sobre tipos distintos. SELECT @g. STIntersection(@h). To. String(); n Resultado: LINESTRING (30 30, 15 15) 20

Métodos para los tipos espaciales u STUnion() n Unión de formas. Devuelve todos los puntos de dos formas. SELECT @g. STUnion(@h). To. String(); n Resultado: POLYGON ((10 10, 40 30, 50 50, 30 50, 30 40, 10 10)) 21

Métodos para los tipos espaciales u STUnion() n También sobre tipos distintos. SELECT @g. STUnion(@h). To. String(); n Resultado: GEOMETRYCOLLECTION ( LINESTRING (40 40, 30 30), POLYGON ((15 15, 30 30, 15 15)), LINESTRING (15 15, 9 9) ) 22

Métodos para los tipos espaciales u STArea() n u Área de una forma. STLength() n Longitud de un camino. DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))' SELECT @g. STArea(), @g. STLength() n Resultado: Area Length 450 102. 426406871193 23

Métodos para los tipos espaciales u STCentroid() n Devuelve el centroide de una forma. DECLARE @g GEOMETRY = 'POLYGON((10 10, 10 40, 40 40, 10 10))' SELECT @g. STCentroid(). To. String() n Resultado: POINT (20 30) 24

Métodos para los tipos espaciales u u STContains() STWithin() n n n Devuelve el valor lógico cierto (1) si una forma contiene completamente a otra. @g. STContains(@h) determina si @g contiene a a @h @g. STWithin(@h) determina si @g está contenido en @h DECLARE @g geometry = 'POLYGON ((10 10, 13 30, 30 15, 10 10))' DECLARE @h geometry = 'LINESTRING (16 16, 16 24, 25 18)‘ SELECT @g. STContains(@h), @g. STWithin(@h) SELECT @h. STContains(@g), @h. STWithin(@g) n Resultado: 1 0 0 1 25

Métodos para los tipos espaciales u ST is … n CLOSED. l n EMPTY. l n Si no se cruza sobre sí misma o se toca a sí misma. RING. l n Si no contiene ningún punto (parecido a NULL, pero no es lo mismo). SIMPLE. l n Si el punto final coincide con el inicial. Si es una forma cerrada (CLOSED) y simple (SIMPLE) VALID. l l Si no tiene elementos superpuestos. Ej: No válido 26

Métodos para los tipos espaciales u Coordenadas de un punto: n n . STX. STY. Z. M DECLARE @g geometry = 'POINT(1 2)' DECLARE @h geometry = 'POINT(1 2 3 4)' SELECT @g. STX, @g. STY, @g. Z, @g. M SELECT @h. STX, @h. STY, @h. Z, @h. M n Resultado: 1 2 NULL 1 2 3 4 27

Métodos para los tipos espaciales u STPoint. On. Surface() n Devuelve un punto arbitrario que pertenece a la forma. DECLARE @g geometry = 'POLYGON((10 10, 14 15, 50 12, 45 30, 10 10))' SELECT @g. STPoint. On. Surface(). To. String() n Resultado: POINT (23 25) 28

4. SQL para tipos de datos espaciales u Creación de tablas: CREATE TABLE Districts (District. Id int IDENTITY (1, 1), District. Name nvarchar(20), District. Geo geometry); CREATE TABLE Streets (Street. Id int IDENTITY (1, 1), Street. Name nvarchar(20), Street. Geo geometry); 29

4. SQL para tipos de datos espaciales u Inserción en tablas: INSERT INTO Districts (District. Name, District. Geo) VALUES ('Downtown', geometry: : STGeom. From. Text ('POLYGON ((0 0, 150 150, 0 0))', 0)); INSERT INTO Streets (Street. Name, Street. Geo) VALUES ('First Avenue', geometry: : STGeom. From. Text ('LINESTRING (100 100, 20 180, 180)', 0)) 30

4. SQL para tipos de datos espaciales u Selección de resultados: SELECT Street. Name, District. Name FROM Districts d, Streets s WHERE s. Street. Geo. STIntersects(District. Geo) = 1 ORDER BY Street. Name District. Name First Avenue Downtown First Avenue Harborside Mercator Street Downtown Mercator Street Green Park Mercator Street Harborside 31

Otro ejemplo CREATE TABLE demo ( ID INT IDENTITY(1, 1) NOT NULL, G GEOMETRY ) INSERT INTO demo (G) VALUES ('LINESTRING(0 0, 10 10, 21 2)'), ('LINESTRING(1 1, 11 11, 22 3)'), ('POINT(5 5)') SELECT ID, G. To. String() AS WKT, G. STLength() AS LENGTH FROM demo ID WKT LENGTH 1 LINESTRING (0 0, 10 10, 21 2) 27. 7436061324664 2 LINESTRING (1 1, 11 11, 22 3) 27. 7436061324664 3 POINT (5 5) 0 32

5. Rendimiento u Tipos espaciales: n Tipos CLR (Common Language Runtime) l n Datos espaciales almacenados en BDR l n n 2 GB (¡Incluso para un único polígono!) Interoperabilidad de datos Índices espaciales Índice en retícula multinivel (árboles cuadráticos: quad-trees) 33

6. Vistas espaciales gráficas integradas u Management Studio: 34

7. Aplicaciones u Integración con otros sistemas. n n Importación y exportación de datos Estándar OGC: l l n Well Known Text (WKT) Well Known Binary (WKB) Aplicaciones GIS / Sistemas GPS 35

7. Aplicaciones u u Uso de datos espaciales en. NET Ej: Virtual Earth 36

8. Management Studio 37

9. Recuperación y disponibilidad u Recuperación de caídas. n u Recuperación de medios. n u Copia y restauración de bases de datos Copias exactas de la base de datos n n u Registro histórico (log) Duplicados de la base de datos en otras máquinas Conmutación automática en caso de error Seguridad n n n Usuarios Roles Autenticación: l l Windows SGBD 38

10. Soporte nativo de XML 39

Tipos XML en SQL u Creación de tablas: create table Informes. Viajes( fechaviaje datetime, Informe xml) 40

Metadatos XML en SQL u Metadatos de las bases de datos: n Conjunto de esquemas XML asocia un identificador de SQL a un conjunto de componentes de esquemasde uno o varios espacios de nombres objetivos. Por ejemplo, la expresión create xml schema collection E 1 as @e u u Crea el conjunto de esquemas XML de SQL Server de nombre E 1, que consiste en los esquemas de XML contenidos en la variable de SQL @e. Cada uno de estos conjuntos contiene toda la información necesaria para llevar a cabo la validación y tipificación y se guarda en los metadatos del esquema de la base de datos. 41

Metadatos XML en SQL u El ejemplo siguiente muestra la definición de una tabla que restringe los ejemplares de la columna XML Informe a un documento bien formado válido de acuerdo con el conjunto de esquemas Esquema. Informes: create table Informes. Viajes(id int, fechaviaje datetime, Informe xml(document Esquema. Informe)) 42

XQuery en SQL u u u Lenguaje de consulta de documentos XML. Desarrollado por el consorcio W 3 C (World Wide Web Consortium). Procede del lenguaje Quilt, que también incluía expresiones XPath es un lenguaje que define expresiones válidas para acceder a partes de un documento XML. XQuery incorpora a XPath. SQL Server incorpora XQuery en las consultas SQL. 43

XQuery en SQL u u Expresión simple de XQuery que resume un elemento Cliente complejo de un documento de informe de viaje que contiene un nombre, un atributo ID y posibilidades de ventas en las notas marcadas del informe de viaje real. El resumen muestra el nombre y la información sobre ventas de los elementos Cliente que tienen posibilidades de ventas. select Informe. query(’ declare namespace c = "urn: ejemplo/cliente"; for $cli in /c: doc/c: cliente where $cli/c: notas//c: posiblesventas return <id_cliente ="$cli/@id"> { $cli/c: nombre, $cli/c: notas//c: posiblesventas }</cliente>’) from Informes. Viajes 44

11. Gestión de geodatabases en Arc. GIS 45

Práctica u Práct. 5. SQL Server. pdf 46