Проблема кластеризации PostGIS

Вопрос или проблема

Я пытаюсь написать функцию, которая будет использовать 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, чтобы выявить возможные ошибки при выполнении кода.

Оцените материал
Добавить комментарий

Капча загружается...