SpatialDB Advisor

Convert GML to SDO_Geometry in Oracle 10gR2

I received this email today from someone in Europe.

Now I’m into XML/GML and I’ve worked out a mechanism to convert daily mutations into XML, with a GML tag. The reverse mechanism also works: convert the XML back to relational form. Problem is however that I’ve build it in Oracle 11g. When I tried @work, where they run 10.2g the reverse mechanism failed. Reason is that 10.2g does not have a from_gmlgeometry function. Now I’m not sure how to convert the GML part back to sdo_geometry.

Do you have any suggestions? I need some sort of replacement for the from_gmlgeometry function.

It’s amazing how time flies. I wrote such a function 3 years ago but never wrote an article on how to implement it! I suppose it is because we tend to have grandiose ideas that circumscribe one’s thinking or activity: in my case it is a bunch of Java functions that I always meant to put together into a consistent package of useful functions but it never happened! I suppose it was my article Calling the Oracle Spatial shapefile loader from within the Oracle database itself that reminded me that one could break up and publish snippets from the larger whole without too much pain!

Anyway, enough of that. Creating a solution is reasonably easy as Oracle already has, in “oracle.spatial.util.GML” the required method: GML.fromNodeToGeometry(). So, first, we need to create an appropriate Java class. I have written two methods for this class: one that takes in a string (varchar2) and one that takes in a CLOB.

package com.spatialdbadvisor.gis.oracle;

// Java imports ...
import java.io.IOException;
import java.sql.DriverManager;
import java.sql.SQLException;
// 10gR2 imports...
import oracle.spatial.geometry.JGeometry;
import oracle.spatial.util.GML;
import oracle.sql.STRUCT;
import oracle.xml.parser.v2.DOMParser;
import oracle.xml.parser.v2.XMLDocument;
// W3C imports ...
import org.w3c.dom.Node;

  /** ----------------------------------------------------------------------------------------
  * class      : GML
  * precis     : Implements two methods for converting from GML to SDO_Geometry for users of Oracle 10gR2.
  * version    : 1.0
  * history    : Simon Greener - June 2006 - Original coding.
  * history    : Simon Greener - October 2009 - Creation of separate Class with renamed methods.
  * copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License. (http://creativecommons.org/licenses/by-sa/2.5/au/)
  **/

public class gml
{

    public static oracle.sql.STRUCT toGeometry(String aGeom)
        throws Exception
    {    
      STRUCT jstruct = null;
      try 
      { 
        DOMParser parser = new DOMParser();
        parser.parse(new java.io.ByteArrayInputStream(aGeom.getBytes()));
        XMLDocument doc  = parser.getDocument();
        Node nodeGeom    = doc.getFirstChild();
        JGeometry jgeom  = GML.fromNodeToGeometry(nodeGeom);
        jstruct = JGeometry.store(jgeom,DriverManager.getConnection("jdbc:default:connection:"));
      }
      catch (IOException ioe) {
          System.err.println(ioe.getMessage());
          System.err.println(ioe.toString());
      } catch (SQLException sqle) {
          System.err.println(sqle.getMessage());
          System.err.println(sqle.toString());
      } catch (Exception e) {
          System.err.println(e.getMessage());
          System.err.println(e.toString());
      }
      return jstruct;
    }
  
    public static oracle.sql.STRUCT toGeometry(oracle.sql.CLOB aGeom)
        throws Exception
    {    
      STRUCT jstruct = null;
      try 
      { 
        DOMParser parser = new DOMParser();
        parser.parse(aGeom.getCharacterStream());
        XMLDocument doc  = parser.getDocument();
        Node nodeGeom    = doc.getFirstChild();
        JGeometry jgeom  = GML.fromNodeToGeometry(nodeGeom);
        jstruct          = JGeometry.store(jgeom,DriverManager.getConnection("jdbc:default:connection:"));
      }
      catch (IOException ioe) {
          System.err.println(ioe.getMessage());
          System.err.println(ioe.toString());
      } catch (SQLException sqle) {
          System.err.println(sqle.getMessage());
          System.err.println(sqle.toString());
      } catch (Exception e) {
          System.err.println(e.getMessage());
          System.err.println(e.toString());
      }
      return jstruct;
    }

}

We compile this using the java compiler that is shipped with the Oracle database (as the Java version of the class has to be the same as the JVM that runs inside the Oracle database).

Since I did this work on Windows I created a cmd file which contained the following:

@ECHO OFF
SET ORACLE_DB_HOME=C:\oracle\product\10.2.0\db_1
SET CLASSPATH=%ORACLE_DB_HOME%\jdk\jre\lib
SET JAVA_HOME=%ORACLE_DB_HOME%\jdk
SET PATH=%JAVA_HOME%\bin;%ORACLE_DB_HOME%\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem
%JAVA_HOME%\bin\javac -classpath .;%ORACLE_DB_HOME%\lib\xmlparserv2.jar;%ORACLE_DB_HOME%\jdbc\lib\ojdbc14.jar;%ORACLE_DB_HOME%\md\lib\sdoutl.jar;%ORACLE_DB_HOME%\md\lib\sdoapi.jar -d %CD%\class %CD%\src\com\spatialdbadvisor\gis\oracle\gml.java 

Once this is compiled, we then use the Oracle loadjava tool to load the java class we have just compiled into Oracle. Again, I wrapped this inside a WIndows cmd tool.

ECHO OFF
SET ORACLE_DB_HOME=C:\oracle\product\10.2.0\db_1
SET CLASSPATH=%ORACLE_DB_HOME%\jdk\jre\lib
SET JAVA_HOME=%ORACLE_DB_HOME%\jdk
SET PATH=%JAVA_HOME%\bin;%ORACLE_DB_HOME%\bin;C:\Windows\system32;C:\Windows;C:\Windows\System32\Wbem
IF %ORACLE_SID%_ EQU _ SET ORACLE_SID=GISDB
SET /P ousr=Enter codesys username (codesys):
IF %ousr%_ EQU _ SET ousr=CODESYS
SET /P opwd=Enter %ouser% password (codemgr):
IF %opwd%_ EQU _ SET opwd=CODEMGR
SET /P osid=Enter TNSName (%ORACLE_SID%):
IF %osid%_ EQU _ SET osid=%ORACLE_SID%
loadjava -user %ousr%/%opwd%@%osid% -r -v -grant public -f %CD%\class\com\spatialdbadvisor\gis\oracle\gml.class 

The running of this, if successful, should produce the following:

E:\BlogBits\TipsTricks\Gml2Geometry>loadgml
E:\BlogBits\TipsTricks\Gml2Geometry>ECHO OFF
Enter codesys username (codesys):
Enter  password (codemgr):
Enter TNSName (GISDB):
arguments: '-user' 'CODESYS/CODEMGR@GISDB' '-r' '-v' '-grant' 'public' '-f' 'E:\BlogBits\TipsTricks\GML2Geometry\class\com\spatialdb
advisor\gis\oracle\gml.class'
creating : class com/spatialdbadvisor/gis/oracle/gml
loading  : class com/spatialdbadvisor/gis/oracle/gml
granting : execute on class com/spatialdbadvisor/gis/oracle/gml to public
resolving: class com/spatialdbadvisor/gis/oracle/gml
Classes Loaded: 1
Resources Loaded: 0
Sources Loaded: 0
Published Interfaces: 0
Classes generated: 0
Classes skipped: 0
Synonyms Created: 0
Errors: 0

So far, so good.

We now have a compiled Java class in the Oracle JVM,

But how do we execute it?

We could create two PL/SQL functions over the top of the two GML class methods, but you cannot overload ordinary functions in PL/SQL. Rather one has to create a class with two overloadable methods as follows.

E:\BlogBits\TipsTricks\Gml2Geometry>sqlplus codesys/codemgr@gisdb

SQL*Plus: Release 10.2.0.2.0 - Production on Fri Oct 16 09:30:04 2009

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create package gml
  2  authid current_user
  3  as
  4    Function toGeometry ( p_GML in varchar2 )
  5      Return MDSYS.SDO_Geometry
  6             Deterministic;
  7
  8    Function toGeometry ( p_GML in CLOB )
  9      Return MDSYS.SDO_Geometry
 10             Deterministic;
 11  end GML;
 12  /

Package created.

SQL> SHOW ERRORS
No errors.
SQL>
SQL> CREATE OR REPLACE PACKAGE BODY GML
  2  AS
  3
  4    Function toGeometry ( p_GML in Varchar2 )
  5      Return mdsys.sdo_geometry
  6          As language java name
  7             'com.spatialdbadvisor.gis.oracle.gml.toGeometry(java.lang.String) return oracle.sql.STRUCT';
  8
  9    Function toGeometry ( p_GML in CLOB )
 10      Return mdsys.sdo_geometry deterministic
 11          As language java name
 12            'com.spatialdbadvisor.gis.oracle.gml.toGeometry(oracle.sql.CLOB) return oracle.sql.STRUCT';
 13
 14  END GML;
 15  /

Package body created.

SQL> SHOW ERRORS
No errors.
SQL>
SQL> GRANT EXECUTE ON GML TO PUBLIC;

Grant succeeded.

For forwards compatibility with 11g I guess these two functions should have been named from_gmlgeometry. However, the full context of the function is SDO_UTIL.FROM_GMLGEOMETRY so compatibility is impossible to fully implement (anyway, I like GML.toGeometry far more than the ugly SDO_UTIL.FROM_GMLGEOMETRY).

Now let’s execute some simple tests.

SQL> set linesize 131 pagesize 1000 long 4000
SQL>
SQL> select to_char(sdo_util.to_gmlgeometry(mdsys.sdo_geometry(2001,8307,sdo_point_type(147.234232,-43.452334,null),null,null))) as
GML from dual;

GML
-----------------------------------------------------------------------------------------------------------------------------------
<gml:Point srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml"><gml:coordinates decimal="." cs="," ts=" ">147.234232,-43.4523
34 </gml:coordinates></gml:Point>

SQL> select gml.toGeometry(sdo_util.to_gmlgeometry(mdsys.sdo_geometry(2001,8307,sdo_point_type(147.234232,-43.452334,null),null,null
))) as GEOM from dual;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(147.234232, -43.452334, NULL), NULL, NULL)

SQL> select gml.toGeometry('<gml:Point srsName="SDO:8307" xmlns:gml="http://www.opengis.net/gml">
  2    <gml:coordinates decimal="." cs="," ts=" ">147.234232,-43.452334 </gml:coordinates>
  3  </gml:Point>') as GEOM from dual;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(147.234232, -43.452334, NULL), NULL, NULL)

SQL> select gml.toGeometry('<gml:Point srsName="EPSG:4326" srsDimension="2"  xmlns:gml="http://www.opengis.net/gml">
  2    <gml:pos xmlns:gml="http://www.opengis.net/gml">4.852,52.31</gml:pos>
  3  </gml:Point>') as GEOM from dual;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------------------------------------------------------


SQL> select gml.toGeometry('<gml:Point gml:id="p21" srsName="urn:ogc:def:crs:EPSG:6.6:4326" xmlns:gml="http://www.opengis.net/gml">
  2    <gml:pos dimension="2">45.67, 88.56</gml:pos>
  3  </gml:Point>') as GEOM from dual;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------------------------------------------------------

SQL> select gml.toGeometry('<gml:LineString gml:id="p21" srsName="urn:ogc:def:crs:EPSG:6.6:4326" xmlns:gml="http://www.opengis.net/g
ml">
  2    <gml:coordinates xmlns:gml="http://www.opengis.net/gml" >45.67, 88.56 55.56,89.44</gml:coordinates>
  3  </gml:LineString >') as GEOM from dual;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(45.67, 88.56, 55.56, 89.44))

SQL> select gml.toGeometry('<gml:LineString gml:id="p21" srsName="urn:ogc:def:crs:EPSG:6.6:4326" xmlns:gml="http://www.opengis.net/g
ml">
  2    <gml:coordinates>45.67, 88.56 55.56,89.44</gml:coordinates>
  3  </gml:LineString >') as GEOM from dual;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(45.67, 88.56, 55.56, 89.44))

SQL> select gml.toGeometry('<gml:Polygon xmlns:gml="http://www.opengis.net/gml">
  2    <gml:outerBoundaryIs>
  3          <gml:LinearRing>
  4                  <gml:coordinates>0,0 100,0 100,100 0,100 0,0</gml:coordinates>
  5          </gml:LinearRing>
  6    </gml:outerBoundaryIs>
  7  </gml:Polygon>') as GEOM from dual;

GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
-----------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARRAY(0, 0, 100, 0, 100, 100, 0, 100, 0, 0))

In all my testing I could not get a Point defined with Pos to work. Perhaps the Oracle GML code doesn’t handle a Point defined in such a way. For example, this does not work:

select gml.toGeometry('<gml:Point srsName="EPSG:4326" srsDimension="2"  xmlns:gml="http://www.opengis.net/gml">
  <gml:pos xmlns:gml="http://www.opengis.net/gml">4.852,52.31</gml:pos>
</gml:Point>') as GEOM 
  from dual;

GEOM
----
NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 

1 rows selected

But, this rather bold-faced hack can get around the problem in this case:

select gml.toGeometry(REPLACE('<gml:Point srsName="EPSG:4326" srsDimension="2"  xmlns:gml="http://www.opengis.net/gml">
  <gml:pos xmlns:gml="http://www.opengis.net/gml">4.852,52.31</gml:pos>
</gml:Point>','gml:pos','gml:coordinates')) as GEOM from dual;

GEOM
------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(2001,4326,MDSYS.SDO_POINT_TYPE(4.852,52.31,null),null,null)

1 rows selected

If anyone can get gml:pos defined points to work in a more robust manner please let me know.

All my code is available for download from here

I hope this article is of use of use to someone.