Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We're migrating some feature classes from SDELOB (or some older binary format) to ST_Geometry. As we write our arcpy script to use the ESRI Migrate Storage Tool we'd like to test whether or not something already is ST_Geom...Other than writing hooks into the database, using the comtypes package etc., does anyone know of a simple way to determine this?

share|improve this question
up vote 6 down vote accepted

To follow-up on PolyGeo's answer, the Describe object is certainly where it should be, but it isn't. Database queries may be the best way to go here.

Although, if you have the SDE command-line binaries, another option would be to try doing something like this (yes it's a hack, but might work):

import subprocess
output = subprocess.check_output("sdelayer -o describe_long -s servername -D dbname -u username -p password -l tablename,columname")
for line in output:
    if line.find("Layer Type") == 0:
        storagetype = line.partition(":")[2].strip() # Should be something like "SDE-BINARY" or "Extended SQL Type/ST_GEOMETRY"
share|improve this answer

It would be good if the Describe object were able to access this.

This has now been submitted as an ArcGIS Idea (on which you can vote).

share|improve this answer

This should be more accessible through ArcPy but in the meantime we can take advantage of the SDE registry tables to find the answer. The SDE.GEOMETRY_COLUMNS table contains the names of feature classes F_TABLE_NAME, their schema owner F_TABLE_SCHEMA, and their corresponding geometry table name G_TABLE_NAME.

If the feature and geometry table names are the same then the feature class' geometry is stored in the same table as the attributes; otherwise the geometry is stored in a separate feature table (aka "F" table).

So our SQL query would be something like:

SELECT COUNT(*) AS GEOMETRY_IN_FEATURE_TABLE
FROM SDE.GEOMETRY_COLUMNS
WHERE F_TABLE_SCHEMA || '.' || G_TABLE_NAME = :featureClassName

For ArcPy, assume "GEODATABASE.sde" is the name of an SDE connection file and "GIS.TAX_PARCELS_POLY" is the name of a feature class stored in the "GIS" schema:

import arcpy, os

dstFeatureClass = "sde/DC_LISDBA (GIS).sde/GIS.PARCEL_PARCELS_POLY"

#open a connection to the geodatabase
wks = arcpy.ArcSDESQLExecute(os.path.dirname(dstFeatureClass))

#create the SQL query statement
sql = \
    "SELECT COUNT(*) AS GEOMETRY_IN_FEATURE_TABLE " \
    "FROM SDE.GEOMETRY_COLUMNS " \
    "WHERE F_TABLE_SCHEMA || '.' || G_TABLE_NAME = " + \
    "'" + os.path.basename(dstFeatureClass) + "'"

#execute the query and return the geometry type
print "ST_Geometry" if wks.execute(sql) else "SDEBINARY"

#delete the geodatabase connection
del [wks, sql]

Note this solution only tells you if the geometry is stored in the same table as the feature class and not the actual column type (e.g. SDEBINARY, SDELOB, ST_Geometry) though I do believe that can be found elsewhere in the SDE tables or Oracle.

This could also be further optimized by creating an Oracle function and/or creating a view on the SDE.GEOEMTRY_COLUMNS table.

* Tested with ArcSDE 10.2.2 on Oracle 11g (64-bit).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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