Question 21790 in Mysql Question posted in
The official MySQL documentation can be found here.

Convert latitude and longitude values to radians – Mysql

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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search