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