Top 5 Recent Articles

ARTICLES CATEGORIES

COGO: Convert Degrees, Minutes and Seconds values to Decimal Degrees

Sometimes data is provided to spatial databases via spreadsheets or other database tables in the form of degrees, minutes and seconds values. For example, data may come like this:

Degree Minute Seconds
45 30 30.0

Which then need converting to a single decimal degree floating point value for use as an ordinate in a spatial coodinate (eg a Point). The following function, DMS2DD, converted from my free Oracle COGO package, is presented for us by SQL Server 2008 Spatial users.

Note that I have a schema called cogo in which I create functions like this. You can use anything you like.

 /** ----------------------------------------------------------------------------------------
   * @function  : DMS2DD
   * @precis     : Function computes a decimal degree floating point number from individual
   *               degrees, minutes and seconds values.
   * @version    : 1.0
   * @usage      : Function DMS2DD(@p_dDeg  Int,
   *                                @p_dMin  Int,
   *                                @p_dSec  Float )
   *                RETURNS Float
   *               eg SELECT DMS2DD(45,30,30)
   * @param      : p_dDeg       : Non-NULL degree value (0-360)
   * @paramType  : p_dDeg       : Int
   * @param      : p_dMin       : Non-NULL degree value (0-60)
   * @paramType  : p_dMin       : Int
   * @param      : p_dSec       : Non-NULL seconds value (0-60)
   * @paramType  : p_dSec       : FLOAT
   * @return    : DD Value     : Decimal degrees equivalent value.
   * @returnType : DD Value     : Float
   * @history    : Simon Greener - Apr 2007 - Original coding.
   * @history    : Simon Greener - May 2011 - Ported to TSQL
   * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
   *               (http://creativecommons.org/licenses/by-sa/2.5/au/)
 **/
 CREATE FUNCTION cogo.DMS2DD(@p_dDeg INT,
                               @p_dMin INT,
                               @p_dSec FLOAT)
 RETURNS FLOAT
 AS
 BEGIN
     DECLARE
        @dDD FLOAT;
     BEGIN
        IF ( @p_dDeg IS NULL OR
             @p_dMin IS NULL OR
             @p_dSec IS NULL )
           RETURN NULL;
        SET @dDD = ABS(@p_dDeg) + @p_dMin / 60.0 + @p_dSec / 3600.0;
        RETURN SIGN(@p_dDeg) * @dDD;
     END;
 END
 GO

Some test cases as examples.

 SELECT cogo.DMS2DD(-44,10,50) AS DD
 UNION
 SELECT cogo.DMS2DD(45,30,30) AS DD;

Result.

DD
-44.0138888888889
45.0083333333333

I hope this is helpful to someone.