Converts Degrees/Minutes/Seconds to Decimals using SQL

convert dms to decimal
convert degrees, minutes seconds to decimal degrees excel
convert latitude minutes to decimals
degrees, minutes, seconds map
convert degrees decimal minutes to decimal degrees
convert coordinates to decimal
fcc dms conversion
convert surveyor units to decimal degrees

Is there any SQL script that converts Degrees/Minutes/Seconds (saved in Column A) to Decimals (to be saved in new Column B)? I googled for existing solutions, but all examples refer to Decimals->Deg/Min/Sec. I appreciate any example, because I'm not strong in SQL scripting.

The following seems to work:

SELECT DMS, DEGREES + (MINUTES / 60) + (SECONDS / (60 * 60)) AS DECIMAL_DEGREES
  FROM (SELECT DMS,
               TRUNC(DMS) AS DEGREES,
               TRUNC((DMS - TRUNC(DMS)) * 100) AS MINUTES,
               ((DMS * 100) - TRUNC(DMS * 100)) * 100 AS SECONDS
          FROM TEST_DMS)

or, if you prefer it all as one expression:

SELECT DMS,
       TRUNC(DMS) +
         (TRUNC((DMS - TRUNC(DMS)) * 100) / 60) +
         (((DMS * 100) - TRUNC(DMS * 100)) * 100) / (60 * 60) AS DECIMAL_DEGREES
  FROM TEST_DMS

At the very least these agree with the results returned by my trusty, rusty HP-41C. :-)

Share and enjoy.

decimal degrees from degrees minutes seconds values sql server 2008 2012 cogo. Summary. This article presents a function that computes a  If degrees, minutes and seconds would be saved in separate columns, then I could simply divide minutes by 60 and seconds by 3600. – Gusgus Aug 29 '12 at 20:44 1

I worked on a project...I used script to solve issue on DMS conversion on oracle db

SELECT 
CASE
WHEN LENGTH(longitude)=7 THEN ROUND (cast(substr(longitude, 1, 1) as numeric(10,5)) +  cast(substr(longitude, 3, 2) as numeric(10,5))/60 +  cast(substr(longitude, 6, 2) as numeric(10,5))/3600,4) 
WHEN LENGTH(longitude)=8  THEN ROUND (cast(substr(longitude, 1, 2) as numeric(10,5)) +  cast(substr(longitude, 4, 2) as numeric(10,5))/60 +  cast(substr(longitude, 7, 2) as numeric(10,5))/3600,4)
ELSE
ROUND (CAST (longitude as Numeric(10,5)),4)
END AS LONGITUDE,
CASE
WHEN LENGTH(latitude)=7 THEN ROUND (cast(substr(latitude, 1, 1) as numeric(10,5)) +  cast(substr(latitude, 3, 2) as numeric(10,5))/60 +  cast(substr(latitude, 6, 2) as numeric(10,5))/3600,4) 
WHEN LENGTH(latitude)=8  THEN ROUND (cast(substr(latitude, 1, 2) as numeric(10,5)) +  cast(substr(latitude, 4, 2) as numeric(10,5))/60 +  cast(substr(latitude, 7, 2) as numeric(10,5))/3600,4)
ELSE
ROUND (CAST (latitude as Numeric(10,5)),4)
END AS LATITUDE

I need to convert these to Decimal, so I can then convert to SQL Geography datatype. Or better yet, directly from DMS to Geography. To calculate decimal degrees, we use the DMS to decimal degree formula below: Decimal Degrees = degrees + (minutes/60) + (seconds/3600) DD = d + (min/60) + (sec/3600)

I use the below to do the same

declare @cord  varchar (max)

set  @cord=  '2504N 05534E'

select

case when  CHARINDEX(' ', @cord,1)>0 then
case when  right(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', @cord,1)-1))),1) = 'N' then
(left(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', @cord,1)-1))),2))
+
(SUBSTRING(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', @cord,1)-1))),3,2)/60.0)
else
-1*(
(left(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', @cord,1)-1))),2))
+
(SUBSTRING(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', @cord,1)-1))),3,2)/60.0)
)
end
end latitude
,case when  CHARINDEX(' ', @cord,1)>0 then
 case when right(LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', @cord,1),100))),1) = 'E' then
(left(LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', @cord,1),100))),3) )
+
(SUBSTRING( LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', @cord,1),100))),4 
,2)/60.0)
else
-1*(
(left(LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', @cord,1),100))),3) )
+
(SUBSTRING( LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', @cord,1),100))),4 
,2)/60.0)
 )
 end
 end  longitude

,geography::Point(
     case when  CHARINDEX(' ', @cord,1)>0 then
        case when  right(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', 
 @cord,1)-1))),1) = 'N' then
        (left(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', @cord,1)-1))),2))
        +
        (SUBSTRING(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', 
 @cord,1)-1))),3,2)/60.0)
        else
        -1*(
        (left(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', @cord,1)-1))),2))
        +
        (SUBSTRING(LTrIM(RTRIM(left( @cord, CHARINDEX(' ', 
 @cord,1)-1))),3,2)/60.0)
        )
        end
     end 
     ,case when  CHARINDEX(' ', @cord,1)>0 then
        case when right(LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', 
 @cord,1),100))),1) = 'E' then
        (left(LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', 
 @cord,1),100))),3) )
        +
        (SUBSTRING( LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', 
 @cord,1),100))),4 ,2)/60.0)
        else
        -1*(
        (left(LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', 
 @cord,1),100))),3) )
        +
        (SUBSTRING( LTrIM(RTRIM(SUBSTRING( @cord, CHARINDEX(' ', 
 @cord,1),100))),4 ,2)/60.0)
        )
        end
    end  
    , 4326)  
      as geography_Point    

How do you convert decimal degrees to degrees minutes seconds? Degrees to degrees,minutes,seconds converter How to convert degrees,minutes,seconds to decimal degrees. One degree is equal to 60 minutes and equal to 3600 seconds: 1° = 60' = 3600" One minute is equal to 1/60 degrees: 1' = (1/60)° = 0.01666667° One second is equal to 1/3600 degrees: 1" = (1/3600)° = 2.77778e-4° = 0.000277778°

Based on @user2621989 answer here is tested SQL Server version of his query:

DECLARE @longitude varchar(max) = '50°03''10"'
DECLARE @latitude varchar(max) = '15°00''50"'

SELECT 
CASE
WHEN LEN(@longitude)=8 THEN ROUND (cast(SUBSTRING(@longitude, 1, 1) as numeric(9,6)) +  cast(SUBSTRING(@longitude, 3, 2) as numeric(9,6))/60 +  cast(SUBSTRING(@longitude, 6, 2) as numeric(9,6))/3600,4) 
WHEN LEN(@longitude)=9  THEN ROUND (cast(SUBSTRING(@longitude, 1, 2) as numeric(9,6)) +  cast(SUBSTRING(@longitude, 4, 2) as numeric(9,6))/60 +  cast(SUBSTRING(@longitude, 7, 2) as numeric(9,6))/3600,4)
ELSE
ROUND (CAST (@longitude as numeric(9,6)),4)
END AS longitude,
CASE
WHEN LEN(@latitude)=8 THEN ROUND (cast(SUBSTRING(@latitude, 1, 1) as numeric(9,6)) +  cast(SUBSTRING(@latitude, 3, 2) as numeric(9,6))/60 +  cast(SUBSTRING(@latitude, 6, 2) as numeric(9,6))/3600,4) 
WHEN LEN(@latitude)=9  THEN ROUND (cast(SUBSTRING(@latitude, 1, 2) as numeric(9,6)) +  cast(SUBSTRING(@latitude, 4, 2) as numeric(9,6))/60 +  cast(SUBSTRING(@latitude, 7, 2) as numeric(9,6))/3600,4)
ELSE
ROUND (CAST (@latitude as numeric(9,6)),4)
END AS latitude

Returns

longitude       latitude
50.05280000000  15.01390000000

How do you convert decimal degrees to degrees minutes seconds in Arcgis? Degrees,minutes, seconds to degrees converter How to convert decimal degrees to degrees,minutes,seconds. One degree (°) is equal to 60 minutes (') and equal to 3600 seconds ("): 1° = 60' = 3600" The integer degrees (d) are equal to the integer part of the decimal degrees (dd): d = integer(dd)

I recently had to create a SQL function to convert latitudes and longitudes from one format to another. The source fields were varchar for each  Degrees Minutes Seconds to Decimal Degrees conversion in Excel. The easiest and quickest way. - Duration: 5:14. Voice of People Today 15,124 views. 5:14.

In the instructions below, you will convert one field in a table of latitude or longitude values in degrees, minutes, and seconds to decimal degrees using the Field  This is the snippet Convert Decimal Degrees to Degrees in Minutes Seconds Format on FreeVBCode. The FreeVBCode site provides free Visual Basic code, examples, snippets, and articles on a variety of other topics as well.

SQL Server 2000: Tested. SQL Server 2005: Tested Description. Converts a GPS decimal format lat/long to a degree, minute, second string repesentation Longitude has to be in interval <-180;180>') IF @isLongitude = 0  Convert Degrees Minutes Seconds to Decimal Degrees or Geography Hopefully somebody already has a nice elegant method for this, and is willing to share. My DB is storing Latitude and Longitude in Degrees/Minutes/Seconds format, in a single string (one for Lat, one for Lon) .

Comments
  • Wait, how are those three values saved in one column? And presumably this is a math problem first, not an SQL one.
  • @X-Zero: Unfortunately,they are saved in one column as e.g. 41.182973. If degrees, minutes and seconds would be saved in separate columns, then I could simply divide minutes by 60 and seconds by 3600.
  • How is 41.182973 degrees/minutes/seconds? That's what I would expect to be in decimal.
  • @Stark - Agreed, that appears to be decimals. Especially because there are only 60 seconds in a minute (not 73)...
  • @stark: No,it was originally written as 41 18' 29.73. Then this was combined into a single column in SQL table. So, it seems to be confusing, but it's deg/min/sec.