-- ==========================================================================
-- Bases de Datos Espaciales - UMNG
-- Sesión 7: Relaciones espaciales
-- 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: @relaciones-espaciales-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 ¿En qué barrio está cada colegio? (Within)
-- Un join espacial: en vez de unir tablas por un código, se unen por su
-- posición. Fíjese en que Jardín Rayitos no aparece: está justo en el borde
-- entre Centro y El Cedro, y estar en el borde no es estar "dentro".
-- Nota: Los operadores de Oracle (SDO_INSIDE, SDO_RELATE, SDO_TOUCH...) se
-- escriben en el WHERE, se comparan con 'TRUE' y necesitan índice espacial en
-- la columna del primer argumento.
SELECT c.nombre AS colegio, b.nombre AS barrio
FROM colegios c, barrios b
WHERE SDO_INSIDE(c.geom, b.geom) = 'TRUE'
ORDER BY b.nombre;

-- B.2 Intersects: el borde sí cuenta
-- Con intersects basta con que compartan al menos un punto. Ahora Jardín
-- Rayitos aparece dos veces, una por cada barrio que toca.
-- Nota: SDO_GEOM.RELATE con DETERMINE devuelve el nombre de la relación:
-- INSIDE para los que están adentro y TOUCH para el que está en el borde.
SELECT c.nombre AS colegio, b.nombre AS barrio,
       SDO_GEOM.RELATE(c.geom, 'DETERMINE', b.geom, 0.005) AS relacion
FROM colegios c, barrios b
WHERE SDO_ANYINTERACT(c.geom, b.geom) = 'TRUE'
ORDER BY c.nombre;

-- B.3 Contar objetos dentro de cada polígono
-- Cuántos colegios y cuántos estudiantes hay por barrio. El LEFT JOIN conserva
-- los barrios sin colegios.
-- Nota: En Oracle es más seguro contar con subconsultas que con LEFT JOIN,
-- porque los operadores espaciales no se pueden usar en la condición de un
-- join externo.
SELECT b.nombre,
       (SELECT COUNT(*) FROM colegios c
        WHERE SDO_INSIDE(c.geom, b.geom) = 'TRUE')             AS colegios,
       (SELECT NVL(SUM(c.matricula), 0) FROM colegios c
        WHERE SDO_INSIDE(c.geom, b.geom) = 'TRUE')             AS estudiantes
FROM barrios b
ORDER BY estudiantes DESC;

-- B.4 Vecinos: barrios que comparten borde (Touches)
-- Dos objetos se tocan cuando comparten borde pero no interior. Todos los
-- barrios son vecinos del Centro.
SELECT v.nombre AS vecino
FROM barrios c, barrios v
WHERE c.nombre = 'Centro'
  AND SDO_TOUCH(v.geom, c.geom) = 'TRUE';

-- B.5 Líneas que se cruzan (Crosses) y los puntos de cruce
-- Dónde hay que construir puentes: vías que cruzan ríos. La intersección de
-- dos líneas que se cruzan es un punto.
-- Nota: Oracle no tiene una máscara "CROSSES". Dos líneas que se cruzan sin
-- que se toquen sus extremos son OVERLAPBDYDISJOINT: sus interiores se cortan
-- y sus bordes (los extremos) no.
SELECT v.nombre AS via, r.nombre AS rio,
       SDO_UTIL.TO_WKTGEOMETRY(SDO_GEOM.SDO_INTERSECTION(v.geom, r.geom, 0.005)) AS puente
FROM vias v, rios r
WHERE SDO_RELATE(v.geom, r.geom, 'mask=OVERLAPBDYDISJOINT') = 'TRUE';

-- B.6 Traslape parcial (Overlaps)
-- Dos polígonos se traslapan cuando comparten parte de su interior, pero
-- ninguno está completamente dentro del otro.
SELECT b.nombre
FROM barrios b, zonas_riesgo z
WHERE z.nombre = 'Inundación Río Frío'
  AND SDO_RELATE(b.geom, z.geom, 'mask=OVERLAPBDYINTERSECT') = 'TRUE';

-- B.7 Contains, Covers y el borde compartido
-- El Parque Principal está dentro del Centro sin tocar su borde. El Parque El
-- Bosque está dentro de Los Pinos pero comparte parte del borde. Según el
-- motor y la función, eso cuenta o no como "contener".
-- Nota: Oracle distingue CONTAINS (sin tocar el borde) de COVERS (tocando el
-- borde). En PostGIS, ST_Contains da verdadero en los dos casos; el
-- equivalente exacto del CONTAINS de Oracle es ST_ContainsProperly.
SELECT b.nombre AS barrio, p.nombre AS parque,
       SDO_GEOM.RELATE(b.geom, 'DETERMINE', p.geom, 0.005) AS relacion
FROM barrios b, parques p
WHERE SDO_ANYINTERACT(b.geom, p.geom) = 'TRUE'
ORDER BY p.nombre, b.nombre;

-- B.8 Lo que no toca nada (Disjoint)
-- Colegios que no están en ninguna zona de riesgo. Es mejor escribirlo como
-- "no existe una zona que lo intersecte" que usar ST_Disjoint, porque así se
-- aprovecha el índice espacial.
SELECT c.nombre
FROM colegios c
WHERE NOT EXISTS (
  SELECT 1 FROM zonas_riesgo z
  WHERE SDO_ANYINTERACT(z.geom, c.geom) = 'TRUE'
);

-- B.9 A menos de cierta distancia (DWithin)
-- Parejas de colegio y centro de salud a 500 m o menos. Es más rápido que
-- calcular la distancia a todos y luego filtrar, porque usa el índice.
SELECT c.nombre AS colegio, h.nombre AS centro_salud,
       ROUND(SDO_GEOM.SDO_DISTANCE(c.geom, h.geom, 0.005)) AS metros
FROM colegios c, centros_salud h
WHERE SDO_WITHIN_DISTANCE(c.geom, h.geom, 'distance=500 unit=M') = 'TRUE'
ORDER BY metros;

-- B.10 El vecino más cercano
-- El centro de salud más cercano a cada colegio. En PostGIS el operador <->
-- ordena por distancia usando el índice.
-- Nota: SDO_NN devuelve los vecinos más cercanos; el último parámetro (1)
-- enlaza con SDO_NN_DISTANCE(1), que da la distancia. La pista ORDERED hace
-- que recorra los colegios uno por uno.
SELECT /*+ ORDERED */ c.nombre AS colegio, h.nombre AS mas_cercano,
       ROUND(SDO_NN_DISTANCE(1)) AS metros
FROM colegios c, centros_salud h
WHERE SDO_NN(h.geom, c.geom, 'sdo_num_res=1', 1) = 'TRUE'
ORDER BY metros;

-- B.11 La matriz de 9 intersecciones (DE-9IM)
-- Todas las relaciones anteriores salen de una misma matriz que compara
-- interior, borde y exterior de los dos objetos. Pedirla directamente sirve
-- para casos que no tienen nombre.
-- Nota: Oracle no expone la matriz completa; DETERMINE la traduce al nombre de
-- la relación.
SELECT a.nombre, b.nombre,
       SDO_GEOM.RELATE(a.geom, 'DETERMINE', b.geom, 0.005) AS relacion
FROM barrios a, parques b
WHERE SDO_ANYINTERACT(a.geom, b.geom) = 'TRUE';

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

-- C.1 ¿Dónde queda cada centro de salud? (Básico)
-- Para cada centro de salud, muestre el nombre del barrio en el que se
-- encuentra.


-- C.2 Estudiantes por barrio (Intermedio)
-- Cuente colegios y estudiantes por barrio usando intersects en vez de within.
-- Compare con el ejemplo de clase: ¿qué colegio cambia el resultado y por qué?
-- ¿Qué regla le pondría usted?


-- C.3 Vecinos de Altos del Río (Básico)
-- ¿Qué barrios son vecinos de Altos del Río?


-- C.4 Vías que cruzan el Centro y vías que solo lo tocan (Intermedio)
-- Haga dos listas: las vías que atraviesan el barrio Centro y las que solo
-- tocan su borde.


-- C.5 Colegios en zona de inundación (Básico)
-- ¿Qué colegios están dentro de la zona de inundación del Río Frío, y cuántos
-- estudiantes tienen en total?


-- C.6 Colegios lejos de un centro de salud (Intermedio)
-- ¿Qué colegios no tienen ningún centro de salud a 600 m o menos?


-- C.7 El centro de salud más cercano (Intermedio)
-- Para cada colegio, muestre el centro de salud más cercano y la distancia.
-- ¿Cuál es el colegio peor ubicado?


-- C.8 Parque El Bosque y Los Pinos (Avanzado)
-- Compare la relación entre Los Pinos y el Parque El Bosque con ST_Contains,
-- ST_Covers y ST_ContainsProperly en PostGIS, y con DETERMINE en Oracle.
-- Explique por qué no todos dan lo mismo y qué opción de ArcGIS escogería para
-- cada caso.


-- C.9 La Avenida Principal y los barrios (Avanzado)
-- La Avenida Principal va por el límite entre barrios. ¿Qué relación tiene con
-- cada barrio? Haga lo mismo con la Variante y Los Pinos: ¿qué tiene de
-- especial ese contacto?


-- C.10 Puentes y desembocaduras (Intermedio)
-- Encuentre los puntos donde una vía cruza un río (puentes) y el punto donde
-- la Quebrada La Honda llega al Río Frío. ¿Por qué el segundo caso no es un
-- cruce?


COMMIT;