5 Replies Latest reply: Aug 8, 2012 6:56 AM by Simon Greener RSS

    WGS84 DEC to DMS conversion

    953175
      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
          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
            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
              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
                Thank you very much!! This works perfekt! :)
                • 5. Re: WGS84 DEC to DMS conversion
                  Simon Greener
                  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