问题

I am working on converting lats and logs to radians and I've two columns (PostgreSQL) namely latitude and longitude in format I've mentioned below.

我正在尝试将纬度和经度转换为弧度，并且我在PostgreSQL中有两列，分别是纬度和经度，格式如下所示。

Could anyone help me on how to convert them into radians using PostgreSQL, Pyspark or Python?

是否有人可以帮助我如何使用PostgreSQL、Pyspark或Python将它们转换为弧度？

Example: 11.08.10N (Latitude), 084.46.07W (Longitude)

示例：11.08.10N（纬度），084.46.07W（经度）

Note:

注意：

The latitude of a location is represented by three two-digit numbers separated by periods in the order of degrees, minutes from degrees, seconds from degrees that are followed by a N for north or a S for south.

位置的纬度由三个两位数以度、分、秒的顺序以点分隔表示，后跟N表示北半球或S表示南半球。

The longitude of a location is represented by a three-digit number and two two-digit numbers separated by periods in the order of degrees, minutes from degrees, seconds from degrees that are followed by a W for west or an E for east

位置的经度由一个三位数和两个两位数以度、分、秒的顺序以点分隔表示，后跟W表示西半球或E表示东半球

I've seen solutions which they directly convert using radians function like below.

我已经看到一些解决方案，它们直接使用弧度函数进行转换，如下所示。

lat = radians(latitude_value) lon = radians(longitude_value)

lat = radians(纬度值) lon = radians(经度值)

This doesn't help me in my scenario.

这在我的情况下并没有帮助我。

答案1

得分: 0

SELECT dms,
       CASE
       WHEN SUBSTR(dms, -1, 1) IN ('N', 'E')
       THEN 1
       ELSE -1
       END
       * ( SUBSTR(dms,  1, 3) ::numeric          -- degrees
         + SUBSTR(dms, -6, 2) ::numeric  /   60   -- minutes
         + SUBSTR(dms, -3, 2) ::numeric / 3600 ) -- seconds
        AS degrees,
       CASE
       WHEN SUBSTR(dms, -1, 1) IN ('N', 'E')
       THEN 1
       ELSE -1
       END
       * ( SUBSTR(dms,  1, 3) ::numeric           -- degrees
         + SUBSTR(dms, -6, 2) ::numeric  /   60   -- minutes
         + SUBSTR(dms, -3, 2) ::numeric  / 3600 ) -- seconds
       * 3.1415926535897932384626433832795 / 180 AS radians
FROM   table_name;

SELECT dms,
       CASE
       WHEN SUBSTR(dms, -1, 1) IN ('N', 'E')
       THEN 1
       ELSE -1
       END
       * ( SUBSTR(dms,  1, 3) ::numeric          -- degrees
         + SUBSTR(dms, -6, 2) ::numeric  /   60   -- minutes
         + SUBSTR(dms, -3, 2) ::numeric / 3600 ) -- seconds
        AS degrees,
       CASE
       WHEN SUBSTR(dms, -1, 1) IN ('N', 'E')
       THEN 1
       ELSE -1
       END
       * ( SUBSTR(dms,  1, 3) ::numeric           -- degrees
         + SUBSTR(dms, -6, 2) ::numeric  /   60   -- minutes
         + SUBSTR(dms, -3, 2) ::numeric  / 3600 ) -- seconds
       * 3.1415926535897932384626433832795 / 180 AS radians
FROM   table_name;