Oracle单表中基于经纬度计算两点间距离的问题排查

一、修复SDO_GEOM.SDO_DISTANCE的ORA-13032错误 #

你遇到的ORA-13032: Invalid NULL SDO_GEOMETRY object错误，通常是构造SDO_GEOMETRY对象时出现无效NULL值，或是语法/依赖问题导致的。可以从以下几个方向排查修复：

1. 先检查经纬度列是否存在NULL值 #

如果long_pl、lat_pl、long_ln、lat_ln任意一列有NULL值，会直接导致SDO_POINT_TYPE构造出无效的几何对象，先执行这条语句排查：

SELECT * FROM table1 
WHERE long_pl IS NULL OR lat_pl IS NULL OR long_ln IS NULL OR lat_ln IS NULL;

如果查到NULL行，要么过滤掉（添加WHERE条件），要么补全数据后再执行距离计算。

2. 修正SDO_GEOMETRY的构造语法 #

确保正确引用MDSYS的空间类型，并且语法没有疏漏，调整后的SQL如下：

SELECT 
    a.*,
    SDO_GEOM.SDO_DISTANCE(
        SDO_GEOMETRY(
            2001,  -- 2001代表点类型
            8307,  -- SRID=8307对应WGS84经纬度坐标系
            MDSYS.SDO_POINT_TYPE(long_pl, lat_pl, NULL),
            NULL,
            NULL
        ),
        SDO_GEOMETRY(
            2001,
            8307,
            MDSYS.SDO_POINT_TYPE(long_ln, lat_ln, NULL),
            NULL,
            NULL
        ),
        0.0001,  -- 公差值，控制计算精度
        'unit=M' -- 指定计算结果单位为米
    ) AS distance_in_m
FROM table1 a;

注意：SDO_POINT_TYPE需要明确加上MDSYS前缀（如果你的会话没有默认导入该 schema），同时要确认Oracle环境已经正确安装并配置了Spatial组件（MDSYS相关对象存在）。

3. 验证Spatial组件的有效性 #

如果上面的修正还是报错，可以检查MDSYS schema的有效性：

SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME='SDO_GEOM' AND OWNER='MDSYS';

如果没有返回结果，说明Spatial组件未正确安装，需要联系DBA完成组件部署。

二、修正球面距离公式得到正确的米级结果 #

你之前用的球面距离公式在单位转换环节有问题，导致结果不符合预期，以下是两种修正方案：

方案1：直接用地球半径计算米级距离（推荐） #

使用地球平均半径（6371000米）直接计算大圆距离，结果直接为米，精度更高：

SELECT 
    a.*,
    ACOS(
        SIN(RADIANS(lat_pl)) * SIN(RADIANS(lat_ln)) +
        COS(RADIANS(lat_pl)) * COS(RADIANS(lat_ln)) * COS(RADIANS(long_pl - long_ln))
    ) * 6371000 AS distance_in_m
FROM table1 a;

说明：

• RADIANS()是Oracle内置函数，自动将角度转弧度，比手动乘π/180精度更高
• 6371000是地球平均半径，单位为米，直接乘以弧度结果就能得到米级距离

方案2：修正原有的单位转换逻辑 #

如果坚持用海里转米的方式，需要替换成标准转换系数（1海里=1852米），同时用Oracle内置PI()函数提升精度：

SELECT 
    a.*,
    (ACOS(
        SIN(RADIANS(lat_pl)) * SIN(RADIANS(lat_ln)) +
        COS(RADIANS(lat_pl)) * COS(RADIANS(lat_ln)) * COS(RADIANS(long_pl - long_ln))
    ) * 180 / PI()) * 60 * 1852 AS distance_in_m
FROM table1 a;

三、将距离列永久新增到表中 #

如果需要把计算结果保存到表中，可以先添加列再填充数据：

-- 1. 新增距离列
ALTER TABLE table1 ADD distance_in_m NUMBER(10,2);

-- 2. 用SDO_GEOM方法填充（精度更高，推荐）
UPDATE table1 a
SET distance_in_m = SDO_GEOM.SDO_DISTANCE(
    SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(long_pl, lat_pl, NULL), NULL, NULL),
    SDO_GEOMETRY(2001, 8307, MDSYS.SDO_POINT_TYPE(long_ln, lat_ln, NULL), NULL, NULL),
    0.0001,
    'unit=M'
);

-- 或者用球面公式填充
UPDATE table1 a
SET distance_in_m = ACOS(
    SIN(RADIANS(lat_pl)) * SIN(RADIANS(lat_ln)) +
    COS(RADIANS(lat_pl)) * COS(RADIANS(lat_ln)) * COS(RADIANS(long_pl - long_ln))
) * 6371000;

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