3 Replies Latest reply: Feb 1, 2012 12:31 AM by Simon Greener RSS

    any way to remove the z element from an sdo_geometry?

      Hi all,

      I'm using :

      Oracle Database 10g Enterprise Edition Release - 64bi
      PL/SQL Release - Production
      CORE Production
      TNS for Linux: Version - Production
      NLSRTL Version - Production

      but I also have access to:

      Oracle Database 11g Enterprise Edition Release - 64bit Production
      PL/SQL Release - Production
      CORE Production
      TNS for Linux: Version - Production
      NLSRTL Version - Production

      I have a bunch of raw spatial data that I have imported from a KML file the kml has nodes that are greater than 64kb in size so I've used my 11g database to manipulate the kml and convert to geometry type using sdo_util.from_kmlgeometry. my target database is the 10g one above so I need my data ultimately in there.

      Because of the index I have set up on the target table in my 10g environment I need to remove the z component of the sdo_geometry prior to inserting into my table. (or at least, that's what I think I need to do).

      here's my index user_sdo_geom_metadata:
      SDO_DIM_ARRAY(SDO_DIM_ELEMENT('longitude', -180, 180, .5), SDO_DIM_ELEMENT('latitude', -90, 90, .5))
      with an SRID of 8307

      the error I'm getting when attempting to insert the geometry as it is is:
      ORA-29875: failed in the execution of the ODCIINDEXINSERT routine
      ORA-13364: layer dimensionality does not match geometry dimensions
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 722
      ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 225
      I'm assuming this is because of the z dimension in the source data?
        • 1. Re: any way to remove the z element from an sdo_geometry?
          Simon Greener
          Hello WhiteHat,

          Either do one of two things.

          1. Change the DIMINFO to accomodate the extra dimension.
          SDO_DIM_ARRAY(SDO_DIM_ELEMENT('longitude', -180, 180, .5), SDO_DIM_ELEMENT('latitude', -90, 90, .5), SDO_DIM_ELEMENT('Z', -9999, 9999, .5))
          2. Change all your SDO_GEOMETRY data in your table so that you remove the z (are you sure you want to throw data away?)

          This function will do it for you.
             *  @function To_2D
             *  @precis   Converts a geometry to a 2D geometry
             *  @version  2.0
             *  @usage    v_2D_geom := geom.To_2D(MDSYS.SDO_Geometry(3001,....)
             *  @history  Albert Godfrind, July 2006, Original Coding
             *  @history  Bryan Hall,      July 2006, Modified to handle points
             *  @history  Simon Greener,   July 2006, Integrated into geom with GF.
             *  @history  Simon Greener,   Aug  2009, Removed GF; Modified Byan Hall''s version to handle compound elements.
            Function To_2D( p_geom IN MDSYS.SDO_Geometry )
              Return MDSYS.SDO_Geometry
              v_gtype           INTEGER;     -- geometry type (single digit)
              v_dim             INTEGER;
              v_2D_geom         MDSYS.SDO_Geometry;
              v_npoints         INTEGER;
              v_i               PLS_INTEGER;
              v_j               PLS_INTEGER;
              v_offset          PLS_INTEGER;
            Function hasElementCircularArcs(p_elem_type in number)
              return boolean 
              Return ( p_elem_type in (4,5,1005,2005) );
            End hasElementCircularArcs;
              -- If the input geometry is null, just return null
              IF p_geom IS NULL THEN
                RETURN (NULL);
              END IF;
              -- Get the number of dimensions and the gtype
              v_dim   := p_geom.Get_Dims();
              v_gtype := MOD(p_geom.sdo_gtype,10); -- Short gtype
              IF v_dim = 2 THEN
                -- Nothing to do, p_geom is already 2D
                RETURN (p_geom);
              END IF;
              -- Compute number of points
              v_npoints := mdsys.sdo_util.GetNumVertices(p_geom);
              -- Construct output object ...
              v_2D_geom           :=  MDSYS.SDO_GEOMETRY (2000 + v_gtype,
                                       MDSYS.sdo_ordinate_array ()
              -- Does geometry have a valid sdo_point?
              If ( V_2d_Geom.Sdo_Point Is Not Null ) Then
                -- It's a point - possibly with sdo_ordinates.... fix this first...
                V_2d_Geom.Sdo_Point.Z := Null;
              End If;
                  If ( V_Gtype = 1 And P_Geom.Sdo_Ordinates Is Not Null ) Then
                V_2d_Geom.Sdo_Ordinates := P_Geom.Sdo_Ordinates;
              ElsIF ( v_gtype != 1 AND v_2D_geom.sdo_ordinates is not null ) THEN
                -- It's not a single point ...
                -- Process the geometry's ordinate array
                v_2D_geom.sdo_ordinates.EXTEND ( v_npoints * 2 );
                -- Copy the ordinates array
                v_i := p_geom.sdo_ordinates.FIRST;      -- index into input ordinate array
                v_j := 1;                               -- index into output ordinate array
                FOR i IN 1 .. v_npoints LOOP
                  v_2D_geom.sdo_ordinates (v_j)     := p_geom.sdo_ordinates (v_i);      -- copy X
                  v_2D_geom.sdo_ordinates (v_j + 1) := p_geom.sdo_ordinates (v_i + 1);  -- copy Y
                  v_i := v_i + v_dim;
                  v_j := v_j + 2;
                END LOOP;
                -- Process the element info array
                -- by adjust the offsets
                v_i := v_2D_geom.sdo_elem_info.FIRST;
                WHILE v_i < v_2D_geom.sdo_elem_info.LAST LOOP
                      If Not ( hasElementCircularArcs(v_2D_geom.sdo_elem_info (v_i + 1) ) ) Then
                        -- Adjust Elem Info offsets
                        v_offset := v_2D_geom.sdo_elem_info (v_i);
                        v_2D_geom.sdo_elem_info(v_i) := (v_offset - 1) / v_dim * 2 + 1;
                      End If;
                      v_i := v_i + 3;
                END LOOP;
              END IF;
              RETURN v_2D_geom;
            END To_2D;
          Execute via:
          update <your table> a set a.<your geom> = to_2d(a.geom);

          • 2. Re: any way to remove the z element from an sdo_geometry?
            hmmm - having the luxury of access to the original kml I've just manually found and replaced the last dimension. (not losing information really as they are all 0 height).

            that function could be useful for the future though definitely. thanks!

            In case you're interested Simon we're importing the ABS's census postcode and suburb boundaries. :-)
            • 3. Re: any way to remove the z element from an sdo_geometry?
              Simon Greener

              Better to fix it at the source.

              The function is available along with a lot of other tools/packages/procedures etc from my website www.spatialdbadvisor.com[.au]

              I do a bit of consulting for the ABS Geography group.... you can let me know how you go privately if you like (simon at spatialdbadvisor dot com dot au).

              (BTW if you have had your question answered, please indicate on this thread.)