nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Find more posts tagged with

Accepted answers

All comments

MatthewSanders1

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.

MatthewSanders1

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?

MatthewSanders1

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

JayStrahan

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…

bixb0012

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.

JayStrahan

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.

bixb0012

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

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!

bixb0012

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

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!

bixb0012

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):

<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> pt<SPAN class="punctuation token">.</SPAN>WKT
u<SPAN class="string token">'MULTIPOINT ((-10251981.3618 5906569.0346999988), (-10251716.777899999 5906621.9514999986))'</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> pt<SPAN class="punctuation token">.</SPAN>WKB
bytearray<SPAN class="punctuation token">(</SPAN>b<SPAN class="string token">'\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'</SPAN><SPAN class="punctuation token">)</SPAN>
<SPAN class="operator token">>></SPAN><SPAN class="operator token">></SPAN> binascii<SPAN class="punctuation token">.</SPAN>hexlify<SPAN class="punctuation token">(</SPAN>pt<SPAN class="punctuation token">.</SPAN>WKB<SPAN class="punctuation token">)</SPAN>
<SPAN class="string token">'010400000002000000010100000098dd93abd98d63c1588638422288564101010000008a8ee498b88d63c14060e57c2f885641'</SPAN>
<SPAN class="line-numbers-rows"><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN><SPAN>‍</SPAN></SPAN>

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

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?

bixb0012

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?

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.

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.