Convert latitude and longitude values to radians

Question

I've two columns (Oracle) namely latitude and longitude in format I've mentioned below.

Could anyone help me on how to convert them into radians using Oracle/MySQL or Python?

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

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.

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

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

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

This doesn't help me in my scenario.

Answer 1

In Oracle, you can use:

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

Which, for the sample data:

CREATE TABLE table_name (dms) AS
SELECT '11.08.10N' FROM DUAL UNION ALL
SELECT '084.46.07W' FROM DUAL;

Outputs:

DMS	DEGREES	RADIANS
11.08.10N	11.13611111111111111111111111111111111111	.1943618047568129798301965636353011651234
084.46.07W	-84.76861111111111111111111111111111111111	-1.47949136623153770555853316874267774151

fiddle