Shapefile Shape field to SQL geometry Data Type

[sotokan80_]

I am using Arcpy and ArcGIS 10.4.1. Is there any way to parse a shapefile's Shape field and get the value in the following format; 0xE6100000010CF091A1F8EB67354050B8D91D27644440.

Currently i am storing in native sql server using the following query; insert into [TABLE_NAME] (Shape) SELECT geometry::Point(21.405944385000055, 40.782443744000034 , 4326)

i.e

# Get data from shapefile
with arcpy.da.SearchCursor(table, ['Shape']) as cursor:
         for row in cursor:
                  Shape = row[0]

                  print Shape

>>>>(21.405944385000055, 40.782443744000034)

And i want it to be 0xE6100000010CF091A1F8EB67354050B8D91D27644440

[JoshuaBixby]

Your example

0xE6100000010CF091A1F8EB67354050B8D91D27644440‍

is obviously a binary format, but which one? 

[VinceAngelo]

You should use 'shape@' as the field name to get the geometry object, 'shape@wkt' to the the Well-Known Text string, and 'shape@wkb' to get Well-Known Binary.

It is confusing to reference "shapefile" and "SQL Server" in the same paragraph without specifying how you converted between them, since you cannot have a shapefile in a database.

- V

[sotokan80_]

When i parse the shapefile i get

# Get data from shapefile
with arcpy.da.SearchCursor(table, ['Shape@WKB']) as cursor:
         for row in cursor:
                  wkb = row[0]
                  Shape=binascii.hexlify(wkb)
                  print Shape

>>>>>>>0101000000f091a1f8eb67354050b8d91d27644440

Which is not the same with 0xE6100000010CF091A1F8EB67354050B8D91D27644440. 

When trying to insert into sql server i get the following error;

ProgrammingError: ('42000', '[42000] [Microsoft][ODBC SQL Server Driver][SQL Server]A .NET Framework error occurred during execution of user-defined routine or aggregate "geometry": \r\nSystem.FormatException: 24114: The label 0101000000f091a1f8eb in the input well-known text (WKT) is not valid. Valid labels are POINT, LINESTRING, POLYGON, MULTIPOINT, MULTILINESTRING, MULTIPOLYGON, or GEOMETRYCOLLECTION.\r\nSystem.FormatException: \r\n   at Microsoft.SqlServer.Types.OpenGisWktReader.ParseTaggedText(OpenGisType type)\r\n   at Microsoft.SqlServer.Types.OpenGisWktReader.Read(OpenGisType type, Int32 srid)\r\n   at Microsoft.SqlServer.Types.SqlGeometry.GeometryFromText(OpenGisType type, SqlChars text, Int32 srid)\r\n   at Microsoft.SqlServer.Types.SqlGeometry.Parse(SqlString s)\r\n. (6522) (SQLExecDirectW); [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]The statement has been terminated. (3621)')

Any ideas?

[VinceAngelo]

I have no idea where the stream that starts with 'E6' comes from, but it isn't WKB or native shapefile.

If you want to insert via Well-Known Text, you shouldn't capture Well-Known Binary from the cursor. They are related, but they are not equivalent. 

There are significant pieces of your code missing, which makes it difficult to help you.

- V

[sotokan80_]

I imported a feature class into sql server using sde connection. The created table has a field named 'Shape' with values like 0xE6100000010CF091A1F8EB67354050B8D91D27644440 . When trying to store the shapefile's shape value using an sql statement i m using the following query: "insert into [TABLE_NAME] (Shape) SELECT geometry::Point(21.405944385000055, 40.782443744000034 , 4326)" It works ok. I just want to find out if there is a way to get that bytearray using a SearchCursor. I assume that the first part of the bytearray has to do with the projection system or the type of shapefile.

[JoshuaBixby]

I assume since you used an "sde connection" to import the feature class, you used some ArcGIS Desktop tool.  There are many that can copy/import data, so I won't guess which one specifically.

As covered in ArcGIS field data types—ArcGIS Help | ArcGIS Desktop ,

Geometry

In ArcGIS, the geometry data type indicates the type of geometry—point, line, polygon, multipoint, or multipatch—the table stores. The field stored as geometry type is called SHAPE when created through ArcGIS.

When you say the table has values "like 0xE6100000010CF091A1F8EB67354050B8D91D27644440", I am guessing you are looking at the table outside of ArcGIS, in SQL Server Management Studio for example.  If you are were looking at the table from within ArcGIS, you would see values line "Polygon" in the Shape column.  The value you are seeing is the SQL Server CLR serialization format that MS uses to store GEOMETRY and GEOGRAPHY in SQL Server.

The short answer to your question, i.e., is there "a way to get that bytearray using a SearchCursor," is no.  The ArcPy cursors work with several geospatial data formats (WKT, WKB, GeoJSON, Esri JSON, etc...), but there is no property or method for returning the native binary format that MS uses for storing spatial data in SQL Server. 

[VinceAngelo]

The only possible option is if Microsoft supports an "asBinary" request on the geometry, and the result is parsed as a BLOB.  But that's the long way around the lake when WKT and WKB should be valid options.

- V