I've created a test SDE.ST_GEOMETRY polyline feature class that has Z coordinates. This is what the WKT text looks like:
select
sde.st_astext(shape)
from
st_geom_z
SDE.ST_ASTEXT(SHAPE)
---------------------------------------------------------
LINESTRING Z ( 0.0 0.0 1.0, 10.0 20.0 2.0, 30.0 40.0 3.0)
^ ^ ^
You'll notice that the WKT has a " Z " in the prefix: LINESTRING Z (). I don't think Oracle SDO_GEOMETRY supports that syntax. I think it needs it to be like this: LINESTRING ().
Steps:
- We can remove the
Zfrom the WKT string using the REPLACE function. - Then we can convert the WKT to SDO_GEOMETRY.
- Unfortunately, ST_GEOMETRY doesn't have a function for removing Z coordinates from geometries. So we'll do it using SDO_GEOMETRY instead.
- We'll remove the Z's from the SDO_GEOMETRY using the SDO_CS.MAKE_2D function.
.
select
sdo_cs.make_2d(
sdo_geometry(
replace(upper(sde.st_astext(shape)),'LINESTRING Z','LINESTRING'))) sdo_geom_no_z
from
st_geom_z
SDO_GEOM_NO_Z
------------------
SDO_GEOMETRY(2002, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), SDO_ORDINATE_ARRAY(0, 0, 10, 20, 30, 40))
Now, the SDO_GEOMETRY vertices only have X&Y coordinates, not Z.
SDO_UTIL.TO_WKTGEOMETRY(SDO_GEOM_NO_Z)
------------------------------------------
LINESTRING (0.0 0.0, 10.0 20.0, 30.0 40.0)
Note: That query will also remove M-values too (not just Z values). If there were Z and M values, and we only wanted to remove the Z's, then I imagine we could use the SDO_LRS.CONVERT_TO_STD_GEOM function.
Related: