SpatialDB Advisor

New To_3D Function

I often have need to convert 2D geometry objects from 2D to 3D and have wrote a function that was part of my free PL/SQL packages that did this. That function depended on another package.

Just this week I had need of a standalone version, so I went back to my old function and removed all dependencies on any other package.

This function, called TO_3D, actually does a number of things:

• Up-scales a 2D geometry to 3D. If only the first Z parameter to the function is provided that is assigned to each and every Z ordinate; if both Z parameters are provided the function computes the appropriate value based on the ratio of the distance to the vertex / total length of the geometry; The function correctly handles ascending and descending ranges;
• If an SDO_POINT object exists independently of any SDO_ELEM_INFO structures that point object is converted and assigned the value of the first z parameter;
• Downscales a 4D object to 3D maintaining the existing 3D ordinate values;
• The function is compound (polygons and linestrings composed of circular arcs and vertex-connected linestrings) element aware.

Here is the function.

   /*
   --  function To_3D
   --  precis   Converts a 2D or 4D geometry to a 3D geometry
   --  version  1.0
   --  usage    v_3D_geom := geom.To_3D(MDSYS.SDO_Geometry(2001,....),50)
   --  history  Simon Greener,   May 2007 Original coding
   --  history  Simon Greener,   Aug 2009 Added support for interpolating Z values 
   */
  Function To_3D( p_geom      IN MDSYS.SDO_Geometry,
                  p_start_z   IN NUMBER,
                  p_end_z     IN NUMBER := NULL,
                  p_tolerance IN NUMBER := 0.05)
     Return MDSYS.SDO_Geometry Deterministic
  Is
    v_sign              PLS_INTEGER := SIGN(p_end_z - p_start_z);
    v_isMeasured        BOOLEAN;
    v_gtype             INTEGER;     -- geometry type (single digit)
    v_dim               INTEGER;
    v_npoints           INTEGER;
    v_i                 PLS_INTEGER;
    v_j                 PLS_INTEGER;
    v_offset            PLS_INTEGER;
    v_length            NUMBER := 0;
    v_cumulative_length NUMBER := 0;
    v_round_factor      NUMBER := case when p_tolerance is null
                                       then null
                                       else round(log(10,(1/p_tolerance)/2))
                                   end;
    v_3D_geom           MDSYS.SDO_Geometry;

  Function isCompoundElement(p_elem_type in number)
    return boolean
  Is
  Begin
    Return ( p_elem_type in (4,5,1005,2005) );
  End isCompoundElement;

  Function isMeasured( p_gtype in number )
    return boolean
  is
  Begin
    Return CASE WHEN MOD(trunc(p_gtype/100),10) = 0
                THEN False
                ELSE True
             END;
  End isMeasured;

  Function GetDimensions( p_gtype in number )
    return integer
  Is
  Begin
    return TRUNC(p_gtype/1000,0);
  End GetDimensions;

  Begin
    -- 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   := GetDimensions(p_geom.sdo_gtype);
    v_gtype := MOD(p_geom.sdo_gtype,10); -- Short gtype
    v_isMeasured := isMeasured(p_geom.sdo_gtype);
   
    IF ( v_dim = 3 ) THEN
      -- Nothing to do, p_geom is already 3D
      Return (p_geom);
    END IF;

    IF ( p_start_z is not null And p_end_z is not null ) THEN
       v_length := mdsys.sdo_geom.SDO_Length( p_geom, p_tolerance );
    END IF;

    -- Compute number of points
    v_npoints := mdsys.sdo_util.GetNumVertices(p_geom);

    -- Construct output object ...
    v_3D_geom           := MDSYS.SDO_GEOMETRY (3000 + v_gtype,
                             p_geom.sdo_srid,
                             p_geom.sdo_point,
                             p_geom.sdo_elem_info,
                             MDSYS.sdo_ordinate_array ()
                           );

    -- Does geometry have a valid sdo_point?
    IF ( v_3D_geom.sdo_point is not null ) Then
      -- It's a point, there's not much to it...
      v_3D_geom.sdo_point.Z := p_start_z;
    END IF;

    -- If Single Point, all done, else...    
    IF ( v_gtype != 1 ) THEN
      -- It's not a single point ...
      -- Process the geometry's ordinate array

      -- Create space in ordinate array for 3D coordinates
      v_3D_geom.sdo_ordinates.EXTEND ( v_npoints * 3 );

      -- Copy the ordinates array
      v_i := p_geom.sdo_ordinates.FIRST;      -- index into input ordinate array
      v_j := 1;                               -- index into output ordinate array
      v_cumulative_length := 0;
      FOR i IN 1 .. v_npoints LOOP
        v_3D_geom.sdo_ordinates (v_j)     := p_geom.sdo_ordinates (v_i);      -- copy X
        v_3D_geom.sdo_ordinates (v_j + 1) := p_geom.sdo_ordinates (v_i + 1);  -- copy Y
        -- compute and assign Z only if doesn't have existing value
        If ( ( v_dim = 4 ) Or ( v_dim = 3 And Not v_isMeasured ) ) Then
           v_3D_geom.sdo_ordinates (v_j + 2) := p_geom.sdo_ordinates (v_i + 2);  -- copy Z
        Else
           -- Compute new Z
          If ( i = 1 ) Then
            v_3D_geom.sdo_ordinates (v_j + 2) := p_start_z; 
          Else
            If ( v_length <> 0 ) Then
              v_cumulative_length := v_cumulative_length + 
                 round(sdo_geom.sdo_distance(mdsys.sdo_geometry(2001,
                                                                p_geom.sdo_srid,
                                                                sdo_point_type(p_geom.sdo_ordinates(v_i - v_dim),
                                                                               p_geom.sdo_ordinates(v_i - v_dim + 1),
                                                                               null),
                                                                null,null),
                                             mdsys.sdo_geometry(2001,
                                                                p_geom.sdo_srid,
                                                                sdo_point_type(p_geom.sdo_ordinates (v_i),
                                                                               p_geom.sdo_ordinates (v_i + 1),null),
                                                                null,null),
                                             p_tolerance),
                       v_round_factor);
            End If;
            If ( i = v_npoints ) Then
              v_3D_geom.sdo_ordinates (v_j + 2) := case when v_length = 0 then p_start_z else p_end_z end; 
            Else
              v_3D_geom.sdo_ordinates (v_j + 2) := case when p_end_z is null then p_start_z
                                                        when v_length != 0   then p_start_z + v_sign * round( ( ( p_end_z - p_start_z ) / v_length ) * v_cumulative_length,v_round_factor)
                                                        else p_start_z
                                                    end; 
            End If;
          End If;
        End If;
        v_i := v_i + v_dim;
        v_j := v_j + 3;
      END LOOP;

      -- Process the element info array
      -- by adjust the offsets
      v_i := v_3D_geom.sdo_elem_info.FIRST;
      WHILE v_i < v_3D_geom.sdo_elem_info.LAST LOOP
            If Not ( isCompoundElement(v_3D_geom.sdo_elem_info (v_i + 1) ) ) Then
              -- Adjust Elem Info offsets
              v_offset := v_3D_geom.sdo_elem_info (v_i);
              v_3D_geom.sdo_elem_info(v_i) := ( v_offset - 1 ) / v_dim * 3 + 1;
            End If;
            v_i := v_i + 2;
      END LOOP;

    END IF;
    RETURN v_3D_geom;
  END To_3D;

Now let’s test it by building a 3D geometry from a 2D one with full Z interpolation.

select geom.to_3d( a.original_geom, -1, -200, 0.05) as threed_geom
  from ( select mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,5,5,10,10)) as original_geom
           from dual 
       ) a;

THREED_GEOM
-----------
MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,-1,5,5,98.9,10,10,-200))

1 rows selected

Now, take the above and push it into a function to add measure information to the new 3D geometry.

select MDSYS.SDO_LRS.CONVERT_TO_LRS_GEOM( threed_geom, 1, 10) as lrs_geom, threed_geom
  from (select geom.to_3d( a.original_geom, -1, -200, 0.05) as threed_geom
          from ( select mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,5,5,10,10)) as original_geom
                   from dual 
               ) a
       ) b;

LRS_GEOM                                                                                                                          threed_geom
--------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(4402,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,-1,1,5,5,98.9,5.5,10,10,-200,10)) MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,-1,5,5,98.9,10,10,-200))

1 rows selected

Now let’s strip the measure information off the geometry to return our 3D geometry of above.

select geom.to_3d(c.lrs_geom,null,null,0.05) as geom_3d, c.threed_geom
          from (select MDSYS.SDO_LRS.CONVERT_TO_LRS_GEOM( threed_geom, 1, 10) as lrs_geom, threed_geom
                  from (select geom.to_3d( a.original_geom, -1, -200, 0.05) as threed_geom
                          from ( select mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,5,5,10,10)) as original_geom
                                   from dual 
                               ) a
                        ) b
               ) c;

GEOM_3D                                                                                                                  threed_geom
------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------
MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,-1,5,5,98.9,10,10,-200)) MDSYS.SDO_GEOMETRY(3002,null,null,MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),MDSYS.SDO_ORDINATE_ARRAY(0,0,-1,5,5,98.9,10,10,-200))

1 rows selected

Finally, let’s check the validity of the final geometry which should be the same as the original.

select sdo_geom.validate_geometry(d.geom_3d,0.05)
  from (select geom.to_3d(c.lrs_geom,null,null,0.05) as geom_3d, c.threed_geom
          from (select MDSYS.SDO_LRS.CONVERT_TO_LRS_GEOM( threed_geom, 1, 10) as lrs_geom, threed_geom
                  from (select geom.to_3d( a.original_geom, -1, -200, 0.05) as threed_geom
                          from ( select mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,5,5,10,10)) as original_geom
                                   from dual 
                               ) a
                        ) b
               ) c
        ) d;

SDO_GEOM.VALIDATE_GEOMETRY(D.GEOM_3D,0.05)
------------------------------------------
TRUE

1 rows selected

Finally, let’s reduce the 3D geometry back to a 2D geometry using a TO_2D function to see if we get the same geometry.

select sdo_geom.relate(geom.to_2D(d.geom_3d),'DETERMINE',mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,5,5,10,10)),0.05) as relate
  from (select geom.to_3d(c.lrs_geom,null,null,0.05) as geom_3d, c.threed_geom
          from (select MDSYS.SDO_LRS.CONVERT_TO_LRS_GEOM( threed_geom, 1, 10) as lrs_geom, threed_geom
                  from (select geom.to_3d( a.original_geom, -1, -200, 0.05) as threed_geom
                          from ( select mdsys.sdo_geometry(2002,null,null,sdo_elem_info_array(1,2,1),sdo_ordinate_array(0,0,5,5,10,10)) as original_geom
                                   from dual 
                               ) a
                        ) b
               ) c
        ) d;

RELATE
---------
EQUAL

1 rows selected

I hope this function is of use to someone as it certainly is useful to me!