12 Replies Latest reply: Feb 1, 2012 6:17 PM by Simon Greener RSS

    Java Exception with SDO_UTIL.FROM_WKTGEOMETRY

    912146
      Anyone have any idea why the following is not working?
       INSERT INTO "OBJECT_EVENT" ("title", GEOMETRY) VALUES ( 'testPoly', SDO_UTIL.FROM_WKTGEOMETRY('POLYGON((-76.57418668270113 38.91891450597657 0, -76.57484114170074 38.91758725401061 0, -76.57661139965057 38.91881851059802 0, -76.57418668270113 38.91891450597657 0))'));
      This is producing the following error:
      ERROR at line 1:
      ORA-29532: Java call terminated by uncaught Java exception:
      java.lang.RuntimeException: -2
      ORA-06512: at "MDSYS.SDO_UTIL", line 137
      I'm a n00b with Oracle Spatial and there's so much documentation I can't find anything that references this issue. Which usually means I have something all wrong.

      Thoughts? Help?

      Thanks so much!
        • 1. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
          Paul Dziemiela
          Hello user10248405,

          Oracle Spatial does not read 3D Well-known text. They only support the OGC Simple Features 1.1 specification which is 2D only.
          See http://www.opengeospatial.org/standards/sfa
          Take out those zeroed Z values and it works fine. Ideally someone needs to log an enhancement request (if anyone has one in already, please chime in with the number).
          But this topic has been around for quite a while!
          WKT and Three Dimensional Coordinates?
          SELECT SDO_UTIL.from_wktgeometry(
                    'POLYGON((-76.57418668270113 38.91891450597657, -76.57484114170074 38.91758725401061, -76.57661139965057 38.91881851059802, -76.57418668270113 38.91891450597657))'
                 )
            FROM DUAL;
          Now that being said, even if Oracle Spatial did read 3D WKT, yours is not quite right. You need to tell us what that 3rd dimension is by using POLYGON Z.

          Cheers,
          Paul
          • 2. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
            Simon Greener
            Hello user10248405,

            I have been working on making additional functions available for SDO_Geometry users by exposing JTS/GeoTools and Jaspa functionality within the Oracle database.

            To convert your geometry, download the JTS package as it can do the following:
            select JTS.ST_GeomFromEWKT('POLYGON((-76.57418668270113 38.91891450597657 0, -76.57484114170074 38.91758725401061 0, -76.57661139965057 38.91881851059802 0, -76.57418668270113 38.91891450597657 0))',8307) as geom
              from dual;
            
            GEOM
            ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
            SDO_GEOMETRY(3003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-76.5741866827011,38.9189145059766,0,-76.5766113996506,38.918818510598,0,-76.5748411417007,38.9175872540106,0,-76.5741866827011,38.9189145059766,0))
            
            -- Similarly one can generate EWKT from this
            select JTS.ST_AsText(SDO_GEOMETRY(3003,8307,NULL,SDO_ELEM_INFO_ARRAY(1,1003,1),SDO_ORDINATE_ARRAY(-76.5741866827011,38.9189145059766,0,-76.5766113996506,38.918818510598,0,-76.5748411417007,38.9175872540106,0,-76.5741866827011,38.9189145059766,0)))
            from dual;
            
            TEXT
            ------------------------------------------------------------------------------------------------------------------------------------------
            SRID=8307;POLYGON ((-76.574186683 38.918914506 0, -76.5766114 38.918818511 0, -76.574841142 38.917587254 0, -76.574186683 38.918914506 0))
            Jaspa/PostGIS detect the Z in a POLYGON: no need for POLYGONZ keyword.

            regards
            Simon
            • 3. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
              Paul Dziemiela
              Hi Simon,

              Thanks for the pointer. So without a Z or M hint; then PostGIS always assumes the third dimension is a Z?

              Note however that ESRI ArcSDE 9.3.1 (SDE.ST_GeomFromText) seems to demand the Z hint.
              The OGC SFA 1.2 spec seems to state in sections 7.2.4 and 7.2.5 that the hint is mandatory.

              It might be a good idea to put it in there. I use LRS quite a bit so I am more sensitive than most folks to the ambiguity of the 3rd dimension.

              Cheers,

              Paul
              • 4. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                Simon Greener
                Paul,

                I am using Jaspa as the source for the implementation available on my website.
                Jaspa, like PostGIS, does not provide a POLYGONZ tag. Rather it provides a
                POLYGONM tag. So, this resolves some of your concerns over ambiguity.

                POLYGON((x y, x y....)) is fine
                POLYGON((x y z, x y z,....)) is also fine as it is considered to be a polygon with Z ordinates
                POLYGONM((x y m, x y m, ...)) is also fine.

                I have not tested POLYGON<M>((x y z m, ....)) to see if they work.

                I am not aiming to produce a implementation that is OGC SFA 1.2 compliant. I am simply
                exposing what is in Jaspa/JTS etc for Oracle users to use as they see fit.

                I have written to the Jaspa people as it is their goal to make a complete "drop in" replacement for a
                database's spatial type (as ESRI does with their SDE.ST_GEOMETRY for Oracle), to ensure
                it is compliant with the relevant standards. I will let you know what they say.

                regards
                Simon
                • 5. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                  912146
                  Wow! You all rock... thanks for the help. Almost there, just need to validate a misunderstanding.

                  So Paul, based on your comment I tried the following (I apologize if my ignorance is obvious),
                  INSERT INTO "OBJECT_EVENT" ("title", "GEOMETRY") VALUES ('test', (SELECT SDO_UTIL.from_wktgeometry('POLYGON((-76.57418668270113 38.91891450597657, -76.57484114170074 38.91758725401061, -76.57661139965057 38.91881851059802, -76.57418668270113 38.91891450597657))') from dual));
                  And received the following error:
                  ERROR at line 1:
                  ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
                  ORA-13365: layer SRID does not match geometry SRID
                  ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 623
                  ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 227
                  I am assuming this is due to the way I set up the spatial index in the schema?

                  Here is how that was done
                  CREATE TABLE object_event (
                      "title" varchar2(255),
                      GEOMETRY SDO_GEOMETRY,
                  );
                  
                  INSERT INTO user_sdo_geom_metadata values('OBJECT_EVENT', 'GEOMETRY', mdsys.sdo_dim_array(mdsys.sdo_dim_element('longitude',-180,180,1), mdsys.sdo_dim_element ('latitude',-90,90,1)), 8307);
                  
                  CREATE INDEX object_event_geomtery  ON object_event (GEOMETRY) indextype is MDSYS.SPATIAL_INDEX;
                  Correct me if I'm wrong but I basically told the spatial indexer to expect 3D geometry? How would I correct that or am I completely misunderstanding the documentation?

                  Thanks for the help!
                  • 6. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                    912146
                    Hello Simon!

                    Thanks for the help. I tried installing the JTS package from http://www.spatialdbadvisor.com/

                    Pretty rough installer. I'm running Oracle 10g on RHEL5.
                    The JTS installer.sh had the .sql script files incorrectly named and the GeoProcess_Package.sql file was missing completely. I assumed JTS_Package.sql is what was meant so I modified the script and got all sorts of errors
                    Errors for PACKAGE BODY JTS:
                    
                    LINE/COL ERROR
                    -------- -----------------------------------------------------------------
                    0/0      PL/SQL: Compilation unit analysis terminated
                    1/14     PLS-00304: cannot compile body of 'JTS' without its specification
                    1/14     PLS-00905: object SPATIAL.JTS is invalid
                    Package SPATIAL.JTS.PACKAGE is invalid.
                    Package SPATIAL.JTS.PACKAGE BODY is invalid.
                    DECLARE
                    *
                    ERROR at line 1:
                    ORA-20000: JTS failed to install.
                    ORA-06512: at line 16
                    I'm guessing renaming JTS_Package to GeoProcess_Package.sql was the wrong thing to do, but the install script didn't even use JTS_Package.sql and GeoProcess_Package.sql was not even provided. Needless to say, trying your select statement gave the following error:
                    ORA-06575: Package or function JTS is in an invalid state
                    At this point I'm kind of at a loss for inserting geometry. If I try SELECT SDO_UTIL.from_wktgeometry( I get:
                    SDO_UTIL.FROM_WKTGEOMETRY('POLYGON((-76.5741866827011338.91891450597657,-76.5748
                    --------------------------------------------------------------------------------
                    SDO_GEOMETRY(2003, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 1003, 1), SDO_ORDINATE_ARR
                    AY(-76.574187, 38.9189145, -76.574841, 38.9175873, -76.576611, 38.9188185, -76.5
                    74187, 38.9189145))
                    Containing a null SRID. But my table is using 8307 for SRID. Is there something wrong with my install? Been scouring documentation and user forums. I think I generally understand the problem but I have no clue how to go about fixing it.

                    Any thoughts?

                    Edited by: user10248405 on Jan 30, 2012 9:49 AM
                    • 7. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                      Paul Dziemiela
                      Hello user10248405,

                      You are indeed correct that SDO_UTIL.FROM_WKTGEOMETRY is wonky in that the designers did not allow any method to apply a SRID to the output. I suppose we should put in an enhancement request though I would see this issue as being a bug or poor implementation myself. To tell the truth I don't much use this old WKT stuff much myself as I long ago coded alternatives.

                      Instead you can call the SDO_GEOMETRY constructor itself in this manner
                      INSERT INTO "OBJECT_EVENT"  (
                         "title", 
                         "GEOMETRY"
                      ) VALUES (
                         'test', 
                         (
                            SELECT SDO_GEOMETRY('POLYGON((-76.57418668270113 38.91891450597657, -76.57484114170074 38.91758725401061, -76.57661139965057 38.91881851059802, -76.57418668270113 38.91891450597657))',8307) from dual
                         )
                      );
                      Note the SRID as the second parameter to the constructor. This will avoid the error with the spatial index. In general its best in the long run to get used to dropping the spatial index before doing any inserts and then rebuild it afterwards. If you have gone that route you could have inserted using your original statement and then updated the SRID afterwards. But the above solution is easier doing it all in one step.

                      Cheers,

                      Paul
                      • 8. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                        Simon Greener
                        user10248405 (what is your name?),
                        Pretty rough installer. I'm running Oracle 10g on RHEL5.
                        The JTS installer.sh had the .sql script files incorrectly named and the GeoProcess_Package.sql file was missing completely. I assumed JTS_Package.sql is what was meant so I modified the script and got all sorts of errors
                        I develop on Windows and have no Linux/Unix OS running Oracle to test installers etc on. The bash script was always a template for non-Windows installers. I have made many changes to GEOPROCESS/JTS over the past few months and have changed the Windows cmd script appropriately but forgot the SH installer as I can't test it anyway.

                        Since I have spent hundreds of hours working on the (free) code would it be naughty of me to ask you to fix the bash script (using the install.cmd as a template) and then provide it back to me as a thank you for the work I have done?
                        If I was charging for the code then it would be reasonable of users to demand a better installer (btw I have asked around Oracle forums about how to build a gui-wizard installer for this stuff as it is not something Oracle makes available
                        for developers via JDeveloper - no great help was provided indicating that there is no straight-forward method for doing this) but I don't as it is not possible to do everything (I don't test against non-English NLS databases or client platforms either.)

                        In other words, "what can you do to help me help you", with respect to building a decent installer for Linux/Unix?

                        (Don't rename any files eg JTS_package.sql to GEOPROCESS_package.sql. Just change the internals of install.sh to match what install.cmd does.)

                        Finally, would suggest you read this: http://www.spatialdbadvisor.com/philosophy.

                        BTW I have decided, since I don't just use JTS in the codebase, that I will change the name of the package again. I have not decided on a name. I can't use acronyms already in use eg GEOAPI or related to organisations eg OGCSFA (I do not aim for OGC SFA compliance even though lots of the code and signatures look like it does). Suggestions?

                        Please contact me privately on simon at spatialdbadvisor dot com

                        regards
                        Simon
                        • 9. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                          912146
                          sgreener wrote:
                          Since I have spent hundreds of hours working on the (free) code would it be naughty of me to ask you to fix the bash script (using the install.cmd as a template) and then provide it back to me as a thank you for the work I have done?
                          I have no problem with that. I'm an open source advocate/developer so I don't mind sharing work. More gets done that way.
                          sgreener wrote:
                          BTW I have decided, since I don't just use JTS in the codebase, that I will change the name of the package again.
                          So does that mean the JTS download at spatialdbadvisor.com is the latest codebase you've contributed? i.e. Do I have your latest work? If not I'll redownload once you update. If so, I'll play around with the .sh script and see if I can't get it working.

                          BTW, My name is Nick if it hasn't already updated.

                          Thanks for everyone's help!
                          • 10. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                            Simon Greener
                            Nick,
                            So does that mean the JTS download at spatialdbadvisor.com is the latest codebase you've contributed? i.e. Do I have your latest work? If not I'll redownload once you update. If so, I'll play around with the .sh script and see if I can't get it working.
                            The current download is still called JTS. At some point the name will change, probably to something like JSC4O (Java Spatial Companion 4 Oracle)... I may drop the "Companion" but I don't want people thinking I am trying to replace SDO_GEOMETRY
                            like ESRI does or Jaspa aspires to. All I want to do is make the SDO_GEOMETRY experience for users a little easier in those areas where gaps appear between releases that users need plugged but the Oracle release/license cycle isn't flexible enough.

                            I often update the JTS library as I find bugs or add new functionality. For example, I release a version a few days ago which contained:
                              /**
                               * ST_LineMerger
                               * Takes set of linestring geometries and constructs a collection of linear components 
                               * that form maximal-length linestrings. The linear components are returned as a MultiLineString.
                               * @param p_resultSet  : RefCur_T : Ref Cursor of Linestring Geometries  
                               * @param p_precision  : int   : Number of decimal places of precision when comparing ordinates.
                               * @return STRUCT      : Collection of linear sdo_geometries as MultiLineString.
                               * @throws SQLException
                               * @history Simon Greener, January 2012, Original Coding
                               * @copyright  : Licensed under a Creative Commons Attribution-Share Alike 2.5 Australia License.
                               *               http://creativecommons.org/licenses/by-sa/2.5/au/
                               */
                              Function ST_LineMerger(p_resultSet in &&defaultSchema..JTS.refcur_t,
                                                     p_precision in number)
                                Return mdsys.sdo_geometry
                                       Deterministic;
                            Also, I am improving/tuning my XSV exporter right now so those changes will go up.

                            As each goes up I modify this page: http://www.spatialdbadvisor.com/source_code/223/geoprocessing-package-documentation

                            I believe the latest version has some modifications to the install.sh script after your comments. Perhaps you might want to look at that version before starting?

                            regards
                            Simon
                            • 11. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                              912146
                              Will do. Out of curiosity, is there a dependency on 11g or is it backward compatible with 10g?
                              • 12. Re: Java Exception with SDO_UTIL.FROM_WKTGEOMETRY
                                Simon Greener
                                It is developed for 10g as it is written in Java 1.4 (JTS is written in Java 1.4).

                                11g is Java 1.5 and runs the JTS code even better because 11g has JIT compilation.

                                You can compile Java code for 10g but it is a lot more work.

                                regards
                                Simon