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.

Improve this question
5
  • Not an SDE user, but from other spatial DBs I'd suggest trying SHAPE.ST_AsText()
    – Russell at ISC
    Commented Oct 2, 2014 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
    Commented Oct 2, 2014 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.
    – bertday
    Commented Oct 2, 2014 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
    Commented Oct 2, 2014 at 16:29
  • Sure enough it was SDELOB... Could you elaborate on what the Fn table is?
    – bertday
    Commented Oct 2, 2014 at 17:49

1 Answer 1

Reset to default
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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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