--1.3 circle_ops — telecom tower coverage
DROP TABLE IF EXISTS cell_towers;
CREATE TABLE cell_towers
(
    tower_id     bigserial PRIMARY KEY,
    operator_nm  text NOT NULL,
    coverage     circle NOT NULL
);

INSERT INTO cell_towers (operator_nm, coverage)
SELECT
    CASE g % 3
        WHEN 0 THEN 'OperatorA'
        WHEN 1 THEN 'OperatorB'
        ELSE 'OperatorC'
    END,
    circle(point(44 + random() * 2, 40 + random() * 2), 0.03 + random() * 0.08)
FROM generate_series(1, 60000) g;

CREATE INDEX ix_cell_towers_gist
    ON cell_towers
 USING gist (coverage);
--Towers covering a customer location
SELECT tower_id, operator_nm
FROM cell_towers
WHERE coverage @> point(44.5152, 40.1811);
--Towers whose coverage overlaps a problematic area
SELECT tower_id, operator_nm
FROM cell_towers
WHERE coverage && circle(point(44.50, 40.18), 0.10);

---------------------------------------
---------------------------------------
--1.4 poly_ops — delivery districts / irregular regions
DROP TABLE IF EXISTS delivery_districts;
CREATE TABLE delivery_districts
(
    district_id   bigserial PRIMARY KEY,
    district_name text NOT NULL,
    region        polygon NOT NULL
);

INSERT INTO delivery_districts (district_name, region)
VALUES
('Center',
 '((44.48,40.16),(44.55,40.16),(44.57,40.20),(44.50,40.24),(44.46,40.20))'),
('North',
 '((44.50,40.24),(44.58,40.24),(44.60,40.30),(44.52,40.31),(44.49,40.27))'),
('South',
 '((44.45,40.09),(44.53,40.10),(44.54,40.15),(44.47,40.16),(44.43,40.12))');

CREATE INDEX ix_delivery_districts_gist
    ON delivery_districts
 USING gist (region);
--Which district contains the point
SELECT district_id, district_name
FROM delivery_districts
WHERE region @> point(44.5123, 40.1772);