CODEWITHSUNDEEP
postgresqlline
Tibor
Tibor

Reputation: 181

creating linestring from series of numbers separated by comma

How can i generate a linestring with a sql question from this source of numbers, where all coordinate items are separated with comma:

[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 should be separated by each second number with comma.

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

Upvotes: 1

Views: 130

Answers (2)

user330315
user330315

Reputation:

I would probably create a function for this, as it makes the actual SQL that has to convert the JSON array to the "string" a lot easier to deal with:

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;

Then you can use it like this:

select id, jsonb_array_to_linestring(input)
from test;

Online example

This assumes your column is defined as jsonb (which it should be). If you are using json instead, you need to adjust the code to that.

Upvotes: 1

S-Man
S-Man

Reputation: 23676

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
  1. Extract all array elements into one record per element
  2. This adds an index to the elements to store their position within the original index
  3. Calculate the coordinate pairs. Here I used ceil(... / 2) to generate the same value for indexes: old index pair (1, 2) -> new index 1, (3, 4) -> 2, (5, 6) -> 3, ... This can be used for grouping
  4. Now we use a conditional aggregation to create two columns: One for the odd indexes and one for the even, to generate a X/Y pair for your coordinates.
  5. These pairs can be used to create a Point
  6. Afterwards all Points can be merged into one line. To ensure the correct order, we use the recently created pair index.

If your input is not a normal Postgres array but a JSON array, you have to change 2 things (demo:db<>fiddle):

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

Upvotes: 0

Related Questions