0

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

enter image description here

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?

4 Answers 4

1

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.

1
1
+100

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.
3
  • Yes. I did not include the removing of the extra 'M'. That is easily done with a replace() call. Apr 14 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
    Apr 15 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. Apr 16 at 18:57
0

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.

Related:

0

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))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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