= Напредна тема: ГИС (просторни податоци со PostGIS) = Како напредна тема инфраструктурниот модул (`or_`) е надграден во вистински географско-информациски (ГИС) потсистем преку '''PostGIS'''. Наместо објектите да чуваат „простор" како текст, тие сега чуваат реална геометрија со SRID, индексирана со GiST, и заштитена со просторни тригери. [attachment:gis_dokumentacija.sql​ gis_dokumentacija.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" }}}