Top 5 Recent Articles
ARTICLES CATEGORIES
- Algorithms (22)
- All (399)
- Biography (1)
- Blog (44)
- Business Requirements (1)
- Commentary (1)
- Conversion (2)
- Customers (2)
- Data Models (1)
- Education (2)
- GeoRaptor (13)
- GPS (1)
- Image Processing (2)
- Import Export (8)
- Licensing (2)
- LiDAR (1)
- Linear Referencing (4)
- Manifold GIS (3)
- Mapping (1)
- MySQL Spatial (7)
- Networking and Routing (including Optimization) (5)
- Open Source (18)
- Oracle Spatial and Locator (194)
- Partitioning (1)
- PostGIS (36)
- Projections (1)
- Published Articles (1)
- qGIS (1)
- Recommendations (1)
- Services (1)
- Software Change Log (1)
- Source Code (37)
- Space Curves (9)
- Spatial Database Functions (109)
- Spatial DB comparison (1)
- Spatial XML Processing (11)
- SQL Server Spatial (92)
- Standards (3)
- Stored Procedure (17)
- Tessellation or Gridding (10)
- Tools (2)
- Topological Relationships (1)
- Training (2)
- Triangulation (2)
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!