如何将表中多组经纬度存储为字符串适配SDO_GEOMETRY插入

现有表结构与数据 #

现有一张存储多组经纬度的表，包含两个数值类型列：

• Latitude：存储纬度值
• Longitude：存储经度值

示例数据如下：

实际数据行数不固定，可能为5、50或200行不等。

需求 #

需要给该表新增一个CLOB类型的layerstring列，将所有经纬度转换为SDO_ORDINATE_ARRAY可接受的格式存入该列，最终把该列值传入变量:P400_STRING，用于构造INSERT语句将多边形几何对象插入site_map_layer表，目标INSERT语句如下：

INSERT INTO site_map_layer (layerid,name,location) VALUES(
2,
'testlayer',
SDO_GEOMETRY(
2003,  -- 二维多边形
NULL,
NULL,
SDO_ELEM_INFO_ARRAY(1,1003,1), -- 单个多边形（外环）
SDO_ORDINATE_ARRAY(:P400_STRING)
)
);

实现步骤 #

1. 添加CLOB列 #

先执行ALTER TABLE语句新增layerstring列：

ALTER TABLE 你的表名 ADD layerstring CLOB;

2. 生成符合要求的坐标字符串 #

使用Oracle的聚合函数拼接坐标，注意多边形需要首尾闭合（重复第一组经纬度）：

UPDATE 你的表名 t
SET t.layerstring = (
    SELECT 
        LISTAGG(Longitude || ',' || Latitude, ',') WITHIN GROUP (ORDER BY 排序列名) 
        || ',' || FIRST_VALUE(Longitude) OVER (ORDER BY 排序列名) 
        || ',' || FIRST_VALUE(Latitude) OVER (ORDER BY 排序列名)
    FROM 你的表名
);

注意事项：

• 替换你的表名为实际表名
• 替换排序列名为能确定坐标顺序的列（比如行号、ID等），确保多边形顶点顺序正确
• LISTAGG负责将所有经度,纬度对用逗号连接，最后追加第一组坐标实现闭合，满足SDO_GEOMETRY外环的要求

3. 执行插入 #

将layerstring列的值赋值给:P400_STRING变量后，执行给定的INSERT语句即可完成几何对象插入。

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