-- ==========================================================================
-- Bases de Datos Espaciales - UMNG
-- Sesiones 5 y 6: Análisis espacial (unión, intersección, diferencia, buffer)
-- Motor: Oracle Spatial
-- Municipio ficticio: San Isidro de la Sabana (Cundinamarca)
-- Sistema de referencia: MAGNA-SIRGAS 2018 / Origen Nacional (EPSG:9377), en metros
-- ==========================================================================

-- Cómo ejecutarlo: en SQL Developer abra el archivo y ejecútelo como script
-- (F5), o en SQL*Plus: @analisis-espacial-oracle.sql
-- Las tablas quedan en el esquema del usuario con el que se conecte.

-- --------------------------------------------------------------------------
-- PARTE A. CARGA BASE DEL MUNICIPIO
-- Es la misma carga de la sesión 3. Si ya la ejecutó y no ha modificado los
-- datos, puede saltarse esta parte. Volver a ejecutarla deja los datos limpios.
-- Ejecútelo como script (F5 en SQL Developer, o @archivo.sql en SQL*Plus).
-- --------------------------------------------------------------------------

SET DEFINE ON
SET SERVEROUTPUT ON

-- A.1 SRID a usar. Si la base de Oracle tiene el 9377 se usa ese; si no, se usa el
--     3116 (MAGNA-SIRGAS Bogotá), que existe en todas las versiones. Los cálculos
--     de áreas, distancias y relaciones dan igual, porque ambos están en metros.
COLUMN srid_elegido NEW_VALUE srid NOPRINT
SELECT NVL(MAX(srid), 3116) AS srid_elegido FROM mdsys.cs_srs WHERE srid = 9377;
PROMPT SRID en uso: &srid

-- A.2 Borrar tablas anteriores (Oracle no tiene DROP TABLE IF EXISTS antes de la 23ai)
BEGIN
  FOR t IN (SELECT table_name FROM user_tables
            WHERE table_name IN ('ZONAS_RIESGO', 'PARQUES', 'CENTROS_SALUD', 'COLEGIOS', 'RIOS', 'VIAS', 'BARRIOS')) LOOP
    EXECUTE IMMEDIATE 'DROP TABLE ' || t.table_name || ' CASCADE CONSTRAINTS PURGE';
  END LOOP;
END;
/
DELETE FROM user_sdo_geom_metadata WHERE table_name IN ('ZONAS_RIESGO', 'PARQUES', 'CENTROS_SALUD', 'COLEGIOS', 'RIOS', 'VIAS', 'BARRIOS');

-- A.3 Tablas
CREATE TABLE barrios (
  id         NUMBER(5) PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  estrato    NUMBER(1),
  poblacion  NUMBER(8),
  geom       SDO_GEOMETRY NOT NULL
);
CREATE TABLE vias (
  id         NUMBER(5) PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  tipo       VARCHAR2(20),
  geom       SDO_GEOMETRY NOT NULL
);
CREATE TABLE rios (
  id         NUMBER(5) PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  tipo       VARCHAR2(20),
  geom       SDO_GEOMETRY NOT NULL
);
CREATE TABLE colegios (
  id         NUMBER(5) PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  sector     VARCHAR2(10),
  matricula  NUMBER(6),
  geom       SDO_GEOMETRY NOT NULL
);
CREATE TABLE centros_salud (
  id         NUMBER(5) PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  tipo       VARCHAR2(20),
  geom       SDO_GEOMETRY NOT NULL
);
CREATE TABLE parques (
  id         NUMBER(5) PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  tipo       VARCHAR2(20),
  geom       SDO_GEOMETRY NOT NULL
);
CREATE TABLE zonas_riesgo (
  id         NUMBER(5) PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  tipo       VARCHAR2(30),
  nivel      VARCHAR2(10),
  geom       SDO_GEOMETRY NOT NULL
);

-- A.4 Datos
INSERT INTO barrios (id, nombre, estrato, poblacion, geom) VALUES (1, 'Centro', 3, 4200,
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4884000,2095800,4884800,2095800,4884800,2096400,4884000,2096400,4884000,2095800)));
INSERT INTO barrios (id, nombre, estrato, poblacion, geom) VALUES (2, 'La Estación', 2, 5100,
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4883000,2095800,4884000,2095800,4884000,2096400,4883000,2096400,4883000,2095800)));
INSERT INTO barrios (id, nombre, estrato, poblacion, geom) VALUES (3, 'El Cedro', 2, 3800,
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4884800,2095800,4886000,2095800,4886000,2096400,4884800,2096400,4884800,2095800)));
INSERT INTO barrios (id, nombre, estrato, poblacion, geom) VALUES (4, 'Santa Lucía', 2, 6900,
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4883000,2095000,4884500,2095000,4884500,2095800,4883000,2095800,4883000,2095000)));
INSERT INTO barrios (id, nombre, estrato, poblacion, geom) VALUES (5, 'Los Pinos', 5, 2700,
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4884500,2095000,4886000,2095000,4886000,2095800,4884500,2095800,4884500,2095000)));
INSERT INTO barrios (id, nombre, estrato, poblacion, geom) VALUES (6, 'Altos del Río', 4, 3300,
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4883000,2096400,4886000,2096400,4886000,2097100,4885000,2097300,4884000,2097150,4883000,2097200,4883000,2096400)));
INSERT INTO vias (id, nombre, tipo, geom) VALUES (1, 'Avenida Principal', 'arterial',
  SDO_GEOMETRY(2002, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(4882800,2095800,4886200,2095800)));
INSERT INTO vias (id, nombre, tipo, geom) VALUES (2, 'Carrera 5', 'principal',
  SDO_GEOMETRY(2002, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(4884400,2094900,4884400,2097250)));
INSERT INTO vias (id, nombre, tipo, geom) VALUES (3, 'Calle 10', 'secundaria',
  SDO_GEOMETRY(2002, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(4883000,2096100,4886000,2096100)));
INSERT INTO vias (id, nombre, tipo, geom) VALUES (4, 'Variante', 'arterial',
  SDO_GEOMETRY(2002, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(4882800,2095300,4883800,2095600,4885200,2096000,4886200,2096250)));
INSERT INTO vias (id, nombre, tipo, geom) VALUES (5, 'Calle 3', 'local',
  SDO_GEOMETRY(2002, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(4883200,2095200,4884200,2095200,4884200,2095600)));
INSERT INTO rios (id, nombre, tipo, geom) VALUES (1, 'Río Frío', 'río',
  SDO_GEOMETRY(2002, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(4882800,2096750,4883700,2096600,4884500,2096700,4885300,2096550,4885700,2096250,4886200,2096300)));
INSERT INTO rios (id, nombre, tipo, geom) VALUES (2, 'Quebrada La Honda', 'quebrada',
  SDO_GEOMETRY(2002, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),
    SDO_ORDINATE_ARRAY(4885100,2097250,4885200,2096900,4885300,2096550)));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (1, 'IE Técnico Central', 'oficial', 820,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4884350, 2096150, NULL), NULL, NULL));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (2, 'Colegio Santa Lucía', 'oficial', 640,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4883600, 2095400, NULL), NULL, NULL));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (3, 'Gimnasio Los Pinos', 'privado', 310,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4885300, 2095350, NULL), NULL, NULL));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (4, 'IE La Estación', 'oficial', 540,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4883500, 2096100, NULL), NULL, NULL));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (5, 'Liceo El Cedro', 'privado', 260,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4885500, 2096200, NULL), NULL, NULL));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (6, 'Colegio Altos', 'oficial', 450,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4883800, 2096800, NULL), NULL, NULL));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (7, 'Jardín Rayitos', 'privado', 90,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4884800, 2096000, NULL), NULL, NULL));
INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES (8, 'Escuela Rural La Honda', 'oficial', 120,
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4885600, 2096900, NULL), NULL, NULL));
INSERT INTO centros_salud (id, nombre, tipo, geom) VALUES (1, 'Hospital San Rafael', 'hospital',
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4884600, 2096250, NULL), NULL, NULL));
INSERT INTO centros_salud (id, nombre, tipo, geom) VALUES (2, 'Puesto de Salud Los Pinos', 'puesto',
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4885700, 2095500, NULL), NULL, NULL));
INSERT INTO centros_salud (id, nombre, tipo, geom) VALUES (3, 'Centro de Salud Altos', 'centro',
  SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4883400, 2096900, NULL), NULL, NULL));
INSERT INTO parques (id, nombre, tipo, geom) VALUES (1, 'Parque Principal', 'plaza',
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4884250,2096000,4884550,2096000,4884550,2096300,4884250,2096300,4884250,2096000)));
INSERT INTO parques (id, nombre, tipo, geom) VALUES (2, 'Parque Lineal Río Frío', 'lineal',
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4883400,2096300,4885000,2096300,4885000,2096750,4883400,2096750,4883400,2096300)));
INSERT INTO parques (id, nombre, tipo, geom) VALUES (3, 'Parque El Bosque', 'ecológico',
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4885400,2095000,4886000,2095000,4886000,2095300,4885400,2095300,4885400,2095000)));
INSERT INTO zonas_riesgo (id, nombre, tipo, nivel, geom) VALUES (1, 'Inundación Río Frío', 'inundación', 'alta',
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4882800,2096450,4886200,2096100,4886200,2096500,4885300,2096750,4884500,2096900,4883700,2096800,4882800,2096950,4882800,2096450)));
INSERT INTO zonas_riesgo (id, nombre, tipo, nivel, geom) VALUES (2, 'Remoción en masa Los Pinos', 'remoción en masa', 'media',
  SDO_GEOMETRY(2003, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1003,1),
    SDO_ORDINATE_ARRAY(4885600,2095200,4886100,2095150,4886100,2095700,4885700,2095650,4885600,2095200)));

-- A.5 Metadatos espaciales
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) VALUES ('BARRIOS', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) VALUES ('VIAS', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) VALUES ('RIOS', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) VALUES ('COLEGIOS', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) VALUES ('CENTROS_SALUD', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) VALUES ('PARQUES', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid) VALUES ('ZONAS_RIESGO', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);

COMMIT;

-- A.6 Índices espaciales
CREATE INDEX barrios_sidx ON barrios (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POLYGON');
CREATE INDEX vias_sidx ON vias (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=LINE');
CREATE INDEX rios_sidx ON rios (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=LINE');
CREATE INDEX colegios_sidx ON colegios (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POINT');
CREATE INDEX centros_salud_sidx ON centros_salud (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POINT');
CREATE INDEX parques_sidx ON parques (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POLYGON');
CREATE INDEX zonas_riesgo_sidx ON zonas_riesgo (geom) INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POLYGON');

-- Verificación rápida: todas las geometrías deben salir TRUE
SELECT 'barrios' AS tabla, b.nombre, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(b.geom, 0.005) AS valida FROM barrios b
UNION ALL SELECT 'parques', p.nombre, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(p.geom, 0.005) FROM parques p
UNION ALL SELECT 'zonas_riesgo', z.nombre, SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(z.geom, 0.005) FROM zonas_riesgo z;

-- --------------------------------------------------------------------------
-- PARTE B. EJEMPLOS DE CLASE
-- --------------------------------------------------------------------------

-- B.1 Unión de dos polígonos
-- Se juntan dos barrios vecinos en un solo polígono. El borde que comparten
-- desaparece y el área del resultado es la suma de las dos.
SELECT SDO_UTIL.TO_WKTGEOMETRY(SDO_GEOM.SDO_UNION(a.geom, b.geom, 0.005)) AS wkt,
       SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_UNION(a.geom, b.geom, 0.005), 0.005) AS area_m2
FROM barrios a, barrios b
WHERE a.nombre = 'Centro' AND b.nombre = 'El Cedro';

-- B.2 Unión de toda una capa: el límite municipal
-- La versión de agregado de la unión: junta todas las filas de la tabla en una
-- sola geometría, como un SUM pero para formas.
SELECT SDO_UTIL.TO_WKTGEOMETRY(SDO_AGGR_UNION(SDOAGGRTYPE(b.geom, 0.005))) AS limite,
       SDO_GEOM.SDO_AREA(SDO_AGGR_UNION(SDOAGGRTYPE(b.geom, 0.005)), 0.005) / 1000000 AS km2
FROM barrios b;

-- B.3 Disolver por un atributo
-- Se juntan los barrios que tienen el mismo estrato y se suma su población:
-- una unión agrupada con GROUP BY.
SELECT b.estrato,
       COUNT(*)         AS barrios,
       SUM(b.poblacion) AS poblacion,
       ROUND(SDO_GEOM.SDO_AREA(SDO_AGGR_UNION(SDOAGGRTYPE(b.geom, 0.005)), 0.005) / 10000, 1) AS hectareas
FROM barrios b
GROUP BY b.estrato
ORDER BY b.estrato;

-- B.4 Intersección: qué parte de cada barrio está en zona de inundación
-- La intersección deja solo lo que está en ambas geometrías. Combinada con un
-- JOIN, cada pedazo conserva los datos del barrio y de la zona: es lo que en
-- SIG se llama superposición u overlay.
-- Nota: SDO_ANYINTERACT filtra rápido con el índice; SDO_GEOM.SDO_INTERSECTION
-- calcula la forma exacta.
SELECT b.nombre AS barrio,
       ROUND(SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(b.geom, z.geom, 0.005), 0.005)) AS area_inundable_m2,
       ROUND(100 * SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(b.geom, z.geom, 0.005), 0.005)
                 / SDO_GEOM.SDO_AREA(b.geom, 0.005), 1) AS porcentaje
FROM barrios b, zonas_riesgo z
WHERE z.nombre = 'Inundación Río Frío'
  AND SDO_ANYINTERACT(b.geom, z.geom) = 'TRUE'
ORDER BY porcentaje DESC;

-- B.5 Diferencia: el área de cada barrio fuera de la zona de inundación
-- La diferencia A − B deja lo que está en A y no en B. El orden importa: A − B
-- no es lo mismo que B − A.
SELECT b.nombre,
       ROUND(SDO_GEOM.SDO_AREA(b.geom, 0.005)) AS area_total,
       ROUND(SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_DIFFERENCE(b.geom, z.geom, 0.005), 0.005)) AS area_segura
FROM barrios b, zonas_riesgo z
WHERE z.nombre = 'Inundación Río Frío'
ORDER BY b.nombre;

-- B.6 Diferencia simétrica
-- Deja lo que está en una u otra geometría, pero no en las dos. Es la unión
-- menos la intersección.
-- Nota: En Oracle la diferencia simétrica se llama SDO_XOR (o exclusivo).
SELECT ROUND(SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_XOR(p.geom, z.geom, 0.005), 0.005))          AS area_simdif,
       ROUND(SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_UNION(p.geom, z.geom, 0.005), 0.005))        AS area_union,
       ROUND(SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_INTERSECTION(p.geom, z.geom, 0.005), 0.005)) AS area_inter
FROM parques p, zonas_riesgo z
WHERE p.nombre = 'Parque Lineal Río Frío'
  AND z.nombre = 'Inundación Río Frío';

-- B.7 Buffer: ronda hídrica de 30 m
-- El buffer (zona de influencia) es el área a una distancia dada de una
-- geometría. Sobre una línea da una franja; sobre un punto, un círculo.
-- Nota: En sistemas proyectados Oracle dibuja las esquinas redondeadas del
-- buffer como arcos verdaderos. Si un visor no los muestra, se convierten a
-- segmentos con SDO_GEOM.SDO_ARC_DENSIFY.
SELECT r.nombre,
       ROUND(SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_BUFFER(r.geom, 30, 0.005), 0.005)) AS area_ronda_m2
FROM rios r;

-- Las dos rondas unidas en una sola geometría
SELECT SDO_GEOM.SDO_AREA(
         SDO_AGGR_UNION(SDOAGGRTYPE(SDO_GEOM.SDO_BUFFER(r.geom, 30, 0.005), 0.005)), 0.005) AS area_total
FROM rios r;

-- B.8 Buffer alrededor de puntos y su traslape
-- Áreas de influencia de 400 m alrededor de los colegios oficiales. La suma de
-- las áreas es mayor que el área de su unión, porque los círculos se
-- traslapan.
-- Nota: PostGIS aproxima el círculo con 32 segmentos por defecto; Oracle usa
-- arcos verdaderos. Por eso las áreas difieren un poco (menos del 1 %).
SELECT ROUND(SUM(SDO_GEOM.SDO_AREA(SDO_GEOM.SDO_BUFFER(c.geom, 400, 0.005), 0.005))) AS suma_areas,
       ROUND(SDO_GEOM.SDO_AREA(
         SDO_AGGR_UNION(SDOAGGRTYPE(SDO_GEOM.SDO_BUFFER(c.geom, 400, 0.005), 0.005)), 0.005)) AS area_unida
FROM colegios c
WHERE c.sector = 'oficial';

-- B.9 Guardar el resultado como una capa nueva
-- Cualquier análisis se puede guardar como tabla para dibujarla en QGIS o
-- ArcGIS. Aquí se crea la capa de la ronda hídrica.
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE ronda_hidrica PURGE';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE != -942 THEN RAISE; END IF;
END;
/
DELETE FROM user_sdo_geom_metadata WHERE table_name = 'RONDA_HIDRICA';

CREATE TABLE ronda_hidrica AS
SELECT r.id, r.nombre,
       SDO_GEOM.SDO_ARC_DENSIFY(SDO_GEOM.SDO_BUFFER(r.geom, 30, 0.005), 0.005, 'arc_tolerance=0.05') AS geom
FROM rios r;

INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
VALUES ('RONDA_HIDRICA', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
COMMIT;
CREATE INDEX ronda_hidrica_sidx ON ronda_hidrica (geom)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POLYGON');

-- --------------------------------------------------------------------------
-- PARTE C. EJERCICIOS
-- Las soluciones están en el HTML de la sesión (botón "Ver solución").
-- --------------------------------------------------------------------------

-- C.1 El límite municipal (Básico)
-- Construya el límite de San Isidro de la Sabana uniendo todos los barrios.
-- ¿Cuál es su área en km² y su perímetro en km?


-- C.2 Unir dos barrios (Básico)
-- Una Santa Lucía y Los Pinos. Compruebe que el área del resultado es igual a
-- la suma de las dos áreas. ¿Qué pasaría si se traslaparan?


-- C.3 Población por estrato (Intermedio)
-- Disuelva los barrios por estrato. Para cada estrato muestre el número de
-- barrios, la población total y el tipo de geometría resultante. ¿Por qué el
-- estrato 2 da un tipo distinto a los demás?


-- C.4 Parques en cada barrio (Intermedio)
-- ¿Cuántos m² de parque tiene cada barrio? Tenga en cuenta que el Parque
-- Lineal pasa por varios barrios: hay que repartir su área. Incluya los
-- barrios sin parques con 0.


-- C.5 Porcentaje de cada barrio en riesgo (Avanzado)
-- Calcule qué porcentaje de cada barrio está en alguna zona de riesgo
-- (inundación o remoción en masa).


-- C.6 Los Pinos sin la zona de remoción (Básico)
-- ¿Cuánta área de Los Pinos queda fuera de la zona de remoción en masa? ¿Y
-- cuánta área de la zona de remoción queda por fuera de Los Pinos?


-- C.7 Parque lineal contra zona de inundación (Intermedio)
-- Calcule la diferencia simétrica entre el Parque Lineal y la zona de
-- inundación. Verifique que su área es igual al área de la unión menos el área
-- de la intersección.


-- C.8 Cobertura de los colegios oficiales (Intermedio)
-- Trace un buffer de 500 m alrededor de cada colegio oficial. ¿Qué porcentaje
-- del municipio queda cubierto por al menos un colegio?


-- C.9 Barrios en la ronda hídrica (Intermedio)
-- Con una ronda de 30 m a cada lado de los ríos y quebradas, calcule cuántos
-- m² de cada barrio quedan dentro de la ronda.


-- C.10 Zona apta para construir (Avanzado)
-- La zona apta para construir es el municipio menos las zonas de riesgo y
-- menos la ronda hídrica de 30 m. Calcule su área en hectáreas y qué
-- porcentaje del municipio representa.


COMMIT;