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:

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:

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 comment