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 Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Barbara Boehmer wrote on Mon, 08 August 2016 01:52It 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
|
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
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 Go to previous message](/forum/theme/orafaq/images/up.png) |
Andrey_R
Messages: 441 Registered: January 2012 Location: Israel
|
Senior Member |
|
|
Barbara Boehmer wrote on Mon, 08 August 2016 02:24You 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
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:20:17 CDT 2024
|