Postgres将PATH类型转换为ARRAY

Question

有没有办法将Postgres PATH类型转换为ARRAY，以便对其点进行索引访问？

Answer 1

单用PostgreSQL是无法做到这一点的——你必须编写自己的C函数。

使用PostGIS扩展，您可以将path转换为geometry，并在那里执行操作：

SELECT array_agg(CAST(geom AS point))
FROM st_dumppoints(CAST(some_path AS geometry));

Answer 2

试试这个的变体。。

CREATE OR REPLACE FUNCTION YADAMU.YADAMU_make_closed(point[])
returns point[]
STABLE RETURNS NULL ON NULL INPUT
as
$$
select case 
when $1[1]::varchar = $1[array_length($1,1)]::varchar then
$1
else 
array_append($1,$1[1])
end
$$
LANGUAGE SQL;
--
CREATE OR REPLACE FUNCTION YADAMU.YADAMU_AsPointArray(path)
returns point[]
STABLE RETURNS NULL ON NULL INPUT
as
$$
--
-- Array of Points from Path
--
select case 
when isClosed($1) then
YADAMU.YADAMU_make_closed(array_agg(point(v)))
else 
array_agg(point(v))
end 
from unnest(string_to_array(left(right($1::VARCHAR,-2),-2),'),(')) v
$$
LANGUAGE SQL;
--

例如

yadamu=# select * from unnest(YADAMU.YADAMU_asPointArray(Path '((0,1),(1,0),(4,0))'));
unnest
--------
(0,1)
(1,0)
(4,0)
(3 rows)

Answer 3

您可以使用st_dumppoints和array_agg。

要获得一个点数组，该数组将成为Python中的点列表：

select o.osm_ID, array_agg(point(o.way)) as points
from (  select osm_ID, (st_dumppoints( way )).geom way 
from planet_osm_line ) o
group by o.osm_ID;

退货：

-14908307   {(4462712.614234681,-1275901.9494768495), ...}
-14908306   {(4467217.391200562,-1289376.0756383499), ...}
...

也可以将每个点作为一个数组，该数组作为Python中的列表列表返回：

select o.osm_ID, array_agg(array[ST_X(o.way), ST_y(o.way)]) as points
from (  select osm_ID, (st_dumppoints( way )).geom way 
from planet_osm_line ) o
group by o.osm_ID;

退货：

-14908307   {{4462712.614234681,-1275901.9494768495}, ...}
-14908306   {{4467217.391200562,-1289376.0756383499}, ...}
...