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

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

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

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

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

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

{{{
#!sql
create extension postgis;
}}}


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

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

{{{
#!sql
alter table airport
add column longitude numeric;

alter table airport
add column latitude numeric;
}}}

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

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

{{{
#!sql
alter table airport
add column location geography(Point, 4326);
}}}

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

{{{
#!sql
update airport
set location = ST_MakePoint(longitude, latitude)::geography;
}}}

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

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

[[Image(World-Airports.png, 800px, align=center)]]

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

[[Image(Balkan.png, 800px, align=center)]]

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

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

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

{{{
#!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 или промена на некој од слотовите за полетување и слетување не се врши повторна пресметка. За таа цел имплементиравме тригер кои при изведување на некои од овие активности врши повторна пресметка на растојанието. Вака се озовможува конзистентност на податоците. 

[attachment:CalculateDistance.sql]

== Поглед Flight Routes == 

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

[attachment:FlightRoutes.sql] 

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

{{{
#!sql
select * from flightroutes
where origin = 'SKP';
}}}

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

[[Image(RoutesFromSkopje.png, 800px, align=center)]]

{{{
#!sql
select * from flightroutes
where destination = 'EWR';
}}}

Летови до Newark

[[Image(RoutesToNewark.png, 800px, align=center)]]

== Функција Find Nearest Airport == 

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

[attachment:FindNearestAirport.sql]

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

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

{{{
#!sql
select * from FindNearestAirport(21.62, 41.96, 200);
}}}

[[Image(NearestAirportsSkopje.png, 800px, align=center)]]

== Функцијa Flight At Time ==

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

[attachment:FlightAtTime.sql]

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

{{{
#!sql
select * from FlightAtTime(847585, '2025-12-07 10:00:00.000000'::timestamp);
}}}

[[Image(FlightAtDemo.png, 800px, align=center)]]

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

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

{{{
#!sql
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
);
}}}