Changes between Initial Version and Version 1 of AdvancedTopics

Timestamp:

06/15/26 15:33:21 (3 days ago)

Author:

231102

Comment:

--

AdvancedTopics

@@ +1 @@
+= Напредна тема: ГИС (просторни податоци со PostGIS) =
+
+Како напредна тема инфраструктурниот модул (`or_`) е надграден во вистински географско-информациски (ГИС) потсистем преку '''PostGIS'''. Наместо објектите да чуваат „простор" како текст, тие сега чуваат реална геометрија со SRID, индексирана со GiST, и заштитена со просторни тригери.
+
+[attachment:14_gis.sql 14_gis.sql]
+
+
+== Мотивација и опфат ==
+
+Просторното мапирање на објектите (згради, катови, простории, точки на интерес) е централно за инфраструктурниот модул. За тоа да носи реална вредност — содржаност, растојание, површина, спречување преклопување — просторот мора да биде разбирлив за базата, а не само текстуален запис. Надградбата опфаќа:
+
+ * миграција на геометријата на `or_mapa_objekt` од текст во `geometry(Geometry, 4326)`
+ * енумерација `mapa_geom_tip` изведен автоматски од самата геометрија
+ * GiST просторен индекс
+ * 2 функции, 3 тригери и 1 процедура со просторна логика
+ * хиерархиска и просторна валидација (кампус → зграда → кат → просторија)
+
+
+== Дизајнерски одлуки ==
+
+=== 1. Отстранет `or_tip_mapa_objekt` ===
+
+Табелата `or_tip_mapa_objekt` беше замислена да ги чува геометриските типови (POINT, LINESTRING, POLYGON, MULTIPOLYGON). Бидејќи PostGIS веќе го знае типот на секоја геометрија преку `GeometryType(geom)`, одделен шифрарник со надворешен клуч е чист вишок. Затоа табелата, колоната `map_object_type_id` и ограничувањето `fk_mapa_obj_tip` се отстранети. (Семантичката класификација на објектите — зграда, просторија — и онака живее во `or_tip_objekt_organizacija`.)
+
+=== 2. Текст → `geometry(Geometry, 4326)` ===
+
+Колоната `spatial_tekst text` е заменета со `geom geometry(Geometry, 4326)`. SRID '''4326''' е WGS84 (географска должина/ширина во степени). Типот `Geometry` (генерички) дозволува различни видови геометрија во иста табела — полигони за отпечатоци и точки за локации.
+
+=== 3. Изведен енум `mapa_geom_tip` (преку тригер, не GENERATED) ===
+
+Типот на геометријата се изложува преку енум `mapa_geom_tip`, но вредноста '''не се внесува рачно''' — се изведува автоматски од геометријата, па никогаш не може да се разликува од податокот. Првично беше пробана `GENERATED` колона со израз `GeometryType(geom)::mapa_geom_tip`, но PostGIS го одби бидејќи изразот не е `IMMUTABLE`:
+
+{{{
+ERROR:  generation expression is not immutable
+}}}
+
+Затоа решението е `BEFORE` тригер (`trg_postavi_geom_tip`) кој ја поставува вредноста при секој внес/измена на геометријата — иста гаранција, без барањето за имутабилност.
+
+
+== Надградба на шемата ==
+
+Енумот мора да постои пред табелата што го користи:
+
+{{{
+CREATE TYPE mapa_geom_tip AS ENUM ('POINT', 'LINESTRING', 'POLYGON', 'MULTIPOLYGON');
+}}}
+
+Структурата на `or_mapa_objekt` по надградбата:
+
+{{{
+id                     integer  (identity, PRIMARY KEY)
+geom                   geometry(Geometry, 4326)   -- вистинска геометрија (WGS84)
+geom_tip               mapa_geom_tip              -- изведен од geom преку тригер
+kapacitet              integer                    -- пресметан од површина
+parent_mapa_objekt_id  integer → or_mapa_objekt(id)  -- просторна хиерархија
+}}}
+
+ * '''geom''' — отпечаток (полигон) или локација (точка) во SRID 4326.
+ * '''geom_tip''' — енум синхронизиран со геометријата (преку тригер 8.1).
+ * '''kapacitet''' — се полни од површината преку процедурата.
+ * '''parent_mapa_objekt_id''' — самореференца за дрвото кампус → зграда → кат → просторија.
+
+
+== Функции ==
+
+Функциите враќаат вредност и се вградуваат директно во прашалник, поглед или друга рутина.
+
+=== 1. fn_povrshina_m2 ===
+
+{{{
+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;
+$$;
+}}}
+
+Функцијата `fn_povrshina_m2` ја враќа површината на објектот во '''квадратни метри'''. Бидејќи SRID 4326 е во степени, геометријата се кастира во `geography` за да се добие геодезиска површина во метри (`ST_Area` врз чиста geometry би дал степени²). За објекти што немаат површина (POINT, LINESTRING) враќа 0. Реализирана е како функција бидејќи враќа единечна вредност што се вградува во прашалници и во процедурата за капацитет.
+
+=== 2. fn_najblisku_objekti ===
+
+{{{
+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;
+$$;
+}}}
+
+Функцијата `fn_najblisku_objekti` ги враќа `k`-те најблиски објекти до дадена точка (KNN — k-nearest neighbours), со опционо филтрирање по геометриски тип. Подредувањето се прави преку GiST операторот `<->` врз geometry (брзо и индексирано), додека прикажаното растојание се пресметува преку `geography` за точни метри. На обем на кампус планарното подредување се совпаѓа со геодезиското, а индексот останува искористен. Враќа множество редови, па е функција (table-valued) што се користи со `SELECT * FROM fn_najblisku_objekti(...)`.
+
+
+== Тригери ==
+
+Тригерите гарантираат дека правилото важи без оглед кој пишува во табелата — апликација, процедура или рачен INSERT.
+
+=== 1. trg_postavi_geom_tip ===
+
+{{{
+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;
+$$;
+
+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();
+}}}
+
+Тригерот `trg_postavi_geom_tip` го изведува енумот `geom_tip` од самата геометрија при секој внес или измена на `geom`. Тоа е замена за `GENERATED` колона (чиј израз не е IMMUTABLE), при што `geom_tip` секогаш останува во синхрон со податокот. Како бонус, ако геометријата е неподдржан тип (нпр. `GEOMETRYCOLLECTION`), кастот во енумот фрла грешка и внесот е одбиен.
+
+=== 2. trg_geom_vo_roditel ===
+
+{{{
+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;
+$$;
+
+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();
+}}}
+
+Тригерот `trg_geom_vo_roditel` го спроведува просторниот интегритет на хиерархијата: геометријата на детето мора да лежи во геометријата на родителот (`ST_CoveredBy`). Реализиран е како тригер, а не `CHECK`, бидејќи правилото гледа во '''друг ред''' (родителот), што `CHECK` не дозволува. Се користи `ST_CoveredBy` наместо `ST_Within` за да се дозволи допир по раб (нпр. просторија до надворешниот ѕид на катот).
+
+=== 3. trg_bez_preklopuvanje ===
+
+{{{
+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;
+$$;
+
+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();
+}}}
+
+Тригерот `trg_bez_preklopuvanje` спречува два полигони под ист родител да се преклопуваат. Комбинацијата `ST_Intersects AND NOT ST_Touches` го издвојува '''вистинското преклопување на внатрешности''' од дозволениот случај кога две соседни простории делат само ѕид (раб). Точките и линиите не се проверуваат. Тоа е круцијално за точни просторни распореди без преклопување на простории.
+
+
+== Процедура ==
+
+=== 1. pr_dodeli_kapacitet_od_povrshina ===
+
+{{{
+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;
+$$;
+}}}
+
+Процедурата `pr_dodeli_kapacitet_od_povrshina` за сите „лист" полигони (простории без подобјекти) поставува `kapacitet = floor(површина_м² / m2_po_student)`. Реализирана е како процедура бидејќи е batch DML операција врз повеќе редови без повратна вредност за прашалник. Капацитет се пресметува само за крајните простории, не за катови/згради што ги содржат.
+
+
+== Тест податоци и верификација ==
+
+За тестирање е внесена мала вгнездена хиерархија (схематски квадрати во WGS84, наменети да ја докажат логиката — содржаност, преклопување, KNN — а не реални димензии): кампус → зграда → кат → 2 простории + 1 точка на интерес.
+
+'''Преглед на објектите (изведен тип и површина):'''
+
+{{{
+ id | geom_tip | parent_mapa_objekt_id |    m2
+----+----------+-----------------------+-----------
+  1 | POLYGON  |                       | 920179.37
+  2 | POLYGON  |                     1 | 147230.98
+  3 | POLYGON  |                     2 | 147230.98
+  4 | POLYGON  |                     3 |  73615.49
+  5 | POLYGON  |                     3 |  73615.49
+  6 | POINT    |                     2 |      0.00
+}}}
+
+`geom_tip` е точно изведен (POLYGON/POINT), а `fn_povrshina_m2` враќа метри (точката има 0).
+
+'''Капацитет од површина''' (`CALL pr_dodeli_kapacitet_od_povrshina(2.0);`) — се ажурираат само листовите (простории 4 и 5):
+
+{{{
+ id | geom_tip | kapacitet |    m2
+----+----------+-----------+----------
+  4 | POLYGON  |     36807 | 73615.49
+  5 | POLYGON  |     36807 | 73615.49
+}}}
+
+'''Најблиски објекти''' од надворешна точка (`fn_najblisku_objekti(21.42, 42.02, 3)`) — растојание во метри, растечки:
+
+{{{
+ mapa_objekt_id |   tip   | rastojanie_m
+----------------+---------+--------------
+              1 | POLYGON |      1385.58
+              2 | POLYGON |      1939.84
+              5 | POLYGON |      1939.84
+}}}
+
+'''GeoJSON''' (`ST_AsGeoJSON(geom)`) — доказ дека податокот е реално просторен и подготвен за рендерирање на мапа:
+
+{{{
+{"type":"Point","coordinates":[21.405,42.005]}
+{"type":"Polygon","coordinates":[[[21.402,42.002],[21.406,42.002],...]]}
+}}}
+
+'''Докази дека тригерите работат:'''
+
+ * Просторија со координати '''надвор''' од катот → одбиена од `trg_geom_vo_roditel`:
+{{{
+ERROR:  Геометријата мора да лежи во родителскиот објект 1 (ST_CoveredBy = false)
+}}}
+ * Две простории што делат ѕид (раб) → '''дозволени'''; просторија што навлегува во соседна → одбиена од `trg_bez_preklopuvanje`:
+{{{
+ERROR:  Полигонот се преклопува со постоечки соседен објект под родителот 3
+}}}
+ * Неподдржан геометриски тип (`GEOMETRYCOLLECTION`) → одбиен од кастот во `trg_postavi_geom_tip`:
+{{{
+ERROR:  invalid input value for enum mapa_geom_tip: "GEOMETRYCOLLECTION"
+}}}
+