== Advanced Concepts

Во оваа фаза од проектот, стандардниот релационен модел е надограден со просторни функционалности преку екстензијата PostGIS. Дополнително, за следење на возилата е имплементирана Lambda архитектура (Hot/Cold складирање) со 4D просторно-временски траектории (LineStringZM).

** Иницијализација на PostGIS и конверзија во полигони (Geofencing) **

Првиот чекор вклучува овозможување на PostGIS екстензијата и мигрирање на постоечките координати во geometry објекти. Дополнителна промена е отфрлањето на обичните радиуси за зоните на компаниите. Наместо тоа, користејќи ја функцијата ST_Buffer, кружните области се конвертирани во прецизни полигони со што се овозможува вистинско дигитално надградување. Креирани се и GiST (Generalized Search Tree) индекси кои се потребни за брзо пребарување на просторни податоци.

{{{
create extension if not exists postgis schema public;

alter table driver add column location geometry(Point, 4326);

update driver
set location = st_setsrid(st_makepoint(longitude, latitude), 4326);

alter table request add column start_location geometry(Point, 4326);

alter table request add column end_location geometry(Point, 4326);

update request
set start_location = st_setsrid(st_makepoint(start_longitude, start_latitude), 4326);

update request
set end_location = st_setsrid(st_makepoint(end_longitude, end_latitude), 4326);

alter table waypoints add column location geometry(Point, 4326);

update waypoints
set location = st_setsrid(st_makepoint(longitude, latitude), 4326);

alter table location add column location geometry(Point, 4326);

update location
set location = st_setsrid(st_makepoint(longitude, latitude), 4326);

alter table report add column location geometry(Point, 4326);

update report
set location = st_setsrid(st_makepoint(longitude, latitude), 4326);

alter table area add column coverage_polygon geometry(Polygon, 4326);

update area
set coverage_polygon = ST_Buffer(
    ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)::geography,
    radius
)::geometry;

create index idx_area_coverage_polygon on area using gist (coverage_polygon);
}}}

** Просторно филтрирање на барања (Spatial Join) **
За ефикасно поврзување на патниците со релевантните такси компании, креиран е нов поглед кој користи просторно спојување (Spatial Join). Преку функцијата ST_Contains, погледот проверува дали почетната локација на патникот (start_location) се наоѓа во рамките на полигонот на покриеност на компанијата (coverage_polygon). Овој пристап е многу поефикасен од пресметување на растојанија. Ова е оптимизацијата која ги подобрува перформансите на погледот vw_unassigned_requests спомнат во делот за QueryOptimization каде почетно беше решено со пагинација на барањата, а во случајот се земаат помал број на барања поради тоа што ќе се земат само барања кои се наоѓаат во одреден радиус.

{{{
create or replace view vw_company_available_requests as
select distinct
       r.id as request_id,
       r.customer_user_id,
       cus.username as customer_username,
       r.timestamp,
       r.number_of_adult_passengers,
       r.number_of_children,
       r.status,
       r.female_driver,
       r.luggage,
       r.luggage_count,
       r.baby_seat_count,
       ca.company_id
from request r
         join AppUser cus on r.customer_user_id = cus.id
         join area a on ST_Contains(a.coverage_polygon, r.start_location)
         join company_area ca on a.id = ca.area_id
where r.status = 'pending';
}}}

**Динамична пресметка на цена преку просторна дистанца**

Креирана е нова функција calculate_price која ја пресметува цената на возењето. Таа користи ST_Distance со кастирање во geography за да ја добие точната воздушна дистанца во километри помеѓу почетната и крајната дестинација, по што го множи растојанието со соодветната тарифа на компанијата или фриленсерот. Тарифата може да биде цена за секоја минута возење или цена за секој изминат километар.

{{{
CREATE OR REPLACE FUNCTION calculate_price(request_id int4, id_company int4, id_area int4, freelance_driver_id int4)
RETURNS numeric(19,2)
LANGUAGE plpgsql
AS
$$
DECLARE
    distance_km numeric;
    price_per_km numeric;
    price_per_min numeric;
BEGIN
    SELECT
        ST_Distance(
            start_location::geography,
            end_location::geography
        ) / 1000.0
    INTO distance_km
    FROM request
    WHERE id = request_id;

    IF distance_km IS NULL THEN
        RAISE EXCEPTION 'Request % not found', request_id;
    END IF;

    SELECT value
    INTO price_per_km
    FROM pricinginfo p
    JOIN company_area c on p.id=c.pricing_info_id
    WHERE c.company_id=id_company and c.area_id=id_area and unit='kilometer'
    LIMIT 1;

    SELECT value
    INTO price_per_km
    FROM pricinginfo p
    JOIN freelancedriver d on p.id=d.pricing_info_id
    WHERE d.driver_user_id=freelance_driver_id and unit='kilometer'
    LIMIT 1;

    SELECT value
    INTO price_per_min
    FROM pricinginfo p
    JOIN company_area c on p.id=c.pricing_info_id
    WHERE c.company_id=id_company and c.area_id=id_area and unit='minute'
    LIMIT 1;

    SELECT value
    INTO price_per_min
    FROM pricinginfo p
    JOIN freelancedriver d on p.id=d.pricing_info_id
    WHERE d.driver_user_id=freelance_driver_id and unit='minute'
    LIMIT 1;

    IF price_per_km IS NULL and price_per_min IS NULL
    THEN
        RAISE EXCEPTION 'Pricing info not found';
    end if;

    IF price_per_km IS NOT NULL
    THEN
        RETURN ROUND((distance_km * price_per_km)::numeric, 2);
    ELSE
        RETURN ROUND((distance_km/40 * price_per_min * 60)::numeric, 2);
    end if;
END;
$$;
}}}