This discussion is archived
4 Replies Latest reply: Dec 18, 2012 8:42 PM by bkazar RSS

Having trouble converting gml to sdo_geometry

magicliver Newbie
Currently Being Moderated
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.
  • 1. Re: Having trouble converting gml to sdo_geometry
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi magicliver,

    Your posList tag should have the srsDimension="2" attribute. If you add that, you get correct sdo_geometry returned.

    I'm not entirely sure if that is according to OGC spec or if it is a bug in the function, but that's what is missing (If you use the reverse function on a polygon-geometry you can see that happening).
    select sdo_util.to_gml311geometry(t.geometry) from testgeom t where rownum=1;
    gives me
    <gml:Polygon srsName="SDO:28992" xmlns:gml="http://www.opengis.net/gml">
      <gml:exterior>
        <gml:LinearRing>
          <gml:posList srsDimension="2">0.0 1.0 4.0 1.0 4.0 4.0 1.0 4.0 1.0 0.0 2.0 0.0 2.0 3.0 3.0 3.0 3.0 2.0 0.0 2.0 0.0 1.0 </gml:posList>
        </gml:LinearRing>
      </gml:exterior>
    </gml:Polygon>
  • 2. Re: Having trouble converting gml to sdo_geometry
    magicliver Newbie
    Currently Being Moderated
    Hi Stefan,

    thanks for the help. Apologies for taking a long time to get back to you, I was put on higher priority work for a while plus the server that held the database I had this issue was brought down and moved over a period.
    I have requested the provider of the source GML to alter their output to put the srsDimension in the poslist element and this now comes out fine. I also requested that the poslist ordinates are provided in lat/long rather than long/lat order and that was the simplest method of removing my issue with reversing the ordinates.

    Thanks again.
  • 3. Re: Having trouble converting gml to sdo_geometry
    Stefan Jager Journeyer
    Currently Being Moderated
    Hi Magic,

    I'm still not a 100% sure where the error is in this case though. According to the documentation on gml 3.1.1 the srsDimension is an optional value, in which case the dimension should be taken from the larger context of the geometry. This would indicate a bug in the function, but it could also be that the function that creates the gml that you are trying to import is neglecting to set the coordinate system properly.

    Maybe somebody from Oracle should look into this, to make sure that it isn't something in the function.

    Glad to be of help,
    Stefan
  • 4. Re: Having trouble converting gml to sdo_geometry
    bkazar Journeyer
    Currently Being Moderated
    Hello,-
    We will look at this asap.
    Thanks

    Looking into this matter and it results in gtype 1003 in 11.2.0.3 with the following input:
     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;
     
      2    3    4    5    6    7    8    9   10   11  
    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))
    Filing a bug for this problem for 11.2.0.3.


    Btw, if you modify posList element by adding srsDimension="2", it results in correct gtype:
     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 srsDimension="2">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(2003, 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))
    Thanks

    Edited by: bkazar on Dec 18, 2012 8:08 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points