You are not logged in.

Announcement

*** NOTICE: forum.openstreetmap.org is being retired. Please request a category for your community in the new ones as soon as possible using this process, which will allow you to propose your community moderators.
Please create new topics on the new site at community.openstreetmap.org. We expect the migration of data will take a few weeks, you can follow its progress here.***

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

Alexandr Zeinalov
Member
From: New-Peredelkino, Moscow, RU
Registered: 2009-08-26
Posts: 1,699
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,699
Website

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

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

Offline

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

Komяpa
Member
From: Minsk
Registered: 2009-04-14
Posts: 1,323
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,699
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,699
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