如何将度分秒格式经纬度转换为弧度？（PostgreSQL/PySpark/Python）

PostgreSQL 实现 #

通过字符串拆分、数值计算结合radians()函数完成转换，以下是针对latitude和longitude列的完整SQL：

合并转换查询 #

SELECT
  -- 纬度转弧度：北正南负，度+分/60+秒/3600后转弧度
  radians(
    CASE WHEN substring(latitude from '[NS]$') = 'N' THEN 1 ELSE -1 END *
    (split_part(latitude, '.', 1)::numeric + split_part(latitude, '.', 2)::numeric/60 + split_part(split_part(latitude, '.', 3), '[NS]', 1)::numeric/3600)
  ) AS lat_radians,
  -- 经度转弧度：东正西负，度+分/60+秒/3600后转弧度
  radians(
    CASE WHEN substring(longitude from '[EW]$') = 'E' THEN 1 ELSE -1 END *
    (split_part(longitude, '.', 1)::numeric + split_part(longitude, '.', 2)::numeric/60 + split_part(split_part(longitude, '.', 3), '[EW]$', 1)::numeric/3600)
  ) AS lon_radians
FROM your_table;

PySpark 实现 #

提供两种实现方式，可根据需求选择：

方式1：内置函数实现（无UDF） #

from pyspark.sql import SparkSession
from pyspark.sql.functions import split, substring, regexp_extract, expr, radians

spark = SparkSession.builder.appName("latlon_to_radians").getOrCreate()
df = spark.read.table("your_table")

# 处理纬度
df = df.withColumn("lat_deg", split("latitude", "\.").getItem(0).cast("double")) \
       .withColumn("lat_min", split("latitude", "\.").getItem(1).cast("double")) \
       .withColumn("lat_sec", regexp_extract("latitude", "\.(\d+)[NS]$", 1).cast("double")) \
       .withColumn("lat_dir", substring("latitude", -1, 1)) \
       .withColumn("lat_decimal", expr("CASE WHEN lat_dir = 'N' THEN 1 ELSE -1 END * (lat_deg + lat_min/60 + lat_sec/3600)")) \
       .withColumn("lat_radians", radians("lat_decimal"))

# 处理经度
df = df.withColumn("lon_deg", split("longitude", "\.").getItem(0).cast("double")) \
       .withColumn("lon_min", split("longitude", "\.").getItem(1).cast("double")) \
       .withColumn("lon_sec", regexp_extract("longitude", "\.(\d+)[EW]$", 1).cast("double")) \
       .withColumn("lon_dir", substring("longitude", -1, 1)) \
       .withColumn("lon_decimal", expr("CASE WHEN lon_dir = 'E' THEN 1 ELSE -1 END * (lon_deg + lon_min/60 + lon_sec/3600)")) \
       .withColumn("lon_radians", radians("lon_decimal"))

# 查看结果
df.select("lat_radians", "lon_radians").show()

方式2：UDF实现 #

from pyspark.sql import SparkSession
from pyspark.sql.functions import udf
from pyspark.sql.types import DoubleType
import math

spark = SparkSession.builder.appName("latlon_to_radians").getOrCreate()

def dms_to_radians(dms_str):
    direction = dms_str[-1]
    deg, minute, sec = map(float, dms_str[:-1].split('.'))
    decimal_deg = deg + minute/60 + sec/3600
    if direction in ('S', 'W'):
        decimal_deg *= -1
    return math.radians(decimal_deg)

# 注册UDF
lat_udf = udf(dms_to_radians, DoubleType())
lon_udf = udf(dms_to_radians, DoubleType())

# 转换列
df = spark.read.table("your_table")
df = df.withColumn("lat_radians", lat_udf("latitude")) \
       .withColumn("lon_radians", lon_udf("longitude"))

df.select("lat_radians", "lon_radians").show()

Python 实现 #

直接通过字符串处理结合math库完成转换：

import math

def dms_to_radians(dms_str):
    direction = dms_str[-1]
    deg, minute, sec = map(float, dms_str[:-1].split('.'))
    decimal_deg = deg + minute / 60 + sec / 3600
    if direction in ('S', 'W'):
        decimal_deg *= -1
    return math.radians(decimal_deg)

# 示例测试
lat_str = "11.08.10N"
lon_str = "084.46.07W"
print(f"纬度弧度: {dms_to_radians(lat_str)}")
print(f"经度弧度: {dms_to_radians(lon_str)}")

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