緯度1度大約111km
緯度1分大約1.85km
緯度1秒大約30.9km
經(jīng)線上跨緯度1度=111km
緯線上跨經(jīng)度1度=111*cosAkm，其中A是緯度

地球半徑R=6370.996km,地球上的點(diǎn)(x1,y1),(x2,y2)

兩點(diǎn)間的距離

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

創(chuàng)建測(cè)試表

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

添加測(cè)試數(shù)據(jù)

INSERT INTO
    DEMO(
        name,
        log,
        lat
    ) values
('上?？系禄?,121.439926,31.331578),
('星巴克',121.511039,31.261306),
('避風(fēng)塘',121.527496,31.230014);

db2中計(jì)算地球上點(diǎn)和點(diǎn)的距離計(jì)算公式

radians將度轉(zhuǎn)換為弧度，測(cè)試數(shù)據(jù)庫(kù)類型是varchar,CAST是DB2或oracle中強(qiáng)制類型轉(zhuǎn)換函數(shù)，使用方法如下：

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
    )

查詢結(jié)果

經(jīng)緯度.png