从逗号分隔的一系列数字创建线串

Question

如何从这个数字源中生成带有sql问题的行字符串，其中所有坐标项都用逗号分隔：

[16.49422,48.8011,16.49432,48.8012,16.49441,48.80127,16.49451,48.80131,16.49464,48.80135,16.49471,48.80139]

Linestring应该用逗号隔开。

LINESTRING(16.49422 48.8011,1 6.49432 48.8012, ... )

Answer 1

我可能会为此创建一个函数，因为它使必须将JSON数组转换为“字符串”的实际SQL更容易处理：

create function jsonb_array_to_linestring(p_input jsonb)
  returns text
as
$$
declare
  l_num_elements int;
  l_idx int;
  l_result text;
begin
  l_num_elements := jsonb_array_length(p_input);
  if l_num_elements = 2 then 
    return 'point('||(p_input ->> 0)||' '||(p_input ->> 1)||')';
  end if;
  l_result := 'linestring(';
  for l_idx in 0 .. l_num_elements - 2 by 2 loop
    l_result := l_result || (p_input ->> l_idx) || ' ' || (p_input ->> l_idx + 1);
    if l_idx < l_num_elements - 2 then 
      l_result := l_result || ',' ;
    end if;
  end loop;
  l_result := l_result || ')';
  return l_result;
end;
$$
language plpgsql;

然后你可以像这样使用它：

select id, jsonb_array_to_linestring(input)
from test;  

Online example

这里假设您的列被定义为jsonb (它应该是这样)。如果您使用的是json，则需要对代码进行相应的调整。

Answer 2

step-by-step demo:db<>fiddle

SELECT
    st_makeline(point order by index)                    -- 6
FROM (
    SELECT 
        ceil(index::numeric / 2) as index,               -- 3
        st_makepoint(                                    -- 5
            MAX(value) FILTER (WHERE index % 2 = 1),     -- 4
            MAX(value) FILTER (WHERE index % 2 = 0)
        ) as point
    FROM 
        unnest(                                          -- 1
            ARRAY[16.49422,48.8011,16.49432,48.8012,16.49441,48.80127,16.49451,48.80131,16.49464,48.80135,16.49471,48.80139
        ]) WITH ORDINALITY elements(value, index)        -- 2
    GROUP BY 1                                           -- 3
) s

将所有数组元素提取到每个元素的一条记录中，这会向元素添加索引，以存储它们在原始坐标对中的位置( index

• Calculate

2. )。在这里，我使用ceil(... / 2)为索引生成相同的值: old index pair (1, 2) -> new index 1，(3, 4) -> 2，(5, 6) -> 3，...现在我们使用条件聚合来创建两列:一列用于奇数索引，另一列用于偶数索引，为您的coordinates.
3. These对生成一个X/Y对可用于创建一个Point
4. Afterwards所有Point都可以合并到一行中。为了确保正确的顺序，我们使用最近创建的pair index.

如果你的输入不是一个普通的Postgres数组，而是一个JSON数组，你必须改变两件事(demo:db<>fiddle)：

• In (1)：unnest(...) to (4)：MAX(value) to MAX(value::numeric)