10

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?

6

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"
2

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

1

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

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.