Напредна тема: ГИС (просторни податоци со PostGIS)

Како напредна тема инфраструктурниот модул (or_) е надграден во вистински географско-информациски (ГИС) потсистем преку PostGIS. Наместо објектите да чуваат „простор" како текст, тие сега чуваат реална геометрија со SRID, индексирана со GiST, и заштитена со просторни тригери.

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"