4 Replies Latest reply: Dec 18, 2012 10:42 PM by bkazar RSS

    Having trouble converting gml to sdo_geometry

    magicliver
      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
          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
            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
              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
                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