Convert Decimal Degrees to Degree-Minute-Second in MySQL

It is common to use PHP (or ASP, JavaScript, etc…) to convert DD to DMS in web projects. However, when the coordinates are stored in a database, it is also possible to do the conversion in the database itself when the data is queried, thus simplifying the code of your webpages.

Here is how to create a function in MySQL to convert Decimal Degrees to Degree-Minute-Second:

DELIMITER $

CREATE FUNCTION `f_convertDDtoDMS`( dd DECIMAL(10,7), is_longitude BOOL) RETURNS VARCHAR(14) CHARSET utf8
BEGIN
/*
Description: MySQL function to convert decimal degrees to Degree-Minute-Second (DMS)
Author: Michaël Niessen (2012)
Website: http://AssemblySys.com

If you find this script useful, you can show your
appreciation by getting Michaël a cup of coffee ;)
https://ko-fi.com/assemblysys

As long as this notice (including author name and details) is included and
UNALTERED, this code can be used and distributed freely.
*/
    DECLARE c CHAR(1); 
    DECLARE dms VARCHAR(14);
    DECLARE direction CHAR(1) DEFAULT 'N'; -- N = north (default value)
    DECLARE degree SMALLINT(3);
    DECLARE min_float DECIMAL(10,7);
    DECLARE minutes TINYINT(3);
    DECLARE sec_float DECIMAL(10,7);
    DECLARE seconds TINYINT(3);

    -- check if value is negative and set direction
    IF dd = 0 THEN
        SET direction = ''; -- latitude is neither north nor south, longitude is neither west nor east
    ELSEIF dd < 0 THEN
        SET dd = -dd;
        IF is_longitude IS TRUE THEN
            SET direction = 'W'; -- W = west
        ELSE
            SET direction = 'S'; -- S = south
        END IF;
    ELSEIF is_longitude IS TRUE THEN
        SET direction = 'E'; -- E = east
    END IF;

    -- get DMS
    SET degree = FLOOR(dd);
    SET min_float = (dd - degree) * 60;
    SET minutes = FLOOR(min_float);
    SET sec_float = (min_float - minutes) * 60;
    SET seconds = ROUND(sec_float,0);

    -- adjust seconds and minutes after rounding (they cannot be equal to 60)
    IF seconds = 60 THEN
        SET seconds = 0;
        SET minutes = minutes + 1;
    END IF;

    IF minutes = 60 THEN
        SET minutes = 0;
        SET degree = degree + 1;
    END IF;

    -- create the DMS output string
    SET dms = CONCAT(degree , '° ', minutes, "' ", seconds, '" ', direction);
    RETURN dms;
END

This function will be saved in the active schema (visible along with the routines, in MySQL Workbench) and you can use it as any other MySQL function, as follows:

f_convertDDtoDMS(DD, is_longitude)
DD: Coordinate in decimal degrees.
is_longitude: true if the value represents the longitude; set it to false if the value is a latitude.

Here is a sample query and output:

SELECT
	f_convertDDtoDMS(45.5423452, FALSE) AS lat1,
	f_convertDDtoDMS(145.77, TRUE) AS long1,
	f_convertDDtoDMS('-36.5465783', FALSE) AS lat2, -- (single or double) quotes are optional
	f_convertDDtoDMS(-73.0065942, TRUE) AS long2;

#-----------------------------------------------------------------
#| lat1          | long1          | lat2          | long2        |
#-----------------------------------------------------------------
#| 45° 32' 32" N | 145° 46' 12" E | 36° 32' 48" S | 73° 0' 24" W |
#-----------------------------------------------------------------

You can also call the function from another schema just as you would reference tables, like this:
db_name.f_convertDDtoDMS(DD, is_longitude)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.