sqloracle-databasetype-conversionoracle18coracle-spatial

Convert MDSYS.ST_LINESTRING subtype value to MDSYS.ST_GEOMETRY supertype


This question pertains to Oracle 18c's MDSYS.ST_GEOMETRY datatype. Which is not to be confused with Esri's SDE.ST_GEOMETRY datatype).


The following query produces an MDSYS.ST_LINESTRING subtype value:

with data as (
select st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as shape from dual)

select
    shape
from
    data
    
Result:
[MDSYS.ST_LINESTRING]

In a query, I want to convert the MDSYS.ST_LINESTRING subtype value to the MDSYS.ST_GEOMETRY supertype.

Reason: My GIS software can only handle MDSYS.ST_GEOMETRY supertype values (not MDSYS.ST_LINESTRING subtype values). I don't have control over this limitation.


I tried using the TREAT() function to make the covnersion, but it didn't seem to have an effect. The result is still the MDSYS.ST_LINESTRING subtype:

with data as (
select st_geometry.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as shape from dual)

select
    treat(shape as mdsys.st_geometry)
from
    data
  
The result is still:
[MDSYS.ST_LINESTRING] 

How can I convert the MDSYS.ST_LINSTRING subtype value to the MDSYS.ST_GEOMETRY supertype?


Solution

  • @SimonGreener pointed out that this works:

    with data as (
    select st_linestring.from_wkt('Linestring(1 2, 3 4, 5 6)',26917) as shape from dual)
    
    select
        treat(st_geometry(a.shape.geom) as mdsys.st_geometry) as shape
    from
        data a
    
    
    SHAPE
    --------------
    [MDSYS.ST_GEOMETRY]