ST_GEOMETRY to SDO_GEOMETRY

Question

Is it possible for QGIS to read ESRI ST_GEOMETRY in an Oracle database? The ESRI geometry type looks like this:

SDE.ST_GEOMETRY(260,61,491455.9942,5456922.8891,492388.3189,5457507.9101,NULL,NULL,NULL,NULL,0,863.557608125545,26910,(BLOB))

Alternatively, is there an SDE function or group of functions that would represent the ST_GEOMETRY as SDO_GEOMETRY? Then I could make a view for the ST_GEOMETRY and view it in QGIS and other non-ESRI software.

Answer 1

One possible solution is to get ESRI's ST_Geometry into a WKT or WKB notation, then use that to construct SDO_GEOMETRY objects. Something like this (assuming table SHAPES contains a column called SHAPE of type sde.ST_Geometry:

select sdo_geometry(sde.st_astext(shape)) from shapes;

You could use that as a view:

create view shapes_sdo as 
select ... sdo_geometry(sde.st_astext(shape)), ...
from shapes;

Now the catch is that this will not have any spatial index, so will be very slow to display and impossible to use with any spatial query. Maybe then you can create a copy instead, in a new table:

create table shapes_sdo as 
select ... sdo_geometry(sde.st_astext(shape)), ...
from shapes;

Then setup the usual metadata and spatial index. That can then be used by any GIS tool on the planet (including all open source tools). If you need to maintain the two copies in sync, just add a trigger to do that.