-- ==========================================================================
-- Bases de Datos Espaciales - UMNG
-- Sesión 7: Relaciones espaciales
-- Motor: PostgreSQL + PostGIS
-- Municipio ficticio: San Isidro de la Sabana (Cundinamarca)
-- Sistema de referencia: MAGNA-SIRGAS 2018 / Origen Nacional (EPSG:9377), en metros
-- ==========================================================================

-- Cómo ejecutarlo: en pgAdmin abra el Query Tool sobre su base y ejecute todo
-- el archivo (F5), o desde consola: psql -d <su_base> -f relaciones-espaciales-postgis.sql
-- Las tablas quedan en el esquema "sabana".

-- --------------------------------------------------------------------------
-- 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.
-- --------------------------------------------------------------------------

-- A.1 Activar la extensión espacial (una sola vez por base de datos)
CREATE EXTENSION IF NOT EXISTS postgis;

BEGIN;

-- A.2 Registrar el sistema de referencia 9377 si la instalación de PostGIS no lo trae
--     (PostGIS 3.4 y anteriores no lo incluyen en spatial_ref_sys)
INSERT INTO spatial_ref_sys (srid, auth_name, auth_srid, proj4text, srtext)
SELECT 9377, 'EPSG', 9377, '+proj=tmerc +lat_0=4 +lon_0=-73 +k=0.9992 +x_0=5000000 +y_0=2000000 +ellps=GRS80 +towgs84=0,0,0,0,0,0,0 +units=m +no_defs',
       'PROJCS["MAGNA-SIRGAS 2018 / Origen-Nacional",GEOGCS["MAGNA-SIRGAS 2018",DATUM["Marco_Geocentrico_Nacional_de_Referencia_2018",SPHEROID["GRS 1980",6378137,298.257222101]],PRIMEM["Greenwich",0],UNIT["degree",0.0174532925199433]],PROJECTION["Transverse_Mercator"],PARAMETER["latitude_of_origin",4],PARAMETER["central_meridian",-73],PARAMETER["scale_factor",0.9992],PARAMETER["false_easting",5000000],PARAMETER["false_northing",2000000],UNIT["metre",1],AUTHORITY["EPSG","9377"]]'
WHERE NOT EXISTS (SELECT 1 FROM spatial_ref_sys WHERE srid = 9377);

-- A.3 Esquema de trabajo, para no mezclar estas tablas con otras
CREATE SCHEMA IF NOT EXISTS sabana;
SET search_path TO sabana, public;

DROP TABLE IF EXISTS zonas_riesgo CASCADE;
DROP TABLE IF EXISTS parques CASCADE;
DROP TABLE IF EXISTS centros_salud CASCADE;
DROP TABLE IF EXISTS colegios CASCADE;
DROP TABLE IF EXISTS rios CASCADE;
DROP TABLE IF EXISTS vias CASCADE;
DROP TABLE IF EXISTS barrios CASCADE;

-- A.4 Tablas
CREATE TABLE barrios (
  id         integer PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  estrato    smallint,
  poblacion  integer,
  geom       geometry(Polygon, 9377) NOT NULL
);
CREATE TABLE vias (
  id         integer PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  tipo       varchar(20),
  geom       geometry(LineString, 9377) NOT NULL
);
CREATE TABLE rios (
  id         integer PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  tipo       varchar(20),
  geom       geometry(LineString, 9377) NOT NULL
);
CREATE TABLE colegios (
  id         integer PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  sector     varchar(10),
  matricula  integer,
  geom       geometry(Point, 9377) NOT NULL
);
CREATE TABLE centros_salud (
  id         integer PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  tipo       varchar(20),
  geom       geometry(Point, 9377) NOT NULL
);
CREATE TABLE parques (
  id         integer PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  tipo       varchar(20),
  geom       geometry(Polygon, 9377) NOT NULL
);
CREATE TABLE zonas_riesgo (
  id         integer PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  tipo       varchar(30),
  nivel      varchar(10),
  geom       geometry(Polygon, 9377) NOT NULL
);

-- A.5 Datos
INSERT INTO barrios (id, nombre, estrato, poblacion, geom) VALUES
  (1, 'Centro', 3, 4200, ST_GeomFromText('POLYGON ((4884000 2095800, 4884800 2095800, 4884800 2096400, 4884000 2096400, 4884000 2095800))', 9377)),
  (2, 'La Estación', 2, 5100, ST_GeomFromText('POLYGON ((4883000 2095800, 4884000 2095800, 4884000 2096400, 4883000 2096400, 4883000 2095800))', 9377)),
  (3, 'El Cedro', 2, 3800, ST_GeomFromText('POLYGON ((4884800 2095800, 4886000 2095800, 4886000 2096400, 4884800 2096400, 4884800 2095800))', 9377)),
  (4, 'Santa Lucía', 2, 6900, ST_GeomFromText('POLYGON ((4883000 2095000, 4884500 2095000, 4884500 2095800, 4883000 2095800, 4883000 2095000))', 9377)),
  (5, 'Los Pinos', 5, 2700, ST_GeomFromText('POLYGON ((4884500 2095000, 4886000 2095000, 4886000 2095800, 4884500 2095800, 4884500 2095000))', 9377)),
  (6, 'Altos del Río', 4, 3300, ST_GeomFromText('POLYGON ((4883000 2096400, 4886000 2096400, 4886000 2097100, 4885000 2097300, 4884000 2097150, 4883000 2097200, 4883000 2096400))', 9377));

INSERT INTO vias (id, nombre, tipo, geom) VALUES
  (1, 'Avenida Principal', 'arterial', ST_GeomFromText('LINESTRING (4882800 2095800, 4886200 2095800)', 9377)),
  (2, 'Carrera 5', 'principal', ST_GeomFromText('LINESTRING (4884400 2094900, 4884400 2097250)', 9377)),
  (3, 'Calle 10', 'secundaria', ST_GeomFromText('LINESTRING (4883000 2096100, 4886000 2096100)', 9377)),
  (4, 'Variante', 'arterial', ST_GeomFromText('LINESTRING (4882800 2095300, 4883800 2095600, 4885200 2096000, 4886200 2096250)', 9377)),
  (5, 'Calle 3', 'local', ST_GeomFromText('LINESTRING (4883200 2095200, 4884200 2095200, 4884200 2095600)', 9377));

INSERT INTO rios (id, nombre, tipo, geom) VALUES
  (1, 'Río Frío', 'río', ST_GeomFromText('LINESTRING (4882800 2096750, 4883700 2096600, 4884500 2096700, 4885300 2096550, 4885700 2096250, 4886200 2096300)', 9377)),
  (2, 'Quebrada La Honda', 'quebrada', ST_GeomFromText('LINESTRING (4885100 2097250, 4885200 2096900, 4885300 2096550)', 9377));

INSERT INTO colegios (id, nombre, sector, matricula, geom) VALUES
  (1, 'IE Técnico Central', 'oficial', 820, ST_SetSRID(ST_MakePoint(4884350, 2096150), 9377)),
  (2, 'Colegio Santa Lucía', 'oficial', 640, ST_SetSRID(ST_MakePoint(4883600, 2095400), 9377)),
  (3, 'Gimnasio Los Pinos', 'privado', 310, ST_SetSRID(ST_MakePoint(4885300, 2095350), 9377)),
  (4, 'IE La Estación', 'oficial', 540, ST_SetSRID(ST_MakePoint(4883500, 2096100), 9377)),
  (5, 'Liceo El Cedro', 'privado', 260, ST_SetSRID(ST_MakePoint(4885500, 2096200), 9377)),
  (6, 'Colegio Altos', 'oficial', 450, ST_SetSRID(ST_MakePoint(4883800, 2096800), 9377)),
  (7, 'Jardín Rayitos', 'privado', 90, ST_SetSRID(ST_MakePoint(4884800, 2096000), 9377)),
  (8, 'Escuela Rural La Honda', 'oficial', 120, ST_SetSRID(ST_MakePoint(4885600, 2096900), 9377));

INSERT INTO centros_salud (id, nombre, tipo, geom) VALUES
  (1, 'Hospital San Rafael', 'hospital', ST_SetSRID(ST_MakePoint(4884600, 2096250), 9377)),
  (2, 'Puesto de Salud Los Pinos', 'puesto', ST_SetSRID(ST_MakePoint(4885700, 2095500), 9377)),
  (3, 'Centro de Salud Altos', 'centro', ST_SetSRID(ST_MakePoint(4883400, 2096900), 9377));

INSERT INTO parques (id, nombre, tipo, geom) VALUES
  (1, 'Parque Principal', 'plaza', ST_GeomFromText('POLYGON ((4884250 2096000, 4884550 2096000, 4884550 2096300, 4884250 2096300, 4884250 2096000))', 9377)),
  (2, 'Parque Lineal Río Frío', 'lineal', ST_GeomFromText('POLYGON ((4883400 2096300, 4885000 2096300, 4885000 2096750, 4883400 2096750, 4883400 2096300))', 9377)),
  (3, 'Parque El Bosque', 'ecológico', ST_GeomFromText('POLYGON ((4885400 2095000, 4886000 2095000, 4886000 2095300, 4885400 2095300, 4885400 2095000))', 9377));

INSERT INTO zonas_riesgo (id, nombre, tipo, nivel, geom) VALUES
  (1, 'Inundación Río Frío', 'inundación', 'alta', ST_GeomFromText('POLYGON ((4882800 2096450, 4886200 2096100, 4886200 2096500, 4885300 2096750, 4884500 2096900, 4883700 2096800, 4882800 2096950, 4882800 2096450))', 9377)),
  (2, 'Remoción en masa Los Pinos', 'remoción en masa', 'media', ST_GeomFromText('POLYGON ((4885600 2095200, 4886100 2095150, 4886100 2095700, 4885700 2095650, 4885600 2095200))', 9377));

-- A.6 Índices espaciales
CREATE INDEX barrios_geom_idx ON barrios USING GIST (geom);
CREATE INDEX vias_geom_idx ON vias USING GIST (geom);
CREATE INDEX rios_geom_idx ON rios USING GIST (geom);
CREATE INDEX colegios_geom_idx ON colegios USING GIST (geom);
CREATE INDEX centros_salud_geom_idx ON centros_salud USING GIST (geom);
CREATE INDEX parques_geom_idx ON parques USING GIST (geom);
CREATE INDEX zonas_riesgo_geom_idx ON zonas_riesgo USING GIST (geom);

COMMIT;

-- Verificación rápida: debe mostrar 7 tablas con su tipo de geometría y SRID 9377
SELECT f_table_name, type, srid FROM geometry_columns WHERE f_table_schema = 'sabana' ORDER BY 1;

SET search_path TO sabana, public;

-- --------------------------------------------------------------------------
-- 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".
SELECT c.nombre AS colegio, b.nombre AS barrio
FROM colegios c
JOIN barrios b ON ST_Within(c.geom, b.geom)
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.
SELECT c.nombre AS colegio, b.nombre AS barrio,
       ST_Within(c.geom, b.geom)  AS dentro,
       ST_Touches(c.geom, b.geom) AS en_el_borde
FROM colegios c
JOIN barrios b ON ST_Intersects(c.geom, b.geom)
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.
SELECT b.nombre,
       COUNT(c.id)                   AS colegios,
       COALESCE(SUM(c.matricula), 0) AS estudiantes
FROM barrios b
LEFT JOIN colegios c ON ST_Within(c.geom, b.geom)
GROUP BY b.nombre
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
JOIN barrios v ON ST_Touches(v.geom, c.geom)
WHERE c.nombre = 'Centro';

-- 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.
SELECT v.nombre AS via, r.nombre AS rio,
       ST_AsText(ST_Intersection(v.geom, r.geom)) AS puente
FROM vias v
JOIN rios r ON ST_Crosses(v.geom, r.geom);

-- 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
JOIN zonas_riesgo z ON ST_Overlaps(b.geom, z.geom)
WHERE z.nombre = 'Inundación Río Frío';

-- 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".
SELECT b.nombre AS barrio, p.nombre AS parque,
       ST_Contains(b.geom, p.geom)         AS contains,
       ST_Covers(b.geom, p.geom)           AS covers,
       ST_ContainsProperly(b.geom, p.geom) AS contains_properly
FROM barrios b
JOIN parques p ON ST_Intersects(b.geom, p.geom)
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 ST_Intersects(c.geom, z.geom)
);

-- 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(ST_Distance(c.geom, h.geom)::numeric) AS metros
FROM colegios c
JOIN centros_salud h ON ST_DWithin(c.geom, h.geom, 500)
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.
SELECT c.nombre AS colegio, h.nombre AS mas_cercano,
       ROUND(ST_Distance(c.geom, h.geom)::numeric) AS metros
FROM colegios c
CROSS JOIN LATERAL (
  SELECT nombre, geom FROM centros_salud
  ORDER BY geom <-> c.geom
  LIMIT 1
) h
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.
SELECT a.nombre, b.nombre, ST_Relate(a.geom, b.geom) AS matriz
FROM barrios a, parques b
WHERE ST_Intersects(a.geom, b.geom);

-- Preguntar por un patrón: ¿comparten un tramo de borde (dimensión 1)?
SELECT a.nombre, b.nombre
FROM barrios a JOIN barrios b ON a.id < b.id
WHERE ST_Relate(a.geom, b.geom, '****1****');

-- --------------------------------------------------------------------------
-- 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?