1

I'm trying to select a geometry column as well-known text from a feature class in an Oracle 11g/ArcSDE 10.1 geodatabase. (This is in a Python script using pypyodbc as a driver). This is my SQL:

SELECT SDE.ST_AsText(SHAPE) FROM OWNER.FEAT_CLASS

When I run this I get a few Oracle errors:

ORA-29900: operator binding does not exist\nORA-06553: PLS-306: wrong number or types of arguments in call to 'ST_ASTEXT

I found this support article which says to fully qualify all references to ST_GEOMETRY operators, but I have the SDE prefix so I'm not sure what's missing. Does anyone have any ideas regarding this?

UPDATE: If I just select the SHAPE field without any SDE functions I get Decimal('214'). Interestingly, that's also the object ID.

  • Not an SDE user, but from other spatial DBs I'd suggest trying SHAPE.ST_AsText() – Russell at ISC Oct 2 '14 at 16:00
  • Is the geometry column using SDE.ST_GEOMETRY or SDELOB/SDEBINARY storage? SDE.ST_AsText only works with a SDE.ST_GEOMETRY storage (and only if the libst_shapelib.so / st_shapelib.dll is registered with EXTPROC). – Vince Oct 2 '14 at 16:10
  • Stupid question but - how can I tell? Is the type stored in an SDE table somewhere? If I just describe the table the SHAPE field shows up as Decimal, which I know is not the case. – Rob Oct 2 '14 at 16:13
  • 1
    Yes, it's buried in a bitmask, but the easiest way is describe it in SQL*Plus -- it will show an SDE.ST_GEOMETRY datatype (or NUMBER(38) if SDELOB/SDEBINARY). Some other display mechanism might well show "Decimal" for a NUMBER(38) type. The only way to get WKT out of an SDELOB/SDEBINARY column is to use the ArcSDE API (or decode & encode on your own, based on a join to the Fn table). – Vince Oct 2 '14 at 16:29
  • Sure enough it was SDELOB... Could you elaborate on what the Fn table is? – Rob Oct 2 '14 at 17:49
1

In order to find out the Fn-Table where the actual points are stored, you can

select object_id from sde.column_registry where table_name = 'MYTABLE' and column_name = 'SHAPE';

The geometry will be stored in the table Fxxxx with xxxx = object_id.

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.