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