-- ==========================================================================
-- Bases de Datos Espaciales - UMNG
-- Sesiones 3 y 4: Creación y manipulación de datos 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 datos-espaciales-postgis.sql
-- Las tablas quedan en el esquema "sabana".

-- --------------------------------------------------------------------------
-- PARTE A. CARGA BASE DEL MUNICIPIO
-- --------------------------------------------------------------------------

-- 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. La columna geom usa el tipo geometry(<TipoGeometría>, <SRID>):
--     PostGIS rechaza cualquier geometría de otro tipo o de otro sistema de referencia.
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. Se usan dos formas de construir geometrías:
--     ST_GeomFromText(WKT, SRID) para polígonos y líneas,
--     ST_SetSRID(ST_MakePoint(x, y), SRID) para puntos.
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 (GiST). Aceleran las consultas espaciales: sin ellos
--     la base compara cada geometría contra todas las demás.
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 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.
DROP TABLE IF EXISTS puntos_interes;
CREATE TABLE puntos_interes (
  id         serial PRIMARY KEY,
  nombre     varchar(60) NOT NULL,
  categoria  varchar(30),
  geom       geometry(Point, 9377)
);
CREATE INDEX puntos_interes_geom_idx ON puntos_interes USING GIST (geom);

-- 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',
        ST_GeomFromText('POINT(4884450 2096150)', 9377));

-- 2) Coordenadas sueltas
INSERT INTO puntos_interes (nombre, categoria, geom)
VALUES ('Plaza de mercado', 'comercio',
        ST_SetSRID(ST_MakePoint(4884700, 2096050), 9377));

-- 3) WKT extendido (EWKT), propio de PostGIS
INSERT INTO puntos_interes (nombre, categoria, geom)
VALUES ('Biblioteca municipal', 'cultural',
        ST_GeomFromEWKT('SRID=9377;POINT(4884200 2096300)'));

-- 4) GeoJSON
INSERT INTO puntos_interes (nombre, categoria, geom)
VALUES ('Estación de bomberos', 'emergencias',
        ST_SetSRID(ST_GeomFromGeoJSON('{"type":"Point","coordinates":[4885200,2095600]}'), 9377));

-- 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 id, nombre, ST_AsText(geom) AS wkt
FROM barrios;

SELECT nombre, ST_AsGeoJSON(geom) AS geojson
FROM colegios;

-- 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.
SELECT nombre,
       ST_GeometryType(geom) AS tipo,
       ST_SRID(geom)         AS srid,
       ST_NPoints(geom)      AS vertices
FROM barrios;

-- B.5 Coordenadas de un punto
-- Las coordenadas X (este) y Y (norte) de cada colegio.
SELECT nombre, ST_X(geom) AS x, ST_Y(geom) AS y
FROM colegios
ORDER BY 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².
SELECT nombre,
       ST_Area(geom)                       AS area_m2,
       ROUND((ST_Area(geom) / 10000)::numeric, 2) AS hectareas,
       ST_Perimeter(geom)                  AS perimetro_m
FROM barrios
ORDER BY area_m2 DESC;

-- B.7 Longitud de líneas
-- Longitud de cada vía, en metros y en kilómetros.
SELECT nombre, tipo,
       ROUND(ST_Length(geom)::numeric, 1)          AS metros,
       ROUND((ST_Length(geom) / 1000)::numeric, 3) AS km
FROM vias
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(ST_Distance(c.geom, h.geom)::numeric, 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 nombre,
       ST_AsText(ST_Centroid(geom)) AS centroide,
       ST_AsText(ST_Envelope(geom)) AS envolvente
FROM barrios;

-- 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.
UPDATE puntos_interes
SET geom = ST_Translate(geom, 50, 0)
WHERE nombre = 'Plaza de mercado';

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

SELECT nombre, ST_AsText(geom) FROM puntos_interes;

-- 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.
SELECT ST_IsValid(g)          AS es_valida,
       ST_IsValidReason(g)    AS motivo,
       ST_AsText(ST_MakeValid(g)) AS reparada
FROM (SELECT ST_GeomFromText(
        'POLYGON((0 0, 10 10, 10 0, 0 10, 0 0))', 9377) AS g) 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.
SELECT nombre,
       ST_X(ST_Transform(geom, 4326)) AS longitud,
       ST_Y(ST_Transform(geom, 4326)) AS latitud
FROM centros_salud;

-- 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 f_table_schema, f_table_name, f_geometry_column, type, srid
FROM geometry_columns
WHERE f_table_schema = 'sabana';

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