经纬度计算点对点距离

纬度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)]

DB2创建测试表

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
	)

查询结果