Convert M-enabled SDE.ST_GEOMETRY to SDO_GEOMETRY using SQL

Question

I have an M-enabled SDE.ST_GEOMETRY polyline FC in an Oracle 18c EGDB.

select sde.st_astext(shape) as st_to_wkt from test_st_geom
MULTILINESTRING M (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0))

In a query, I want to convert the ST_GEOMETRY to SDO_GEOMETRY so that I can use Oracle Spatial's Linear Referencing package.

For reference, the following test works. It takes a M-enabled WKT string and converts it to SDO_GEOMETRY without issue.

select
    sdo_util.from_wktgeometry('MULTILINESTRING (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0))')
    as wkt_to_sdo
from
    dual

However, if I try to convert ST_GEOMETRY to WKT to SDO_GEOMETRY, then I get an error:

select
    sdo_util.from_wktgeometry(sde.st_astext(shape))
    as st_to_wkt_to_sdo
from
    test_st_geom

ORA-29532: Java call terminated by uncaught Java exception: java.lang.RuntimeException:
    oracle.spatial.util.GeometryExceptionWithContext: java.lang.RuntimeException: Opening parentheses missing -3  
    at oracle.spatial.util.WKT$WKTInputStream.readStartList(WKT.java)     
    at oracle.spatial.util.WKBasis.readStartList(WKBasis.java)    
    at oracle.spatial.util.WKBasis.toJGeometry_WKB_MULTIXXXSTRING(WKBasis.java)

    at oracle.spatial.util.WKBasis.toJGeometry_WKB_MULTILINESTRING(WKBasis.java)

    at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)  
    at oracle.spatial.util.WKBasis.toJGeometry(WKBasis.java)  
    at oracle.spatial.util.WKBasis.toStruct(WKBasis.java)     
    at oracle.spatial.util.Adapters.wktToSTRUCT2(Adapters.java)   
    at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java)    
    at oracle.spatial.util.Adapters.wktToSTRUCT(Adapters.java) 
ORA-06512: at "MDSYS.SDO_JAVA_STP", line 73 
ORA-06512: at "MDSYS.SDO_UTIL", line 6250
29532. 00000 -  "Java call terminated by uncaught Java exception: %s"
*Cause:    A Java exception or error was signaled and could not be resolved by the Java code.
*Action:   Modify Java code, if this behavior is not intended.

Why do I get that error?

Simon Greener · Accepted Answer · 2022-04-14 05:10:19Z

These are all good solutions.

My take would be to do everything, sdo_gtype and sdo_srid assignment, in a dedicated function:


CREATE OR REPLACE FUNCTION ST_FromSDEGeom( p_geom in sde.st_geometry )
RETURN mdsys.sdo_geometry
IS
  v_srid integer;
  v_ewkt clob;
  v_geom sdo_geometry;
BEGIN
  -- Get SRID from SDE.ST_GEOMETRY
  SELECT sde.ST_SRID(p_geom) INTO v_srid FROM dual;
  -- Get WKT from SDE.ST_AsText
  SELECT SDE.ST_AsText(p_geom) INTO v_ewkt FROM dual;
  -- Convert WKT to SDO_GEOMETRY (includes srid)
  v_geom := SDO_UTIL.FROM_WKTGEOMETRY(REPLACE(REPLACE(v_ewkt,'M ',''),'Z ',''),v_srid);
  -- Fix sdo_gtype (only handles where Z or M not ZM)
  IF ( INSTR(v_ewkt,'M ') <> 0 ) THEN
    v_geom.sdo_gtype := v_geom.sdo_gtype + 300;
  END IF;
  RETURN v_geom;
END ST_FromSDEGeom;
/
show errors

I cannot debug this as I do not have an SDE installation in my database.

Thanks Simon. I used some of your ideas in the SELECT in the answer below (gis.stackexchange.com/a/429145/62572). Would be happy to hear about any potential issues. — User1974, Commented Apr 18, 2022 at 20:00

Albert Godfrind · Accepted Answer · 2022-04-14 16:58:15Z

Actually, the following returns a wrong result:

SQL> select sdo_util.from_wktgeometry('MULTILINESTRING (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0))') from dual;

SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))')(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORD
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(3006, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, -100000, 10, 10, -100000, 30, 0, -100000, 50, 10, -100000, 60, 10, -100000))

1 row selected.

Notice that the GTYPE is 3006, i.e. a 3D multiline, i.e. the measures are now interpreted as Z values. It is not a LRS segment.

And the rejection of MULTILINESTRING M is because the WKT parser used by Oracle does not know about this notation.

For now, you can use a function like this:

create or replace function set_lrs (geom sdo_geometry)
return sdo_geometry 
is 
  lrs_geom sdo_geometry;
begin
  lrs_geom := geom;
  lrs_geom.sdo_gtype := lrs_geom.sdo_gtype + 300;
  return lrs_geom;
end;
/

That function sets the second digit of the GTYPE to 3, indicating that the third dimension is a measure instead of a z value:

SQL> select set_lrs(sdo_util.from_wktgeometry('MULTILINESTRING (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0))')) from dual;

SET_LRS(SDO_UTIL.FROM_WKTGEOMETRY('MULTILINESTRING((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))'))(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(3306, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, -100000, 10, 10, -100000, 30, 0, -100000, 50, 10, -100000, 60, 10, -100000))

1 row selected.

or simpler:

SQL> select set_lrs(sdo_geometry('MULTILINESTRING (( 0.0 5.0 -100000.0, 10.0 10.0 -100000.0, 30.0 0.0 -100000.0),( 50.0 10.0 -100000.0, 60.0 10.0 -100000.0))')) from dual;

SET_LRS(SDO_GEOMETRY('MULTILINESTRING((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))'))(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINAT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(3306, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, -100000, 10, 10, -100000, 30, 0, -100000, 50, 10, -100000, 60, 10, -100000))

1 row selected.

Now the GTYPE is 3306 indicating that we have a valid LRS segment.

EDIT I realize I did not do anything about the extra M not recognized by Oracle. This can be eliminated with a simple SQL replace() function:

SQL> select replace('MULTILINESTRING M ((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))','LINESTRING M','LINESTRING') from dual;

REPLACE('MULTILINESTRINGM((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))','LINESTRINGM','LINESTRING')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MULTILINESTRING ((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))

1 row selected.

And the complete operation would be like this:

select set_lrs(sdo_geometry(replace(wkt_geom,'LINESTRING M','LINESTRING')))from dual;

Or better, let's use a simple function that makes it simpler:

create or replace function from_lrs_wkt (wkt_geom clob)
return sdo_geometry 
is 
  lrs_geom sdo_geometry;
begin
  lrs_geom := sdo_geometry(replace(upper(wkt_geom),'LINESTRING M','LINESTRING'));
  lrs_geom.sdo_gtype := lrs_geom.sdo_gtype + 300;
  return lrs_geom;
end;
/

And an example:

SQL> select from_lrs_wkt('MULTILINESTRING M ((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))') from dual;

FROM_LRS_WKT('MULTILINESTRINGM((0.05.0-100000.0,10.010.0-100000.0,30.00.0-100000.0),(50.010.0-100000.0,60.010.0-100000.0))')(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SDO_GEOMETRY(3306, NULL, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(.05, 0, -100000, 10.01, 0, -100000, 30, 0, -100000, 50.01, 0, -100000, 60.01, 0, -100000))

1 row selected.

Yes. I did not include the removing of the extra 'M'. That is easily done with a replace() call. — Albert Godfrind, Commented Apr 14, 2022 at 16:44
Thanks Albert. Just in case anyone's interested, and as you're aware, there is a related post here: Modify object attribute/property — without creating custom function. But as you mentioned, that doesn't seem to be possible. Cheers! — User1974, Commented Apr 15, 2022 at 17:16
Indeed. But it would make sense (and not very hard) to add methods on the SDO_GEOMETRY type to allow some of those on-the-fly modifications. — Albert Godfrind, Commented Apr 16, 2022 at 18:57

User1974 · Accepted Answer · 2022-04-18 20:24:19Z

Here's a way of doing it in the SELECT clause. It avoids the need to create a custom function or an inline function.

select
    objectid,
    --Oracle misinterprets the Ms as Zs. So we need to change the GTYPE after-the-fact (from 3006 to 3306).
    sdo_geometry(a.shape.sdo_gtype + 300,
                 a.shape.sdo_srid,
                 a.shape.sdo_point,
                 a.shape.sdo_elem_info,
                 a.shape.sdo_ordinates) as shape
from
    (
    select
        cast(objectid as number(38,0)) as objectid,
        --Oracle doesn't support the 'LINESTRING M' WKT syntax
        sdo_geometry(replace(sde.st_astext(shape),'LINESTRING M','LINESTRING'),sde.st_srid(shape)) shape 
    from
        my_st_geom_fc
    ) a

OBJECTID  SHAPE
--------  --------------------------
     321  SDO_GEOMETRY(3306, 26917, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1, 10, 2, 1), SDO_ORDINATE_ARRAY(0, 5, -100000, 10, 10, -100000, 30, 0, -100000, 50, 10, -100000, 60, 10, -100000))

That query runs without errors in my 18c environment (with the SDE.ST_GEOMETRY datatype).

And if I create a view from it, the view can be used in ArcMap (the M-values, OBJECTID/unique-ID, and SRID work as expected). Although, performance will likely be poor for large datasets. Ideally, I'd just change the datatype in the table from ST_GEOMETRY to SDO_GEOMETRY.

User1974 · Accepted Answer · 2022-05-20 19:11:49Z

Here's my version of the function. The resulting SDO_GEOMETRY has an SRID.

with function wkt_m_to_sdo_lrs(wkt_geom clob, srid number)
return sdo_geometry 
is 
    lrs_geom sdo_geometry;
begin
    lrs_geom := sdo_geometry(replace(upper(wkt_geom),'LINESTRING M','LINESTRING'),srid);
    lrs_geom.sdo_gtype := lrs_geom.sdo_gtype + 300;
    return lrs_geom;
end;

select
    wkt_m_to_sdo_lrs(sde.st_astext(shape),sde.st_srid(shape)) sdo_lrs
from
    infrastr.active_transportation


SDO_LRS            
------------       🡇srid🡇
SDO_GEOMETRY(3302, 300125, NULL, SDO_ELEM_INFO_ARRAY(1, 2, 1), 
    SDO_ORDINATE_ARRAY(672897.21770533, 4862588.37130088, 0, 672905.3878064, 4862591.23370202, 8.65, 672979.82340422, 4862617.3121031, 87.55, 672986.24540502, 4862619.56210056, 94.35, 672990.07590644, 4862620.90410221, 98.4))

Stack Exchange Network

Convert M-enabled SDE.ST_GEOMETRY to SDO_GEOMETRY using SQL

4 Answers 4

Your Answer

Not the answer you're looking for? Browse other questions tagged
enterprise-geodatabase
oracle-spatial
oracle-dbms
st-geometry
sdo-geometry
or ask your own question.

Linked

Hot Network Questions

Convert M-enabled SDE.ST_GEOMETRY to SDO_GEOMETRY using SQL

4 Answers 4

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged enterprise-geodatabaseoracle-spatialoracle-dbmsst-geometrysdo-geometry or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
enterprise-geodatabase
oracle-spatial
oracle-dbms
st-geometry
sdo-geometry
or ask your own question.