2

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.

  • Your question title appears to be your intended solution. I'd recommend that you rewrite the question to focus on your goal (e.g., "Access SDE.ST_GEOMETRY from QGIS"). There is no reason why this could not have been accomplished while the ArcSDE SDK was available, but now that the API has been deprecated for two releases, there's less in the way of options. Best practice is to store geometries in SDO_GEOMETRY if third-party tools require access, so you're looking to circumvent the simple solution. – Vince Apr 1 '16 at 18:50
  • Our organization has decided to store geometry as ESRI ST_GEOMETRY, so it is not an option for me to store everything as SDO_GEOMETRY. But some our software, Autodesk, for example, can't read ESRI ST_GEOMETRY. In lieu of an ETL process to copy all of our ST_GEOMETRY into another database with SDO_GEOMETRY, I am hoping that there is a way to represent (via a function or combination of functions) that ST_GEOMETRY as SDO_GEOMETRY. QGIS, Autodesk, and MapInfo are three clients who need this. Is it possible? – Jim Apr 1 '16 at 20:35
  • Please put these details in the question – Vince Apr 1 '16 at 21:09
4

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.

  • 1
    Yes, that worked. I created a view by MDSYS.SDO_GEOMETRY (SDE.ST_AsText (shape), 26910) AS SHAPE. I then added a record to the USER_SDO_GEOM_METADATA of the form: INSERT INTO USER_SDO_GEOM_METADATA (TABLE_NAME,column_name,diminfo,srid) values('VW_PARK_POLYGONS_ST','SHAPE', MDSYS.SDO_DIM_ARRAY ( MDSYS.SDO_DIM_ELEMENT('X',481237.596, 498270.4919, 0.001), MDSYS.SDO_DIM_ELEMENT( 'Y',5449712.9147, 5462386.1671, 0.001), MDSYS.SDO_DIM_ELEMENT('Z',0,0,0.0005) ), 26910); Now I can see the features in QGIS. Thanks for your help. – Jim Apr 4 '16 at 14:46
  • @Jim - please read gis.stackexchange.com/help/someone-answers – user2856 Jan 5 '18 at 4:22

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.