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;
$$;