Напредни бази на податоци

Бонус — 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 го забрза пребарувањето на возења.