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


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)).

  • 2
    Please edit the question to clarify the mechanism for geometry creation. Tables created by ArcGIS register the allowed types in a bitmask integer column in sde.layers, but SQL creation doesn't make such distinctions.
    – Vince
    May 12, 2016 at 23:28
  • Thanks for the feedback @Vince, I've added some details to the bottom.
    – serverpunk
    May 13, 2016 at 14:06
  • Did you look into the Geometry_type column of the SDE Geometry_Columns table? May 13, 2016 at 15:29
  • @KirkKuykendall mine just says ST_GEOMETRY
    – serverpunk
    May 13, 2016 at 16:36

1 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)


  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) "+"


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

         1          0          4          8          0     262144

         1          0          0          0         16     262144

         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.

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.