经纬度计算点对点距离

纬度1度大约111km
纬度1分大约1.85km
纬度1秒大约30.9km
经线上跨纬度1度=111km
纬线上跨经度1度=111*cosAkm，其中A是纬度

地球半径R=6370.996km,地球上的点(x1,y1),(x2,y2)

两点间的距离

d=R*arcos[cos(y1)*cos(y2)*cos(x1-x2)+sin(y1)*sin(y2)]

创建测试表

CREATE TABLE DEMO(
    name varchar(50) NOT NULL,
    log varchar(50) NOT NULL,
    lat varchar(50) NOT NULL
)

添加测试数据

INSERT INTO
    DEMO(
        name,
        log,
        lat
    ) values
('上海肯德基',121.439926,31.331578),
('星巴克',121.511039,31.261306),
('避风塘',121.527496,31.230014);

db2中计算地球上点和点的距离计算公式

radians将度转换为弧度，测试数据库类型是varchar,CAST是DB2或oracle中强制类型转换函数，使用方法如下：

CAST ( expression AS data_type )

SELECT
    name,log,lat,DISTANCE
FROM
    (SELECT name,log,lat,
        6370.996*(
            ACOS(
                cos(radians(CAST(31.331578 as DECIMAL(15,6))))*
                cos(radians(CAST(lat as DECIMAL(15,6))))*
                cos(radians(CAST(121.439926 as DECIMAL(15,6)))-radians(CAST(log as DECIMAL(15,6))))+
                sin(radians(CAST(31.331578 as DECIMAL(15,6))))*
                sin(radians(CAST(lat as DECIMAL(15,6))))
            )
        )AS DISTANCE
    FROM
        DEMO
    )

查询结果

经纬度.png