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 ArcGIS
2
 1. Introducción

 Historia:
 Nace en Sybase (década de 1980) para Unix
 Microsoft lo traduce posteriormente a Windows NT
 Es un SGBDR completo:
 Propiedades ACID
 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:
 Pocket PC -> Mainframes
3
 1. Introducción

 Extensiones:
 XML
 OLAP (Online Analytical Processing)
 Data Mining (Minería de datos)
 Extensiones espaciales para GIS

4
 2. Soporte de datos espaciales

 Modelos espaciales geodésicos:

 Superficie de la Tierra:
 Aproximada por un elipsoide (esfera achatada)
 Datum
 Coordenadas:
 Latitud y Longitud
(generalmente)
 Aunque hay
otros sistemas…

5
 2. Soporte de datos espaciales

 Modelos espaciales planos:

 Proyecciones bidimensionales del esferoide

 Proyección UTM
 Desarrollo cilíndrico conforme de Gauss

6
 3. Tipos de datos espaciales en
SQL Server

 Datos espaciales geodésicos:

 Tipo de datos geography
 Coordenadas latitud-longitud
 Usado en mapas y aplicaciones globales
 Ej: Geolocalización con GPS

 Datos espaciales planos:

 Tipo de datos geometry
 Coordenadas en un plano arbitrario
 Usado en mapas regionales
 Ej: Mapas de carreteras
 Conforme al estándar OGC (Open Geospatial Consortium)
 Simple Features Specification for SQL

 Implementados como tipos de datos CLR (Common

Language Runtime)
 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

 WKT (Well-Known Text)

 Lenguaje de marcas de texto.
 Para representar objetos geométricos vectoriales.

 WKB (Well-Known Binary)

 Equivalente en formato binario.
 Almacenamiento en bases de datos.

10
Jerarquía de tipos: herencia

11
 Ejemplos

 En WKT:
 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.
 Punto simple definido por coordenadas X Y:
 POINT(10 10)
 Punto con coordenadas X Y Z
(elevación) M (medida):
 POINT(10 10 10 1)
 Camino de líneas:
 LINESTRING(0 0, 10 10)
 Polígono:
 POLYGON((0 0, 0 10, 10 10, 10 0, 0 0))
 Polígono con un hueco en forma de triángulo:
 POLYGON((0 0, 0 10, 10 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

 STGeomFromText()
 INSERT INTO SpatialTable (GeomCol1) VALUES
(geometry::STGeomFromText(
'LINESTRING (100 100, 20 180, 180 180)', 0));
 INSERT INTO SpatialTable (GeomCol1) VALUES
(geometry::STGeomFromText(
'POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));
 STLineFromText()
 INSERT INTO SpatialTable (GeomCol1) VALUES
(geometry::STLineFromText(
'LINESTRING (100 100, 20 180, 180 180)', 0));
 Parse(). Se puede usar en lugar de los anteriores.
 INSERT INTO SpatialTable (GeomCol1) VALUES
(geometry::Parse(
'LINESTRING (100 100, 20 180, 180 180)',));
 ToString(). Para escribir datos geográficos.
 SELECT GeomCol1.ToString() FROM SpatialTable

14
 Métodos para los tipos espaciales

 Ejemplo a usar en las descripciones de los métodos

(Transact-SQL):

 DECLARE @g geometry =
'POLYGON((10 10, 40 10, 40 40, 10 40, 10 10))'
 DECLARE @h geometry =
'POLYGON((30 30, 50 30, 50 50, 30 50, 30 30))'

15
 Transact-SQL (T-SQL)

 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:
 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 Número: 0
WHILE @i < 5 Número: 1
BEGIN Número: 2
PRINT 'Número: ' + @i Número: 3
SET @i = @i + 1 Número: 4
END
16
 Métodos para los tipos espaciales

 STDifference()
 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).ToString();

 Resultado:

POLYGON ((10 10, 40 10, 40 30, 30 30, 30 40, 10 40, 10 10))

17
 Métodos para los tipos espaciales

 STDifference()
 También sobre tipos distintos.

DECLARE @g geometry =
'LINESTRING(9 9, 40 40)'
DECLARE @h geometry =
'POLYGON((15 15, 15 30, 30 30, 30 15, 15 15))'
SELECT @g.STDifference(@h).ToString();

 Resultado:

MULTILINESTRING ((40 40, 30 30), (15 15, 9 9))

18
 Métodos para los tipos espaciales

 STIntersection()
 Intersección de formas. Devuelve la parte común de dos
formas.

SELECT @g.STIntersection(@h).ToString();

 Resultado:

POLYGON ((30 30, 40 30, 40 40, 30 40, 30 30))

19
 Métodos para los tipos espaciales

 STIntersection()
 También sobre tipos distintos.

SELECT @g.STIntersection(@h).ToString();

 Resultado:

LINESTRING (30 30, 15 15)

20
 Métodos para los tipos espaciales

 STUnion()
 Unión de formas. Devuelve todos los puntos de dos
formas.

SELECT @g.STUnion(@h).ToString();

 Resultado:

POLYGON ((10 10, 40 10, 40 30, 50 30, 50 50, 30 50,

30 40, 10 40, 10 10))

21
 Métodos para los tipos espaciales

 STUnion()
 También sobre tipos distintos.

SELECT @g.STUnion(@h).ToString();

 Resultado:

GEOMETRYCOLLECTION (
LINESTRING (40 40, 30 30),
POLYGON ((15 15, 30 15, 30 30, 15 30, 15 15)),
LINESTRING (15 15, 9 9) )

22
 Métodos para los tipos espaciales

 STArea()
 Área de una forma.

 STLength()
 Longitud de un camino.

DECLARE @g GEOMETRY =
'POLYGON((10 10, 10 40, 40 40, 10 10))'

SELECT @g.STArea(), @g.STLength()

 Resultado:

Area Length
450 102.426406871193

23
 Métodos para los tipos espaciales

 STCentroid()
 Devuelve el centroide de una forma.

DECLARE @g GEOMETRY =
'POLYGON((10 10, 10 40, 40 40, 10 10))'

SELECT @g.STCentroid().ToString()

 Resultado:

POINT (20 30)

24
 Métodos para los tipos espaciales

 STContains()
 STWithin()
 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 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)

 Resultado:
10
01
25
 Métodos para los tipos espaciales

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

26
 Métodos para los tipos espaciales

 Coordenadas de un punto:
 .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

 Resultado:

1 2 NULL NULL
1234

27
 Métodos para los tipos espaciales

 STPointOnSurface()
 Devuelve un punto arbitrario que pertenece a la forma.

DECLARE @g geometry =
'POLYGON((10 10, 14 15, 50 12, 45 30, 10 30, 10 10))'

SELECT @g.STPointOnSurface().ToString()

 Resultado:

POINT (23 25)

28
4. SQL para tipos de datos espaciales

 Creación de tablas:

CREATE TABLE Districts

(DistrictId int IDENTITY (1,1),
DistrictName nvarchar(20),
DistrictGeo geometry);

CREATE TABLE Streets

(StreetId int IDENTITY (1,1),
StreetName nvarchar(20),
StreetGeo geometry);

29
4. SQL para tipos de datos espaciales

 Inserción en tablas:

INSERT INTO Districts (DistrictName, DistrictGeo)

VALUES ('Downtown',
geometry::STGeomFromText
('POLYGON ((0 0, 150 0, 150 150, 0 150, 0 0))', 0));

INSERT INTO Streets (StreetName, StreetGeo)

VALUES ('First Avenue',
geometry::STGeomFromText
('LINESTRING (100 100, 20 180, 180 180)', 0))

30
 4. SQL para tipos de datos
espaciales

 Selección de resultados:

SELECT StreetName, DistrictName

FROM Districts d, Streets s
WHERE s.StreetGeo.STIntersects(DistrictGeo) = 1
ORDER BY StreetName

StreetName DistrictName
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.ToString() 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

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

33
6. Vistas espaciales gráficas integradas

 Management Studio:

34
 7. Aplicaciones

 Integración con otros sistemas.

 Importación y exportación de datos
 Estándar OGC:
 Well Known Text (WKT)
 Well Known Binary (WKB)
 Aplicaciones GIS / Sistemas GPS

35
 7. Aplicaciones

 Uso de datos espaciales en .NET

 Ej: Virtual Earth

36
8. Management Studio

37
 9. Recuperación y disponibilidad

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

38
10. Soporte nativo de XML

39
 Tipos XML en SQL

 Creación de tablas:

create table InformesViajes(

fechaviaje datetime,
Informe xml)

40
 Metadatos XML en SQL

 Metadatos de las bases de datos:

 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 E1 as @e

 Crea el conjunto de esquemas XML de SQL Server de

nombre E1, 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

 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
EsquemaInformes:

create table InformesViajes(id int,

fechaviaje datetime,
Informe xml(document EsquemaInforme))

42
 XQuery en SQL

 Lenguaje de consulta de documentos XML.

 Desarrollado por el consorcio W3C (World Wide Web
Consortium).
 Procede del lenguaje Quilt, que también incluía
expresiones XPath
 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

 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 InformesViajes
44
11. Gestión de geodatabases en ArcGIS

45
 Práctica

 Práct. 5. SQL Server.pdf

46