Report on the geometry storage type used for all spatial data in an Enterprise Geodatabase using Python

Summary

All geodatabases store geometry (hence the geo in geodatabase). Each DBMS product used for enterprise geodatabases provides specific geometry storage mechanisms. Oracle, SQL Server, and PostgreSQL DBMSs allow you to choose from different geometry storage options.

For SQL Server databases, you can use Microsoft's geometry or geography spatial types. For Oracle databases, you can use either ST_Geometry, or Oracle Spatial. For PostgreSQL, you can use ST_Geometry or the PostGIS geometry type.

The following geometry storage types have been deprecated in ArcGIS Pro:

• SDEBINARY
• SDELOB
• WKB_GEOMETRY

You may view data with these geometry storage types, but you cannot create new data using these storage types.

Organizations may require to report on or obtain a list of the geometry storage type used for each feature class in an enterprise geodatabase.

The list can be used to, for example, migrate all feature classes using SDEBINARY to a geometry storage type supported with ArcGIS Pro and the DBMS.

Procedure

We can use Python scripting and the ArcPy site package to extract the geometry storage information for each feature class from the geodatabase. Firstly, to obtain a list of all the feature classes in an enterprise geodatabase use the ListFeatureClasses function. Secondly, describe each feature class using the Describe function. The describe function returns a describe object that provides detailed information on each feature class using a set of dynamic properties. One of the properties returned with describe object for feature classes is the 'geometryStorage' property. The property specifies the geometry storage type used by the feature class.

A sample script to obtain a list of the geometry storage type used for each feature class in an enterprise geodatabase:

import arcpy

# Set the workspace connection to the geodatabase
arcpy.env.workspace = "geodatabase.sde"

# Get a list of all the feature datasets
datasets = arcpy.ListDatasets(feature_type='feature')
datasets = [''] + datasets if datasets is not None else []

for ds in datasets:
    # Get a list of the feature classes in each feature dataset
    for fc in arcpy.ListFeatureClasses(feature_dataset=ds):
        # Describe the dataset and print out the storage type
        fc_ds = arcpy.Describe(fc)
        print("Feature Class Name: {0}, Geometry Storage Type: {1}".format(fc_ds.name, fc_ds.geometryStorage))

The script produces the following output:

Feature Class Name: new_sql.GISOWNER.BV_Points, Geometry Storage Type: MSSQLGeometry
Feature Class Name: new_sql.GISOWNER.point_attachments_02875242, Geometry Storeage Type: MSSQLGeometry
Feature Class Name: new_sql.GISOWNER.geomTest, Geometry Storage Type: MSSQLGeography
Feature Class Name: new_sql.GISOWNER.geomTest2, Geometry Storage Type: MSSQLGeometry