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

# WGS84 DEC to DMS conversion

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