uDIG
  1. uDIG
  2. UDIG-1509

Oracle DataStore does not supply SRID on update; resulting in exception writing geometry column

    Details

    • Type: Bug Bug
    • Status: Analysis Analysis
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: UDIG 1.1.1
    • Fix Version/s: UDIG 1.4.1
    • Component/s: database
    • Labels:
      None

      Description

      From Simon Greener via email:

      I create a connection, add a layer with Oracle SRID 28356. I set the map's
      projection to the same and make sure the layer's SRID is the same.

      All looks OK.

      I then use the "Hole Cutter" to add a hole to a polygon (or move a vertex,
      it doesn't matter) and press the COmmit button on the toolbar. Visibly the
      edits disappear and the geometry returns to the original.

      Checking the Submit Log (is there a way to CLEAR this log?) I get:

      org.geotools.data.DataSourceException: SQL Exception writing geometry column
      ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
      ORA-13365: layer SRID does not match geometry SRID
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 243

      I have double checked. The map and the layer have the following uDIG SRID at
      the time of edit and commit:

      "GDA94 / MGA zone 56 (EPSG:28356)"

      and the Oracle database tabe sdo_geometry sdo_srid is also 28356.

      Additional details from email:

      I did a bit of mucking about with uDig and Oracle to find out what uDig is writing that is causing the mismatch in SRIDs.

      I ascertained this:

      1. You are doing an UPDATE using the primary key OID attribute (fine).
      2. The geometry you are writing does not honor the SDO_SRID value in the original geometry replacing it with NULL.

      What I did was

      a. let uDig get the data it needed into memory.
      b. Selected and editing my feature.
      c. Went in to Oracle and killed the spatial index.
      d. Pressed commit in uDig (the data is committed but, of course, the refresh of the display does not because there is not spatial index).
      e. Checked what was committed:

      select a.geom.sdo_srid,count
      from parcel_drawing a
      group by a.geom.sdo_srid;

      GEOM.SDO_SRID COUNT
      ------------- --------
      (null) 1
      28356 1218

      f. manually updated the NULL SDO_SRID value;
      g. Rebuilt the spatial index
      h. Refreshed uDig --> Bingo. The edit is there.

      So, it looks like when you are updating an existing shape the code seems to NULL out the existing value.

      Conclusion: don't edit Oracle Spatial data until a patch/fix is made available!

        Issue Links

          Activity

          Jody Garnett made changes -
          Field Original Value New Value
          Description From Simon Greener via email:
          {panel}
          I create a connection, add a layer with Oracle SRID 28356. I set the map's
          projection to the same and make sure the layer's SRID is the same.

          All looks OK.

          I then use the "Hole Cutter" to add a hole to a polygon (or move a vertex,
          it doesn't matter) and press the COmmit button on the toolbar. Visibly the
          edits disappear and the geometry returns to the original.

          Checking the Submit Log (is there a way to CLEAR this log?) I get:

          org.geotools.data.DataSourceException: SQL Exception writing geometry column
          ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
          ORA-13365: layer SRID does not match geometry SRID
          ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 243

          I have double checked. The map and the layer have the following uDIG SRID at
          the time of edit and commit:

          "GDA94 / MGA zone 56 (EPSG:28356)"

          and the Oracle database tabe sdo_geometry sdo_srid is also 28356.
          {panel}
          From Simon Greener via email:
          {panel}
          I create a connection, add a layer with Oracle SRID 28356. I set the map's
          projection to the same and make sure the layer's SRID is the same.

          All looks OK.

          I then use the "Hole Cutter" to add a hole to a polygon (or move a vertex,
          it doesn't matter) and press the COmmit button on the toolbar. Visibly the
          edits disappear and the geometry returns to the original.

          Checking the Submit Log (is there a way to CLEAR this log?) I get:

          org.geotools.data.DataSourceException: SQL Exception writing geometry column
          ORA-29877: failed in the execution of the ODCIINDEXUPDATE routine
          ORA-13365: layer SRID does not match geometry SRID
          ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 243

          I have double checked. The map and the layer have the following uDIG SRID at
          the time of edit and commit:

          "GDA94 / MGA zone 56 (EPSG:28356)"

          and the Oracle database tabe sdo_geometry sdo_srid is also 28356.
          {panel}

          Additional details from email:
          {panel}
          I did a bit of mucking about with uDig and Oracle to find out what uDig is writing that is causing the mismatch in SRIDs.

          I ascertained this:

          1. You are doing an UPDATE using the primary key OID attribute (fine).
          2. The geometry you are writing does not honor the SDO_SRID value in the original geometry replacing it with NULL.

          What I did was

          a. let uDig get the data it needed into memory.
          b. Selected and editing my feature.
          c. Went in to Oracle and killed the spatial index.
          d. Pressed commit in uDig (the data is committed but, of course, the refresh of the display does not because there is not spatial index).
          e. Checked what was committed:

          select a.geom.sdo_srid,count(*)
           from parcel_drawing a
           group by a.geom.sdo_srid;

          GEOM.SDO_SRID COUNT(*)
          ------------- --------
          (null) 1
          28356 1218

          f. manually updated the NULL SDO_SRID value;
          g. Rebuilt the spatial index
          h. Refreshed uDig --> Bingo. The edit is there.

          So, it looks like when you are updating an existing shape the code seems to NULL out the existing value.

          Conclusion: don't edit Oracle Spatial data until a patch/fix is made available!
          {panel}
          Jody Garnett made changes -
          Link This issue depends upon GEOT-2476 [ GEOT-2476 ]
          made changes -
          Status Open [ 1 ] Analysis [ 10002 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.2.0 [ 16264 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.2.1 [ 16440 ]
          Fix Version/s UDIG 1.2.0 [ 16264 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.2.x [ 15072 ]
          Fix Version/s UDIG 1.2.1 [ 16440 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.2.3 [ 17485 ]
          Fix Version/s UDIG 1.2.2 [ 15072 ]
          Jody Garnett made changes -
          Fix Version/s uDig 1.3.0 [ 17860 ]
          Fix Version/s UDIG 1.2.3 [ 17485 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.2.3 [ 17485 ]
          Fix Version/s uDig 1.3.0 [ 17860 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.3.1 [ 18149 ]
          Fix Version/s UDIG 1.3.0 [ 17485 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.3.2 [ 18235 ]
          Fix Version/s UDIG 1.3.1 [ 18149 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.3.3 [ 18773 ]
          Fix Version/s UDIG 1.3.2 [ 18235 ]
          Jody Garnett made changes -
          Fix Version/s UDIG 1.4.1 [ 19165 ]
          Fix Version/s UDIG 1.4.0 [ 18773 ]

            People

            • Assignee:
              Unassigned
              Reporter:
              Jody Garnett
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated: