Something wrong with oracle.sdoapi.sref? - Spatial

I stored my coordinates with sid = 82202 in the tables.
I want my coordinates in the same coordinate system but in Lat/Long.
The sid for this is 8288.
So I have tested oracle.sdoapi.sref using this code section:
//Transformation i Oracle RT90 -> RT90 Lat/Long
SRManager srManager = OraSpatialManager.getSpatialReferenceManager();
// Create spatial references from ID 82202 = RT90 X/Y
int id1 = 82202;
SpatialReference sr1 = srManager.retrieve(id1);
// Create spatial references from ID 8288 = RT90 Lat/long
int id2 = 8288;
SpatialReference sr2 = srManager.retrieve(id2);
// Create transformation sr1 -> sr2
Transform xform = srManager.createTransform(sr1, sr2);
// Transform a geometry object from sr1 to sr2
Geometry geom2 = xform.transform(geom1);
The result is like this:
Coordinates stored in Oracle
Same point calculated using oracle.sdoapi.sref to
This point should have been like this
If I take the same point calculated using oracle.sdoapi.sref
and I calculate it my self to XY I will get
What is wrong?
Doing Coordinate change from XY to Lat/Long is nothing more then mathematics.
It is not any coordinate system change and it should not be any error at all.
Here it is like 1 meter error.
Please give me some help! 

Coordinate transformations are not supported in SDOAPI.
Use the Oracle Spatial functionality instead.

Sorry but
I don't understand the answer.
I'am reading "Java Library User's Guide"
"Release 8.1.7 or later."
Page 1-21 and 1-22 "Using Spatial Reference Systems".
Can you please tell me which part of SDOAPI is in use?

SDOAPI is only for converting between the database structure and a Java object, i.e. SDO_GEOMETRY <--> oracle.sdoapi.Geometry
Transforming between coordinate systems is not fully implemented or supported.
The Transform interface may be used to implement your own coordinate transformation functions. SDOAPI does not provide supported functionality for Coordinate Transformations. That is it is incomplete and as you indicate possibly incorrect.
Did the server side (SQL) transform functions work correctly?

Yes, nearly.
RT90 X-coordinate precision in millimetres.
RT90 Y-coordinate precision in centimetres.
I would have seen both with accuracy in millimetres.
If we put in and out coordiantes a couple of times we will get lost.

we met the same problems with the SDOAPI. The transform method doesn't work correctly.
Do you know when this problem will be resolved ?
more precisely our problem is this one :
we try to make a conversion from a latitude/longitude coordinate system (Netherlands in our example) to another latitude/longitude (WGS84) coordinate system. Coordinates in decimal degrees of the geometry object are transformed but the results in decimal degrees seems to have been converted as if they were radians:
coordinates before transformation:
coordinates after transformation:
Those coordinates should be quite the same.
code :
SpatialReference sourceSR = m_srManager.retrieve(8273);
SpatialReference targetSR = m_srManager.retrieve(8307);
m_geomTransformer = m_srManager.createTransform(sourceSR,targetSR);
GeometryFactory geoFactory = OraSpatialManager.getGeometryFactory();
Point point1 = geoFactory.createPoint (42.327,3.312);
Point pointResult = m_geomTransformer.transform ( point1 );
pointResult is wrong !
Thank you for your help
Bertrand F.

Use ths coordinate conversion functionality of Oracle Spatial, i.e. the MDSYS.SDO_CS package, instead of the sample implementation in sdoapi.

As Jayant mentioned, the java functionality for coordinate systems transformation is not supported. Please
use the server for coordinate systems transformations. 

Our problem is to convert many geometries on the client side.
So, the solution with Oracle spatial is not good for us because it will generate many network communications.
We expect Oracle will correct the SDOAPI about this problem.
Is it planned ? if Yes, when it's planned ?
Thank you for your help.
Pierre J & Bertrand F.

Regarding adding coordiante transform support in SDOAPI see the note on OTN:
Note: This will be the last version of this Spatial Java API released on OTN. A new Java API is planned for the next major Oracle database release, and it will be shipped with Oracle Spatial. This new API will be available only with Oracle Spatial and will not be available through the OTN.
Regarding your issue about converting multiple geometries and network round trips.
If the geometries came from the server in the first place then they could have been transformed there before fetching to the client.

Thank you for your answer.


Any support for spatial and .NET?

I have been developing in Microsoft .NET, using C# and managed and unmanaged C++. Is there any data provider for .NET that will allow me to work with spatial data? Are there any examples?
If not, can someone point me to the best examples for interacting with spatial data using C++?
I'm continuing my search, but thought you guys might be able to point me in a good direction quicker.
currently there is OCCI, which is a c++ interface. ODP.NET will hopefully support spatial in the next release. There is a current workaround that involves changing spatial data to a clob type, but it is not an elegant solution. 
Anyone got any pointers to useful OCCI and spatial examples?
I've been searching for .NET, I'll switch my searches to be for OCCI. 
Maybe you can use OO4O, which is based on COM ? 
hi did sth like that:
string constr = "User Id=scott;Password=tiger;Data Source=oracle";
OracleConnection con = new OracleConnection(constr);
OracleCommand cmd = new OracleCommand("select * from cola_markets", con);
cmd.XmlCommandType = OracleXmlCommandType.Query;
cmd.CommandText = query;
cmd.BindByName = true;
int rows = cmd.ExecuteNonQuery();
XmlReader xmlReader = cmd.ExecuteXmlReader();
XmlDocument xmlDocument = new XmlDocument();
xmlDocument.PreserveWhitespace = true;
return xmlDocument;
and it returns sth like this:
<?xml version="1.0" encoding="utf-8"?>
but i have problems when i want to fill dataset with this data :|
oh, i wonder if sb have any experience in storing gml (geography markup language) in oracle db.
Bartosz Machocki 
I've been using the XmlDocument and XmlTextReader technique for a while now to query spatial tables and it's very fast but you need to use a XmlTextReader to get good performance and skip the XmlDocument.Load() step. Never could figure out how to load a data table either. OCI was another method we tried to use but it proved very painful in .NET so we went with this Xml approach.
Inserting/updating large geometry objects, or using SDO_FILTER with a large transient polygons with hundreds of points, is a pain though when you have large linestrings of ordinates that are > 2048 characters in length. Eg.
select T.ID, T.GEOM from TABLE T where SDO_FILTER(T.GEOM, MDSYS.SDO_GEOMETRY(...some really big polygon...), 'mask=anyinteract') = 'TRUE';
This statement will fail with ORA-03113 errors and access violations/core dumps reported in the logs if the length of the SQL statement is too long, which for me maxes out at about 200 points.
There doesn't appear to be a way in ODP.NET to bind a VARRAY of numbers representing the SDO_ORDINATE_ARRAY(), or bind an SDO_GEOMETRY object itself, to your SQL statement.
Does anyone know which release Oracle plans to support objects as bind variables in ODP.NET? 
I have programmed OO4O in VB6 and it works quite well (read and write)
but the COM wrapper over the Oracle Objects is quite slow. I got some
very, very acceptable (read) performance by using OraCustomDynaset()
and breaking up the Sdo_Geometry into its constituent parts on the server
by doing the following:
from <table> a
(With variations for SDO_POINT.)
Of course, this is not good if you want to do a SELECT ... FOR UPDATE.
Still, for fast read only access it is quite good.
Wrt access in ODP.NET I add in my vote for some decent support for
all Oracle Spatial vector objects in the ODP provider.
For those using 10g, instead of using the XML engine (nice approach)
you could use the TO_WKT() (returns CLOB) or TO_WKB() (returns BLOB)
and then process them accordingly in your .NET cient application (there are
some "GIS" development kits around - like TatukGIS's DK - that will map
WKT/WKB into Shapes (for shapefiles) etc.
For read only, you could push the conversions back into the database via materialized views with function based indexes eg
Create Materialized View WKT_VIEW
<mv properties>
SELECT gid, a.geom.to_wkt()
FROM <my table> a;
Then create a function based index over the WKT/WKB column using an SDO_GEOMETRY constructor that takes the WKT/WKB and the SRID
as its elements.
In fact, you could simply store the WKT/WKB on the base table alongside the Sdo_Geometry and use triggers to keep them synchronised.
So, with WKT/WKB at 10g, you can do your insert,update as follows...
insert into <table> a set a.shape = MDSYS.SDO_GEOMETRY(:WKT, SRID);
Using WKT/WKB would give you maximum portability across SpatialDBs (eg MySQL, PostGIS etc) if this is what you are hoping to do in the future.
This cannot be construed as a commitment, but I've heard recently that beta for that includes object (and spatial) support should be before the end of the year.
Now this is good news.
I know you can't let the cat out of the bag, but I do hope that the ODP.NET
stuff provides some good constructor and accessor functions for Sdo_Geometry
so we don't have to write our own! What I mean by this is that a programmer
shouldn't have to know the details of how the Sdo_Elem_Info array is organised
and interpreted, or how the ordinate array is arranged. This would make data
import/export so much easier.
It would also be nice if the ODP.NET API included some constructors/accessors
for WKB and WKT manipulations (other than having to have the server do the
computations via to_wkt()/from_wkt() etc.
You could check CoreLabs, which provide access to Oracle Objects from .NET.
They do have a Data Provider capable of quering Objects, although it's not too performant,
this might help you out for the time being. 
Hi Everyone,
If ODP.NET supports SDO_GEOMETRY object type mapping, what type(s) of the attributes in a corresponding C# or VB type/class do you perfer?
For example, if we use these instances with the System.Drawing.Drawing2D for vector graphic functionality, the ordinates attribute would be better represented as PathData instance, which encapsulates an array of points.
Or a vector of double will do for the ordinates attribute, as in JGeometry Java class?
Another issues is whether we should do safe type mapping to perserve data precision and null semantics of Oracle NUMBERs.
OracleDataAdapter Safe type mapping background info:
OracleDecimal Type and .NET Decimal Type:
OracleDecimal can store up to 38 precision, while the .NET Decimal datatype can only hold up to 28 precision. When accessing the OracleDecimal.Value property from an OracleDecimal that has a value greater than 28 precision, loss of precision can occur.
To retrieve the actual value of OracleDecimal, use the OracleDecimal.ToString() method.
If NUMBER is stored as.NET Decimal or double, precision could be lost.
The OracleDataAdapter Safe Type Mapping feature prevents data loss when populating Oracle data for any of these types into a .NET DataSet. By setting the SafeMapping property appropriately, these types can be safely represented in the DataSet, as either of the following:
.NET byte[] in Oracle format
.NET String
By default, Safe Type Mapping is disabled.
I work for a company called Spatial Eye (see and we have developed our own ODP.NET variant from scratch for having Object support (this until Oracle delivers Object support in ODP.NET).
But to start answering your question.
Do not use PathData or any other UI/Graphics related object to represent the Sdogeometry data, for several reasons:
1) Today we use Graphics class to render, but tomorow it might be Avalon (PathData is useless).
2) You might want to know what the Spatial Reference ID is...PathData doesn't know it.
3) Use an SdoGeometry type of object which holds all information in it's most native format, similar to what you have in JGeometry I guess, but I do not know the details of that implementation.
The most important things when using Oracle Spatial is performance, therefore we (in our ODP.NET solution) choose to convert the numbers from the elementInfo array to Int32 and the Ordinates to Double.We believe that for 99% of the cases a Double is accurate enough.
Converting the Ordinate array to a OracleDecimal (or .NET Decimal) first and later for rendering to a float might impact performance to much (although we have no hard figures here). We did had a look at the CoreLab Oracle Data Provider in the past, but it was really
slow when retrieving the SdoGeometry object using the generic OracleObject.
What we really would like to have is a way of handling the conversion from the internal SdoGeometry to whatever we like to have (and back when writing geometry) ourselves.
For that we have developed an OracleObjectFactory which can do those tricks.
So in our case we can use a simple conversion when rendering and another when we want
to do complex operations on the geometry outside the Oracle Server.
To summarize:
Use a JGeometry like structure (or class) with a Double[] for the Ordinates.
but please provide means of extracting other Oracle Objects using a factory mechanism,
so that the value can be converted to the appropriate .NET type.
Hi Patrick,
Thanks for your info and experience. I agree with you that the performance of ODP object-relational mapping is very important.
Your Solution looks very good. But I have some Problems to realize it.
1) Where can I find a OraCustomDynaset?
2) When I try to run a Query for e.g. sdo_gtype, then I get the error: "ORA-00904: Ungültiger Spaltenname" which means Wrong ColumnName.
My Select is:
"SELECT GST_SYMBOL.sdogeometry.sdo_gtype from GST_SYMBOL;"
GST_Symbol is the Table
sdogeometry is the Columname
Can you tell me whats going wrong?
mfg Barny 
Please read carefull, just must use aliases (as in the sample provided by sgreener)
The following should work:
select a.sdogeometry.sdo_gtype from gst_symbol a;

How can I use the locator and spatial stored procs, e.g. SDO_CS.TRANSFORM?

Hi. Very grateful if anyone can help with this one. Does anyone know how to use the stored procs provided in the locator packages? For instance, there is one that can be used to transform coordinate systems which would be really useful to us:
to_srid IN NUMBER
But how - in a .NET language (preferably C#) - do you send in the input SDO_GEOMETRY and how do you get the return value? None of the properties in OracleCommand and OracleParameter seem to do the job...but maybe I just haven't found the right combination!
Steve Clay 
Hi Steve,
As you are probably aware, ODP.NET does not currently have support for object types. I've never used spatial myself, but this posting here:
Re: ODP.Net + Spatial
seems to describe the most commonly used workaround. I've not actually tried that technique, but, on the other hand, I haven't seen any posts saying it doesn't work (beyond what you see in that thread).
Good luck,

Convert 3D wkt to SDO_GEOMETRY

I have a 3D WKT point and I try to insert it to an SDO_GEOMETRY column.
When executing the following:
Select SDO_GEOMETRY('POINT(2 2)') from dual;
I get the correct result, but when executing the 3D point like
the following:
Select SDO_GEOMETRY('POINT(2 2 3)') from dual;
I get an error.
How can I convert 3D wkt to an SDO_GEOMETRY?
In your posts:
1. try to mention DB version
2. try to include the type of error and the error message
I use oracle 11g.
When executing the following:
select SDO_UTIL.FROM_WKTGEOMETRY('POINT(2 2 2)') from dual;
I get the error:
ORA-29532: Java call terminated by uncaught Java exception: java.lang.RuntimeException: -2
ORA-06512: at "MDSYS.SDO_UTIL", line 177
Is there a way to convert 3D point in WKT to SDO_GEOMETRY?
Hi dyahav,
I believe the answer is "No". Oracle Spatial implements OGC Simple Features per ISO 13249-3 which I think equates to OGC Simple Feature OGC 05-126 version 1.10.
This specification only includes 2D geometries. I think it would be useful if the documentation just said that. The newer OGC 06-103r3 version 1.2.0 specification does support Z and M values on geometries.
So you either need to write your own WKT parser or submit an enhancement request to Oracle to support WKT version 1.2.0.
Also, your WKT example does not specify what exactly your third dimension is. It could be a Z or an M (measure). Extended WKT specifies this by appending this information to the type name, eg.
POINT Z('2 2 2')
POINT M('2 2 0.25')
POINT ZM('2 2 2 0.25')
POINT MZ('2 2 0.25 2')
Hope that helps!
Thanks for the detailed answer


When I tried to export a 3-dimension point, as the codes below, I got the InvalidGeometryException exception.
Could you tell me what wrong with the code? I got no error when I export a 2-dimension point, is this mean that WKT adpater does not support 3-dimension or I missed something? I am using Java SDO API.
--------- code segments ---------------------
Geometry loGeometry = poXmlAdapter.importGeometry(poNode);
// The point is created using the code like
// geometryFactory.createPoint( 1.2, 2.3, 3,4);
GeometryAdapter wktAdapter =
OraSpatialManager.getGeometryAdapter("WKT", "1.0", null, byte[].class, null);
byte[] wktGeom = (byte[]) wktAdapter.exportGeometry(byte[].class, loGeometry);
It likely means 3-d is not supported for the WKT Adapter.
If the error message you get is
Incorrect or unsupported dimensionality.
then it definitely means 3-D is not supported.
We've hit the same problem... Unfortunately, the SDO Java API states that it does not support 3D data.
We're hoping we can work around this by reading the STRUCTS directly.

Transforming single co-ordinates using spatial java interface

I'm looking to implement a co-ordinate transform from Java using Spatial's tools.
I've examined the code for transforming geometries from java, but all I need to do is a fast look-up that converts single co-ordinate pairs rather than whole geometries.
Does anybody have (or know of) any code that demonstrates this?
Sorry I don't have a good answer for you, but you shouldn't use the JAVA API for client side coordinate system transformations. Coordinate transformations should always be done on the server. 
Thanks Daniel.
I actually discovered that the transform() function can be used on coordinates in addition to geometries - was just a case of finding the relevant part of the SDK.
Just to put your mind at rest though, this java actually lives within a servlet, so it is actually working server-side!
Hello Matt,
I have a similar problem to yours and am trying to use Transform.transform(). It seemed to me that I needed to pass two 2-element arrays - one with the x,y coords to be transformed, and one empty 2-element array to hold the results.
The destination array is getting filled, but the "transformed" coordinates are the same as the original coordinates. I also notice that the transform method returns false. My code snippet is attached below, can you (or anyone else!) provide a suggestion as to what I'm doing wrong?
-- john
SRManager srManager = OraSpatialManager.getSpatialReferenceManager();
SpatialReference nad27 = srManager.retrieve(8260);
SpatialReference wgs84 = srManager.retrieve(8307);
Transform transform = srManager.createTransform(nad27,wgs84);
stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT LONGITUDE,LATITUDE,OBJECTID FROM " + m_sourceTableName);
while ( {           
double[] src = {rs.getDouble("LONGITUDE"), rs.getDouble("LATITUDE")};
double[] dst = new double[2];
System.out.println("src: "+src[0]+", "+src[1]);
System.out.println("dst: "+dst[0]+", "+dst[1]);
The API doesn't support transformations. Coordinate system transformations should be done on the server. If you just want to transform as single coordinate pair, then pass in a geometry that is a single point. This is the only way I know of to transform geometries using spatial. 
Thanks for your reply, Daniel.
I'm confused though, what is the Transform.transform(double[],double[]) method for?
Also, regardless of whether you pass in a geometry object or a pair of coordinates, aren't they both on the "client" side?
In my mind the client might be a stand-alone program or a servlet, but is a client as distinguished from the oracle server.
Thanks for the clarification!
-- john
Hi John,
The stub for doing transformations is there, but I don't think there is anything behind it. The trasformation routines should not be used on the client (in the java program/servlet/applet/whateveryouwanttocallit).
The sdo_cs.transform functions should be called on the server. 
Thanks, Daniel. I'm still struggling to understand what an example of "server-side" use of these java classes and methods would be.
It seems like you're saying that the transform methods are only usable in a java stored procedure in oracle - is that correct?
Thanks again for your help and patience!
-- john
Hi John,
What I am trying to say is that this functionality is only available using the SDO_CS.TRANSFORM package which is Oracle Spatial in the database server functionality (not JAVA functionality).
Hope this helps,
By server side Dan means database (Oracle Spatial) functionality that SDO_CS package.
The SDOAPI does not implement coordinate transformations correctly or fully. The interface supports plugging in user defined coordinate system transformation packages.
The user's guide Pg 1-2 states this:
The current release of the Spatial Java Library does not implement a supported
set of coordinate transformations. To perform coordinate transformations, you
will need to implement the Projection, ProjectedCS, and GeographicCS