如何在PostgreSQL中从指定JSON数组生成LineString几何类型？

实现逻辑 #

直接利用PostgreSQL内置JSON处理函数拆分坐标数组，再拼接为要求的WKT格式，无需额外扩展：

• 用->运算符提取JSON中的coordinates坐标数组
• 用json_array_elements展开数组为单行的坐标点元素
• 每个坐标点提取经度（第一个元素）和纬度（第二个元素），拼接为lon lat格式
• 用string_agg将所有坐标点用逗号拼接，外层包裹LINESTRING()即可

完整SQL示例 #

WITH input_data AS (
    -- 此处替换为你的实际JSON数据
    SELECT '{"type": ["LineString","LineString"],"coordinates": [[11.617730473115067,48.19782098770711],[11.617959999927661,48.19828004114453],[11.617999999927662,48.1983600411445],[11.618139999927674,48.19867004114437],[11.61840999992768,48.19925004114419],[11.618709999927699,48.19985004114398]]}'::json AS raw_json
)
SELECT 
    'LINESTRING(' || string_agg(point_elem->>0 || ' ' || point_elem->>1, ',') || ')' AS linestring_text
FROM input_data,
     json_array_elements(raw_json->'coordinates') AS point_elem;

执行结果 #

输出完全符合要求的格式：

LINESTRING(11.617730473115067 48.19782098770711,11.617959999927661 48.19828004114453,11.617999999927662 48.1983600411445,11.618139999927674 48.19867004114437,11.61840999992768 48.19925004114419,11.618709999927699 48.19985004114398)

可选方案（需PostGIS扩展） #

如果需要直接生成PostGIS原生LineString几何类型，可直接修正原JSON不符合标准GeoJSON的type字段后，调用内置函数转换：

WITH input_data AS (
    SELECT '{"type": ["LineString","LineString"],"coordinates": [[11.617730473115067,48.19782098770711],[11.617959999927661,48.19828004114453],[11.617999999927662,48.1983600411445],[11.618139999927674,48.19867004114437],[11.61840999992768,48.19925004114419],[11.618709999927699,48.19985004114398]]}'::json AS raw_json
)
SELECT 
    ST_GeomFromGeoJSON(
        json_build_object(
            'type', 'LineString',
            'coordinates', raw_json->'coordinates'
        )
    ) AS linestring_geom
FROM input_data;

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