MySQL convert Degree, Minutes, Seconds to Degree decimal

Question

I have multiple rows of Degrees Minutes Seconds that I need to convert with a query.

36°19'11.46" N = 36.31985

95°36'02.22" W = 95.600617

Each row is going to be different. I've been stuck on this for two days. Is this even possible?

tumick · Answer 1 · 2015-09-05T14:55:16.557

Nice lifehack: reverse problem solution (degree to DMS) using SEC_TO_TIME built-in MySQL function:

CREATE FUNCTION `geocoords`(lon double, lat double) RETURNS varchar(24) CHARSET cp1251
    NO SQL
    DETERMINISTIC
begin    
  declare alon double;
  declare alat double;
  declare slon varchar(12);
  declare slat varchar(12);
  set alon = abs(lon);
  set alat = abs(lat);
  set slon = TIME_FORMAT(SEC_TO_TIME(alon*3600), '%H°%i''%s"');
  set slat = TIME_FORMAT(SEC_TO_TIME(alat*3600), '%H°%i''%s"');
  if lon>0 then
    set slon = concat(slon, 'E');
  elseif lon<0 then  
    set slon = concat(slon, 'W');
  end if;  
  if lat>0 then
    set slat = concat(slat, 'N');
  elseif lat<0 then  
    set slat = concat(slat, 'S');
  end if;  
  return concat(slat, ' ', slon);
end

SELECT geocoords(30.550157546997, 50.344024658203)

50°20'38"N 30°33'01"E

score 1 · Answer 2 · answered Jan 09 '15 at 20:03

1

The following should work:

SELECT D + M/60 + S/3600;

For example, in MySQL:

SELECT 36 + 19/60 + 11.46/3600;

returns: 36.319850

answered Jan 09 '15 at 20:03

mti2935

11,465
3
29
33

How would I right this to pull the specific D M S since each line changes? – Joker327 Jan 09 '15 at 20:17
Without seeing your table structure, it's hard to answer. But, it would probably be something like this: SELECT degrees + minutes/60 + seconds/3600 FROM tablename; – mti2935 Jan 09 '15 at 20:19
What would you need to see to make this easier? Each DMS is in it's on row throughout a Column on the Table. There are approximately 1,000 rows. Each Row has it's own like 36°19'24.52" N then the next row would be like 36°24'12.06" N – Joker327 Jan 09 '15 at 20:47
If all three values are stored as a string in one field, then you would need to use MySQL string functions (http://dev.mysql.com/doc/refman/5.0/en/string-functions.html) to parse the string and extract the individual values for degrees, minutes, and seconds. – mti2935 Jan 09 '15 at 20:57
Ok so I need to break the String into 3 seperate parts as DMS and then apply the math. Ok – Joker327 Jan 09 '15 at 21:11
mti2935 Thanks for you help on this, I figured out my big problem, My table is set up as CHAR not INT, so the math won't work, I'm going to try to go at this another way. – Joker327 Jan 10 '15 at 07:00
Yeah, storing all three values (D, M, S) as a string in one field is going to make this more difficult. Best would be to store each value in a separate field. – mti2935 Jan 10 '15 at 12:43

score 0 · Answer 3 · answered Sep 08 '15 at 13:59

I ended up building this, and it worked flawlessly with what I needed. You will note that I added a C to the numbers, this is to flag them, so if it had already been converted it wouldn't continue to convert.

UPDATE
    MasterTable
SET
    MasterTable.Latitude_A = MasterTable.Latitude,
    MasterTable.Longitude_A = MasterTable.Longitude
WHERE
    ProjectID = 'ProjectAlpha'
    and Sequence = '0'
    and MasterTable.Latitude NOT LIKE '%C%'
    and MasterTable.Longitude NOT LIKE '%C%';

TRUNCATE TABLE gpsconvert;
    INSERT into gpsconvert(gpsconvert.`Account Number`,gpsconvert.Latitude,gpsconvert.Longitude)
SELECT
        MasterTable.AccountNumber,
        MasterTable.Latitude,
        MasterTable.Longitude
FROM
    MasterTable
WHERE
    MasterTable.ProjectID = 'ProjectAlpha'
    and MasterTable.Sequence = '0'
    and MasterTable.Latitude NOT LIKE '%c%'
    and MasterTable.Longitude NOT LIKE '%c%'
    and MasterTable.Latitude <> ''
    and MasterTable.Longitude <> '';

UPDATE
    gpsconvert
SET
    gpsconvert.LatDegree = LEFT(gpsconvert.Latitude,2),
    gpsconvert.LatMinutes = SUBSTRING(gpsconvert.Latitude,-7,2),
    gpsconvert.LatSeconds = SUBSTRING(gpsconvert.latitude,-5,5),
    gpsconvert.LatDecimal = gpsconvert.LatDegree + (gpsconvert.LatMinutes/60) + (gpsconvert.LatSeconds/3600),
    gpsconvert.LongDegree = LEFT(gpsconvert.Longitude,2),
    gpsconvert.LongMinutes = SUBSTRING(gpsconvert.Longitude,-7,2),
    gpsconvert.LongSeconds = SUBSTRING(gpsconvert.Longitude,-5,5),
    gpsconvert.LongDecimal = gpsconvert.LongDegree +       (gpsconvert.LongMinutes/60) + (gpsconvert.LongSeconds/3600);

UPDATE
    MasterTable
INNER JOIN
    gpsconvert on gpsconvert.`Account Number` = MasterTable.AccountNumber
SET
    MasterTable.Latitude = CONCAT(gpsconvert.LatDecimal,'c'),
    MasterTable.Longitude = CONCAT(gpsconvert.LongDecimal,'c')
WHERE
    MasterTable.ProjectID = 'ProjectAlpha'
    and MasterTable.Sequence = '0'
    and MasterTable.AccountNumber = gpsconvert.`Account Number`

score 0 · Answer 4 · answered May 08 '21 at 00:54

This converts strings in the form 21°48'6.384" to decimal

CREATE FUNCTION database.DMS_TO_DECIMAL(dms varchar(20))
RETURNS decimal(12,9)
BEGIN
    DECLARE deg decimal(12,9);
    DECLARE mins decimal(12,9);
    DECLARE secs decimal(12,9);

    SET deg = CAST(  SUBSTRING_INDEX(dms, '°', 1) AS decimal(12,9));
    SET mins = CAST( (SUBSTR(dms, POSITION('°' IN dms) + 1, POSITION("'" IN dms) -  POSITION('°' IN dms) - 1)) AS decimal(12,9));
    SET secs = CAST( (SUBSTR(dms, POSITION("'" IN dms) + 1, POSITION("\"" IN dms) -  POSITION("'" IN dms) - 1)) AS decimal(12,9));

    RETURN deg + mins/60 + secs/3600;
END

score 0 · Answer 5 · answered Jun 30 '21 at 03:56

Add orientation (W/S):

DROP FUNCTION IF EXISTS `DmsToDecimal`;
DELIMITER $$

CREATE FUNCTION `DmsToDecimal`(`dms` VARCHAR(50)) 
   RETURNS DECIMAL(12,9) 
   DETERMINISTIC 
   NO SQL 
   SQL SECURITY INVOKER 
BEGIN
    DECLARE deg decimal(12,9);
    DECLARE mins decimal(12,9);
    DECLARE secs decimal(12,9);
    DECLARE sign integer;
  
    SET dms = UPPER(TRIM(REPLACE(dms, ",", ".")));
  
    SET deg = CAST(  SUBSTRING_INDEX(dms, '°', 1) AS decimal(12,9));
    SET mins = CAST( (SUBSTR(dms, POSITION('°' IN dms) + 1, POSITION("'" IN dms) -  POSITION("°" IN dms) - 1)) AS decimal(12,9));
    SET secs = CAST( (SUBSTR(dms, POSITION("'" IN dms) + 1, POSITION("""" IN dms) -  POSITION("'" IN dms) - 1)) AS decimal(12,9));

    SET sign = 1 - 2 * (RIGHT(dms, 1) = "W" OR RIGHT(dms, 1) = "S";

    RETURN  sign * (deg + mins / 60 + secs / 3600);
END$$
DELIMITER ;

MySQL convert Degree, Minutes, Seconds to Degree decimal

5 Answers5

Linked