如何利用经纬度计算直线距离 SQL

一个很老的小萌新

已于 2022-10-08 16:21:31 修改

阅读量332

点赞数

分类专栏： SQL 文章标签： mysql spark 大数据

于 2022-03-29 08:47:31 首次发布

本文链接：https://blog.csdn.net/dhq15800562693/article/details/123811222

版权

SQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

如何利用经纬度计算直线距离 SQL

Source： https://blog.csdn.net/weixin_42382211/article/details/80852786

SELECT c.province_name, c.city_name, c.address, d.station_name, d.train_station
    , CAST(d.distance AS BIGINT)
FROM (
    SELECT DISTINCT a.station_name, b.station_name AS train_station
        , MIN(6378137 * 2 * ASIN(SQRT(POWER(SIN((a.coordinate_y - b.latitude) * ACOS(-1) / 360), 2) + COS(a.coordinate_y * ACOS(-1) / 180) * COS(b.latitude * ACOS(-1) / 180) * POWER(SIN((a.coordinate_x - b.longitude) * ACOS(-1) / 360), 2)))) AS distance
    FROM dw_busdb.tbl_dc_station a
        JOIN tmp_busdb.tmp_train_station_info b
    WHERE a.coordinate_x <> '' and a.coordinate_y <> '' and a.d = '2020-03-18'
    GROUP BY a.station_name, b.station_name
) d
    JOIN dw_busdb.tbl_dc_station c ON d.station_name = c.station_name and d = '2020-03-18'
WHERE distance < 3000
ORDER BY c.province_name