2

I have a bunch of points in an SDE table which is currently stored in an MSSQL database.

enter image description here enter image description here

The full entry is below, while the data i'm trying to convert from binary to geometry is: 0x0B000000000100008AA1D6D50F8DC091D80600 enter image description here

Inside the SQL table the data is stored as an 'image' datatype.

I'm chasing the correct way to convert this data into a geometry type so I can see its components.

I've tried 2 general methods methods, both have failed. I've now retiring as not knowing what i'm doing.. help?

declare @binary VARBINARY(MAX) = cast((select top 1 points 
                                       from networks.networks.f43) as varbinary(MAX))
declare @geo geometry = cast(@binary as geometry)

select convert(geometry,0x0B000000000100008AA1D6D50F8DC091D80600,0)
select cast(0x0B000000000100008AA1D6D50F8DC091D80600 as geometry)

These all return the same error, with an unexpected version??

A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24210: Geometry type with an unexpected version of 0 received; only versions up to 2 are accepted. System.FormatException: at Microsoft.SqlServer.Types.GeoData.Read(BinaryReader r, SerializationVersion maxVersion, String strUnexpectedVersion, Boolean isGeography) at Microsoft.SqlServer.Types.SqlGeometry.Read(BinaryReader r) at SqlGeometry::.DeserializeValidate(IntPtr , Int32 , CClrLobContext* )

declare @g geometry = geometry::STGeomFromWKB(0x0B000000000100008AA1D6D50F8DC091D80600, 0);

Returns this error:

A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": System.FormatException: 24115: The well-known binary (WKB) input is not valid. System.FormatException: at Microsoft.SqlServer.Types.WellKnownBinaryReader.ParseWkb(OpenGisType type) at Microsoft.SqlServer.Types.WellKnownBinaryReader.Read(OpenGisType type, Int32 srid) at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromBinary(OpenGisType type, SqlBytes binary, Int32 srid)

Improve this question
4
  • 3
    You appear to have an ancient installation, since no recent build has the ability to create layers with old-style multi-table geometry storage. All the documentation on the compressed binary format is likely to have been taken down (though there might be some in your install media). Whatever is available is not going to be of any use directly from SQL, since it involves bit-masking to populate an integer array and integer-float conversion.
    – Vince
    Commented Nov 20, 2019 at 11:39
  • @Vince considering Esri has EOL'd SDE any installation is ancient. The nuts 'n bolts of a personal geodatabase table structure are fairly similar. The issue here is that the user is trying to cast ST_Geometry binary in a function that wants WKB, which it's not. There are still some references to Esri binary in 9.3 if you search very deep in the web but you're 100% correct about the bit masking and shifting in the ST_Geometry binary format... I'd want to see some C++ ability before I'd even provide a link that the user is unlikely to understand and even less likely to be able to use.
    – Michael Stimson
    Commented Nov 22, 2019 at 5:09
  • 1
    @MichaelStimson The Question has been completely rewritten, so my previous comment may not be completely active. The question is about SQL Server, though, so ST_GEOMETRY isn't applicable; it's either SDEBINARY or native storage. The default storage shows as native GEOMETRY, but the actual storage is SDEBINARY, which predates WKB (and native storage) by 3-5 years, and is very different (being integer-based). Even the old documentation on SDEBINARY may be incomplete if the data is post-9.1 and in HIGH PRECISION binary format.
    – Vince
    Commented Nov 22, 2019 at 12:31
  • 1
    The exact versions of SQL Server, the geodatabase install, and your available Esri clients are needed to determine what your options are for accessing this data. While possible, accessing the binary stream directly requires significant developer skills; using the API would likely be far easier (if the install is intact).
    – Vince
    Commented Nov 22, 2019 at 12:42

1 Answer 1

Reset to default
1

There is a utility that is installed with SDE, which ensures that it's the correct version, to export from SDE to a shapefile called SDE2SHP and the reverse SHP2SDE which is part of the administration utilities installed in your %ProgramFiles%\ArcGIS\ArcSDE\<RDBMS>\bin folder on the server that SDE is installed on.

sde2shp -o init    -l <table,column> [-V <version_name>]
                   -f <shape_file>    -t <file_type>
                  [-a {all | file=<file_name>}]
                  [-w <"where_clause">] [-i <service>] [-s <server_name>]
                  [-D <database>] -u <DB_User_name> [-p <DB_User_password>]

Use -i sde:sqlserver:<your server> as your connection string. Provided you know the rest of the information and have a valid username/password for your SDE service it should work up to the limit of shapefile data storage. Considering you can get to the feature class properties you should be able to direct connect ArcCatalog to your SDE server rather than trying to muck around with Esri binary representations.

You will notice that these helps are from version 9.3 and 10 as Esri has retired SDE at 10.2.2 but Desktop still maintains backward compatibility - I can't comment on compatibility with Pro.

Improve this answer
1
  • 2
    My old sdequery utility would write the rows as Well-Known Text using the SDE API, but the FTP server for se_toolkit was retired from service years ago now. The API is unchanged, just used only internally; both ArcMap and Pro would be able to access the data, if it weren't for the incompatibility of modern Desktop with ancient SQL Server installs. Elderly Desktop installs might be able to access the feature class in situ and write out WKT via ArcPy (using old-style cursors, unfortunately).
    – Vince
    Commented Nov 22, 2019 at 12:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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