postgresql - 将线串转换为点数组？

Question

我有带 postgis 的 postgres，我需要将线串转换为点数组

我已经检查过这个 answer ，我的问题类似，但我也需要将它聚合到数组

从这里

Linestring((3.584731 60.739211,3.590472 60.738030,3.592740 60.736220))

对此

["3.584731 60.739211","3.590472 60.738030","3.592740 60.736220"]

我该怎么做？

Answer 1

好吧，我不知道你为什么需要这个，但是给你:

WITH
    route_sequence AS (
      SELECT id, path
      FROM (
        VALUES
          (1, 'Linestring(3.584731 60.739211,3.590472 60.738030,3.592740 60.736220)'),
          (2, 'Linestring(3.584731 60.739211,3.590472 60.738030,3.592740 60.736220)')
      ) AS route_sequence(id, path)
  ),
  rstemp AS (
      SELECT
        route_sequence.id,
        array_agg(
            ST_X(points.geom)::CHARACTER VARYING
            || ' '
            || ST_Y(points.geom)::CHARACTER VARYING
            ORDER BY points.path[1] ASC
        ) AS coordinates
      FROM route_sequence, ST_DumpPoints(path) as points(path, geom)
      GROUP BY route_sequence.id
  )
SELECT *
FROM rstemp
;

我很好奇为什么这里需要一个数组。也许还有另一种方法。为什么不能保留 ST_DumpPoints 生成的初始 geom？