Напредна тема: Имплементација на PostGIS

За имплементација на напредна тема за нашиот проект избравме PostGIS кој овозможува надградување на базата на податоци да управува со географски информации. Во реалните системи на светските авиокомпании овозможено е следење на летови, нивните рути како и самите авиони употребувајќи GPS технологии и калкулации на рути. Со цел пореалистично моделирање на проектот сметаме дека оваа тема би дала малку поголема прецизност на дел од податоците.

Пред имплементаицја на оваа тема, во табелата ScheduledFlight која претставува закажан лет (урнек), колоната Distance беше генерирана на рандомизиран начин односно според времето на полетување и слетување, што не претставува реален податок. Проблем се јавува при употреба на оваа вредност во калкулациите на цена на авионски билети како и додавање на поени за летање на патниците. Со воведување на PostGIS, податоците се реални и употребливи во бизнис логиката на апликацијата. Исто така овозможено е имплементирање на дополнителни функционалности присутни во реални системи.

Со цен јасно прикажување на резултатите кои ги добивме со имплементација на PostGIS, за визуелизација на податоците ја употребивме алатката QGIS која директно се поврзува со нашата база на податоци во Postgres и во која може да се извршуваат и потребните прашалници. Во документацијата по имплементација на секој дел ја имаме прикажано и соодветната визуелизација.

Инсталација на PostGIS

За воведување на PostGIS во нашата база на податоци потребно беше локална инсталација на алатката и нејзино вклучување во базата преку извршување на командата

create extension postgis;

Пормена на таблеата Airport

За успешно имплементирање на PostGIS потребно беше да се направи промена во табелата Airport. За да можеме да пресметаме растојание помеѓу аеродромите (километража на лет) потрбено беше додавање на колони Longitude (географска должина) и Latitude (географска ширина). Со додавање на овие две колони овозможено е одредување на реалните координати на аеродромот.

alter table airport
add column longitude numeric;

alter table airport
add column latitude numeric;

По додавање на колоните тие беа пополнети со веќе постоечките податоци од OpenFlight CSV датотеката која исто така ја употребивме и за првобитното пополнување на табелата што ни овозможи одржување на конзистентност во податоците.

Како следен чекор потребно беше претставување на координатите во форма на Point. За таа цел додадовме нова колона location од тип geography(Point, 4326).

alter table airport
add column location geography(Point, 4326);

За пополнување на колоната location потребно беше да се искористат податоците за longitude и latitude на секој аеродром. За креирање на објект Point потребно беше да се изврши функцијата ST_MakePoint, која создава Point од два аргументи - координати.

update airport
set location = ST_MakePoint(longitude, latitude)::geography;

При визуелизација ја искористивме колоната location во Airport и како лабела на секоја точка колоната Code.

Сите аеродроми

]]

Аеродромите на балканскиот полуостров

]]

3. Каде е интегриран PostGIS?

PostGIS се протега низ повеќе компоненти на постоечкиот проект:

Компонента	Тип на интеграција
Табела Airport	Додадени се колоните latitude, longitude, location (geography Point)
Колона Airport.location	Просторен GIST индекс за брзи геопросторни прашалници
Табела ScheduledFlight	Колоната distance е ажурирана со пресметани реални дистанци
Тригер SetDistance	Автоматски пресметува дистанца при INSERT/UPDATE на ScheduledFlight
Поглед FlightRoutes	Нов поглед кој ги претставува летовите како LineString геометрии
Функција FindNearestAirport	Нова функција за наоѓање најблиски аеродроми
Функција FlightAtTime	Нова функција за пресметка на локација на лет во даден момент
Тригер AwardMilePoints (Фаза 4)	Сега ги пресметува миљните поени врз основа на реални растојанија

---

4. Технички предуслови

Пред да може да се користи PostGIS, екстензијата мора да биде активирана во базата на податоци:

CREATE EXTENSION postgis;

Оваа команда ги регистрира сите PostGIS типови на податоци (geography, geometry), функции (ST_Distance, ST_MakePoint, ST_LineInterpolatePoint итн.) и оператори (просторниот KNN оператор <->) во моменталната база.

---

5. Промени на постоечките табели

5.1 Додавање на колоните latitude и longitude

Првиот чекор беше да се обогати табелата Airport со географски координати. Овие податоци ги имавме од оригиналниот CSV фајл (airports.csv од OpenFlights), но во Фаза 2Б беа отфрлени бидејќи тогаш немавме просторна функционалност.

ALTER TABLE airport ADD COLUMN latitude  numeric;
ALTER TABLE airport ADD COLUMN longitude numeric;

Колоните потоа се пополнија со податоци од привремена табела temp_airports, спарувајќи по IATA код:

UPDATE airport a
SET latitude  = ta.latitude::numeric,
    longitude = ta.longitude::numeric
FROM (
    SELECT DISTINCT ON (iata) iata, latitude, longitude
    FROM temp_airports
    WHERE latitude  NOT IN ('\N','')
      AND longitude NOT IN ('\N','')
    ORDER BY iata
) ta
WHERE a.code = ta.iata;

Овие колони служат како „сурова" форма на координатите — лесни за читање и експортирање, но не директно искористливи во просторни прашалници.

5.2 Додавање на колоната location (geography Point)

Главната просторна колона е location, која ги обединува latitude и longitude во една PostGIS геометрија:

ALTER TABLE airport
ADD COLUMN location geography(Point, 4326);

UPDATE airport
SET location = ST_MakePoint(longitude, latitude)::geography;

Неколку важни детали:

• Тип geography(Point, 4326). PostGIS нуди два главни типа на просторни податоци: geometry (рамна, евклидска геометрија) и geography (вистинска сферна геометрија на Земјата). За авиокомпаниски проект, geography е правилниот избор бидејќи летовите се на голема скала и кривината на Земјата е значајна — растојанијата мора да се мерат како great-circle distances, а не како евклидски растојанија.
• SRID 4326. Ова е стандардот WGS 84 (World Geodetic System 1984), истиот референтен систем кој го користат GPS уредите. Со ова, координатите од нашиот CSV (кои се во WGS 84) се компатибилни со колоната без потреба од репроекција.
• Редослед на параметри: ST_MakePoint(longitude, latitude). PostGIS следи XY-конвенција, што значи дека longitude е X (надолжна, „хоризонтална" оска) и latitude е Y. Замена на овие два параметра е честа грешка која води до точки во погрешен дел на светот.

5.3 Просторен GIST индекс

За да може PostGIS брзо да изврши просторни прашалници врз милиони редици, на колоната location се додаде GIST индекс:

CREATE INDEX idx_airport_location ON airport USING GIST (location);

За разлика од B-tree индексите (кои се користат за подредени скаларни вредности — броеви, текстови, датуми), GIST индексите се специјализирани за повеќедимензионални податоци. Тие овозможуваат:

• Range queries во просторот — „пронајди ги сите аеродроми во радиус од 500 km околу точката X"
• Nearest-neighbor queries — „пронајди ги најблиските 10 аеродроми од дадена точка", преку K-nearest-neighbor операторот <->
• Intersection queries — „пронајди ги сите рути кои поминуваат низ дадена област"

Без GIST индекс, секоја таква претрага би била sequential scan низ целата табела.

---

6. Поглед FlightRoutes

Создаден е нов поглед кој ги претставува летовите како геометриски линии на мапата:

CREATE VIEW FlightRoutes (FlightID, FlightNumber, Departure, Arrival,
    Origin, Destination, Route, Distance) AS
SELECT flight.id, flight.flightnumber,
       flight.departure, flight.arrival,
       ad.code, aa.code,
       ST_MakeLine(ad.location::geometry, aa.location::geometry),
       scheduledflight.distance
FROM flight
JOIN gate     AS ga ON flight.actualgatearrivalid   = ga.id
JOIN gate     AS gd ON flight.actualgatedepartureid = gd.id
JOIN terminal AS ta ON ta.id = ga.terminalid
JOIN terminal AS td ON td.id = gd.terminalid
JOIN airport  AS aa ON aa.id = ta.airportid
JOIN airport  AS ad ON ad.id = td.airportid
JOIN scheduledflight ON flight.scheduleid = scheduledflight.id
WHERE aa.location IS NOT NULL AND ad.location IS NOT NULL;

Како функционира:

• Функцијата ST_MakeLine(departure_point, arrival_point) гради LineString геометрија — права линија помеѓу две точки во геометрискиот простор.
• Низ ланецот од JOIN-ови, погледот ги доведува аеродромите за полетување и слетување на секој лет (преку gate → terminal → airport).
• Резултатот е една редица по лет, со полно име, временски печат, кодови на полетниот и слетниот аеродром, реална географска линија помеѓу нив и пресметана дистанца.

Случаи на употреба:

• Визуелизација во QGIS — погледот може да се вчита како просторен слој и да прикаже сите рути на мапа.
• Мрежна анализа — кои аеродроми се најпрометни, кои рути се најфреквентни, која е географската покриеност на авиокомпанијата.
• Просторни прашалници — на пример, „кои летови поминуваат во радиус од 200 km околу одредена точка" може да се изврши со ST_DWithin врз колоната Route.

6.1 Скриншоти

Сите аеродроми на светот:

Аеродроми на Балканот:

Рути од Скопје:

Рути до Newark (EWR):

---

7. Ажурирање на дистанците во ScheduledFlight

Една од најважните придобивки на PostGIS во овој проект е замена на лажните, синтетички дистанци со вистински географски вредности.

UPDATE scheduledflight AS sf
SET distance = GREATEST(1, ROUND(ST_Distance(dep.location, arr.location) / 1000)::int)
FROM slot AS ds
JOIN gate     AS dg ON ds.gateid     = dg.id
JOIN terminal AS dt ON dg.terminalid = dt.id
JOIN airport  AS dep ON dep.id       = dt.airportid,
     slot AS asl
JOIN gate     AS ag ON asl.gateid    = ag.id
JOIN terminal AS at ON at.id         = ag.terminalid
JOIN airport  AS arr ON arr.id       = at.airportid
WHERE sf.departure = ds.id
  AND sf.arrival   = asl.id
  AND dep.location IS NOT NULL
  AND arr.location IS NOT NULL;

Детали за пресметката:

• ST_Distance(dep.location, arr.location) враќа great-circle дистанца помеѓу две geography точки во метри.
• Делењето со 1000 ја претвора во километри.
• ROUND(...)::int ја кружи до цел број за да одговара на типот на колоната.
• GREATEST(1, ...) гарантира минимална вредност од 1 km (поради CHECK ограничувањето distance > 0). Ова е важно за ретки случаи каде два аеродроми се толку близу што дистанцата заокружена на километри би била 0.

Последица низ остатокот на системот:

Постоечкиот тригер AwardMilePoints (од Фаза 4) ги пресметува миљните поени на патниците врз основа на ScheduledFlight.Distance, помножена со множител по класа (Economy: 1×, Business: 2×, First: 3×). По ова ажурирање, поените повеќе не се „измислени" — патник со First класа на лет JFK → LAX добива ~4000 km × 3 = 12,000 поени, што одговара на реално долг лет.

---

8. Тригер SetDistance (CalculateDistance)

За да не мора рачно да се пресметува дистанцата секогаш кога ќе се додаде нов закажан лет, имплементиран е тригер кој автоматски ја пресметува:

CREATE OR REPLACE FUNCTION CalculateDistance()
RETURNS trigger AS $$
BEGIN
    SELECT GREATEST(1, ROUND(ST_Distance(dep.location, arr.location) / 1000)::int)
    INTO NEW.distance
    FROM slot AS ds
    JOIN gate     AS dg ON ds.gateid     = dg.id
    JOIN terminal AS dt ON dg.terminalid = dt.id
    JOIN airport  AS dep ON dep.id       = dt.airportid,
         slot AS asl
    JOIN gate     AS ag ON asl.gateid    = ag.id
    JOIN terminal AS at ON at.id         = ag.terminalid
    JOIN airport  AS arr ON arr.id       = at.airportid
    WHERE NEW.departure = ds.id
      AND NEW.arrival   = asl.id
      AND dep.location IS NOT NULL
      AND arr.location IS NOT NULL;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER SetDistance
BEFORE INSERT OR UPDATE OF departure, arrival ON scheduledflight
FOR EACH ROW EXECUTE FUNCTION CalculateDistance();

Карактеристики:

• BEFORE INSERT OR UPDATE — тригерот се извршува пред редицата да биде впишана/ажурирана. Тоа му овозможува да ја смени вредноста на NEW.distance пред таа да биде валидирана од CHECK ограничувањата.
• UPDATE OF departure, arrival — тригерот се извршува само кога овие конкретни колони се менуваат. Доколку се ажурира некоја друга колона (на пример, active), тригерот не се активира — оптимизација која штеди непотребна работа.
• SELECT ... INTO NEW.distance — наместо да враќа вредност на повикувачот, тригерот ја менува вредноста на NEW пред commit-ирање.

Зошто тригер наместо generated column? PostgreSQL поддржува GENERATED ALWAYS AS (...) STORED колони, но тие не можат да користат subqueries или JOIN-ови (бидејќи мораат да бидат детерминистички и без странични ефекти). Бидејќи пресметката на дистанцата бара JOIN преку slot → gate → terminal → airport, мораше да се користи тригер.

Случај на употреба: доколку во иднина се додадат нови закажани летови (нови дестинации, проширување на мрежата), нивната дистанца автоматски се пресметува правилно — администраторот не мора да внимава да ја внесе како посебен чекор.

---

9. Функција FindNearestAirport

CREATE OR REPLACE FUNCTION FindNearestAirport(
    longitude_P numeric,
    latitude_P  numeric,
    distanceKm  int
) RETURNS TABLE (
    AirportCode     char(3),
    AirportName     text,
    AirportLocation geography(Point, 4326),
    Distance_KM     numeric
) AS $$
BEGIN
    RETURN QUERY
    SELECT a.code, a.name, a.location,
           (ST_Distance(a.location, ST_MakePoint(longitude_P, latitude_P)::geography) / 1000)::numeric
    FROM airport AS a
    WHERE ST_DWithin(
        a.location,
        ST_MakePoint(longitude_P, latitude_P)::geography,
        distanceKm * 1000
    )
    ORDER BY a.location <-> ST_MakePoint(longitude_P, latitude_P)::geography;
END;
$$ LANGUAGE plpgsql;

Како функционира:

• Прима три параметри: longitude и latitude на референтна точка, плус максимален радиус во километри.
• Враќа табела на сите аеродроми во тој радиус, заедно со нивната локација и точното растојание.
• Резултатите се сортирани по растојание — најблискиот аеродром е прв.

Клучни PostGIS компоненти:

• ST_DWithin(geog_a, geog_b, distance_meters) — враќа TRUE доколку две географски точки се на растојание помало или еднакво на дадениот број метри. Ова е првиот филтер кој го „сече" пребарувањето.
• Множењето distanceKm * 1000 ги претвора километрите во метри (PostGIS интерно работи во метри за geography).
• KNN оператор <-> — посебен оператор за nearest-neighbor сортирање. За разлика од обичен ORDER BY ST_Distance(...), овој оператор го користи GIST индексот директно и е значајно побрз при големи табели.

Случаи на употреба:

• Препораки за алтернативни аеродроми — кога одреден лет се откажува, можеме да им предложиме на патниците блиски аеродроми (на пример, ако се откажува лет до LHR, можеме да предложиме LGW, STN, LTN).
• Логистика на товар — наоѓање најблизок аеродром до магацин или дистрибутивен центар.
• Кориснички интерфејс — „наоѓаш ли се близу аеродром?" функционалност за мобилна апликација.

Демонстрација: наоѓање на најблиските аеродроми во радиус од 100 km околу Скопје (координати 21.62°E, 41.96°N):

SELECT * FROM FindNearestAirport(21.62, 41.96, 100);

---

10. Функција FlightAtTime

Оваа функција пресметува каде се наоѓа определен лет (естимативно) во даден временски момент — без потреба од посебна табела со логирани локации.

CREATE OR REPLACE FUNCTION FlightAtTime(
    FlightID_P int,
    Time_P     timestamp
) RETURNS TABLE (
    CurrentLocation geography,
    Route           geography
) AS $$
DECLARE
    DepartureTime     timestamp;
    ArrivalTime       timestamp;
    DepartureLocation geography;
    ArrivalLocation   geography;
    Fraction          numeric;
    CurrentPosition_V geography;
    Route_V           geography;
BEGIN
    SELECT flight.departure, flight.arrival, ad.location, aa.location
    INTO DepartureTime, ArrivalTime, DepartureLocation, ArrivalLocation
    FROM flight
    JOIN gate     AS ga ON flight.actualgatearrivalid   = ga.id
    JOIN gate     AS gd ON flight.actualgatedepartureid = gd.id
    JOIN terminal AS ta ON ta.id = ga.terminalid
    JOIN terminal AS td ON td.id = gd.terminalid
    JOIN airport  AS aa ON aa.id = ta.airportid
    JOIN airport  AS ad ON ad.id = td.airportid
    WHERE flight.id = FlightID_P;

    IF Time_P <= DepartureTime THEN
        CurrentPosition_V := DepartureLocation;
    ELSIF Time_P >= ArrivalTime THEN
        CurrentPosition_V := ArrivalLocation;
    ELSE
        Fraction := EXTRACT(epoch FROM (Time_P - DepartureTime))
                  / EXTRACT(epoch FROM (ArrivalTime - DepartureTime));

        CurrentPosition_V := ST_LineInterpolatePoint(
            ST_MakeLine(DepartureLocation::geometry, ArrivalLocation::geometry),
            Fraction
        )::geography;
    END IF;

    Route_V := ST_MakeLine(
        DepartureLocation::geometry,
        ArrivalLocation::geometry
    )::geography;

    RETURN QUERY SELECT CurrentPosition_V, Route_V;
END;
$$ LANGUAGE plpgsql;

Како функционира:

1. Се извлекуваат деталите за летот — времето на полетување и слетување, и локациите на двата аеродрома преку познатиот ланец на JOIN-ови.
2. Се определува моменталната локација според временскиот параметар:
   • Доколку времето е пред полетувањето, авионот сè уште е на полетниот аеродром.
   • Доколку времето е по слетувањето, авионот веќе пристигнал на слетниот аеродром.
   • Во спротивно, се пресметува Fraction — фракција помеѓу 0 и 1 која означува „колку процент од летот е изминат".
3. Се користи ST_LineInterpolatePoint — функција која враќа точка на одредена фракција должина по една линија. Со fraction = 0.5, се добива средината на патот; со fraction = 0.25, една четвртина од него.
4. Се враќа и моменталната локација и целата рута — корисно за визуализација (точка на мапа за авионот, плус линија која ја прикажува неговата траектoрија).

Битна забелешка за реалност:

Оваа функција користи линеарна интерполација по great-circle линија — претпоставува дека авионот лета со константна брзина по најкраткиот пат на сфера. Во вистинскиот свет:

• Авионите не летаат со константна брзина (имаат фази на качување, крстарење и спуштање).
• Реалните рути често отстапуваат од идеалната great-circle линија поради воздушни кориди, временски услови, забранети воздушни простори.
• Реалните системи за следење на летови (Flight Tracking) користат посебна табела за логирање на локации — на пример, ADS-B транспондерите на авионите емитуваат локација секои неколку секунди, а сите тие точки се чуваат во база.

Бидејќи во овој проект немаме вистински авиони, импровизираме со пресметана локација. Во документацијата експлицитно споменуваме дека ова е демонстративен пристап, не продукциски модел.

Случаи на употреба:

• Air Traffic Control визуелизација — приказ каде се сите авиони во моментот.
• Информации за патници — „вашиот лет моментално е над Атлантскиот океан, на 70% од патот".
• Анализа на оперативни инциденти — со повикување на FlightAtTime(flight_id, incident_time) се добива каде се наоѓал авионот кога настанал инцидентот.

Демонстрација:

---

11. Визуелизација со QGIS

Една од најголемите предности на PostGIS е природната интеграција со QGIS — индустриски стандарден отворен GIS алат. QGIS може директно да се поврзе со нашата PostGIS база и да ги визуелизира просторните слоеви во реално време.

Процес на конекција:

1. Во QGIS се додава PostGIS конекција (Browser → !PostgreSQL → New Connection) со параметрите на базата.
2. Сите табели и погледи со просторни колони (Airport, FlightRoutes итн.) автоматски се препознаваат како слоеви.
3. Со drag-and-drop се додаваат на мапата.
4. Како подлога се користи OpenStreetMap (преку XYZ Tiles), кој дава реален географски контекст.

Што е визуелизирано:

• Слој на аеродроми — секој аеродром се прикажува како точка, со label-от поставен на IATA код.
• Слој на рути — секоја линија претставува лет помеѓу два аеродроми. При зголемување на zoom, се гледа конкретниот географски пат.
• Демонстрација на функциите — FindNearestAirport и FlightAtTime се повикуваат преку DB Manager → SQL Window, и нивните резултати се вчитуваат како нови слоеви.

QGIS проектот е зачуван како SBAirlines.qgz и претставува дел од испораката на оваа фаза.

---

12. Краток преглед на новите компоненти

Компонента	Тип	Намена
airport.latitude, airport.longitude	Нови колони	Сурови географски координати
airport.location	Нова колона	PostGIS геометриска точка (geography Point, 4326)
idx_airport_location	Просторен индекс	GIST индекс за брзи просторни прашалници
FlightRoutes	Нов поглед	Сите летови како LineString геометрии
ScheduledFlight.distance	Ажурирана колона	Реални great-circle растојанија наместо синтетички
CalculateDistance() + SetDistance	Нов тригер	Автоматска пресметка на дистанца при INSERT/UPDATE
FindNearestAirport(lon, lat, km)	Нова функција	Враќа аеродроми во даден радиус, сортирани по близина
FlightAtTime(id, time)	Нова функција	Пресметува локација на лет во даден момент

---

13. Заклучок

Интеграцијата на PostGIS во проектот SBAirlines не претставува површно додавање на функционалност, туку природна еволуција на моделот кон вистинскиот свет на авиокомпанијата. Со неа:

• Аеродромите имаат реални координати, а не само имиња и кодови.
• Растојанијата на летовите се вистински, што влијае врз постоечките компоненти (миљни поени, оптимизација на флота, оперативни извештаи).
• Се отвораат нови случаи на употреба кои се невозможни во чист релациски модел (наоѓање најблиски аеродроми, визуализација на мрежа, следење на локација).
• Се демонстрира просторно индексирање — нов вид индекс кој не беше покриен во основните фази на проектот.
• Базата на податоци станува извор на вистина за визуелни алатки како QGIS, кои директно ги читаат просторните слоеви.

PostGIS ја претвора нашата база од „евиденција за авиокомпанија" во „геопросторна оперативна платформа" — токму она што го користат вистинските авиокомпании во светот.