This discussion is archived
5 Replies Latest reply: Aug 8, 2012 4:56 AM by Simon Greener RSS

WGS84 DEC to DMS conversion

953175 Newbie
Currently Being Moderated
Hi,

how to convert my WGS84 DEC Coordinates into DMS? (I know the formula) is there a Oracle/sql funktion to do that to show the result from 15,8515065952945 to 15°41'40.405'' ?
So that in the ResultTable 15°41'40.405''
  • 1. Re: WGS84 DEC to DMS conversion
    _jum Journeyer
    Currently Being Moderated
    Don't know if there is a function in ORACLE, here is a SELECT as example:
    WITH data AS
      (SELECT 15.8515065952945 deg FROM dual UNION ALL
       SELECT 15.851507            FROM dual UNION ALL
       SELECT 15.50                FROM dual UNION ALL
       SELECT 15.75                FROM dual)
    SELECT FLOOR(deg) deg,
           FLOOR((deg-floor(deg))*60) mi,
           MOD((deg-floor(deg))*3600,60) ss FROM data; 
    
    deg  mi      ss
    ------------------------------------
    15     51     5,4237430602
    15     51     5,4252
    15     30     0
    15     45     0
    BTW your calculation seems not ok, test online
  • 2. Re: WGS84 DEC to DMS conversion
    953175 Newbie
    Currently Being Moderated
    thx, but i don´t see how to use this statement wihtout having the values to put in directly..


    I have a select like this:
    select mdsys.sdo_cs.transform(mdsys.sdo_geometry(3001,4255 ,mdsys.sdo_point_type(v.x,v.y,NULL),NULL,NULL),4326) from testtable O, TABLE(SDO_UTIL.GETVERTICES(O.G3E_GEOMETRY )) v;
    The Result is a table with x and y coordinates in DEC.

    I like to modify this statement to display the coordinates in DMS.

    And I´m completely lost in doing this......
  • 3. Re: WGS84 DEC to DMS conversion
    _jum Journeyer
    Currently Being Moderated
    Here an example for latitude:
    DROP   TABLE testtable; 
    
    CREATE TABLE testtable 
     ( g3e_fid      NUMBER,
       g3e_geometry mdsys.sdo_geometry);
    
    INSERT INTO testtable VALUES 
      (1000, sdo_geometry(3002,4255, NULL, SDO_ELEM_INFO_ARRAY(1,2,1),SDO_ORDINATE_ARRAY(50.2,15.4, 100, 50.21, 15.42, 105)));
    
    COMMIT; 
       
    SELECT g3e_fid, point_id,
           lat,
           FLOOR(lat) deg,
           FLOOR((lat-floor(lat))*60) mi,
           MOD((lat-floor(lat))*3600,60) ss
    FROM 
    (
    SELECT g3e_fid, point_id,
           treat(transgeom as sdo_geometry).sdo_point.x lat,
           treat(transgeom as sdo_geometry).sdo_point.y lon
      FROM
    (SELECT g3e_fid, id point_id,
            mdsys.sdo_cs.transform(mdsys.sdo_geometry(3001,4255 ,mdsys.sdo_point_type(v.x,v.y,NULL),NULL,NULL),4326) transgeom
       FROM testtable o, TABLE(SDO_UTIL.GETVERTICES(o.G3E_GEOMETRY )) v) 
    );
    
    g3e_fid id      lat                   deg   mi     ss
    -------------------------------------------------------------------------------
    1000     1     50,2010595131526     50      12    3,81424734936
    1000     2     50,211060238389      50      12   39,8168582004
    The treat function here is a bit tricky, I found no way to get sdo_point.x direct from transgeom.
    May be one here is cleverer than me ;-)
  • 4. Re: WGS84 DEC to DMS conversion
    953175 Newbie
    Currently Being Moderated
    Thank you very much!! This works perfekt! :)
  • 5. Re: WGS84 DEC to DMS conversion
    Simon Greener Journeyer
    Currently Being Moderated
    If you want to encapsulate the calculations behind a function, do this:
    Create or Replace Function DD2DMS( dDecDeg in Number,
                         pDegree in NChar default '°',
                         pMinute in NChar default '''',
                         pSecond in NChar default '"' )
    Return varchar2
    IS
        sDegreeSymbol NCHAR(1) := NVL(pDegree,'°');
        sMinuteSymbol NCHAR(1) := NVL(pMinute,'''');
        sSecondSymbol NCHAR(1) := NVL(pSecond,'"');
        iDeg          Integer;
        iMin          Integer;
        dSec          Number;
    BEGIN
        iDeg := Trunc(dDecDeg);
        iMin := Trunc((Abs(dDecDeg) - Abs(iDeg)) * 60);
        dSec := Round((((Abs(dDecDeg) - Abs(iDeg)) * 60) - iMin) * 60, 3);
        Return TO_CHAR(iDeg) || sDegreeSymbol || TO_CHAR(iMin) || sMinuteSymbol || TO_CHAR(dSec) || sSecondSymbol;
    End DD2DMS;
    /
    
    select DD2DMS(15.8515065952945) as DMS from dual;
    DMS
    ---
    15°51'5.424"
    If this is useful, award points.

    regards
    Simon

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points