Get the geometry type of an empty ArcSDE feature class

Question

I'm trying to use raw SQL to get the geometry type (e.g. POINT, LINESTRING, POLYGON) of an Oracle ArcSDE feature class using the ST_Geometry spatial type. If I run

SELECT SDE.ST_GeometryType(SHAPE) FROM STREETS

and the table is empty, I get zero results, even though I specified that it's a line layer at the time of creation. I've run into the same thing in PostGIS but there's a workaround to still find the geometry type. Is there an equivalent for Esri ST_Geometry?

EDIT: The feature class was created interactively in ArcCatalog and will be populated using SQL statements, i.e. INSERT INTO STREETS (SHAPE) VALUES (SDE.ST_Geometry('LINESTRING (...)', 4326)).

Answer 1

The sde.ST_GEOMETRY type itself is typeless (it doesn't care if multiple topology classes exist). However, ArcGIS for Desktop and Server do care, and won't permit multiple topology classes.

When geometry is created by PostGIS in PostgreSQL, the topology class is registered via an explicit AddGeometryColumn request. sde.ST_GEOMETRY does not have an analogous method, but registration with the geodatabase does populate the sde.LAYERS table (and sde.ST_GEOMETRY_COLUMNS, though it's of no use for this purpose). Of course, when you use ArcObjects (through Desktop, Pro, Server, or ArcPy) to create a feature class with sde.ST_GEOMETRY geometry storage, this population is automatically performed.

The permitted entity types are stored in the EFLAGS column in sde.LAYERS masked in with the following bit positions:

    NIL           -> 2^0    (1)
    POINT         -> 2^1    (2)
    LINE          -> 2^2    (4)
    SIMPLELINE    -> 2^3    (8)
    POLYGON       -> 2^4    (16)
    MULTIPART     -> 2^18   (262144)

Notes:

1. sde.ST_GEOMETRY distinguishes between Line ("spaghetti") and SimpleLine (the formal non-self-intersecting line), though most "line" features are defined to support either (bitwise AND)
2. Multi-part is just an attribute of Point, Line, SimpleLine and Polygon layers, not a list of separate types as in some other implementations

Therefore, it is only possible to determine the range of supported types in sde.ST_GEOMETRY if the table has been registered, at which point you can use the sde.LAYERS.EFLAGS mask with bitwise operators to determine which geometry flavors are supported. Oracle itself has an idiosyncratic way of accessing bitwise operators, but the following example shows detection of the type of four tables (note that most layers are permitted NIL geometry)

SELECT  table_name,
        bitand(eflags,1) n,
        bitand(eflags,2) p,
        bitand(eflags,4) s,
        bitand(eflags,8) l,
        bitand(eflags,16) a,
        bitand(eflags,262144) "+"
FROM    sde.LAYERS
/


TABLE_NAME
--------------------------------------------------------------------------------

         N          P          S          L          A          +
---------- ---------- ---------- ---------- ---------- ----------
EX_POINT
         1          2          0          0          0          0

EX_LINE
         1          0          4          8          0     262144

EX_POLYGON
         1          0          0          0         16     262144

EX_MULTIPOINT
         1          2          0          0          0     262144

There are other bit values masked in there as well, so it is best practice to avoid doing any sort of update on the EFLAGS value.

If you use a naming scheme that embeds topology class into the table name (e.g., "political_level0_a", and "water_250k_l"), you could simplify your processing significantly.