ST_Geometry vs SDE Binary: seeking recommendations, experiences

Question

I've got some questions I've collected as we try to come to a decision about whether to use ST_Geometry or SDE native geometry for an Oracle/SDE datastore on a new project. The organization has quite a few legacy systems that use the SDE Binary type, and this would be the first to use ST_Geometry if we went that route.

Apart from the ability to access spatial data in ArcSDE with SQL, what are the benefits and drawbacks of ST_Geometry?

What sort of pitfalls have you experienced in implementing with one type or the other?

Can more than one data type live in the same schema?

Note: for this project the data in SDE will be all points and polygons.

Answer 1

I would say the main pitfall would be the documentation and configuration requirements on rebuild and cluster server building.
Simply put to remember exactly how you set up the last time. With all the little changes.
If the dba is seasoned and has built several systems it is just a matter of shifting repetitious activity to the new paradigm.
I have always preferred "other" data types to sdebinary.
But until you have been down the road, traveled up each side road, smoothed out the big bumps, and decided which turns were rabbit trails and which were necessary r your environment.
It is hard to know what needed to be documented.
But do it anyway!
That way you have something to go back through to add and remove from.
The question about using both in the same schema.
You don't want that.
Each data type has its own side management table schema and will render the other data unusable. (Ok it has been about 7 years since I went down that road. So I don't remember exactly what happened. But I do remember problems with the issue.)

Answer 2

As ST_Geometry has been the default storage type since 9.3 I would just go with that, but a breakdown of the pros/cons of each, especially as it relates to performance would be useful. It is likely to be faster and better supported by various applications, such as ArcGIS Spatial Data Server.

@Brad is probably right that for sanity's sake it's probably best to use the same storage type for all the data in a database schema, but it is definitely possible to mix and match by setting the GEOMETRY_STORAGE keyword when creating feature classes.

Regarding storage size, I did find a comparison showing ST_Geometry to be slightly better than SDEBINARY on the ESRI forums:

Storage    X/Y Coordref            Precision   -aall   -anone  Total%  Geom%
SDEBINARY   -210,-120,1000000       BASIC       1339    1261    -       -
SDEBINARY   -210,-120,1000000       HIGH        1339    1261    0.0     0.0
SDEBINARY   -400,-400,1000000000    HIGH        1540    1464    15.0    16.1
SDELOB      -210,-120,1000000       BASIC       1585    1509    18.4    19.7
SDELOB      -210,-120,1000000       HIGH        1588    1509    18.6    19.7
SDELOB      -400,-400,1000000000    HIGH        1826    1748    36.4    38.6
ST_GEOMETRY -210,-120,1000000       BASIC       1309    1224    -2.2    -2.9
ST_GEOMETRY -210,-120,1000000       HIGH        1313    1224    -1.9    -2.9
ST_GEOMETRY -400,-400,1000000000    HIGH        1548    1461    15.6    15.9

Answer 3

As an un-answer due to platform differences I think it is still useful to say that on SQL Server 2008 R2 with ArcSDE 10.1, we have a polygon feature class with only 197 records which was drawing in ArcMap significantly slower than the identical feature class in shapefile or file geodatabase format. The same database/sde has a polyline feature class with millions of records and was performing on par with the shapefile/fgdb of the polygon feature class.

All that said, ESRI suggested that we use SDEBinary as the spatial storage type and it worked. Apparently there is a conversion needed from SQL Server Geometry storage type for ArcMap to draw the data.