编写PostgreSQL函数：遍历嵌套数组生成PostGIS LINESTRING输入字符串

一、原函数的语法错误说明 #

你的函数无法创建主要有以下几个问题：

1. 声明了language sql，但使用了PL/pgSQL的流程控制语法（DECLARE、BEGIN、FOREACH循环），两者不兼容，需改为language plpgsql。
2. 循环内引用了未定义的变量m，实际应该用声明的循环变量p。
3. 未对数组flattened进行初始化，也没有正确将每个点的坐标拼接后加入数组。
4. RETURN flattened返回的是数组类型，但函数声明返回text，需要转换为字符串。

二、修正后的flatten_points函数 #

以下是符合需求的函数实现：

CREATE OR REPLACE FUNCTION flatten_points(points text[][])
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $func$
DECLARE
    flattened TEXT[] := '{}'::TEXT[]; -- 初始化空数组
    p TEXT[];
BEGIN
    FOREACH p SLICE 1 IN ARRAY points
    LOOP
        -- 将每个子数组的两个元素用空格拼接，加入flattened数组
        flattened := flattened || (p[1] || ' ' || p[2]);
    END LOOP;
    -- 将数组元素用逗号加空格连接成最终字符串
    RETURN array_to_string(flattened, ', ');
END;
$func$;

测试示例 #

调用函数：

SELECT flatten_points(ARRAY[
    ['65.8434183140001','38.905535066'],
    ['65.8433595220001','38.905479615'],
    ['65.843286031','38.9054103'],
    ['65.843108611','38.9055251450001'],
    ['65.8432114970001','38.905622185'],
    ['65.8434183140001','38.905535066']
]);

输出结果与你期望的一致：

65.8434183140001 38.905535066, 65.8433595220001 38.905479615, 65.843286031 38.9054103, 65.843108611 38.9055251450001, 65.8432114970001 38.905622185, 65.8434183140001 38.905535066

三、直接用嵌套数组创建PostGIS多边形 #

不需要先拼接字符串，PostGIS提供了更高效的直接处理方式：

方法1：使用ST_MakePolygon + ST_MakeLine #

如果你的嵌套数组是闭合的（首尾点相同），可以用以下函数直接生成多边形：

CREATE OR REPLACE FUNCTION array_to_polygon(points double precision[][])
RETURNS geometry
LANGUAGE plpgsql
IMMUTABLE
AS $func$
DECLARE
    line_geom geometry;
BEGIN
    -- 将二维数组转换为LineString
    line_geom := ST_MakeLine(ARRAY(
        SELECT ST_MakePoint(p[1], p[2])
        FROM unnest(points) AS p
    ));
    -- 将闭合LineString转换为Polygon
    RETURN ST_MakePolygon(line_geom);
END;
$func$;

测试示例 #

SELECT ST_AsText(array_to_polygon(ARRAY[
    [65.8434183140001,38.905535066],
    [65.8433595220001,38.905479615],
    [65.843286031,38.9054103],
    [65.843108611,38.9055251450001],
    [65.8432114970001,38.905622185],
    [65.8434183140001,38.905535066]
]));

输出的WKT格式为：

POLYGON((65.8434183140001 38.905535066,65.8433595220001 38.905479615,65.843286031 38.9054103,65.843108611 38.9055251450001,65.8432114970001 38.905622185,65.8434183140001 38.905535066))

方法2：结合拼接函数生成（适合字符串场景） #

如果需要基于字符串拼接的方式，可以结合之前的flatten_points函数：

SELECT ST_GeomFromText('POLYGON((' || flatten_points(your_array) || '))');

但方法1的效率更高，避免了字符串转换的额外开销。

内容的提问来源于stack exchange，提问作者ABDULLOKH MUKHAMMADJONOV