mysql经纬度计算

最新推荐文章于 2025-05-25 15:22:45 发布

原创最新推荐文章于 2025-05-25 15:22:45 发布 · 5.6k 阅读

3 ·

CC 4.0 BY-SA版权

纬度：22.5356803547 经度：114.0394370593

方法一、SELECT
  c.cinema_id AS cinemaId,
  c.longitude AS longitude,
  c.latitude AS latitude,
  c.longitude_bd AS bdLongitude,
  c.latitude_bd AS bdLatitude,
  (
    6378.137 * ACOS(
      COS(RADIANS(22.5356803547)) * COS(RADIANS(c.latitude_bd)) * COS(
        RADIANS(c.longitude_bd) - RADIANS(114.0394370593)
      ) + SIN(RADIANS(22.5356803547)) * SIN(RADIANS(c.latitude_bd))
    )
  ) AS distance
FROM
  bas_cinema_info AS c

距离单位：km

方法二、

select
       bc.cinema_id as cinemaId,
       bc.cinema_name as cinemaName,
       bci.address as cinemaAddress,
bci.longitude,
bci.latitude,
ROUND(
6378.138 * 2 * ASIN(
SQRT(
POW(
SIN(
(
22.535680 * PI() / 180 - bci.latitude * PI() / 180
) / 2
),
2
) + COS(22.535680 * PI() / 180) * COS( bci.latitude * PI() / 180) * POW(
SIN(
(
114.039437 * PI() / 180 - bci.longitude * PI() / 180
) / 2
),
2
)
)
) * 1000
) as distance
       from bas_cinema bc
       left join bas_cinema_info bci
       on bc.cinema_id=bci.cinema_id
       where bc.cinema_id in(472048961608220672,472048961876656128)