= Напредни теми =

Во рамки на проектот се имплементирани две напредни теми:

1. Геопросторно пребарување на најблиски продавници
2. Партиционирање на историјата на нарачки според година

= 1. Геопросторно пребарување =

== 1.1 Цел ==

Целта на оваа функционалност е за дадена адреса на корисник да се пронајдат продавниците што се наоѓаат во зададен радиус. Продавниците се подредуваат според растојанието од корисникот.

Бидејќи PostGIS екстензијата не е достапна на серверот, решението е имплементирано со вградените можности на PostgreSQL:

* географска ширина и должина;
* вградениот тип `point`;
* GiST просторен индекс;
* ограничувачки правоаголник, односно bounding box;
* Haversine формула за пресметување растојание.

== 1.2 Географски координати ==

Во табелата `public.address` се додадени колоните `latitude` и `longitude`.

{{{#!sql
ALTER TABLE public.address
ADD COLUMN latitude DOUBLE PRECISION
    CHECK (latitude BETWEEN -90 AND 90),
ADD COLUMN longitude DOUBLE PRECISION
    CHECK (longitude BETWEEN -180 AND 180);
}}}

Ограничувањата `CHECK` спречуваат внесување невалидни координати.

Бидејќи адресите во податочното множество се генерирани и не секогаш претставуваат реални адреси, за демонстрацијата се користат приближни координати на ниво на град. Изворот на координатите се означува како `CITY_APPROXIMATION`.

== 1.3 Поглед со локации на продавници ==

Погледот `advanced_gis.store_locations` ги поврзува продавниците со нивните адреси и координати.

{{{#!sql
CREATE OR REPLACE VIEW advanced_gis.store_locations AS
SELECT
    si.store_instance_id,
    s.store_id,
    s.store_name,
    si.address_id,
    a.street,
    a.street_number,
    a.latitude,
    a.longitude,
    point(a.longitude, a.latitude) AS map_point,
    'CITY_APPROXIMATION'::VARCHAR(30) AS coordinate_source
FROM public.storeinstance si
JOIN public.store s
    ON s.store_id = si.store_id
JOIN public.address a
    ON a.address_id = si.address_id
WHERE a.latitude IS NOT NULL
  AND a.longitude IS NOT NULL;
}}}

Колоната `map_point` ја претставува локацијата како PostgreSQL точка во формат:

{{{
(longitude, latitude)
}}}

== 1.4 Пресметување растојание со Haversine формула ==

Функцијата `advanced_gis.distance_meters` го пресметува воздушното растојание меѓу две географски точки.

Haversine формулата го зема предвид сферичниот облик на Земјата. Како радиус на Земјата се користи вредноста `6.371.000` метри.

Функцијата прима:

* географска ширина на првата точка;
* географска должина на првата точка;
* географска ширина на втората точка;
* географска должина на втората точка.

Како резултат враќа растојание во метри.

== 1.5 Пронаоѓање најблиски продавници ==

Функцијата `advanced_gis.find_nearest_stores` прима идентификатор на адреса на корисник и максимален радиус во метри.

{{{#!sql
SELECT *
FROM advanced_gis.find_nearest_stores(5745513, 5000);
}}}

Функцијата работи во две фази:

1. Со bounding box брзо ги избира можните продавници во околината.
2. За избраните кандидати го пресметува точното растојание со функцијата `distance_meters`.

На крај се задржуваат само продавниците чие растојание е помало или еднакво на зададениот радиус, а резултатите се подредуваат од најблиска кон најдалечна продавница.

== 1.6 GiST просторен индекс ==

За побрзо пребарување според координати е креиран парцијален GiST индекс.

{{{#!sql
CREATE INDEX IF NOT EXISTS address_coordinates_gist_idx
ON public.address
USING GIST (
    (point(longitude, latitude))
)
WHERE latitude IS NOT NULL
  AND longitude IS NOT NULL;
}}}

Индексот ги содржи само адресите што имаат координати. Тој се користи за побрзо просторно филтрирање на кандидатите пред пресметувањето на точното растојание.

== 1.7 Ограничување на решението ==

Имплементацијата не користи PostGIS. Таа претставува геопросторно решение изработено со основните типови и индекси на PostgreSQL.

PostGIS не беше достапен на серверот, а корисникот на базата нема администраторски права за негово инсталирање. Поради тоа се користат `point`, GiST индекс и Haversine формула.

= 2. Партиционирање на нарачките =

== 2.1 Цел ==

Табелата `public."Order"` содржи `10.000.013` нарачки со датуми од `2021-01-01` до `2026-05-10`.

Целта на партиционирањето е големата логичка табела да се подели на помали физички табели според годината на нарачката.

Оригиналната табела `public."Order"` не се менува. За демонстрацијата е креирана посебна шема `advanced_partitioning`.

== 2.2 Родителска партиционирана табела ==

{{{#!sql
CREATE SCHEMA IF NOT EXISTS advanced_partitioning;

CREATE TABLE advanced_partitioning.order_history (
    LIKE public."Order"
)
PARTITION BY RANGE (order_date);
}}}

`PARTITION BY RANGE (order_date)` означува дека редовите се распределуваат во партиции според опсегот на `order_date`.

== 2.3 Годишни партиции ==

За секоја година е креирана посебна партиција.

{{{#!sql
CREATE TABLE advanced_partitioning.order_history_2021
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');

CREATE TABLE advanced_partitioning.order_history_2022
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE advanced_partitioning.order_history_2023
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE advanced_partitioning.order_history_2024
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE advanced_partitioning.order_history_2025
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

CREATE TABLE advanced_partitioning.order_history_2026
PARTITION OF advanced_partitioning.order_history
FOR VALUES FROM ('2026-01-01') TO ('2027-01-01');
}}}

Долната граница е вклучена, додека горната граница не е вклучена. На пример, партицијата за 2025 година ги чува датумите:

{{{
2025-01-01 <= order_date < 2026-01-01
}}}

Креирана е и `DEFAULT` партиција за датуми што не припаѓаат во дефинираните годишни опсези.

{{{#!sql
CREATE TABLE advanced_partitioning.order_history_default
PARTITION OF advanced_partitioning.order_history
DEFAULT;
}}}

== 2.4 Внесување примерок од реалните податоци ==

За тестирање е земен приближно 1% примерок од оригиналната табела.

{{{#!sql
INSERT INTO advanced_partitioning.order_history
SELECT *
FROM public."Order"
TABLESAMPLE SYSTEM (1)
REPEATABLE (2026);
}}}

Вметнати се вкупно `102.606` реални нарачки. PostgreSQL автоматски го насочува секој ред во соодветната партиција според `order_date`.

Добиена е следната распределба:

|| Партиција || Број на нарачки ||
|| 2021 || 19.122 ||
|| 2022 || 19.251 ||
|| 2023 || 19.015 ||
|| 2024 || 19.330 ||
|| 2025 || 19.273 ||
|| 2026 || 6.615 ||

== 2.5 Индекс на датумот ==

На родителската табела е дефиниран индекс врз `order_date`.

{{{#!sql
CREATE INDEX IF NOT EXISTS order_history_order_date_idx
ON advanced_partitioning.order_history (order_date);

ANALYZE advanced_partitioning.order_history;
}}}

PostgreSQL автоматски креира соодветни индекси на поединечните партиции.

== 2.6 Partition pruning ==

Ефикасноста на партиционирањето е проверена со следниот план за извршување:

{{{#!sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT COUNT(*)
FROM advanced_partitioning.order_history
WHERE order_date >= DATE '2025-01-01'
  AND order_date < DATE '2026-01-01';
}}}

Во планот за извршување се добива:

{{{
Index Only Scan using order_history_2025_order_date_idx
on order_history_2025
Heap Fetches: 0
Execution Time: 3.486 ms
}}}

Иако барањето се извршува над родителската табела `order_history`, PostgreSQL пристапува само до `order_history_2025`.

Оваа оптимизација се нарекува `partition pruning`. Од условот за датум PostgreSQL определува дека потребните податоци можат да се наоѓаат само во партицијата за 2025 година и ги исклучува останатите партиции од пребарувањето.

Потоа се применува `Index Only Scan`, со што податоците се читаат директно од индексот, без дополнително читање од табелата.

= Заклучок =

Со геопросторното пребарување се овозможува пронаоѓање продавници во зададен радиус преку координати, просторен индекс и Haversine формула.

Со партиционирањето на нарачките по година се намалува бројот на податоци што треба да се пребаруваат. Комбинацијата од partition pruning и индексирање овозможува поефикасно извршување на временски ограничени барања над голема историја на нарачки.