Changes between Version 7 and Version 8 of AdvancedConcepts

Timestamp:

06/15/26 21:33:09 (14 hours ago)

Author:

231093

Comment:

--

AdvancedConcepts

@@ -147 +147 @@
 $$;
 }}}
+
+
+** Наоѓање на најблизок возач (K-Nearest Neighbors) **
+
+При креирањето на понуда од страна на диспечерот, системот автоматски го бара најблискиот слободен возач од таа компанија. За оваа цел се користи PostGIS операторот `<->`, кој го пресметува просторното растојание користејќи го GiST индексот. Ова овозможува пронаоѓање на возачот во `O(1)` односно константно време, без да се скенираат сите возачи.
+
+{{{
+create or replace procedure create_offer(
+    v_request_id int4,
+    v_dispatcher_user_id int4,
+    v_price numeric(19, 2),
+    v_currency_catalog_id int4,
+    v_eta timestamp
+)
+    language plpgsql
+AS
+$$
+declare
+    v_driver_user_id        int4;
+    v_dispatcher_company_id int4;
+    v_start_position        geometry;
+    v_customer_user_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);
+
+    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_driver_user_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_driver_user_id is null then
+        raise exception 'No available drivers found for request %', v_request_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_driver_user_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');
+}}}