OpenStreetMap Forum

The Free Wiki World Map

You are not logged in.

#1 2010-11-15 10:34:49

Alexandr Zeinalov
Member
From: New-Peredelkino, Moscow, RU
Registered: 2009-08-26
Posts: 1,691
Website

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

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

Да, можно извлечь по условию where 'route'=any(tags), а потом отфильтровать вывод своими силами. Но это неспортивно 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 лучше меня и подскажет?

Offline

#2 2010-11-15 10:45:06

Ezhick
Member
From: Moscow
Registered: 2008-10-08
Posts: 2,508
Website

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

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

Offline

#3 2010-11-15 10:51:05

Alexandr Zeinalov
Member
From: New-Peredelkino, Moscow, RU
Registered: 2009-08-26
Posts: 1,691
Website

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

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

Offline

#4 2010-11-15 10:51:24

Komяpa
Member
From: Minsk
Registered: 2009-04-14
Posts: 1,321
Website

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

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


world processing is what we do.
[OSMF BY Team] [http://komzpa.net/] [jabber: komzpa@gmail.com] [mobile/SMS: +375257407159]

Offline

#5 2010-11-15 12:08:21

Ezhick
Member
From: Moscow
Registered: 2008-10-08
Posts: 2,508
Website

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

Alexandr Zeinalov wrote:

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

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

Offline

#6 2010-11-15 12:08:55

Ezhick
Member
From: Moscow
Registered: 2008-10-08
Posts: 2,508
Website

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

Komяpa wrote:

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

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

Offline

#7 2010-11-15 12:43:19

Alexandr Zeinalov
Member
From: New-Peredelkino, Moscow, RU
Registered: 2009-08-26
Posts: 1,691
Website

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

Вообще да, если остаться с 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))

Offline

#8 2010-12-19 04:45:35

Aleksandr Dezhin
Member
From: Msk
Registered: 2008-05-25
Posts: 2,109
Website

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

Делал тоже самое для ролей, получился свой велосипед, который быстрее ездит. Ну и колеса более круглые, как мне кажется  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[] делать, ведь вы массив возвращаете.

Last edited by Aleksandr Dezhin (2010-12-19 05:34:12)


[ Проверка границ и НП | Дампы | Валидатор рек | Кое-что ещё ]
- Нет! Не надо пытаться. Делай. Или не делай. Не надо пытаться. (c) Йода

Offline

#9 2010-12-19 08:05:34

Alexandr Zeinalov
Member
From: New-Peredelkino, Moscow, RU
Registered: 2009-08-26
Posts: 1,691
Website

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

Aleksandr Dezhin wrote:

там надо поставить RETURNS text[] делать, ведь вы массив возвращаете.

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

Offline

#10 2010-12-19 22:41:35

Aleksandr Dezhin
Member
From: Msk
Registered: 2008-05-25
Posts: 2,109
Website

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

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


[ Проверка границ и НП | Дампы | Валидатор рек | Кое-что ещё ]
- Нет! Не надо пытаться. Делай. Или не делай. Не надо пытаться. (c) Йода

Offline

Board footer

Powered by FluxBB