Напредна тема: Имплементација на 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.

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

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

Промена во табелата ScheduledFlight

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

За исполнување на таа цел потребно беше да се пресмета вистинското растојание употребувајќи ја новата колона Location во табелата Airport. PostGIS ја нуди функционалноста за пресметка на растојание помеѓу две точки ST_Distance. За промена на колоната Distance го извршивме следниот SQL код:

update airport
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;

Поради тоа што поставен е Check кој проверува дали растојанието е барем 1 километар, потребно беше да се избере поголемата вредност помеѓу 1 и ST_Distance(dep.location, arr.location) за да може успешно да се вметнат сите колони.

Тригер Calculate Distance

Иако кодот погоре ги коригира веќе постоечките податоци за Distance, при додавање на нов ScheduledFlight или промена на некој од слотовите за полетување и слетување не се врши повторна пресметка. За таа цел имплементиравме тригер кои при изведување на некои од овие активности врши повторна пресметка на растојанието. Вака се озовможува конзистентност на податоците.

CalculateDistance.sql​

Поглед Flight Routes

За излистување на сите рути на веќе постоечки летови потребно беше креирање на поглед - Flight Routes. Во овој поглед се прикажуваат податоците за број на лет, време и аеродром на полетување и слетување, рутата како објект Line во PostGIS и растојанието. За преставување на рутата како Line ја употребивме функцијата ST_MakeLine која како аргументи ги прима координатите на аеродромите.

FlightRoutes.sql​

За успешна визуелизација ги извршивме следните прашалници:

select * from flightroutes
where origin = 'SKP';

Летови од Скопје

select * from flightroutes
where destination = 'EWR';

Летови до Newark

Функција Find Nearest Airport

При непосакувана ситуација, некогаш потребно е авионите итно да слетаат на најблискиот аеродром, или пак при пребарување на летови потребно е и пребарување од аеродромите во близина. Со цел имплементација на оваа функционалност, ја воведовме следната функција

FindNearestAirport.sql​

Функцијата прима три аргументи: должина (прва координата), ширина (втора координата) и растојание во километри (максималниот радиус во кој се пребаруваат аеродроми).

За визуелизација пребарувавме според координати во близина на Скопје сo радиус од 200км.

select * from FindNearestAirport(21.62, 41.96, 200);

Функцијa Flight At Time

Во реални системи податоците за тоа каде одреден авион се наоѓа во реално време се добиваат според GPS координатите на авионот и се чуваат во посебни табели. Поради тоа што летовите и авионите се вештачки изгенерирани во нашата база, ова не е реалистично и немаме начин како да пристапиме до GPS координати до авионите во нашата база. Поради тоа, имплементиравме функција FlighAtTime која ја пресметува локацијата на некој авион (за време на конкретен лет) во дадено време. Оваа пресметка се добива преку употреба на времетраењето на летот, изминатото време од полетување и растојанието помеѓу аеродромот на полетување и на слетување. Како аргументи се праќаат ID на некој лет и времето за тоа каде се наоѓа авионот (timestamp). Функцијата враќа моментална локација (Point) и самата рута на летот (Line).

FlightAtTime.sql​

За визуелизација го извршивме прашалникот:

select * from FlightAtTime(847585, '2025-12-07 10:00:00.000000'::timestamp);

Забранети зони за летање (Restricted Airspace)

Во реалниот свет, постојат многу делови од земјата (воздушни зони) во кои е забрането летање на лични или комерцијални летала. Овие простори се најчесто воени зони, забранети територии (цели држави или региони, како што е Северна Кореја) и слично. За подобар увид во тоа каде немаат дозвола да летаат авионите и кои рути на летови поминуваат низ такви зони, во нашата база креиравме две нови табели AirspaceType - типот на зона, како и RestrictedAirspace - забранета зона.

CREATE TABLE AirspaceType
(
    ID   SERIAL,
    Name text UNIQUE,
    PRIMARY KEY (ID)
);

CREATE TABLE RestrictedAirspace
(
    ID          SERIAL,
    Name        text,
    TypeID      int4,
    Boundary    geography(POLYGON, 4326) NOT NULL,
    ActiveFrom  timestamp,
    ActiveTo    timestamp,
    Description text,
    PRIMARY KEY (ID),
    FOREIGN KEY (TypeID) REFERENCES AirspaceType (ID) ON DELETE RESTRICT ON UPDATE CASCADE
);

Во табелата AirspaceType се чува името на типот на зона.

Во табелата RestrictedAirspace се чува името на забранетата зона, типот, границите на зоната (претставени во вид на полигон - geography(POLYGON, 4326)), од кога и до кога е активна рестрикцијата, дополнителен опис.

Податоците за табелите ги земавме од јавно достапен извор на ​FAA кој има податоци за сите забранети зони за летање во САД. Податоците ги внесовме во табелите со следниот код:

insert into AirspaceType (Name)
select distinct TYPE_CODE from airspace_staging
on conflict (Name) do nothing;

Поради тоа што полигоните во податоците се во 3D облик, потребно е да ја искористиме функцијата ST_Force2D за да можеме да ги претвориме во 2D - компатибилни со нашата табела.

insert into RestrictedAirspace (Name, TypeID, Boundary, Description)
select s.NAME, at.ID,
    ST_Force2D(ST_SetSRID(s.wkb_geometry::geometry, 4326))::geography,
    s.REMARKS
from airspace_staging s
join AirspaceType at on at.Name = s.TYPE_CODE;

За да имаме подетален увид во тоа кои летови поминуваат низ кои забранети зони го креиравме погледот FlightAirspaceConflicts. Во него чуваме податоци за ID и број на летот, време и аеродром на полетување и слетување, растојание, ID, име и тип на зона, местото на конфликт (една или повеќе прави - Line, каде што се пресекуваат патот на летот и забранетата зона), должина на конфликтот во километри.

FlightAirspaceConflicts.sql​