2 Replies Latest reply: Jul 5, 2013 11:05 AM by Paul Dziemiela RSS

    Cannot store geometry from external program (ODCIINDEXINSERT / not matching SRID)

    beHam

      Hello,

       

      I try to insert geometries into a table with a spatial index, I always get "ORA-13365: layer SRID does not match geometry SRID".


      Checking the database gives me the following information:

      select * from sdo_coord_ref_sys where SRID =3857 returns a proper entry


       

      3857Popular Visualisation CRS / MercatorPROJECTED44996055405519847FALSETRUETRUE


      I created the spatial index by:


      insert into user_sdo_geom_metadata(table_name, column_name, srid,diminfo) values ('MYTAB', 'THE_GEOM', 3857, sdo_dim_array(sdo_dim_element('LONGITUDE',-180, 180,0.05), sdo_dim_element('LATITUDE', -90,90,0.05)));


      CREATE INDEX PLOT_GEO_IDX_1 ON MYTAB (THE_GEOM) indextype is mdsys.spatial_index;


      I try to insert the geometry with the Java libs Geotools + JTS (which is also used by the Geoserver) and tried different ways to get a solution:

      • I tried to set the SRID 3857
      • I tried to transform the geometry (even though it is already in the correct CRS)
      • I tried to set the SRS by WKT.


      Nothing helped.


      When I drop the index the geometry can be stored without problems, but then I cannot use some of the SDO_ operations.


      I found some references to this problem, but never a solution.

      Any ideas?

      It would be helpful to see what the Java application tries to insert so that I can narrow the problem.




        • 1. Re: Cannot store geometry from external program (ODCIINDEXINSERT / not matching SRID)
          Stefan Jager

          Seems to me this is a problem with the tools you are using, those are not entering the SRID correctly. I can easily insert geometries with a spatial index on the table and a SRID.

           

          To see what has been executed, check v$sql:

          select sql_fulltext, parsing_schema_name, module f
          rom v$sql
          where parsing_schema_name='your_schema_name'
                and module is not null;
          

          If you know what your application's name is inside Oracle you could change module is not null to module='your_module_name';

           

          HTH,

          Stefan

          • 2. Re: Cannot store geometry from external program (ODCIINDEXINSERT / not matching SRID)
            Paul Dziemiela

            Hello beham,

             

            Well Stefan's comment is in the right area.  I think there is a conflict between you, the database and/or your application.  For instance your metadata is incorrect for 3857 which is Spherical Mercator, not geodetic.  So I don't think it will solve your issues but alter the metadata to something like

             

            INSERT INTO user_sdo_geom_metadata(

                table_name

               ,column_name

               ,srid

               ,diminfo

            ) VALUES (

                'MYTAB'

               ,'THE_GEOM'

               ,3857

               ,SDO_DIM_ARRAY(

                    SDO_DIM_ELEMENT('X', -20037508.34, 20037508.34,0.05)

                   ,SDO_DIM_ELEMENT('Y', -20037508.34, 20037508.34,0.05)

                )

            );


            In general the element mins and maxs are ignored by all parties.  But its possible your application is sniffing the 180s/90s and deciding you really have geodetic information.  Not sure. 


            It would be interesting to know what SRID the application is inserting.  Seems to me you can just drop your spatial index and let the application do its thing inserting some records.  Then look in the table at the SRID that was inserted and report back to us. 


            Cheers,

            Paul