Блог

Главная - Вопросы по программированию - агрегат не предоставляет информацию, которую я ожидаю, и группируется по повторяющимся записям

агрегат не предоставляет информацию, которую я ожидаю, и группируется по повторяющимся записям

#sql #postgresql #postgis

Вопрос:

Я создаю материализованное представление, которое формируется из двух таблиц.

 CREATE MATERIALIZED VIEW poly_mv AS (
   SELECT p.id,
          p.name,
          p.code,
          p.descrip,
          p.geom,
          string_agg(distinct t.id::text, ',') as boundary_1_id,
          string_agg(distinct t.boundary_2, ',') as boundary_2, 
    string_agg(distinct t.country, ',') as country      
   FROM boundary p,
        territories t
   GROUP BY p.id,
            p.name,
            p.code,
            p.descrip,
            p.geom
);

но это приводит к накоплению для каждой строки всех значений, т. е. для страны все строки имеют England, Wales вместо Англии или Уэльса в зависимости от каждого многоугольника, как это происходит в таблице границ, где каждая строка имеет Англию или Уэльс.

Если я добавлю в group by t.country строку, в ней появятся значения в соответствии со страной, но она дублирует полигоны и создает одну строку для Уэльса, а другую для Англии.

Как просто правильно получить информацию о стране для каждого полигона?

Комментарии:

1. Вы пересекаете границу и территории. Вам нужно будет выяснить, какова фактическая связь между таблицами, и выполнить соединение другого типа.

2. @JimJones Я пробовал, но при добавлении в sql-скрипку появляются ошибки, я пытался преобразовать в ascii, но, поскольку в одном из столбцов есть геометрия, это не работает хорошо. есть какие-то идеи о том, как я могу предоставить некоторые данные?

3. @jr.gcj возьмите эту скрипку, которую я создал для вас, и добавьте данные в инструкции insert: dbfiddle.uk/…

4. @jr.gcj ваши операторы вставки недействительны. Вам не нужно публиковать так много информации, достаточно простого минимального воспроизводимого примера. Запрос в вашем вопросе соединяет две таблицы без каких-либо критериев. Является ли это пространственным соединением? Если да, то скажите нам, какой из них (пересекается, перекрывается, содержит и т. Д.)

5. Полностью взаимосвязано, но: круглые скобки вокруг ВЫБОРА бесполезны.

Ответ №1:

Запрос в вашем вопросе соединяет две таблицы без каких-либо критериев (перекрестное соединение). Чтобы пространственно объединить обе таблицы, вы должны сначала определить, как они связаны, и выбрать наилучшую операцию, например ST_Contains , ST_Intersects и ST_Touches т. Д… список довольно длинный. После определения пространственной операции просто добавьте ее в JOIN , например.

 SELECT * FROM t1
JOIN t2 ON ST_Contains(t1.geom,t2.geom)

или в WHERE пункте

 SELECT * FROM t1,t2
WHERE ST_Contains(t1.geom,t2.geom)
Метки: , ,