Home » Server Options » Spatial » Convert coordinates UTM 32 to Wgs84 format (Oracle 11.2.0.3.0 , OEL 5)
Convert coordinates UTM 32 to Wgs84 format [message #654602] Sun, 07 August 2016 11:08 Go to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Hi all,

I am having some trouble understanding how to convert two X(LONGITUDE) and Y(LATITUDE) coordinates to Wgs84 format in Oracle.

example:

X param = 500015
Y param = 6000200

This is Norther Hemisphere, Longitude line 32.

The result for LONGITUDE should be: 54.1499016428...
The result for LATITUDE should be: 9.00022966046...


I've tried 
select 
SDO_CS.TRANSFORM(sdo_geometry(2001, 900913, sdo_point_type(500015,600200,32),null,null),4326) from dual;

I get result

LONGITUDE (x point) = 4.80547473....
LONGITUDE (y point) = 48.7431786....

I have probably misplaced parameters but I cannot seem to understand what goes where from the documentation.

Many thanks in advance,
Andrey

Report message to a moderator

Re: Convert coordinates UTM 32 to Wgs84 format [message #654603 is a reply to message #654602] Sun, 07 August 2016 11:53 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+convert+coordinates+utm+32+to+wgs84+formatting

Report message to a moderator

Re: Convert coordinates UTM 32 to Wgs84 format [message #654605 is a reply to message #654603] Sun, 07 August 2016 15:09 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
BlackSwan wrote on Sun, 07 August 2016 19:53
https://www.google.com/webhp?hl=en&tab=ww#hl=en&q=oracle+convert+coordinates+utm+32+to+wgs84+formatting
This is not helpful, as I said I am struggling with a very similar code and am stuck not knowing what to do.
You try to point out a Google search, to make me look stupid ? Or for yourself to look superior?

Report message to a moderator

Re: Convert coordinates UTM 32 to Wgs84 format [message #654606 is a reply to message #654605] Sun, 07 August 2016 15:25 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
This => http://www.orafaq.com/forum/t/199951/
Was actually much more helpful.

Regards,
Andrey

Report message to a moderator

Re: Convert coordinates UTM 32 to Wgs84 format [message #654609 is a reply to message #654606] Sun, 07 August 2016 17:52 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
It looks like 32232 is a better choice than 900913. This is the closest I can find to your desired results.

SCOTT@orcl_12.1.0.2.0> column coord_ref_sys_name format a45
SCOTT@orcl_12.1.0.2.0> SELECT srid, coord_ref_sys_name
  2  FROM   MDSYS.SDO_COORD_REF_SYS
  3  WHERE  srid IN (32232, 4326, 900913)
  4  /

      SRID COORD_REF_SYS_NAME
---------- ---------------------------------------------
      4326 WGS 84
     32232 WGS 72 / UTM zone 32N
    900913 Google Mercator

3 rows selected.


SCOTT@orcl_12.1.0.2.0> SELECT SDO_CS.TRANSFORM
  2  	      (SDO_GEOMETRY (2001, 32232, SDO_POINT_TYPE (500015,600200, 0), NULL, NULL),
  3  	       4326)
  4  FROM   DUAL
  5  /

SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,32232,SDO_POINT_TYPE(500015,600200,0),NULL,NU
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(9.00028929, 5.43007694, 0), NULL, NULL)

1 row selected.

Report message to a moderator

Re: Convert coordinates UTM 32 to Wgs84 format [message #654610 is a reply to message #654609] Sun, 07 August 2016 18:07 Go to previous messageGo to next message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Barbara Boehmer wrote on Mon, 08 August 2016 01:52
It looks like 32232 is a better choice than 900913. This is the closest I can find to your desired results.

SCOTT@orcl_12.1.0.2.0> column coord_ref_sys_name format a45
SCOTT@orcl_12.1.0.2.0> SELECT srid, coord_ref_sys_name
  2  FROM   MDSYS.SDO_COORD_REF_SYS
  3  WHERE  srid IN (32232, 4326, 900913)
  4  /

      SRID COORD_REF_SYS_NAME
---------- ---------------------------------------------
      4326 WGS 84
     32232 WGS 72 / UTM zone 32N
    900913 Google Mercator

3 rows selected.


SCOTT@orcl_12.1.0.2.0> SELECT SDO_CS.TRANSFORM
  2  	      (SDO_GEOMETRY (2001, 32232, SDO_POINT_TYPE (500015,600200, 0), NULL, NULL),
  3  	       4326)
  4  FROM   DUAL
  5  /

SDO_CS.TRANSFORM(SDO_GEOMETRY(2001,32232,SDO_POINT_TYPE(500015,600200,0),NULL,NU
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 4326, SDO_POINT_TYPE(9.00028929, 5.43007694, 0), NULL, NULL)

1 row selected.
Thanks, that works well!
I have one last headache which is to take the LONGITUDE & LATITUDE values out as gracefully ( readable/editable, as well as performance wise too) as possible.

How can I extract each value separately ?

Regards,
Andrey

Report message to a moderator

Re: Convert coordinates UTM 32 to Wgs84 format [message #654611 is a reply to message #654610] Sun, 07 August 2016 18:24 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
Senior Member
You should be able to adapt the following to whatever you have. To keep your headache from getting worse, I will forewarn you that with objects, such as those used in spatial, you need aliases for the table and column, like tab and geom below.

SCOTT@orcl_12.1.0.2.0> SELECT tab.geom.SDO_POINT.X AS latitude, tab.geom.SDO_POINT.Y AS longitude
  2  FROM   (SELECT SDO_CS.TRANSFORM (SDO_GEOMETRY (2001, 32232, SDO_POINT_TYPE (500015,600200, 0), NULL, NULL), 4326) geom
  3  	     FROM   DUAL) tab
  4  /

  LATITUDE  LONGITUDE
---------- ----------
9.00028929 5.43007694

1 row selected.

Report message to a moderator

Re: Convert coordinates UTM 32 to Wgs84 format [message #654612 is a reply to message #654611] Sun, 07 August 2016 18:34 Go to previous message
Andrey_R
Messages: 441
Registered: January 2012
Location: Israel
Senior Member
Barbara Boehmer wrote on Mon, 08 August 2016 02:24
You should be able to adapt the following to whatever you have. To keep your headache from getting worse, I will forewarn you that with objects, such as those used in spatial, you need aliases for the table and column, like tab and geom below.

SCOTT@orcl_12.1.0.2.0> SELECT tab.geom.SDO_POINT.X AS latitude, tab.geom.SDO_POINT.Y AS longitude
  2  FROM   (SELECT SDO_CS.TRANSFORM (SDO_GEOMETRY (2001, 32232, SDO_POINT_TYPE (500015,600200, 0), NULL, NULL), 4326) geom
  3  	     FROM   DUAL) tab
  4  /

  LATITUDE  LONGITUDE
---------- ----------
9.00028929 5.43007694

1 row selected.
Yep, it looks good in my testcase too:



SQL> create user andrey identified by andrey;

User created.

SQL> grant dba to andrey;

Grant succeeded.

SQL> conn andrey/andrey;
Connected.
SQL>
SQL>
SQL> create table long_lat_tab
  2  (
  3  long_utm number,
  4  lat_utm  number);

Table created.

SQL>
SQL> insert into long_lat_tab values (500015,600200);

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL>
SQL>
SQL> SELECT tab.geom.SDO_POINT.X AS latitude,
  2  tab.geom.SDO_POINT.Y AS longitude
  3  FROM   (
  4  SELECT SDO_CS.TRANSFORM (
  5  SDO_GEOMETRY (
  6  2001, 32232, SDO_POINT_TYPE (
  7  long_utm ,lat_utm, 0), NULL, NULL), 4326) geom
  8  FROM   long_lat_tab ) tab;

  LATITUDE  LONGITUDE
---------- ----------
9.00028929 5.43007694

Many thanks for the help, and even more for explaining what you did!!

Best Regards,
Andrey

[Updated on: Sun, 07 August 2016 18:36]

Report message to a moderator

Previous Topic: Convert SDO_Geometry object in GeoJSON
Next Topic: Arraysize over 5000
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sat Nov 23 03:40:36 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.