Вопрос или проблема
Я пытаюсь написать функцию, которая будет использовать PostGIS для группировки геометрических точек на основе ограничивающего прямоугольника, передаваемого в функцию.
Я создал следующую таблицу:
CREATE TABLE items (
id SERIAL PRIMARY KEY,
location GEOMETRY(Point, 4326) -- SRID 4326 для географических координат
);
И наполнил ее случайными данными в пределах указанного ограничивающего прямоугольника, используя следующий код:
DO
$$
DECLARE
i INT := 0;
min_lat FLOAT := 54.542;
max_lat FLOAT := 54.559;
min_lon FLOAT := -5.761;
max_lon FLOAT := -5.729;
rand_lat FLOAT;
rand_lon FLOAT;
BEGIN
WHILE i < 1000 LOOP
-- Генерация случайной широты и долготы в пределах ограничивающего прямоугольника
rand_lat := min_lat + (max_lat - min_lat) * random();
rand_lon := min_lon + (max_lon - min_lon) * random();
-- Вставка новой строки с сгенерированным местоположением
INSERT INTO items (location)
VALUES (ST_SetSRID(ST_MakePoint(rand_lon, rand_lat), 4326));
i := i + 1;
END LOOP;
END
$$;
Функция для выполнения кластеризации выглядит следующим образом:
CREATE OR REPLACE FUNCTION get_items_within_bbox(
min_x FLOAT,
min_y FLOAT,
max_x FLOAT,
max_y FLOAT,
cluster_distance FLOAT
)
RETURNS TABLE (
item_id INT,
cluster_id BIGINT,
cluster_geom GEOMETRY,
cluster_count BIGINT
) AS $$
DECLARE
bbox GEOMETRY;
BEGIN
-- Создание геометрии ограничивающего прямоугольника
bbox := ST_MakeEnvelope(min_x, min_y, max_x, max_y, 4326);
RETURN QUERY
WITH items_in_bbox AS (
SELECT
i.id AS item_id,
i.location AS item_location
FROM items i
WHERE ST_Intersects(i.location, bbox)
),
clusters AS (
SELECT
unnest(ST_ClusterWithin(item_location, cluster_distance)) AS cluster_geom
FROM items_in_bbox
),
cluster_points AS (
SELECT
i.item_id AS point_id,
ROW_NUMBER() OVER () AS point_cluster_id,
c.cluster_geom AS cluster_geom
FROM items_in_bbox i
JOIN clusters c ON ST_Intersects(i.item_location, c.cluster_geom)
),
cluster_counts AS (
SELECT
cp.point_cluster_id AS cluster_id,
COUNT(*) AS cluster_count
FROM cluster_points cp
GROUP BY cp.point_cluster_id
)
SELECT
MIN(cp.point_id) AS item_id,
cp.point_cluster_id AS cluster_id,
ST_Collect(cp.cluster_geom) AS cluster_geom,
cc.cluster_count AS cluster_count
FROM cluster_points cp
JOIN cluster_counts cc ON cp.point_cluster_id = cc.cluster_id
GROUP BY cp.point_cluster_id, cc.cluster_count;
END;
$$ LANGUAGE plpgsql;
Затем я пытаюсь вызвать функцию, используя:
SELECT * FROM get_items_within_bbox(
-5.761, -- min_x (западная долгота)
54.542, -- min_y (южная широта)
-5.729, -- max_x (восточная долгота)
54.559, -- max_y (северная широта)
1609.34 -- расстояние для кластеризации в метрах
);
Независимо от того, какое значение я указываю для расстояния кластеризации, я всегда получаю 1000 строк данных и количество кластеров для каждого элемента всегда равно 1.
Ответ или решение
Проблема, с которой вы столкнулись, связана с возвращаемыми результатами функции get_items_within_bbox
. Давайте подробно проанализируем ваш код и предложим возможные решения.
1. Результаты функции и возвращаемое количество строк
Вы получаете 1000 строк с cluster_count
равным 1, что указывает на то, что каждый элемент не попадает в кластер и обрабатывается отдельно. Это может происходить по нескольким причинам:
- Параметры кластеризации: Убедитесь, что значения
min_x
,min_y
,max_x
иmax_y
задают корректную область, и что ваши точки действительно попадают в заданную рамку, а также находятся достаточно близко друг к другу, чтобы формировать кластеры в пределах указанного расстоянияcluster_distance
. Проверьте, действительно ли все точки случайно генерируются в пределах этой области.
2. Работа с ST_ClusterWithin
Функция ST_ClusterWithin
принимает в качестве входного параметра набор геометрий и группирует их на основе расстояния, указанного в cluster_distance
. Проверьте, правильно ли вы передаете данные в эту функцию:
clusters AS (
SELECT
unnest(ST_ClusterWithin(item_location, cluster_distance)) AS cluster_geom
FROM items_in_bbox
)
Здесь стоит удостовериться, что переменная item_location
, которую вы обрабатываете, действительно содержит все нужные точки, и они правильно собираются в кластеры. Попробуйте сделать запрос по отдельно выбранным точкам, чтобы проверить работу ST_ClusterWithin
.
3. Возможные исправления
-
Проверьте данные: Убедитесь, что данные в таблице
items
действительно распределены по указанным координатам и между ними есть достаточно близкие точки для кластеризации с заданным радиусом. -
Отладка запросов: Попробуйте временно убрать часть кода с кластеризацией и просто выполните запрос на получение всех точек, попадающих в заданный этап.
SELECT
i.id AS item_id,
i.location
FROM items i
WHERE ST_Intersects(i.location, bbox);
Проверьте, возвращает ли это ожидаемое количество точек, и корректно ли они располагаются.
- Расчет кластеров: Вместо того, чтобы использовать
ST_ClusterWithin
через обертку, рассмотрите возможность расчета кластеров отдельно в другом Common Table Expression (CTE).
Пример исправленного CTE:
clusters AS (
SELECT
unnest(ST_ClusterWithin(item_location, cluster_distance)) AS cluster_geom
FROM items_in_bbox
)
Убедитесь, что item_location
возвращает нужные точки. Если точки немассово разбросаны по области, например, если они находятся довольно далеко друг от друга;
- Проверка заполненности: Если в INSERT в
items
кто-то не смог попасть в заданные границы, обязательно отладьте это, чтобы увидеть, как данные созданы.
Заключение
Если после этих шагов данные все еще возвращаются не так, как ожидалось, стоит проверить документацию на использование функций, касающихся работы с PostGIS и географией. Возможно, имеет смысл использовать дополнительные инструменты для отладки запросов или визуализации пространственных данных, чтобы получить более полное представление о вашем наборе данных и его поведении.
Также рекомендуем просмотреть логи выполнения функций и запросов в PostgreSQL, чтобы выявить возможные ошибки при выполнении кода.