Как задать сортировку по массиву в PostgreSQL?

Question

[Sergei Iamskoi]

Есть две таблицы. Первая planet_osm_ways:

id :: bigint, nodes :: bigint[], tags :: text[] - в ней содержится объект и в поле nodes (массив bigint) список точек объекта в нужном порядке.

Вторая таблица planet_osm_nodes - в ней содержатся точки с координатами.
id :: bigint, lat::bigint, lon::bigint

Запрос:

select
    pon.id,
    ST_FlipCoordinates(ST_Transform(ST_GeomFromText('POINT(' || lon / 100 || ' ' || lat / 100 || ')', 3785), 4326)) as coords
from
    planet_osm_ways AS pow
left join planet_osm_nodes AS pon on pon.id = any (pow.nodes)
where
    pow.id = 128676234
order by 
    pow.nodes -- ??

Как бы не крутил запрос и сортировку, он сортирует по pon.id. Можно ли заставить его выводить список точек в том порядке, как указано в массиве planet_osm_ways.nodes ?? Ибо если делать в отсортированном по возрастанию, то полигоны собираются не так как надо (((

Answer 1

[terrier]

Если я правильно понимаю, возможно такое поможет:
Можно сортировать по результатам некоторой функции. То есть
select
...
from
planet_osm_ways AS pow
left join planet_osm_nodes AS pon on pon.id = any (pow.nodes)
where
pow.id = 128676234
order by
my_cool_sorting_func( pow.nodes );

Где my_cool_sorting_func - функция, которая принимает на вход массив и выдает результат ( например int ), который и будет определять порядок сортировки

Comments

[Sergei Iamskoi]

Что-то я про функции совсем забыл:) Спасибо!

CREATE OR REPLACE FUNCTION test_order(nodes ANYELEMENT, id ANYELEMENT)
RETURNS INT AS $$
SELECT i
FROM generate_subscripts($1, 1) AS i
WHERE $1[i] = $2
ORDER BY i
$$ LANGUAGE sql STABLE;

[Grass_hopper]

Не надо выдумывать никаких функций, она уже сущесвует. Это array_position.

select
...
from
planet_osm_ways AS pow
left join planet_osm_nodes AS pon on pon.id = any (pow.nodes)
where
pow.id = 128676234
order by array_position(pow.nodes, pon.id);