Advanced Concepts

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

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

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

За да се користи на Linux (Ubuntu/Debian), екстензијата се инсталира во два брзи чекори:

Инсталација на пакетот на серверот преку терминал: sudo apt update && sudo apt install postgresql-15-postgis-3 (верзијата се прилагодува според инсталираниот PostgreSQL).

Активирање во самата база со извршување на командата: create extension postgis;

Иницијализација на 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;
$$;

Наоѓање на најблизок возач (K-Nearest Neighbors)

При креирањето на понуда од страна на диспечерот, системот автоматски го бара најблискиот слободен возач од таа компанија. За оваа цел се користи PostGIS операторот <->, кој го пресметува просторното растојание користејќи го GiST индексот. Ова овозможува пронаоѓање на возачот во O(1) односно константно време, без да се скенираат сите возачи.

create or replace procedure create_offer(
    v_request_id int4,
    v_dispatcher_user_id int4,
    v_driver_user_id int4,
    v_price numeric(19, 2),
    v_currency_catalog_id int4,
    v_eta timestamp
)
    language plpgsql
AS
$$
declare
    v_dispatcher_company_id int4;
    v_start_position        geometry;
    v_customer_user_id      int4;
    v_computed_driver_id    int4;
begin
    if not exists(select * from request where request.id = v_request_id and request.status = 'pending') then
        raise exception 'Request with id % and pending status does not exist', v_request_id;
    end if;
    if v_price <= 0 then
        raise exception 'Price has to be greater than 0';
    end if;
    if v_eta <= now() then
        raise exception 'ETA cannot be lower than the time of creation';
    end if;

    v_customer_user_id := (select customer_user_id from request where request.id = v_request_id);

    if v_dispatcher_user_id is not null then
        v_dispatcher_company_id := (select eh.company_id
                                    from dispatcher d
                                             join employmenthistory eh on eh.employee_user_id = d.user_id and
                                                                          (eh.end_date is null or eh.end_date > now())
                                    where d.user_id = v_dispatcher_user_id
                                    limit 1);

        if v_dispatcher_company_id is null then
            raise exception 'Dispatcher % is not assigned to a company', v_dispatcher_user_id;
        end if;

        v_start_position := (select start_location from request where id = v_request_id);

       
        v_computed_driver_id := (select d.user_id
                                 from driver d
                                          join driver_vehicle dc on d.user_id = dc.id_driver
                                          join employmenthistory eh
                                               on eh.employee_user_id = d.user_id and (eh.end_date is null or
                                                                                       eh.end_date > now())
                                 where dc.time_to is null
                                   and eh.company_id = v_dispatcher_company_id
                                   and d.location is not null
                                 order by d.location <-> v_start_position
                                 limit 1);

        if v_computed_driver_id is null then
            raise exception 'No available drivers found for request %', v_request_id;
        end if;

    else
        if v_driver_user_id is null then
            raise exception 'A driver_user_id must be specified if dispatcher is null';
        end if;

        if not exists(select 1 from freelancedriver where driver_user_id = v_driver_user_id) then
            raise exception 'Driver % is not a registered freelance driver', v_driver_user_id;
        end if;

        v_computed_driver_id := v_driver_user_id;
    end if;

    insert into offer(status, created_at, request_id, dispatcher_user_id, driver_user_id, price, currency_catalog_id,
                      eta, customer_user_id)
    values ('pending', now(), v_request_id, v_dispatcher_user_id, v_computed_driver_id,
            v_price, v_currency_catalog_id, v_eta, v_customer_user_id);
            
    commit;
end;
$$;

Lambda Архитектура и 4D Траектории на возење

За следење на возилата во реално време, воведена е Lambda архитектура. Табелата location служи како "Hot storage" каде што се запишуваат илјадници GPS точки додека возилото се движи. Кога возењето ќе заврши, се активира тригер кој ги собира сите точки и ги компресира во една 4-димензионална линија (LineStringZM – каде Z е брзината, а M е Unix времето) во табелата ride ("Cold storage"). Откако траекторијата е зачувана, сировите точки се бришат за да се ослободи меморија.

alter table location 
    add column speed numeric(5,2) default 0;

alter table ride 
    add column route_path geometry(LineStringZM, 4326);

create or replace function archive_ride_trajectory()
returns trigger 
language plpgsql
as $$
begin
    if NEW.status = 'completed' and OLD.status != 'completed' then
        NEW.route_path := (
            select ST_MakeLine(
                       ST_MakePoint(
                           l.longitude, 
                           l.latitude, 
                           COALESCE(l.speed, 0),
                           extract(epoch from l.timestamp)
                       )
                       order by l.timestamp
                   )
            from location l
            where l.ride_id = NEW.id
        );

        delete from location where ride_id = NEW.id;
    end if;

    return NEW;
end;
$$;

create trigger trigger_archive_trajectory
    before update on ride
    for each row
    execute function archive_ride_trajectory();

update ride r
set route_path = (
    select ST_MakeLine(
               ST_MakePoint(
                   l.longitude, 
                   l.latitude, 
                   COALESCE(l.speed, 0), 
                   extract(epoch from l.timestamp)
               )
               order by l.timestamp
           )
    from location l
    where l.ride_id = r.id
)
where r.status = 'completed' and r.route_path is null;

delete from location 
where ride_id in (select id from ride where status = 'completed');