如何将度分秒格式经纬度转换为弧度（Oracle/MySQL/Python）

转换逻辑 #

先将度分秒格式转为十进制度数：
十进制度数 = 度 + 分/60 + 秒/3600
再根据方向调整符号：

• 纬度：N为正，S为负
• 经度：E为正，W为负
  最后用弧度转换函数将十进制度数转为弧度。

Oracle实现 #

使用字符串函数拆分度、分、秒和方向，计算后转弧度：

纬度转换示例 #

SELECT
  -- 拆分度、分、秒和方向
  SUBSTR(latitude, 1, INSTR(latitude, '.') - 1) AS deg,
  SUBSTR(latitude, INSTR(latitude, '.') + 1, 2) AS min,
  SUBSTR(latitude, INSTR(latitude, '.', 1, 2) + 1, 2) AS sec,
  SUBSTR(latitude, -1) AS dir,
  -- 计算十进制度数并转弧度
  RADIANS(
    TO_NUMBER(SUBSTR(latitude, 1, INSTR(latitude, '.') - 1)) +
    TO_NUMBER(SUBSTR(latitude, INSTR(latitude, '.') + 1, 2)) / 60 +
    TO_NUMBER(SUBSTR(latitude, INSTR(latitude, '.', 1, 2) + 1, 2)) / 3600 *
    CASE SUBSTR(latitude, -1) WHEN 'S' THEN -1 ELSE 1 END
  ) AS lat_radians
FROM your_table;

经度转换示例 #

SELECT
  SUBSTR(longitude, 1, INSTR(longitude, '.') - 1) AS deg,
  SUBSTR(longitude, INSTR(longitude, '.') + 1, 2) AS min,
  SUBSTR(longitude, INSTR(longitude, '.', 1, 2) + 1, 2) AS sec,
  SUBSTR(longitude, -1) AS dir,
  RADIANS(
    TO_NUMBER(SUBSTR(longitude, 1, INSTR(longitude, '.') - 1)) +
    TO_NUMBER(SUBSTR(longitude, INSTR(longitude, '.') + 1, 2)) / 60 +
    TO_NUMBER(SUBSTR(longitude, INSTR(longitude, '.', 1, 2) + 1, 2)) / 3600 *
    CASE SUBSTR(longitude, -1) WHEN 'W' THEN -1 ELSE 1 END
  ) AS lon_radians
FROM your_table;

MySQL实现 #

利用SUBSTRING_INDEX和RIGHT等函数拆分字段，计算逻辑与Oracle一致：

纬度转换示例 #

SELECT
  SUBSTRING_INDEX(latitude, '.', 1) AS deg,
  SUBSTRING_INDEX(SUBSTRING_INDEX(latitude, '.', 2), '.', -1) AS min,
  SUBSTRING_INDEX(RIGHT(latitude, 5), '.', -1) AS sec,
  RIGHT(latitude, 1) AS dir,
  RADIANS(
    CAST(SUBSTRING_INDEX(latitude, '.', 1) AS DECIMAL) +
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(latitude, '.', 2), '.', -1) AS DECIMAL)/60 +
    CAST(SUBSTRING_INDEX(RIGHT(latitude, 5), '.', -1) AS DECIMAL)/3600 *
    CASE RIGHT(latitude, 1) WHEN 'S' THEN -1 ELSE 1 END
  ) AS lat_radians
FROM your_table;

经度转换示例 #

SELECT
  SUBSTRING_INDEX(longitude, '.', 1) AS deg,
  SUBSTRING_INDEX(SUBSTRING_INDEX(longitude, '.', 2), '.', -1) AS min,
  SUBSTRING_INDEX(RIGHT(longitude, 5), '.', -1) AS sec,
  RIGHT(longitude, 1) AS dir,
  RADIANS(
    CAST(SUBSTRING_INDEX(longitude, '.', 1) AS DECIMAL) +
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(longitude, '.', 2), '.', -1) AS DECIMAL)/60 +
    CAST(SUBSTRING_INDEX(RIGHT(longitude, 5), '.', -1) AS DECIMAL)/3600 *
    CASE RIGHT(longitude, 1) WHEN 'W' THEN -1 ELSE 1 END
  ) AS lon_radians
FROM your_table;

Python实现 #

通过字符串拆分提取各部分数值，计算后用math.radians转弧度：

import math

def dms_to_radians(dms_str):
    # 拆分方向和度分秒部分
    dir = dms_str[-1]
    dms_part = dms_str[:-1]
    deg, min, sec = map(float, dms_part.split('.'))
    
    # 计算十进制度数
    decimal_deg = deg + min/60 + sec/3600
    # 调整符号
    if dir in ['S', 'W']:
        decimal_deg *= -1
    # 转弧度
    return math.radians(decimal_deg)

# 示例使用
lat_dms = "11.08.10N"
lon_dms = "084.46.07W"
lat_rad = dms_to_radians(lat_dms)
lon_rad = dms_to_radians(lon_dms)

print(f"纬度弧度: {lat_rad}")
print(f"经度弧度: {lon_rad}")

内容的提问来源于stack exchange，提问作者sandy