topic Re: How do I create and add SDEBINARY points to database? in Python Questions

How do I create and add SDEBINARY points to database?

MatthewSanders1 — Mon, 12 Feb 2018 10:59:05 GMT

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

Re: How do I create and add SDEBINARY points to database?

XanderBakker — Mon, 12 Feb 2018 12:23:01 GMT

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.

Re: How do I create and add SDEBINARY points to database?

JoshuaBixby — Mon, 12 Feb 2018 15:43:27 GMT

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?

Re: How do I create and add SDEBINARY points to database?

MatthewSanders1 — Wed, 14 Feb 2018 14:37:18 GMT

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?

Re: How do I create and add SDEBINARY points to database?

JoshuaBixby — Sat, 11 Dec 2021 15:15:41 GMT

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.

Re: How do I create and add SDEBINARY points to database?

MatthewSanders1 — Thu, 15 Feb 2018 21:53:03 GMT

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!

Re: How do I create and add SDEBINARY points to database?

JoshuaBixby — Thu, 15 Feb 2018 22:21:35 GMT

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

Re: How do I create and add SDEBINARY points to database?

MatthewSanders1 — Thu, 15 Feb 2018 22:43:34 GMT

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!

Re: How do I create and add SDEBINARY points to database?

JoshuaBixby — Thu, 15 Feb 2018 23:18:05 GMT

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?

Re: How do I create and add SDEBINARY points to database?

JayStrahan — Fri, 16 Feb 2018 00:09:07 GMT

Hi Matthew,

From what I understand, you have lat/lon fields in a non-Esri table that come from another system. You want a method of adding the records as point features into an existing SDEBINARY feature class stored in an enterprise geodatabase using either SQL or Python?

For that, you could always create an on-the-fly SHAPE field within a view of the original tabular data and use a built-in SQL function for its definition, such as STPointFromText (geography Data Type) | Microsoft Docs. From Python (ArcPy site package), reference the view as an input layer and Append—Help | ArcGIS Desktop (Python example at the bottom of the page) into the SDE Binary data. In that case, you won't have to reverse engineer the Binary storage and will still be within Esri best practices.

Please let me know if this is what you're looking for.

Re: How do I create and add SDEBINARY points to database?

JoshuaBixby — Fri, 16 Feb 2018 16:28:46 GMT

I think what the OP has is a dump of either one or more database tables (I asked which tables earlier and haven't heard back yet) that make up a SDEBINARY feature class. Since his spatial data appears to be SDEBINARY, SQL Server tools like STPointFromText won't know how to work with it because the SQL Server tools are expecting WKT/WKB. It seems a feature class wasn't exported using Esri tools but one of the tables that make up a feature class was dumped using database tools.

If there was a simple way to convert SDEBINARY (not fully intact feature classes, just raw SDEBINARY data) to WKB, that would be the way to go, I think. Since the SDE command line tools have been retired, I am struggling to think of what options are available.

Re: How do I create and add SDEBINARY points to database?

JayStrahan — Fri, 16 Feb 2018 16:47:54 GMT

You're absolutely right that STPointFromText won't work converting SDEBINARY to other formats, however, I think OP has a few issues merged into that one question. His final being, "How can I convert my lat/long pairs to this SDEBINARY?"

Simply getting lat/lon pairs into SDEBINARY is the easy part, but once it's in there, I don't know of a way to quickly translate that type back out to lat/lon (or WKB for that matter) completely within SQL, if that's what OP means by "readable". Other than constantly recalculating geometry on new columns within the feature class, since SDEBINARY is a proprietary spatial type, I can't think of a way to do it easily.

arcpy - Using Python to field calculate Lat/Long with decimal degrees rather than meters - Geographic Information System…

Re: How do I create and add SDEBINARY points to database?

MatthewSanders1 — Fri, 16 Feb 2018 17:20:26 GMT

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

Re: How do I create and add SDEBINARY points to database?

MatthewSanders1 — Fri, 16 Feb 2018 17:29:51 GMT

Jay Strahan wrote:

Simply getting lat/lon pairs into SDEBINARY is the easy part,

How would I go about doing this?

You are correct though: I have a non-Esri table that uses lat/long pairs to store geodata (points, and lines). My main goal is to take that table and add its entries into a customer's database which is used with ArcGIS. The customer's database has 3 tables (base(?), f, and s). I'm taking my data and inserting it accordingly, but the customer's F table uses SDEBinary instead of lat/longs. I'm struggling with finding an easy way to convert my lat/longs into SDEBinary.

Eventually I'll want to be able to transform them back from SDEBinary, but that can wait.

How do you suggest I convert lat/long to SDEBinary?

Re: How do I create and add SDEBINARY points to database?

JayStrahan — Fri, 16 Feb 2018 18:50:14 GMT

If you're looking for a way to do this without using Esri software to convert, I don't think it's possible. If you are open to using multiple steps, then it's more likely. The first issue is that lat/lon fields are not geometry. They're coordinates, probably a number field. We need to get them into a spatial format.

One way to do that is (like Xander Bakker said earlier) to use XY events and make the lat/lon pairs into a point layer. The problem with XY event layers is that they're static (Make XY Event Layer—Help | ArcGIS for Desktop).

Another method, since you're working directly in SQL Server, is to use STPointFromText (geography Data Type) | Microsoft Docs to convert the coordinate pair into points in a view definition, not a physical table, with a shape column. The new column is a true shape and like SDEBINARY, MS Geometry is another kind of spatial type. It can be read and automatically be recognized as points if dragged into ArcMap. It's a little slower to draw since it performs these conversions every time you pan/zoom/redraw, but if you're just looking for a method for a limited number of points, it's not a deal breaker.

Once ArcMap could potentially read the source (originally lat/lon) through its shape column, you can use ArcGIS tools to append the data and map the attribute fields accordingly. Here's where it gets limiting for your use case. Every different spatial type is written differently in its values. You'll need Esri tools to convert between them. Running an Append tool (which can be scripted through ArcPy) will automatically make the conversion between source and destination behind the scenes with client-side processing.

Re: How do I create and add SDEBINARY points to database?

JoshuaBixby — Sun, 18 Feb 2018 14:35:46 GMT

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

I must admit, I am confused by this last response. If the three tables exist in a production database, it is likely the production database is a geodatabase, right? It would be highly unusual to have 3 geodatabase-type of tables sitting an a database that isn't a geodatabase. Is the production database a geodatabase? If not, do you have any idea how/why 3 tables that are specific to a geodatabase are residing in a non-geodatabase?

Re: How do I create and add SDEBINARY points to database?

MatthewSanders1 — Thu, 08 Mar 2018 16:33:12 GMT

Thanks for all the help. I've since realized I need to bite the bullet and just use ArcPy. It did exactly what I needed it to do, and with much ease (after a steep learning curve!). The downside is that I have modules that do not work with the Python environment used by ArcPy, so I am rewriting some of my existing code.

Thanks again!

Re: How do I create and add SDEBINARY points to database?

MatthewSanders1 — Thu, 08 Mar 2018 16:39:40 GMT

Joshua, thanks for all the help you provided. In the end I realized I just need to use ArcPy. Unfortunately, some of my existing code doesn't work in the Python interpreter supplied by ArcGIS Desktop, and therefor needs rewriting.

Thanks again for not only helping me with the problem, but helping me learn as I dove straight into ArcGIS.

Re: How do I create and add SDEBINARY points to database?

TinaMorgan1 — Thu, 08 Mar 2018 17:36:12 GMT

Hi JAY!

Right, it sounds like someone had an SDEBINARY feature class in SQL Server then just gave him the base, s and f tables (the f table will have a points column with a hex data).

If that is the case, then there are better methods to send this data to you in the future.