How do I create and add SDEBINARY points to database?

[MatthewSanders1]

I got tasked with taking some data from a completely unrelated system and putting it into our SQL Server database for use with our ArcGIS server, and I can't for the life of me figure out how to create, use, manipulate, convert, etc SDEBINARY data.

There are tables which contain the xminx, xmaxx, etc entries and then have a 'points' column which shows a bunch of hex data and I can only assume it's the geometry of the shape or point. From what I can tell, it's SDEBINARY data but I could be wrong.

How can I convert my lat/long pairs to this SDEBINARY?

Also, how can I convert it back from SDEBINARY to something readable?

Thanks,

Sanders

[XanderBakker]

Probably the easiest way to handle this is using ArcGIS Desktop (ArcMap or ArcGIS Pro). Connect to the data source and load the table into Desktop and use display XY events (Adding x,y coordinate data as a layer—Help | ArcGIS Desktop ) and export that to your enterprise geodatabase as a point feature class and let ArcGIS take care of all the tables and indexes that should be created.

[JoshuaBixby]

It is fairly unusual to has SDEBINARY exported as text.  If you do have something binary looking in a text file, it is more likely well-known binary (WKB).

To clarify, do you have an xminx, xmaxx, etc... for the data set as a whole or for each record in the data set?  If you don't actually have text representation of the points, and only have WKB representation of the points, you may need to use a cursor and FromWKB—Help | ArcGIS Desktop .

You are using SQL Server, do you really have to store the resulting points as SDEBINARY?  What about storing them as ST_GEOMETRY?  Or, are you saying SDEBINARY simply because you want to load them into an enterprise geodatabase, regardless of actual spatial storage type in the geodatabase?

[MatthewSanders1]

Good morning Joshua, and thanks for the reply! Sorry it took so long for me to respond, I've been out of the office this week.

I do have eminx and such.

I seem to have two or three tables for each feature layer. One table has data about a point, one has the eminx/emaxx/eminy/emaxy. numofpoints, and points(the varbinary in question) columns, and the third table ALMOST seems like an exact copy of the first table I mentioned which leads me to believe it's used for something else.

My problem is that I don't know how to interpret or create the data in the points column. Here is an example of the points binary:

0x0C00000001000000838C9A99EF12A091D8A3EC19 (where numofpoints = 1)

0x3D03000001000000A7D0DEEBEC128FDAA2BFCA19DA0EA7E3019E9E01C0A302A2E90FEFAB098BF90EFCDD089BC20EFB8E09BCFE0ECBD808A5BA0BE3F406EA0892118B14D211 (where numofpoints = 4)

Does that sound familiar to you?

[JoshuaBixby]

The "0x" at the start of the sequence denotes a hexadecimal number, which is important to know, but it doesn't answer all of your questions by itself.

When looking at a 2-pt MULTIPOINT for my home town, below are different representations of the same information (SRID 3857):

>>> pt.WKT
u'MULTIPOINT ((-10251981.3618 5906569.0346999988), (-10251716.777899999 5906621.9514999986))'
>>> pt.WKB
bytearray(b'\x01\x04\x00\x00\x00\x02\x00\x00\x00\x01\x01\x00\x00\x00\x98\xdd\x93\xab\xd9\x8dc\xc1X\x868B"\x88VA\x01\x01\x00\x00\x00\x8a\x8e\xe4\x98\xb8\x8dc\xc1@`\xe5|/\x88VA')
>>> binascii.hexlify(pt.WKB)
'010400000002000000010100000098dd93abd98d63c1588638422288564101010000008a8ee498b88d63c14060e57c2f885641'
‍‍‍‍‍‍

It is the third representation, i.e., hex number, that you appear to have in your files.

Unfortunately, when I try to work with the example hex numbers you provided, all I get are errors.  I am able to convert the hex number into a byte array, but the byte arrays don't appear to represent valid well-known geometries.  It isn't that the hex numbers are bad/wrong, it may just be they aren't hex representations of well-known binary geometries, or it could be there is an encoding we are missing.

At this point, without more metadata or background information, I can't offer any further suggestions.  If I could get your hex values working, I could provide more specific suggestions at loading them.

[MatthewSanders1]

I've done some digging myself. It appears that the data in my database is Esri Binary spatial type (SDEBinary) and is Esri's own way to encode geo data. However, I believe support for SDEBinary is being phased out. The link I provided explains the logical and physical representation of geometry. I'm going to keep looking for a premade tool to convert lat/long coordinates to SDEBinary, and the worst case scenario is that I have to make my own. 

Thanks again for the help!

[JoshuaBixby]

I am sharing with Geodatabase‌, there are some sharp folks that follow that group that may be able to help.

What I failed to notice earlier in your first reply back to me were the change in column names from your original post.  Originally you stated "xminx, xmaxx, etc" but later said "eminx/emaxx/eminy/emaxy."  What you list for column names belong to the feature table for SDEBINARY storage type.  Let me guess, do your table structures look like the tables listed here? 

Feature classes in a geodatabase in SQL Server—Help | ArcGIS Desktop 

[MatthewSanders1]

Yes, sorry, the original column names were a mistype. The tables are set up like the link you provided, and the column names are eminx/emaxx/etc

Thanks for sharing my post. Hopefully that will bring in some good help!

[JoshuaBixby]

Do you have dumps of all three tables [base table, feature table (F<layer_id>), and spatial index table (S<layer_id>)] or just the feature table? 

[MatthewSanders1]

I do. I have access to their entire production database, so I have all three tables.