1

I need to remove Z dimension on spatial column (ST_GEOMETRY) with Esri on Oracle using the PL/SQL packages

I need to do the following, but using the ESRI PL/SQL packages on Oracle.

I have used a couple of the packages such as SDE.ST_GEOMETRY_OPERATORS, however I am unsure if doing the below (removing Z) is possible in PL/SQL.

There are directions of how to do this in ArcMap:

https://support.esri.com/en/technical-article/000010389

  • Browse to ArcToolbox > Conversion Tools > To Geodatabase.
  • Open the Feature Class to Geodatabase (multiple) tool.
  • Add all the feature classes into the Input Feature Class parameter.
  • Select an Output Geodatabase.
  • Click the Environments button at the bottom of the tool dialog box.
  • Expand the General Settings.
  • For the parameter, Output has Z Values, change the value to Disabled.
  • For the parameter, Output has M Values, change the value to Disabled.
  • Click OK in the Environments dialog box.
  • Click OK to execute the geoprocessing tool.

How can I do the equivalent in PL/SQL?

Improve this question
5
  • That is what I want to do. I am trying to ETL esri tables into an oracle data warehouse with sdo_geometries through an oracle db_link.
    – Doc
    Commented Aug 31, 2020 at 11:55
  • Also I have an oracle plsql function which will convert the st_geometry into a wkt well know text. I also have another plsql function to pull the wkt clob through the dblink. The only thing I could not get via plsql was the conversion to a tableau (resorting) friendly 2d layer.
    – Doc
    Commented Aug 31, 2020 at 11:58
  • I meant reporting tool friendly ( such as tableau ) by removing the z dimension
    – Doc
    Commented Aug 31, 2020 at 12:00
  • My experience with Z-enabled feature classes has been that the Z values are often in the feature classes by mistake; they're not actually used. Do you have the option of asking the data owner to remove the Z-dimension from the feature classes?
    – User1974
    Commented Sep 2, 2020 at 2:09
  • I have reporting tools such as Tableau and Thoughtspot. They only use 2D geometries. That is why I hope to remove the Z attributes. I am not sure if Oracle can accept the ESRI exported Z WKT's (Well known text). I have a regex expression to remove the z dimensions from the string, but I would MUCH prefer do it with pl/sql via a transform type of function ...such as ESRI's sde.st_transform(shape,4269) or Oracle's SDO_CS.TRANSFORM(SHAPE,4269)
    – Doc
    Commented Sep 9, 2020 at 2:18

1 Answer 1

Reset to default
0

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:

  1. We can remove the Z from the WKT string using the REPLACE function.
  2. 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.
  3. 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).

Related:

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.