Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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.