AdvancedTopics: gis_dokumentacija.sql

-- ============================================================================
-- ГИС (просторни податоци) — дефиниција на просторниот слој
--
-- Овој фајл го документира географско-информацискиот (ГИС) слој на системот,
-- имплементиран со PostGIS врз табелата or_mapa_objekt. Опфаќа: енум за
-- геометриски тип и просторните функции,
-- тригери и процедура што ја носат бизнис логиката над просторот.
--
-- Дизајнерски начела:
--   * Геометријата се чува како PostGIS geometry(Geometry, 4326) — WGS84
--     (географска должина/ширина), а не како текст.
--   * Геомриетскиот тип не се чува во посебен шифрарник — PostGIS веќе го знае
--     преку GeometryType(); изложен е како изведен енум mapa_geom_tip.
--   * Должините и површините се мерат во метри преку кастирање во geography.
--   * Хиерархијата кампус → зграда → кат → просторија е и логичка (parent FK)
--     и просторна (геометриска содржаност, гарантирана со тригери).
-- ============================================================================


-- Екстензија PostGIS (предуслов за просторните типови и функции).
CREATE EXTENSION IF NOT EXISTS postgis;


-- ============================================================================
-- 1. Енум за геометриски тип
--
-- Лабелите се идентични со резултатот на GeometryType() (голема буква, без
-- префикс ST_), што овозможува директен каст од геометрија во енум.
-- ============================================================================
CREATE TYPE mapa_geom_tip AS ENUM ('POINT', 'LINESTRING', 'POLYGON', 'MULTIPOLYGON');


-- ============================================================================
-- 2. Просторна табела or_mapa_objekt
--
-- Секој ред е просторен објект (кампус, зграда, кат, просторија, точка на
-- интерес). Хиерархијата се гради преку самореференцата parent_mapa_objekt_id.
-- ============================================================================
CREATE TABLE or_mapa_objekt (
    id                    integer GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    geom                  geometry(Geometry, 4326) NOT NULL,        -- отпечаток (полигон) или локација (точка) во WGS84
    geom_tip              mapa_geom_tip,                            -- изведен од geom (се поставува со тригер)
    kapacitet             integer,                                  -- капацитет на простории, пресметан од површина
    parent_mapa_objekt_id integer REFERENCES or_mapa_objekt(id)    -- родител во просторната хиерархија
);

-- ============================================================================
-- 3. Функции
--
-- Функциите враќаат вредност и се вградуваат директно во прашалник, поглед или
-- друга рутина.
-- ============================================================================

-- 3.1 fn_povrshina_m2 — површина на објект во квадратни метри.
-- Геометријата се кастира во geography бидејќи SRID 4326 е во степени;
-- ST_Area врз чиста geometry би вратил степени², а не метри. За објекти без
-- површина (POINT, LINESTRING) враќа 0.
CREATE OR REPLACE FUNCTION fn_povrshina_m2(p_mapa_objekt_id integer)
RETURNS numeric
LANGUAGE sql
STABLE
AS $$
    SELECT ROUND(ST_Area(geom::geography)::numeric, 2)
    FROM or_mapa_objekt
    WHERE id = p_mapa_objekt_id;
$$;

-- 3.2 fn_najblisku_objekti — k најблиски објекти до дадена точка (KNN),
-- опционо филтрирани по геометриски тип. Подредувањето користи GiST преку
-- операторот <-> врз geometry (индексирано), а прикажаното растојание е во
-- точни метри преку geography.
CREATE OR REPLACE FUNCTION fn_najblisku_objekti(
    p_lon      double precision,
    p_lat      double precision,
    p_k        integer        DEFAULT 5,
    p_geom_tip mapa_geom_tip  DEFAULT NULL
)
RETURNS TABLE (mapa_objekt_id integer, tip mapa_geom_tip, rastojanie_m numeric)
LANGUAGE sql
STABLE
AS $$
    SELECT o.id,
           o.geom_tip,
           ROUND(ST_Distance(
                     o.geom::geography,
                     ST_SetSRID(ST_MakePoint(p_lon, p_lat), 4326)::geography
                 )::numeric, 2)
    FROM or_mapa_objekt o
    WHERE p_geom_tip IS NULL OR o.geom_tip = p_geom_tip
    ORDER BY o.geom <-> ST_SetSRID(ST_MakePoint(p_lon, p_lat), 4326)
    LIMIT p_k;
$$;


-- ============================================================================
-- 4. Тригери
--
-- Тригерите гарантираат дека просторното правило важи без оглед кој пишува во
-- табелата — апликација, процедура или рачен INSERT.
-- ============================================================================

-- 4.1 trg_postavi_geom_tip — го изведува енумот geom_tip од самата геометрија
-- при секој внес/измена на geom, со што geom_tip секогаш е во синхрон со
-- податокот. (Изведениот тип не може да биде GENERATED колона бидејќи изразот
-- GeometryType()::enum не е IMMUTABLE.) Неподдржан геометриски тип паѓа на
-- кастот и внесот е одбиен.
CREATE OR REPLACE FUNCTION trgf_postavi_geom_tip()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.geom_tip := GeometryType(NEW.geom)::mapa_geom_tip;
    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_postavi_geom_tip ON or_mapa_objekt;
CREATE TRIGGER trg_postavi_geom_tip
    BEFORE INSERT OR UPDATE OF geom ON or_mapa_objekt
    FOR EACH ROW
    EXECUTE FUNCTION trgf_postavi_geom_tip();


-- 4.2 trg_geom_vo_roditel — геометријата на детето мора да лежи во родителот
-- (ST_CoveredBy). Реализирано како тригер, а не CHECK, бидејќи правилото гледа
-- во друг ред (родителот). Се користи ST_CoveredBy наместо ST_Within за да се
-- дозволи допир по раб (нпр. просторија до надворешниот ѕид на катот).
CREATE OR REPLACE FUNCTION trgf_geom_vo_roditel()
RETURNS trigger
LANGUAGE plpgsql
AS $$
DECLARE
    v_parent geometry;
BEGIN
    IF NEW.parent_mapa_objekt_id IS NULL THEN
        RETURN NEW;   -- врвен објект (кампус) — нема родител
    END IF;

    SELECT geom INTO v_parent
    FROM or_mapa_objekt
    WHERE id = NEW.parent_mapa_objekt_id;

    IF v_parent IS NULL THEN
        RETURN NEW;
    END IF;

    IF NOT ST_CoveredBy(NEW.geom, v_parent) THEN
        RAISE EXCEPTION 'Геометријата мора да лежи во родителскиот објект % (ST_CoveredBy = false)',
            NEW.parent_mapa_objekt_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_geom_vo_roditel ON or_mapa_objekt;
CREATE TRIGGER trg_geom_vo_roditel
    BEFORE INSERT OR UPDATE OF geom, parent_mapa_objekt_id ON or_mapa_objekt
    FOR EACH ROW
    EXECUTE FUNCTION trgf_geom_vo_roditel();


-- 4.3 trg_bez_preklopuvanje — два полигони под ист родител не смеат да се
-- преклопуваат. Комбинацијата ST_Intersects AND NOT ST_Touches го издвојува
-- вистинското преклопување на внатрешности од дозволениот случај кога две
-- соседни простории делат само ѕид (раб). Точките и линиите не се проверуваат.
CREATE OR REPLACE FUNCTION trgf_bez_preklopuvanje()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    IF NEW.parent_mapa_objekt_id IS NULL THEN
        RETURN NEW;
    END IF;

    IF GeometryType(NEW.geom) NOT IN ('POLYGON', 'MULTIPOLYGON') THEN
        RETURN NEW;
    END IF;

    IF EXISTS (
        SELECT 1
        FROM or_mapa_objekt o
        WHERE o.parent_mapa_objekt_id = NEW.parent_mapa_objekt_id
          AND o.id <> NEW.id
          AND GeometryType(o.geom) IN ('POLYGON', 'MULTIPOLYGON')
          AND ST_Intersects(o.geom, NEW.geom)
          AND NOT ST_Touches(o.geom, NEW.geom)
    ) THEN
        RAISE EXCEPTION 'Полигонот се преклопува со постоечки соседен објект под родителот %',
            NEW.parent_mapa_objekt_id;
    END IF;

    RETURN NEW;
END;
$$;

DROP TRIGGER IF EXISTS trg_bez_preklopuvanje ON or_mapa_objekt;
CREATE TRIGGER trg_bez_preklopuvanje
    BEFORE INSERT OR UPDATE OF geom, parent_mapa_objekt_id ON or_mapa_objekt
    FOR EACH ROW
    EXECUTE FUNCTION trgf_bez_preklopuvanje();


-- ============================================================================
-- 5. Процедура
--
-- pr_dodeli_kapacitet_od_povrshina — за сите „лист" полигони (простории без
-- подобјекти) поставува kapacitet = floor(површина_м² / m²_по_студент).
-- Реализирана е како процедура бидејќи е batch DML операција врз повеќе редови
-- без повратна вредност за прашалник. Капацитет се пресметува само за крајните
-- простории, не за катови/згради што ги содржат.
-- ============================================================================
CREATE OR REPLACE PROCEDURE pr_dodeli_kapacitet_od_povrshina(p_m2_po_student numeric)
LANGUAGE plpgsql
AS $$
DECLARE
    r       RECORD;
    v_count integer := 0;
BEGIN
    IF p_m2_po_student <= 0 THEN
        RAISE EXCEPTION 'м² по студент мора да биде позитивно (добиено: %)', p_m2_po_student;
    END IF;

    FOR r IN
        SELECT o.id, ST_Area(o.geom::geography) AS povrsina
        FROM or_mapa_objekt o
        WHERE GeometryType(o.geom) IN ('POLYGON', 'MULTIPOLYGON')
          AND NOT EXISTS (SELECT 1 FROM or_mapa_objekt c
                          WHERE c.parent_mapa_objekt_id = o.id)
    LOOP
        UPDATE or_mapa_objekt
        SET kapacitet = floor(r.povrsina / p_m2_po_student)
        WHERE id = r.id;
        v_count := v_count + 1;
    END LOOP;

    RAISE NOTICE 'Поставен капацитет за % простории.', v_count;
END;
$$;