Changes between Initial Version and Version 1 of AdvancedTopics

Timestamp:

06/12/26 21:33:17 (9 days ago)

Author:

231138

Comment:

--

AdvancedTopics

@@ +1 @@
+= Напредни бази на податоци =
+= Бонус — PostGIS просторни прашалници =
+== Проект: DriveNet ==
+
+Александар Милошевски 231138
+Исидора Кузмановска 231052
+Андон Михајлов 231016
+
+Во оваа фаза го прошируваме проектот DriveNet со PostGIS — просторно проширување за PostgreSQL кое овозможува складирање и пребарување на географски податоци. Наместо пресметување на растојанија во апликацискиот код, сите просторни операции се извршуваат директно во базата со индексирани геометриски колони.
+
+----
+
+= Зошто PostGIS? =
+
+Во нашата постоечка шема имаме `lat/lng` колони во следните табели:
+
+|| **Табела** || **Употреба** ||
+|| `locations` || Места за качување и слегување ||
+|| `cities` || Центри на градови ||
+|| `location_pings` || Живо следење на возачот ||
+|| `toll_points` || Патарински точки на рутата ||
+|| `user_addresses` || Зачувани адреси на корисници ||
+
+Без PostGIS, пребарување "возења во близина" би барало пресметување на Haversine формулата во апликацискиот код за секој ред. Со PostGIS, истото се прави со еден индексиран SQL прашалник.
+
+----
+
+= Инсталација и активација =
+
+{{{
+-- Активирање на PostGIS проширувањето
+CREATE EXTENSION postgis;
+
+-- Верификација
+SELECT PostGIS_Version();
+-- Резултат: 3.6 USE_GEOS=1 USE_PROJ=1 USE_STATS=1
+}}}
+
+----
+
+= Миграција на постоечката шема =
+
+==== Чекор 1 — Додавање на геометриски колони:
+{{{
+ALTER TABLE locations      ADD COLUMN geom GEOMETRY(Point, 4326);
+ALTER TABLE cities         ADD COLUMN geom GEOMETRY(Point, 4326);
+ALTER TABLE location_pings ADD COLUMN geom GEOMETRY(Point, 4326);
+ALTER TABLE toll_points    ADD COLUMN geom GEOMETRY(Point, 4326);
+ALTER TABLE user_addresses ADD COLUMN geom GEOMETRY(Point, 4326);
+}}}
+
+==== Чекор 2 — Пополнување од постоечките lat/lng:
+{{{
+UPDATE locations      SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
+UPDATE cities         SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
+UPDATE location_pings SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
+UPDATE toll_points    SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
+UPDATE user_addresses SET geom = ST_SetSRID(ST_MakePoint(lng, lat), 4326);
+}}}
+
+==== Чекор 3 — Просторни индекси (GIST):
+{{{
+CREATE INDEX idx_locations_geom      ON locations      USING GIST(geom);
+CREATE INDEX idx_cities_geom         ON cities         USING GIST(geom);
+CREATE INDEX idx_location_pings_geom ON location_pings USING GIST(geom);
+CREATE INDEX idx_toll_points_geom    ON toll_points    USING GIST(geom);
+CREATE INDEX idx_user_addresses_geom ON user_addresses USING GIST(geom);
+}}}
+
+Просторните индекси користат `GIST` (Generalized Search Tree) наместо стандардниот `BTREE` — специјализирани за двородимензионални геометриски пребарувања.
+
+----
+
+= Просторни функции =
+
+=== 1. find_rides_near ===
+
+==== Опис:
+
+Ги наоѓа сите достапни возења чија почетна точка се наоѓа во одреден радиус од локацијата на патникот. Го заменува комплексното пресметување на растојанија во апликацискиот код со еден индексиран просторен прашалник.
+
+==== Сигнатура:
+{{{
+find_rides_near(p_lat DECIMAL, p_lng DECIMAL, p_radius_km INT DEFAULT 10)
+RETURNS TABLE (ride_id, departure_time, driver_name, driver_avg_rating,
+               origin_city, origin_location, destination_city,
+               estimated_fare, seats_available, distance_km)
+}}}
+
+==== Влезни параметри:
+
+|| **Параметар** || **Тип** || **Опис** ||
+|| `p_lat` || DECIMAL || Географска ширина на патникот ||
+|| `p_lng` || DECIMAL || Географска должина на патникот ||
+|| `p_radius_km` || INT || Радиус на пребарување во км (default 10) ||
+
+==== Логика:
+
+ 1. Ја зема локацијата на патникот и ја претвора во PostGIS точка со `ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)`
+ 2. Ги филтрира сите достапни возења преку `v_available_rides` со `ST_DWithin` — ги враќа само возењата чија почетна локација е во рамки на бараниот радиус
+ 3. За секое возење го пресметува точното растојание во км со `ST_Distance` и `::geography` cast за метарска точност
+ 4. Ги сортира резултатите по растојание
+
+==== Имплементација:
+{{{
+CREATE OR REPLACE FUNCTION find_rides_near(
+    p_lat       DECIMAL,
+    p_lng       DECIMAL,
+    p_radius_km INT DEFAULT 10
+)
+RETURNS TABLE (
+    ride_id           INT,
+    departure_time    TIMESTAMP,
+    driver_name       TEXT,
+    driver_avg_rating NUMERIC,
+    origin_city       TEXT,
+    origin_location   TEXT,
+    destination_city  TEXT,
+    estimated_fare    NUMERIC,
+    seats_available   INT,
+    distance_km       NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        var.ride_id,
+        var.departure_time,
+        var.driver_name::TEXT,
+        var.driver_avg_rating,
+        var.origin_city::TEXT,
+        var.origin_location::TEXT,
+        var.destination_city::TEXT,
+        var.estimated_fare,
+        var.seats_available,
+        ROUND(ST_Distance(
+            l.geom::geography,
+            ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography
+        )::numeric / 1000, 2) AS distance_km
+    FROM v_available_rides var
+    JOIN locations l ON l.name = var.origin_location
+    WHERE ST_DWithin(
+        l.geom::geography,
+        ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography,
+        p_radius_km * 1000
+    )
+    ORDER BY distance_km;
+END;
+$$;
+}}}
+
+==== Пример за употреба:
+{{{
+-- Пронајди возења во близина на центарот на Скопје (радиус 10km)
+SELECT * FROM find_rides_near(41.9981, 21.4254, 10);
+
+-- Пронајди возења во близина на одредена локација (радиус 25km)
+SELECT * FROM find_rides_near(41.9981, 21.4254, 25);
+}}}
+
+==== Споредба — без PostGIS vs со PostGIS:
+
+Без PostGIS (Haversine во SQL):
+{{{
+SELECT r.id, ...
+FROM rides r
+JOIN locations l ON ...
+WHERE (
+    6371 * acos(
+        cos(radians(41.9981)) * cos(radians(l.lat)) *
+        cos(radians(l.lng) - radians(21.4254)) +
+        sin(radians(41.9981)) * sin(radians(l.lat))
+    )
+) <= 10;
+-- Нема индекс — целосен скен на табелата
+}}}
+
+Со PostGIS:
+{{{
+WHERE ST_DWithin(
+    l.geom::geography,
+    ST_SetSRID(ST_MakePoint(21.4254, 41.9981), 4326)::geography,
+    10000
+);
+-- Користи GIST индекс — директен просторен пристап
+}}}
+
+----
+
+=== 2. find_nearest_stop ===
+
+==== Опис:
+
+За дадено возење и локација на патник, ги враќа сите постојки на рутата сортирани по растојание. Му помага на патникот да ја избере најблиската постојка за качување.
+
+==== Сигнатура:
+{{{
+find_nearest_stop(p_ride_id INT, p_lat DECIMAL, p_lng DECIMAL)
+RETURNS TABLE (stop_id, stop_order, location_name, distance_meters)
+}}}
+
+==== Влезни параметри:
+
+|| **Параметар** || **Тип** || **Опис** ||
+|| `p_ride_id` || INT || ID на возењето ||
+|| `p_lat` || DECIMAL || Географска ширина на патникот ||
+|| `p_lng` || DECIMAL || Географска должина на патникот ||
+
+==== Логика:
+
+ 1. Го наоѓа `route_id` на возењето
+ 2. За сите постојки на рутата го пресметува растојанието во метри од локацијата на патникот со `ST_Distance`
+ 3. Ги сортира по растојание — најблиската постојка е прва
+
+==== Имплементација:
+{{{
+CREATE OR REPLACE FUNCTION find_nearest_stop(
+    p_ride_id INT,
+    p_lat     DECIMAL,
+    p_lng     DECIMAL
+)
+RETURNS TABLE (
+    stop_id         INT,
+    stop_order      INT,
+    location_name   TEXT,
+    distance_meters NUMERIC
+)
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    RETURN QUERY
+    SELECT
+        rs.id          AS stop_id,
+        rs.stop_order,
+        l.name::TEXT   AS location_name,
+        ROUND(ST_Distance(
+            l.geom::geography,
+            ST_SetSRID(ST_MakePoint(p_lng, p_lat), 4326)::geography
+        )::numeric, 2) AS distance_meters
+    FROM route_stops rs
+    JOIN locations l ON l.id = rs.location_id
+    WHERE rs.route_id = (SELECT route_id FROM rides WHERE id = p_ride_id)
+    ORDER BY distance_meters;
+END;
+$$;
+}}}
+
+==== Пример за употреба:
+{{{
+-- Најди најблиска постојка за возење 1, патник е во центарот на Скопје
+SELECT * FROM find_nearest_stop(1, 41.9981, 21.4254);
+}}}
+
+----
+
+= Дополнителни просторни прашалници =
+
+==== Локации во рамки на одреден радиус:
+{{{
+SELECT
+    l.name,
+    c.name AS city,
+    ROUND(ST_Distance(
+        l.geom::geography,
+        ST_SetSRID(ST_MakePoint(21.4254, 41.9981), 4326)::geography
+    )::numeric / 1000, 2) AS distance_km
+FROM locations l
+JOIN cities c ON c.id = l.city_id
+ORDER BY distance_km
+LIMIT 10;
+}}}
+
+==== Патарински точки долж рута:
+{{{
+SELECT tp.name, tp.price_car
+FROM toll_points tp
+WHERE ST_DWithin(
+    tp.geom::geography,
+    ST_MakeLine(
+        ST_SetSRID(ST_MakePoint(21.43, 41.99), 4326),
+        ST_SetSRID(ST_MakePoint(22.64, 41.04), 4326)
+    )::geography,
+    500
+);
+}}}
+
+==== Најблизок град до одредена точка:
+{{{
+SELECT
+    name,
+    ROUND(ST_Distance(
+        geom::geography,
+        ST_SetSRID(ST_MakePoint(21.4254, 41.9981), 4326)::geography
+    )::numeric / 1000, 2) AS distance_km
+FROM cities
+ORDER BY distance_km
+LIMIT 5;
+}}}
+
+----
+
+= Резиме =
+
+|| **Функција** || **PostGIS функција** || **Опис** ||
+|| Растојание меѓу две точки || `ST_Distance` || Точно растојание во метри ||
+|| Точки во радиус || `ST_DWithin` || Индексирано просторно филтрирање ||
+|| Креирање точка || `ST_MakePoint` || Од lng/lat во геометрија ||
+|| Поставување координатен систем || `ST_SetSRID(..., 4326)` || WGS84 — стандард за GPS ||
+|| Просторен индекс || `USING GIST` || Наместо BTREE за 2D пребарување ||
+|| Geography cast || `::geography` || Метарска точност наместо степени ||
+
+Клучната предност на PostGIS е комбинацијата од `ST_DWithin` + `GIST` индекс — просторното филтрирање се извршува директно во индексот без скенирање на целата табела, слично како `idx_rides_status_dep` го забрза пребарувањето на возења.