1

We are currently using ArcSDE 9.3 to store our Geodata. In a project which is currently being implemented, the software being used can access most data formats including the ArcSDE binary format and/or the MSSQL Geometry or Geography format. Our current problem is that the new software cannot implement complicated nested sql queries or use certain functions on the SDE binary format with the ArcSDE 9.3 API. According to the software firm, these features apparently only work with the MSSQL geometry or geography format.

Based on the context above, I am looking for some advice regarding the following:

  1. How can I change all my data which is currently stored as SDE Binary into the MSSQL Geometry or Geography data format?

  2. Are MSSQL Geometry and Geography formats fully compatible with ArcSDE and ArcGIS?

  3. What disadvantages do Geometry and Geography formats have in comparison to SDE binary?

closed as too broad by PolyGeo Jan 27 '17 at 6:19

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

8

Your question is a mélange of misinformation and cross-threaded topics, which makes it exceedingly difficult to answer.

First off, there is no comparison of the "SDE API" and "MSSQL Native geodata format". ArcSDE is many things, including a code library for accessing data (not spatial data, all types of data, including geometry) in multiple SQL (and no-SQL) databases, a networking protocol, several geodata storage implementations, an application server, and a suite of utilities written using the API. The ArcSDE API is compatible with geometry storage in MSSQL GEOMETRY and GEOGRAPHY formats.

Any "complicated" SQL query can be submitted through both the ArcSDE API and through the ArcObjects (and Python) interface, as is. It's not complicated which causes trouble so much as non-standard/unsupported return datatypes, or results which are missing a unique identifier (which is an ArcObjects, not ArcSDE, requirement).

Esri has been phasing out direct support for the ArcSDE API for some time. ArcGIS 10.2 was the last release in which the API, command-line utilities, and application servers were available. Most of the functionality of the command-line utilities have been either obviated or replaced with ArcObjects/Python geoprocessing utilities (some work to support spatial view registration is still ongoing). In addition, ArcGIS 10.0 introduced Query Layers, which are direct SQL queries through RDBMS drivers (not ArcSDE), which removed the requirement of a full enterprise geodatabase install for spatial data access.

Finally, there is no one configuration option for geodata storage type. Any Esri enterprise geodatabase installation has access to all supported storage formats for that RDBMS at all times (using DBTUNE keywords). You can certainly choose to migrate part or all of your data to any of the supported formats, though there may be performance implications that are specific to individual datasets and tuning options, but you can continue to create new feature classes in any supported format.

In general, the GEOMETRY and GEOGRAPHY types perform well in comparison to SDEBINARY storage (+/-10%), though they really ought to perform much better, given the natural advantages they have (these advantages, most notably the "one table" solution vice an ugly three-way join, are why GEOMETRY has been the default storage format since ArcGIS 10.1). The issue is generally the difficulty in tuning the Microsoft spatial index scheme. With proper tuning, most datasets in GEOMETRY format can at least equal SDEBINARY performance, though some users have given up on the native formats and moved back to SDEBINARY.

In the end, Esri users can choose the storage format which meets their needs, which span too many potential parameters for a one-size-fits-all solution. As with all tuning situations, it's probably wise to evaluate performance with actual data (or a properly scaled representative sample) before committing to a format change.

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