Работа с географическими координатами на примере расширения PostGIS в PostgeSQL

CREATE TABLE IF NOT EXISTS edu.objects (
    id SERIAL PRIMARY KEY,
    name TEXT,
    type TEXT, -- дом, аптека, магазин и т.д.
    geom GEOMETRY(Point, 4326), -- координаты (широта/долгота)
    residents int – кол-во жилых квартир
);

CREATE TABLE IF NOT EXISTS edu.area (
    id SERIAL PRIMARY KEY,
    name TEXT,
    geom geometry(Polygon, 4326)
);

WITH params AS (
    SELECT 300::double precision AS radius -- радиус охвата, м
)
SELECT
    b.id AS candidate_building_id,
    b.name AS candidate_building,
    b.geom,
    SUM(nb.residents) AS total_residents,-- кол-во жилых квартир
    COUNT(nb.id) AS buildings_covered    -- кол-во жилых домов
FROM edu.objects b
CROSS JOIN params p
JOIN edu.objects nb
    ON ST_DWithin(b.geom::geography, nb.geom::geography, p.radius) and nb.type = 'жилой дом'
WHERE b.type = 'жилой дом' and
NOT EXISTS (
    SELECT 1
    FROM edu.objects o
    WHERE o.type = 'пвз'
      AND ST_DWithin(o.geom::geography, b.geom::geography, p.radius) -- фильтр: слишком близко к уже существующему ПВЗ
)
GROUP BY b.id, b.name, b.geom
ORDER BY total_residents DESC
LIMIT 5;

WITH params AS (
  SELECT 300::double precision AS radius  -- радиус охвата, м
)
SELECT
  b.id                     AS candidate_building_id,
  b.name                   AS candidate_building,
  b.geom,
  COALESCE(nb_agg.total_residents, 0) AS total_residents_full,
 (COALESCE(nb_agg.total_residents, 0)::double precision) /
    (COALESCE(pvz_agg.cnt_pvz, 0) + 1) AS total_residents, -- score: жителей вокруг / (кол-во ПВЗ в радиусе + 1)
  COALESCE(nb_agg.buildings_covered, 0) AS buildings_covered, -- число зданий в заданном радиусе
  COALESCE(pvz_agg.cnt_pvz, 0) AS cnt_pvz – кол-во ПВЗ в радиусе охвата
FROM edu.objects b
CROSS JOIN params p
-- агрегат по соседним жилым домам (без дублирования)
LEFT JOIN LATERAL (
  SELECT
    SUM(nb.residents) AS total_residents,
    COUNT(*)          AS buildings_covered
  FROM edu.objects nb
  WHERE nb.type = 'жилой дом'
    AND nb.id <> b.id  -- исключаем сам дом
    AND ST_DWithin(b.geom::geography, nb.geom::geography, p.radius)
) nb_agg ON true
-- агрегат по ПВЗ в радиусе
LEFT JOIN LATERAL (
  SELECT COUNT(*) AS cnt_pvz
  FROM edu.objects pvz
  WHERE pvz.type = 'пвз'
    AND ST_DWithin(b.geom::geography, pvz.geom::geography, p.radius)
) pvz_agg ON true
WHERE b.type = 'жилой дом'
ORDER BY total_residents DESC
LIMIT 5;

WITH params AS (
    SELECT 300::double precision AS radius  -- радиус охвата, м
),
extent AS (
    -- рамка по всем жилым домам
    SELECT ST_Envelope(ST_Collect(geom)) AS geom
    FROM edu.objects 
    WHERE type = 'жилой дом'
),
grid AS (
    -- гексагональная сетка
    SELECT
        row_number() OVER () AS id,
        ST_Centroid(cell) AS geom
    FROM extent e,
         LATERAL ST_HexagonGrid(
             0.00025, -- шаг сетки в градусах (~25 м)
             e.geom
         ) g(cell)
),
scores AS (
    SELECT
        g.id,
        g.geom,
        round((COALESCE(nb_agg.total_residents, 0)::double precision) /
              (COALESCE(pvz_agg.cnt_pvz, 0) + 1))::int AS total_residents,
        COALESCE(nb_agg.buildings_covered, 0) AS buildings_covered,
        COALESCE(nb_agg.total_residents, 0)   AS residents,
        COALESCE(pvz_agg.cnt_pvz, 0)          AS cnt_pvz
    FROM grid g
    CROSS JOIN params p
    -- агрегат по соседним жилым домам
    LEFT JOIN LATERAL (
        SELECT
            SUM(nb.residents) AS total_residents,
            COUNT(*)          AS buildings_covered
        FROM edu.objects nb
        WHERE nb.type = 'жилой дом'
          AND ST_DWithin(g.geom::geography, nb.geom::geography, p.radius)
    ) nb_agg ON TRUE
    -- агрегат по ПВЗ в радиусе
    LEFT JOIN LATERAL (
        SELECT COUNT(*) AS cnt_pvz
        FROM edu.objects pvz
        WHERE pvz.type = 'пвз'
          AND ST_DWithin(g.geom::geography, pvz.geom::geography, p.radius)
    ) pvz_agg ON TRUE
    -- фильтруем по полигону ЖК
    WHERE ST_Within(g.geom, (SELECT geom FROM edu.area WHERE name = 'ЖК'))
)
SELECT s.id,
       s.geom,
       s.total_residents,
       s.buildings_covered,
       s.residents,
       s.cnt_pvz
FROM scores s
ORDER BY s.total_residents DESC
LIMIT 5;

SELECT 
    d.name AS dom_name,
    -- считаем разные типы объектов в радиусе 300 м
    COALESCE(a.cnt, 0) AS "Аптеки",
    COALESCE(c.cnt, 0) AS "Кафе",
    COALESCE(m.cnt, 0) AS "Магазины",
    COALESCE(p.cnt, 0) AS "Парки",
    COALESCE(s.cnt, 0) AS "Школы",
    COALESCE(o.cnt, 0) AS "Офисы",
    COALESCE(tc.cnt, 0) AS "ТЦ",
    COALESCE(b.cnt, 0) AS "Больницы",
    COALESCE(pvz.cnt, 0) AS "ПВЗ",
     COALESCE(a.cnt, 0) + COALESCE(c.cnt, 0) +
     COALESCE(m.cnt, 0) + COALESCE(p.cnt, 0) + 
     COALESCE(s.cnt, 0) + COALESCE(o.cnt, 0) + 
     COALESCE(tc.cnt, 0) + COALESCE(b.cnt, 0) + COALESCE(pvz.cnt, 0) as sum_object
FROM edu.objects d
-- ограничиваем только жилыми домами
-- аптеки
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'аптека'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) a ON TRUE


-- кафе
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'кафе'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) c ON TRUE


-- магазины
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'магазин'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) m ON TRUE


-- парки
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'парк'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) p ON TRUE


-- школы
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'школа'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) s ON TRUE


-- офисы
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'офис'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) o ON TRUE


-- торговые центры
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'торговый центр'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) tc ON TRUE


-- больницы
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'больница'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) b ON TRUE


-- ПВЗ
LEFT JOIN LATERAL (
    SELECT COUNT(*) AS cnt
    FROM edu.objects o
    WHERE o.type = 'пвз'
      AND ST_DWithin(d.geom::geography, o.geom::geography, 300)
) pvz ON true
WHERE d.type = 'жилой дом'
order by d.name;