mysql转换小数,MySQL将度，分，秒转换为度小数

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?

解决方案

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