Oracle / SDE: ST_GEOMETRY or SDE Compressed Binary

Question

I read data from a SDE database (Oracle) directly. How do I know, which format is used to store the geometry data? Do I have to check the DataType for the geometry column or is this information available in one of the global tables?

Answer 1

You can run the command "desc [feature class name];" in SQL Plus (Or any other way you access oracle"

Look at the Shape columns data type, that should tell you which Geometry type is used, for instance:

SQL> desc roads;

Name        NULL?       Type
------------------------------------
OBJECTID    NOT NULL    NUMBER(38)
FNODE_                  NUMBER(38)
TNODE_                  NUMBER(38)
LPOLY_                  NUMBER(38)
RPOLY_                  NUMBER(38)
LENGTH                  NUMBER(38,8)
ROADS_                  NUMBER(38)
ROADS_ID                NUMBER(38)
NAME                    NVARCHAR2(30)
ALTERNATE_NAME_1        NVARCHAR2(30)
ALTERNATE_NAME_2        NVARCHAR2(30)
ROAD_DIRECTION          NVARCHAR2(4)
LANE_DIRECTION          NVARCHAR2(2)
SHAPE                   SDE.ST_GEOMETRY

SHAPE SDE.ST_GEOMETRY

Answer 2

In order to get default geometry storage format (in most cases all your data will be in default format) you can run SQL query:

SELECT config_string FROM DBTUNE WHERE keyword='DEFAULTS' AND parameter_name='GEOMETRY_STORAGE'

But this default variable can be overrided of course.

UPDATE

To get shape column name and type you can execute such SQL query:

select spatial_column, layer_config from sde.LAYERS where table_name = 'YOUR_FEATURECLASS_NAME'

Column type can have next values:

1. DEFAULT - then default geometry type can be determined from the first query.
2. Overridden geometry type, for example SDO_GEOMETRY.