Возникла у меня задача выборки релейшнов type=route из PostGIS. Для этого я залил в базу файл с ключом --slim. Соответственно все тэги лежат в поле planet_osm_rels.tags text[]. Что писать в where, если массив “совсем плоский” и ключи перемешаны со значениями?
Да, можно извлечь по условию where ‘route’=any(tags), а потом отфильтровать вывод своими силами. Но это неспортивно Пара часов мозгового штурма - получилось такое:
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 лучше меня и подскажет?
Ezhick, можно, но неспортивно.
мысль в том, чтобы уложить-таки всё в одну osm2pgsql-базу, ибо результат-то потом надо будет возвращать обратно в нее же.
Вообще да, если остаться с 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))
Делал тоже самое для ролей, получился свой велосипед, который быстрее ездит. Ну и колеса более круглые, как мне кажется
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[] делать, ведь вы массив возвращаете.