Converting between Geodatabase and SQL Server Spatial

[DarylBlanchett]

How difficult is it to convert spatial data stored in a Geodatabase to native SQL Server spatial types?  I want to do this in an automated fashion, either in .Net or tsql if possible.

Thanks

[BrentPierce]

Daryl,

At ArcGIS 10, you can use the Migrate Storage GP Tool to automate the conversion of the geometry storage from SDE Binary to SQL Server Geometry/Geography.

See the following help topic: http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//00170000000q000000.htm

This tool takes a dataset or list of datasets and a configuration keyword which specifies the storage type you wish to migrate to.

Hope this helps,
-Brent

[DarylBlanchett]

Hi Brent

I appreciate the suggestion but it's not quite what I'm looking for.  Basically I'm trying to come up with a way around the read only nature of QueryLayers.  I would like to provide editing of spatial features without the overhead of using a geodatabase, so just use the spatial data type in SQL Server.  One possible solution I was thinking of was dumping features from a SQL table into a geodatabase for editing then pushing them back into the SQL table when editing was finished.

Thanks

[VinceAngelo]

You don't need convert between geodatabase and SQL tables because ArcSDE geodatabases
*are* SQL tables. If you chose to store the geometries of your geodatabase in a native SQL
format, then you won't need to do any further conversion (beyond the one-time storage method
change Brent suggested).

If you don't want to utilize the benefits of versioning, there's no reason to do so, but there's
also no reason to incur the cost of storing your tables twice (and keeping them synchronized).

- V

[DarylBlanchett]

Hi Vince

I realize that the geodatabase tables are SQL tables, my concern is more with the additional tables\procedures\etc that are part of the geodatabase and how they would interact with existing tables in my database. 

As an example, in one database I'm working on I use a snowflake schema to store information as well as some moderately complex logic wrapped up in stored procedures and functions that is used to create, update and delete entities.  I would like to include spatial analysis within the central fact table, if I just use the native SQL spatial type then it's just a matter of adding a column to the fact table to store the geometries related to each item.  I believe that integrating this within a geodatabase would appear to be much more involved since the geodatabase is using a much more complex methodology to manage the spatial data.

I agree that storing the data twice and having to keep it synchronized would be a pain, ideally I would like to write to the native SQL spatial column without going through a geodatabase.

Cheers

[VinceAngelo]

You don't really need more than a 32-bit unique integer column and a GEOMETRY to register a
table with ArcSDE. When I use SQL to create databases, I use SQL, with only a few registers
at the end.  I mostly use the ArcSDE API to load the data, but only because I've got a very
powerful ASCII loader -- there's no reason SQL can't be used for that as well (provided you
validate your geometries to be sure they honor ArcSDE topology rules -- lines vs. simple lines,
polygon boundaries which are non-intersecting,...).  There is no inherent geodatabase complexity
when working with "simple feature classes", you just don't get the full monty in terms of object
behaviors, complex topology, et. al.

- V

[DarylBlanchett]

Hi Vince

Thanks for the information, I think registering an existing table with ARCSDE is what I'm looking for.  Is this something that can be done with a standard ArcGIS desktop license and ArcSDE for SQL Express, or is an ArcServer license required?  With my current setup I can't seem to run ArcSDE commands.

Cheers

[VinceAngelo]

The ArcSDE 'C'/Java APIs and command-line utilities are only supported with Enterprise ArcSDE,
which is part of ArcGIS Server.

Query Layers are the means to access native SQL tables from Desktop without registration.

- V

[TomBrown]

You can easily register a table with ArcGIS through ArcCatalog. Establish the connection with ArcCatalog to your instance. Find the table in the Catalog, right click to register with the geodatabase and then open the properties page and add a shape attribute.

The only restriction to this workflow is you must register the table without a spatial type being present (because the types are not well defined - being a User Defined Type, UDT). So, unfortunately at this time, its a two step process.

You don't need the ArcSDE command lines for all cases, although if your existing table does have a geometry attribute - then you could register it with ArcSDE via the command line and then register it with the geodatabase in Catalog.

Good luck.