Advanced Concepts

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

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

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

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

Инсталација на пакетот на серверот преку терминал: 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
       r.id as request_id,
       r.customer_user_id,
       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 company_area ca
         join area a on ca.area_id = a.id
         join request r on ST_Intersects(a.coverage_polygon, r.start_location)
                        and r.status = 'pending';
select * from vw_company_available_requests where company_id = 3;

Резултантно време на извршување:

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

Креирана е нова функција 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;
$$;

Останати рефакторирани процедури

create or replace procedure make_request(
    out request_id int4,
    in customer_id int4,
    in start_position geometry,
    in end_position geometry,
    in number_of_adult_passengers int4 default 1,
    in number_of_children int4 default 0,
    in female_driver boolean default false,
    in luggage boolean default false,
    in luggage_count int4 default 0,
    in baby_seat_count int4 default 0
)
    language plpgsql as
$$
begin
    if not exists(select user_id
                  from customer c
                  where c.user_id = customer_id)
    then
        raise exception 'Customer with id % does not exist', customer_id;
    end if;

    if number_of_adult_passengers < 0 then
        raise exception 'Number of adult passengers cannot be negative';
    end if;

    if number_of_children < 0 then
        raise exception 'Number of children cannot be negative';
    end if;

    if number_of_adult_passengers + number_of_children < 0 then
        raise exception 'There must be at least 1 passenger';
    end if;

    if baby_seat_count < 0 then
        raise exception 'Baby seat count cannot be negative';
    end if;

    if luggage = false and luggage_count > 0 then
        raise exception 'Luggage count cannot be greater than zero if the luggage flag is set to false.';
    end if;

    if luggage = true and luggage_count <= 0 then
        raise exception 'Invalid luggage count parameters';
    end if;

    if start_position = end_position then
        raise exception 'Start and destination location cannot be the same';
    end if;

    insert into request(customer_user_id, start_latitude, start_longitude, end_latitude, end_longitude, timestamp,
                        number_of_adult_passengers, number_of_children, status, female_driver, luggage, luggage_count,
                        baby_seat_count, start_location,
                        end_location)
    values (customer_id,
            ST_Y(start_position::geometry),
            ST_X(start_position::geometry),
            ST_Y(end_position::geometry),
            ST_X(end_position::geometry),
            now(),
            make_request.number_of_adult_passengers,
            make_request.number_of_children,
            'pending',
            make_request.female_driver,
            make_request.luggage,
            make_request.luggage_count,
            make_request.baby_seat_count,
            make_request.start_position,
            make_request.end_position)
    returning id into request_id;
    commit;
end;
$$;

create or replace procedure start_ride(
    in v_offer_id int4
)
    language plpgsql as
$$
declare
    v_request_id  int4;
    v_driver_id   int4;
    v_vehicle_vin varchar(17);
begin
    if not exists(select id from offer where id = v_offer_id) then
        raise exception 'Offer with id % does not exist', v_offer_id;
    end if;

    if not exists (select 1
                   from offer
                   where id = v_offer_id
                     and status = 'pending') then
        raise exception 'Offer % is not pending', v_offer_id;
    end if;

    v_request_id := (select o.request_id
                     from offer o
                     where o.id = v_offer_id);

    v_driver_id := (select driver_user_id from offer where id = v_offer_id);

    if v_driver_id is null then
        raise exception 'No active drivers available';
    end if;

    v_vehicle_vin := (select vin_vehicle
                      from driver_vehicle
                      where id_driver = v_driver_id
                        and (time_to is null or time_to > now())
                      limit 1);

    if v_vehicle_vin is null then
        raise exception 'No active vehicle for driver %', v_driver_id;
    end if;

    insert into ride(start_time,
                     end_time,
                     distance_traveled,
                     vehicle_vin,
                     driver_user_id,
                     request_id,
                     status,
                     offer_id)
    values (now(),
            null,
            0,
            v_vehicle_vin,
            v_driver_id,
            v_request_id,
            'in_progress',
            v_offer_id);

    update offer
    set status='accepted'
    where id = v_offer_id;

    update request
    set status='accepted'
    where id = v_request_id;

    commit;
end;
$$;

create or replace procedure add_waypoint(
    in v_request_id int4,
    in v_location geometry
)
    language plpgsql as
$$
declare
    v_prev_seqno int4;
begin
    if not exists(select id from request where id = v_request_id) then
        raise exception 'Request with id % does not exist', v_request_id;
    end if;

    if exists(select id
              from waypoints w
              where w.request_id = v_request_id
                and ST_DWithin(w.location, v_location, 0.000001)) then
        raise exception 'Waypoint already added';
    end if;

    v_prev_seqno := (select count(*)
                     from waypoints w
                     where w.request_id = v_request_id);

    insert into waypoints(latitude, longitude, seqno, request_id, location)
    values (st_y(v_location), st_x(v_location), v_prev_seqno + 1, v_request_id, v_location);
    commit;
end;
$$;

create or replace procedure write_report(
    id_ride int,
    user_id int,
    new_message text,
    new_title text,
    incident_location geometry,
    new_reason text
)
language plpgsql
as $$
    begin
        if not exists (
            select 1
            from Ride r
            join Request req on r.request_id = req.id
            where r.id = id_ride and req.customer_user_id = user_id
        ) then
            raise exception 'Invalid report: Customer % was not part of Ride %', user_id, id_ride;
        end if;

        if exists (
            select 1
            from Report
            where ride_id = id_ride and customer_user_id = user_id
        ) then
            raise exception 'Duplicate report: Customer % has already reported Ride %', user_id, id_ride;
        end if;

        if trim(new_message) = '' or trim(new_title) = '' or trim(new_reason) = '' then
            raise exception 'Validation error: Title, message, and reason cannot be empty strings.';
        end if;

        insert into report(ride_id, customer_user_id, message, title, location, latitude, longitude, reason)
        values (
            id_ride,
            user_id,
            new_message,
            new_title,
            st_setsrid(incident_location, 4326),
            ST_Y(incident_location),
            ST_X(incident_location),
            new_reason
        );

        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');