I've noticed that SQL clients select the SHAPE column from SDE.ST_GEOMETRY in different ways:

select shape from roads 
  • Toad: SDE.ST_GEOMETRY(1,1,666666.0,4444444.5,666666.0,4444444.5,NULL,NULL,NULL,NULL,0,0,300051,(BLOB))
  • SQL Developer: [SDE.ST_GEOMETRY]
  • MS Access: Error: Invalid field definition 'SHAPE' in definition of index or relationship. (I know MS Access isn't a real SQL client; but it helps demonstrate the point.)


What does the text that is returned from Toad represent? Is it the properties of the blob?


TOAD writes out the meta data of Oracles ST_GEOMETRY UDT implementation for ESRIs SDE; it's essentially a list of geometric predicates describing the geometry, and the vertex array that builds the geometry type, held as BLOB:

  <ENTITY>,    -- geometry type (POINT, LINESTRING, ...)
  <NUMPTS>,    -- number of points
  <MINX>,      -- coordinates of the envelope (bbox)
  <MINY>,      -- ^
  <MAXX>,      -- ^
  <MAXY>,      -- ^
  <MINZ>,      -- range of Z values
  <MAXZ>,      -- ^
  <MINM>,      -- range of M values
  <MAXM>,      -- ^
  <AREA>,      -- area in CRS units (if any)
  <LEN>,       -- length in CRS units (if any)
  <SRID>,      -- CRS identifier
  <POINT_BLOB> -- vertex storage

as described in detail in ST_Geometry storage in Oracle.

  • I'm kind of surprised that TOAD dev team would have gone out of their way to do this (account for ESRI's implementation of ST_GEOMETRY). – User1973 Sep 18 '20 at 22:44

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.