SELECT релейшнов заданного типа из PostGIS

Возникла у меня задача выборки релейшнов type=route из PostGIS. Для этого я залил в базу файл с ключом --slim. Соответственно все тэги лежат в поле planet_osm_rels.tags text[]. Что писать в where, если массив “совсем плоский” и ключи перемешаны со значениями?

Да, можно извлечь по условию where ‘route’=any(tags), а потом отфильтровать вывод своими силами. Но это неспортивно :slight_smile: Пара часов мозгового штурма - получилось такое:

create or replace function tags2pairs(a text[]) RETURNS text AS $$

DECLARE s TEXT[];
DECLARE i TEXT;
DECLARE p TEXT;
DECLARE n INT;
BEGIN
  n = 0;
  FOR i IN SELECT CAST(UNNEST(a) AS TEXT) LOOP
    n = n+1;
    if n%2=0 then
      s[n/2]=p || '=' || i;
    else
      p = i;
    end if;
  END LOOP;
  RETURN s;
END;

$$ LANGUAGE plpgsql;

Сам запрос при этом может выглядеть так:

select id,members from planet_osm_rels where 'type=route'=any(cast(tags2pairs(tags)as text[]))

Но необходимость cast меня напрягает, хочется от него избавиться. Я с трудом понимаю, почему из всех шаманств заработало это, и мне трудно представить, куда можно шаманить дальше. Может, кто-то разбирается в PostgreSQL лучше меня и подскажет?

Этта, а попробовать использовать не базу osm2pgsql, а осмосисовскую postgre simple? Там тэги хранятся в hstore, имхо поудобнее с ними работать будет.

Тогда хочу нормальноготовую схему для такой базы, чтоб попробовать.

Ezhick, можно, но неспортивно. :slight_smile:
мысль в том, чтобы уложить-таки всё в одну osm2pgsql-базу, ибо результат-то потом надо будет возвращать обратно в нее же.

Так она есть в осмосисе, в папке script

Этого в исходных условиях не было :slight_smile:

Вообще да, если остаться с osm2pgsql, то можно будет выполнить необходимые действия прямо в базе, а не делать экспорт в OSM и импорт в результирующую osm2pgsql.

Пока я придумал добавить ещё одну функцию:

CREATE OR REPLACE FUNCTION tags2pairs2(a TEXT[]) RETURNS text[] AS $$
  SELECT CAST(tags2pairs($1) AS TEXT[]);
$$ LANGUAGE sql;

Это работает, запрос упростился до:

select id,members from planet_osm_rels where 'type=route'=any(tags2pairs2(tags))

Делал тоже самое для ролей, получился свой велосипед, который быстрее ездит. Ну и колеса более круглые, как мне кажется :slight_smile:


CREATE OR REPLACE FUNCTION tags2pairs(a text[]) RETURNS text[] AS $SQL$
    SELECT array_accum($1[i] || '=' || $1[i+1])
    FROM generate_series(1, array_upper($1,1)) i
    WHERE i % 2 = 1
$SQL$ LANGUAGE sql;

Лишний CAST этой функции не нужен. Да и в исходном варианте он тоже не очень нужен - там надо поставить RETURNS text[] делать, ведь вы массив возвращаете.

Я пробовал, не получалось. Хотя, конечно, я не очень умею их готовить.

Еще 10% процентов времени можно сэкономить добавив флаг IMMUTABLE, ведь она не меняет данных.