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

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

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

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