topic Re: SDE to STGeometry in Data Management Questions

SDE to STGeometry

KeithSandell — Sun, 01 Apr 2012 22:25:14 GMT

I'll spare you the details, but I need to move an SDE Featureclass to SQL Spatial Type.

For various reasons I can't use Shape2SQL, nor a python script that I have to read/write geometries.

I need to move it using SSMS from my SDE DB to my plain ole regular SQL DB.

Found some stuff online that said the following should work, but I get the following error:

Msg 257, Level 16, State 3, Line 5
Implicit conversion from data type geometry to varbinary is not allowed. Use the CONVERT function to run this query.

declare @g varbinary(max);
set @g = 0x3200000001000000A6D0E5BEAA0681AFEFC0D713D4F705ECD5F30497E222BA049DB30597B1BB04B080B704ADEA018C3DA8F937DCDCD904FAC401;

declare @h geometry;
set @h = geometry::STGeomFromWKB(convert(geometry, @g), 3857)

select @h FEATURE_SHAPE

Or the same implementation on the table directly, acting upon the same geometry as above:

SELECT
geometry::STGeomFromWKB(convert(geometry, points), 3857) as FEATURE_SHAPE
FROM [SDE_WORK_GIS].[sde].[f216]
where fid = 1

Other methods result in errors indicating that the varbinary format is invalid.

Read/writing to datatype geometry using python results in the following geometry binary, completely different (kind of expect that since the datatypes are different):

0x110F0000010407000000AEB662F3667861C1091B9EE6B5464B418A8EE48E677861C10F0BB5CEB4454B41228E75FF637861C15AF5B9D2B4454B41637FD971637861C13EE8D9DC9E464B4153962162297861C152B81E9D9F464B419A999955297861C1D656EC87B6464B41AEB662F3667861C1091B9EE6B5464B4101000000020000000001000000FFFFFFFF0000000003

Recently back from the Dev Summit where numerous Product Engineers and the like said this could be done, and that SDE stores geometry in the same format as SQL ST.

Thoughts?

Thanks in advance.

Re: SDE to STGeometry

VinceAngelo — Mon, 02 Apr 2012 02:21:58 GMT

ArcSDE's SDEBINARY format is not the same as Microsoft's native GEOMETRY or GEOGRAPHY.
Only when a layer is configured to use GEOMETRY/GEOGRAPHY would it be the same.

The ArcSDE API can export SDEBINARY as well-known text or well-known binary, but that
couldn't be done using SSMS.

Your easiest solution is to start over with ArcSDE in a new database, using an owner other
than 'sde' (as per best practice), with a default storage of GEOMETRY, and copy your old data
into the new database. Then you can use Microsoft tools to migrate the Microsoft-formatted
geometry objects.

If you have more flexibilty in the way you export the data, you could write a simple 'C' or Java
ArcSDE API app (or just use the 'sdequery' utility of se_toolkit) to create the needed data
stream of ASCII or binary data.

- V

Re: SDE to STGeometry

BruceHarold — Mon, 02 Apr 2012 14:13:32 GMT

Keith

Another option is the Data Interoperability extension. It supports all the native spatial types in DBMS platforms supported by SDE.
The Quick Export geoprocessing tool would be the simplest option to migrate SDE layers one at a time.

Regards

Re: SDE to STGeometry

KeithSandell — Mon, 02 Apr 2012 16:04:40 GMT

Vince/Bruce,

Thanks for the direction and clarification. I'll chalk up the info I got at the Dev Summit as just overly simplified, not incorrect. Nevertheless the Dev Summit was awesome and extremely helpful, as usual.

Not sure about redoing SDE just for this issue, and I just paid my maintenance, so no more toys (Interop) this year.

Guess I'll just muddle through until 10.1 is released, then it is on!

Thanks

Keith