We have an existing oracle gis which is being reworked. We're not really touching the oracle stored procedures or tables. In the table, the sdo_geometry appears to be a 3D line, with 0 as the z value. I'm not entirely sure why it was written this way, I wasn't there when it was written. However, when this data gets brought into an openlayers vector layer and I observer the feature geometry, there is no Z value, so somehow it's being converted from 3D to 2D. In my new code I'm actually sending the data to oracle as a WKT, and then converting it to an sdo_geometry in an oracle helper function which passes the data to the other procedure which takes an sdo_geometry (I couldn't find a way to convert to sdo_geometry format in c# or javascript). So the linestring is being sent as 2D which the database expects 3D so therein lies my problem. I think I can get around this on a few ways, but I don't know how to do any of them. I could...

A. Force my vector layer in openlayers to be 3D. with z value of 0.

B. use some (unknown to me) openlayers function to return a 3D version of the geometry (or WKT) given a 2D, with Z set to 0.

C. use Oracle (in my helper function) to make the sdo_geometry 3D (a different constuctor maybe?) currently using sdo_geometry(wkt_in,srid) srid is 8307 if that matters.

D. Write my own function in javascript or C# which will just parse the WKT and add a 0 to each coordinate set. I don't have a problem doing this, but would rather use existing functions if they're available.

E. something else I haven't thought of :)


What version of Oracle database do you use ?

My recommended approach is definitely to turn the shapes in the database into 2D shapes by removing the 0 Z values. This is easily done using the SDO_CS.MAKE_2D() function (see https://docs.oracle.com/database/121/SPATL/sdo_cs_ref.htm#SPATL1068)

3D shapes with all Z set to 0 are generally uploaded from CAD systems. But the Z are just fillers and should not be there in the first place. Having 3D shapes with Z to 0 was fine with old versions (Oracle 10g and before) where those Z are ignored. But on current supported versions (11g and later), the Z are really interpreted as such.

So a line with Z set to 0 is considered to be at an elevation of 0, and all queries and processing use that information. For example another line with explicit Z will not be considered as intersecting that line at elevation 0 - but it did with the older Oracle 10g versions.

Using Oracle geometries in Java is achieved using the Oracle Spatial Java API. Check out the oracle.spatial.geometry package at https://docs.oracle.com/database/121/SPAJV/toc.htm

If you insist on keeping the shapes as is, then you can use option C: just use the SDO_CS.MAKE_3D() function (see https://docs.oracle.com/database/121/SPATL/sdo_cs_ref.htm#SPATL1069) It lets you turn a 2D shape (without Z) into a 3D shape with all Z set to a fixed value, for example:

  • Thank you for the reply. We're using Oracle 11g (11.203 I've ben told). The database is an existing live db which was written using a 3D geometry. My boss thinks it was set up that way because of a port from ArcGIS(I have no personal knowledge about this). But we're unable to change this. I'm able to send the wkt to an oracle helper function which I wrote which will convert it to sdo_geometry, and that works just fine for 2D but sdo_util's from_wktgeometry does not support 3D. Converting the WKT to 3D was fairly easy, I was able to parse it myself. And we're using C#, not java.
    – Joe
    Jan 21 '15 at 19:16
  • I found that sdo_util's from_gmlgeometry does in fact support conversion to a 3D sdo_geometry from a 3D gml. And, Openlayers has a function to return the gml format for the geometry of the selected feature. I will need to parse and tweak the gml a bit to force it to 3D from 2D, but I think this route will work. Thanks again for your input. Oh and I see above where I made a typo to make you think java was an option, I meant javascript, not java. Although the legacy system was total java. We're going to mvc C# and HTML5.
    – Joe
    Jan 21 '15 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.