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.

CodePudding user response：

It's just basic string manipulation and math. In mysql, for latitude:

radians((substring_index(lat,'.',1)
     substring_index(substring_index(lat,'.',2),'.',-1)/60
     trim(trailing 'N' from trim(trailing 'S' from substring_index(lat,'.',-1)))/3600
)*(case substr(lat from -1) when 'S' then -1 else 1 end))

Similarly, for longitude:

radians((substring_index(lon,'.',1)
     substring_index(substring_index(lon,'.',2),'.',-1)/60
     trim(trailing 'E' from trim(trailing 'W' from substring_index(lon,'.',-1)))/3600
)*(case substr(lon from -1) when 'W' then -1 else 1 end))

fiddle

CodePudding user response：

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:

fiddle