-- ==========================================================================
-- Bases de Datos Espaciales - UMNG
-- Sesiones 3 y 4: Creación y manipulación de datos 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: @datos-espaciales-oracle.sql
-- Las tablas quedan en el esquema del usuario con el que se conecte.

-- --------------------------------------------------------------------------
-- PARTE A. CARGA BASE DEL MUNICIPIO
-- 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. La columna geom es de tipo SDO_GEOMETRY. A diferencia de PostGIS,
--     el tipo no restringe si es punto, línea o polígono: eso se controla en el índice.
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. SDO_GEOMETRY(gtype, srid, punto, elem_info, ordenadas):
--     gtype 2001 = punto 2D, 2002 = línea 2D, 2003 = polígono 2D.
--     elem_info (1,2,1) = línea de segmentos rectos; (1,1003,1) = anillo exterior.
--     El anillo exterior de un polígono va en sentido antihorario y se cierra
--     repitiendo el primer vértice al final.
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. Oracle exige registrar cada columna geométrica en
--     USER_SDO_GEOM_METADATA (rango de coordenadas, tolerancia y SRID) antes de
--     crear el índice. La tolerancia 0.005 significa medio centímetro.
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 (R-tree). En Oracle son obligatorios para usar los
--     operadores SDO_RELATE, SDO_ANYINTERACT, SDO_WITHIN_DISTANCE y SDO_NN.
--     layer_gtype restringe el tipo de geometría que acepta la columna.
--     SPATIAL_INDEX_V2 existe desde Oracle 12.2; en versiones anteriores use
--     MDSYS.SPATIAL_INDEX.
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 Crear una tabla espacial con su índice
-- Se crea una tabla de puntos de interés. En PostGIS la columna dice qué tipo
-- de geometría acepta; en Oracle eso lo controla el índice, y además hay que
-- registrar la columna en los metadatos. En ArcGIS se crea una feature class
-- dentro de la geodatabase.
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE puntos_interes PURGE';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE != -942 THEN RAISE; END IF;
END;
/
DELETE FROM user_sdo_geom_metadata WHERE table_name = 'PUNTOS_INTERES';

CREATE TABLE puntos_interes (
  id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  nombre     VARCHAR2(60) NOT NULL,
  categoria  VARCHAR2(30),
  geom       SDO_GEOMETRY
);
INSERT INTO user_sdo_geom_metadata (table_name, column_name, diminfo, srid)
VALUES ('PUNTOS_INTERES', 'GEOM',
  SDO_DIM_ARRAY(SDO_DIM_ELEMENT('X', 4882000, 4887000, 0.005),
                SDO_DIM_ELEMENT('Y', 2094000, 2098000, 0.005)), &srid);
COMMIT;
CREATE INDEX puntos_interes_sidx ON puntos_interes (geom)
  INDEXTYPE IS MDSYS.SPATIAL_INDEX_V2 PARAMETERS ('layer_gtype=POINT');

-- B.2 Insertar geometrías de cuatro formas
-- Las cuatro filas quedan iguales por dentro; solo cambia cómo se escribe la
-- geometría: texto WKT, constructor por coordenadas, WKT extendido (con el
-- SRID dentro) y GeoJSON.
-- 1) Texto WKT + SRID
INSERT INTO puntos_interes (nombre, categoria, geom)
VALUES ('Iglesia San Isidro', 'cultural',
        SDO_GEOMETRY('POINT (4884450 2096150)', &srid));

-- 2) Constructor con SDO_POINT_TYPE (la forma más usada para puntos)
INSERT INTO puntos_interes (nombre, categoria, geom)
VALUES ('Plaza de mercado', 'comercio',
        SDO_GEOMETRY(2001, &srid, SDO_POINT_TYPE(4884700, 2096050, NULL), NULL, NULL));

-- 3) Constructor con arreglo de ordenadas (otra forma válida de guardar un punto)
INSERT INTO puntos_interes (nombre, categoria, geom)
VALUES ('Biblioteca municipal', 'cultural',
        SDO_GEOMETRY(2001, &srid, NULL, SDO_ELEM_INFO_ARRAY(1,1,1),
                     SDO_ORDINATE_ARRAY(4884200, 2096300)));

-- 4) GeoJSON (Oracle 12.2 o superior)
INSERT INTO puntos_interes (nombre, categoria, geom)
VALUES ('Estación de bomberos', 'emergencias',
        SDO_UTIL.FROM_GEOJSON('{"type":"Point","coordinates":[4885200,2095600]}', NULL, &srid));
COMMIT;

-- B.3 Ver la geometría como texto
-- Por dentro la geometría se guarda en binario. Para leerla se convierte a WKT
-- o a GeoJSON.
SELECT b.id, b.nombre, SDO_UTIL.TO_WKTGEOMETRY(b.geom) AS wkt
FROM barrios b;

SELECT c.nombre, SDO_UTIL.TO_GEOJSON(c.geom) AS geojson
FROM colegios c;

-- B.4 Tipo de geometría, SRID y número de vértices
-- Cada geometría sabe qué es, en qué sistema de referencia está y cuántos
-- vértices tiene.
-- Nota: Para leer los atributos internos de SDO_GEOMETRY (b.geom.SDO_GTYPE) la
-- tabla debe tener alias.
SELECT b.nombre,
       b.geom.SDO_GTYPE              AS gtype,
       b.geom.SDO_SRID               AS srid,
       SDO_UTIL.GETNUMVERTICES(b.geom) AS vertices
FROM barrios b;

-- B.5 Coordenadas de un punto
-- Las coordenadas X (este) y Y (norte) de cada colegio.
SELECT c.nombre, c.geom.SDO_POINT.X AS x, c.geom.SDO_POINT.Y AS y
FROM colegios c
ORDER BY c.nombre;

-- B.6 Área y perímetro de polígonos
-- Como el SRID 9377 está en metros, el área sale en m² y el perímetro en
-- metros. Una hectárea son 10.000 m².
-- Nota: Las funciones de SDO_GEOM piden siempre la tolerancia (0.005 m).
-- SDO_LENGTH de un polígono da su perímetro.
SELECT b.nombre,
       SDO_GEOM.SDO_AREA(b.geom, 0.005)                   AS area_m2,
       ROUND(SDO_GEOM.SDO_AREA(b.geom, 0.005) / 10000, 2) AS hectareas,
       SDO_GEOM.SDO_LENGTH(b.geom, 0.005)                 AS perimetro_m
FROM barrios b
ORDER BY area_m2 DESC;

-- B.7 Longitud de líneas
-- Longitud de cada vía, en metros y en kilómetros.
SELECT v.nombre, v.tipo,
       ROUND(SDO_GEOM.SDO_LENGTH(v.geom, 0.005), 1)        AS metros,
       ROUND(SDO_GEOM.SDO_LENGTH(v.geom, 0.005) / 1000, 3) AS km
FROM vias v
ORDER BY metros DESC;

-- B.8 Distancia entre objetos
-- Distancia en línea recta de cada colegio al Hospital San Rafael.
SELECT c.nombre,
       ROUND(SDO_GEOM.SDO_DISTANCE(c.geom, h.geom, 0.005), 1) AS distancia_m
FROM colegios c, centros_salud h
WHERE h.nombre = 'Hospital San Rafael'
ORDER BY distancia_m;

-- B.9 Centroide y rectángulo envolvente
-- El centroide es el "centro de masa" del polígono. El rectángulo envolvente
-- (MBR o envelope) es la caja más pequeña que contiene la geometría; es lo que
-- usa el índice espacial.
SELECT b.nombre,
       SDO_UTIL.TO_WKTGEOMETRY(SDO_GEOM.SDO_CENTROID(b.geom, 0.005)) AS centroide,
       SDO_UTIL.TO_WKTGEOMETRY(SDO_GEOM.SDO_MBR(b.geom))            AS envolvente
FROM barrios b;

-- B.10 Actualizar y eliminar geometrías
-- Se mueve un punto 50 metros al oriente y se elimina otro. Mover es
-- simplemente reemplazar la geometría por una nueva.
-- Nota: El punto de la Biblioteca se guardó con arreglo de ordenadas, así que
-- su SDO_POINT es nulo; por eso el ejemplo mueve la Plaza, que se guardó con
-- SDO_POINT_TYPE.
UPDATE puntos_interes p
SET p.geom = SDO_GEOMETRY(2001, p.geom.SDO_SRID,
               SDO_POINT_TYPE(p.geom.SDO_POINT.X + 50, p.geom.SDO_POINT.Y, NULL),
               NULL, NULL)
WHERE p.nombre = 'Plaza de mercado';

DELETE FROM puntos_interes
WHERE nombre = 'Estación de bomberos';
COMMIT;

SELECT p.nombre, SDO_UTIL.TO_WKTGEOMETRY(p.geom) FROM puntos_interes p;

-- B.11 Geometrías inválidas y cómo repararlas
-- Un polígono "en moño" (sus bordes se cruzan) es inválido: su área y sus
-- operaciones dan resultados errados. Ambos motores permiten detectarlo y
-- repararlo.
-- Nota: El error 13349 significa que el polígono se cruza a sí mismo. Oracle
-- también marca como inválido un anillo exterior en sentido horario (error
-- 13367).
SELECT SDO_GEOM.VALIDATE_GEOMETRY_WITH_CONTEXT(t.g, 0.005) AS validacion,
       SDO_UTIL.TO_WKTGEOMETRY(SDO_UTIL.RECTIFY_GEOMETRY(t.g, 0.005)) AS reparada
FROM (SELECT SDO_GEOMETRY('POLYGON ((0 0, 10 10, 10 0, 0 10, 0 0))', &srid) AS g
      FROM dual) t;

-- B.12 Cambiar de sistema de referencia
-- Pasar de Origen Nacional (metros) a WGS84 (grados de latitud y longitud),
-- que es el que usan Google Maps y los GPS.
-- Nota: Si su Oracle no tiene el SRID 9377 y el script usó 3116, esta
-- transformación ubica los puntos en un lugar equivocado, porque las
-- coordenadas están en Origen Nacional.
SELECT t.nombre, t.g.SDO_POINT.X AS longitud, t.g.SDO_POINT.Y AS latitud
FROM (SELECT h.nombre, SDO_CS.TRANSFORM(h.geom, 4326) AS g
      FROM centros_salud h) t;

-- B.13 Catálogo de columnas espaciales
-- Cada motor lleva un registro de qué tablas tienen geometría, de qué tipo y
-- en qué SRID.
SELECT table_name, column_name, srid
FROM user_sdo_geom_metadata
ORDER BY table_name;

SELECT index_name, table_name
FROM user_sdo_index_info;

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

-- C.1 Crear la capa de árboles (Básico)
-- Cree una tabla arboles con id, especie y altura_m, para puntos en SRID 9377,
-- con su índice espacial. Inserte tres árboles: un Roble de 12 m en (4884300,
-- 2096000), un Urapán de 8 m en (4884550, 2096250) y un Eucalipto de 20 m en
-- (4885800, 2095300).


-- C.2 Una vía nueva (Básico)
-- Agregue a la tabla vias la "Calle 15" (id 6, tipo secundaria), una línea
-- recta de (4883000, 2096300) a (4886000, 2096300). Luego muestre su longitud
-- en kilómetros.


-- C.3 Barrios por tamaño (Básico)
-- Liste los barrios con su área en hectáreas, de mayor a menor.


-- C.4 Densidad de población (Intermedio)
-- Calcule la densidad de población de cada barrio en habitantes por hectárea.
-- ¿Cuál es el más denso?


-- C.5 Kilómetros de vía por tipo (Intermedio)
-- ¿Cuántos kilómetros de vía hay de cada tipo (arterial, principal,
-- secundaria, local)?


-- C.6 Coordenadas para Google Maps (Intermedio)
-- Obtenga la latitud y la longitud (WGS84, EPSG:4326) de los colegios
-- oficiales, redondeadas a 6 decimales.


-- C.7 Colegios cerca al hospital (Intermedio)
-- Muestre la distancia de cada colegio al Hospital San Rafael y diga cuáles
-- están a menos de 1 km.


-- C.8 Detectar y reparar una geometría mala (Avanzado)
-- Inserte en parques el "Parque Mal Dibujado" (id 9, tipo barrial) con este
-- polígono en moño: POLYGON((4884100 2095100, 4884300 2095300, 4884300
-- 2095100, 4884100 2095300, 4884100 2095100)). Encuentre con una consulta los
-- parques inválidos y repárelo.


-- C.9 Mover un centro de salud (Básico)
-- El Puesto de Salud Los Pinos quedó mal ubicado: muévalo 100 m al norte y
-- verifique su nueva coordenada.


-- C.10 Exportar a GeoJSON (Intermedio)
-- Genere el GeoJSON de cada parque con su nombre. Pegue uno de los resultados
-- en geojson.io: ¿por qué no aparece en Colombia?


COMMIT;