Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Having trouble converting gml to sdo_geometry

magicliverOct 29 2012 — edited Dec 18 2012
Hi, I am using Oracle 11.2.0.3 on Windows Server 2003. I receive XML data containing GML data all of which is used to poulate a relational table in some code I am writing. First though I am just attempting to try to add the GML data into a SDO_GEOMETRY type fields but I am encountering a couple of issues.
The polygon below should be over Malaysia
select sdo_util.from_gml311geometry (
'<gml:Polygon xmlns:gml="http://www.opengis.net/gml/3.2" gml:id="bp1"
  srsName="EPSG:4326" srsDimension="2">
<gml:exterior>
<gml:LinearRing>
<gml:posList>4.5 115.25 4.25 115.25 4.25 115.5 4.5 115.5 4.5 115.25</gml:posList>
</gml:LinearRing>
</gml:exterior>
</gml:Polygon>'
) as result
from dual;

RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(1003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(4.5, 115.25, 4.25, 115.25, 4.25, 115.5, 4.5, 115.5, 4.5, 115.25))
The first issue I can see here is that the Gtype is returned as a one dimensional polygon!? How can that be, is there a bug here?
Then although the EPSG:4326 is equivalent to wgs84 I may potentially receive other CRS, so I need to add transform to 8307
select SDO_CS.TRANSFORM (
sdo_util.from_gml311geometry (
'<gml:Polygon xmlns:gml="http://www.opengis.net/gml/3.2" gml:id="bp1"
  srsName="EPSG:4326" srsDimension="2">
<gml:exterior>
<gml:LinearRing>
<gml:posList>4.5 115.25 4.25 115.25 4.25 115.5 4.5 115.5 4.5 115.25</gml:posList>
</gml:LinearRing>
</gml:exterior>
</gml:Polygon>'
              ), 8307
) as result
from dual;

RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(1003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(-175.5, 64.75, -175.75, 64.75, -175.75, 64.5, -175.5, 64.5, -175.5, 64.75))
The GML I receive is lat/long which has caused the transform to not work correctly. If I reverse the ordinates to long/lat I get the following outputs:
select sdo_util.from_gml311geometry (
'<gml:Polygon xmlns:gml="http://www.opengis.net/gml/3.2" gml:id="bp1"
  srsName="EPSG:4326" srsDimension="2">
<gml:exterior>
<gml:LinearRing>
<gml:posList>115.25 4.5 115.25 4.25 115.5 4.25 115.5 4.5 115.25 4.5</gml:posList>
</gml:LinearRing>
</gml:exterior>
</gml:Polygon>'
) as result
from dual;

RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(1003, 4326, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(115.25, 4.5, 115.25, 4.25, 115.5, 4.25, 115.5, 4.5, 115.25, 4.5))

select SDO_CS.TRANSFORM (
sdo_util.from_gml311geometry (
'<gml:Polygon xmlns:gml="http://www.opengis.net/gml/3.2" gml:id="bp1"
  srsName="EPSG:4326" srsDimension="2">
<gml:exterior>
<gml:LinearRing>
<gml:posList>115.25 4.5 115.25 4.25 115.5 4.25 115.5 4.5 115.25 4.5</gml:posList>
</gml:LinearRing>
</gml:exterior>
</gml:Polygon>'
              ), 8307
) as result
from dual;

RESULT(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(1003, 8307, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
AY(115.25, 4.5, 115.25, 4.25, 115.5, 4.25, 115.5, 4.5, 115.25, 4.5))
So I have two problems. Firstly I should have a gtype of 2003 and I am not sure why I cannot get this. Secondly I need to reverse the ordinate pairs from lat/long to long/lat and potentially I could write some function to deal with this but would rather know if there is any functions or utilities or if there are any parameters already there to achieve this. I have spent time searching documentation, internet and forum looking for people with this same issue without any luck.
Many thanks in advance.
This post has been answered by [Deleted User] on Oct 30 2012
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 9 2013
Added on Oct 29 2012
4 comments
982 views