-- ==========================================================================
-- Bases de Datos Espaciales - UMNG
-- Sesiones 5 y 6: Análisis espacial (unión, intersección, diferencia, buffer)
-- 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 analisis-espacial-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 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 ST_AsText(ST_Union(a.geom, b.geom)) AS wkt,
       ST_Area(ST_Union(a.geom, b.geom))      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 ST_AsText(ST_Union(geom))          AS limite,
       ST_Area(ST_Union(geom)) / 1000000   AS km2
FROM barrios;

-- 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 estrato,
       COUNT(*)                       AS barrios,
       SUM(poblacion)                 AS poblacion,
       ROUND((ST_Area(ST_Union(geom)) / 10000)::numeric, 1) AS hectareas,
       ST_GeometryType(ST_Union(geom)) AS tipo
FROM barrios
GROUP BY estrato
ORDER BY 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.
SELECT b.nombre AS barrio,
       ROUND(ST_Area(ST_Intersection(b.geom, z.geom))::numeric) AS area_inundable_m2,
       ROUND((100 * ST_Area(ST_Intersection(b.geom, z.geom)) / ST_Area(b.geom))::numeric, 1) AS porcentaje
FROM barrios b
JOIN zonas_riesgo z ON ST_Intersects(b.geom, z.geom)
WHERE z.nombre = 'Inundación Río Frío'
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(ST_Area(b.geom)::numeric)                        AS area_total,
       ROUND(ST_Area(ST_Difference(b.geom, z.geom))::numeric) 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.
SELECT ROUND(ST_Area(ST_SymDifference(p.geom, z.geom))::numeric) AS area_simdif,
       ROUND(ST_Area(ST_Union(p.geom, z.geom))::numeric)          AS area_union,
       ROUND(ST_Area(ST_Intersection(p.geom, z.geom))::numeric)   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.
SELECT nombre,
       ROUND(ST_Area(ST_Buffer(geom, 30))::numeric) AS area_ronda_m2
FROM rios;

-- Las dos rondas unidas en una sola geometría
SELECT ST_Area(ST_Union(ST_Buffer(geom, 30))) AS area_total
FROM rios;

-- 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.
SELECT ROUND(SUM(ST_Area(ST_Buffer(geom, 400)))::numeric)        AS suma_areas,
       ROUND(ST_Area(ST_Union(ST_Buffer(geom, 400)))::numeric)      AS area_unida
FROM colegios
WHERE 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.
DROP TABLE IF EXISTS ronda_hidrica;
CREATE TABLE ronda_hidrica AS
SELECT id, nombre,
       ST_Buffer(geom, 30)::geometry(Polygon, 9377) AS geom
FROM rios;
CREATE INDEX ronda_hidrica_geom_idx ON ronda_hidrica USING GIST (geom);

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