Convert a SQL Server Geometry into PostGIS spatial data with Python and shapely

Question

I'm trying to migrate spatial data from a SQL Server database to a PostGIS database with Python. I need this to be automated since its not just one table, but a lot tables in a lot of databases.

I am struggling with the geometry types.

From the SQL Server I get this:

b'\xe8d\x00\x00\x01\x04\x05\x00\x00\x00\x05\xa4\x1e\xbe\xee\x15\x12Aa/\xb9y)\x97UA\xdc\xd2\xe2\xd6\xeb\x15\x12A\xc73\xd7\xf7)\x97UA \xa8\xb07\xef\x15\x12Ac\xa9\xbf\x0b*\x97UAIy\xec\x1e\xf2\x15\x12A\xfd\xa4\xa1\x8d)\x97UA\x05\xa4\x1e\xbe\xee\x15\x12Aa/\xb9y)\x97UA\x01\x00\x00\x00\x02\x00\x00\x00\x00\x01\x00\x00\x00\xff\xff\xff\xff\x00\x00\x00\x00\x03'

Which according to my research should be a WKB (Well Known Binary)

Inserting this directly into my PostGIS database gives me this error :

ERROR:  Invalid endian flag value encountered.

Which, again according to some research, I need to parse either into WKT or a string before being able to insert it.

Right now my idea is, to use shapely to parse it into a simple string representation and then insert it, but i cannot even parse it, code looks like this :

def WKBtoStr(self, WKB):
    print('befpre')
    print(WKB) # gives the above byte sequence
    geomString = wkb.loads(WKB) # crashes see error below
    print('after')
    print(geomString)

This gives me the following error :

ParseException: Unknown WKB type 100
Could not create geometry because of errors while reading input.

I have also tried : Decoding the byte sequence, which just yields wierd gibberish.

Any suggestions?

Answer 1

When you simply read a geometry value from Microsoft SQL Server, you get a blob in the CLR Type Serialization Format, which is different from WKB.

If you have an SQL Server database, you can cast that value back into the geometry type (although it would be a better idea to export the value as WKB or WKT to begin with). If you do not have SQL Server, you have to read the documentation and parse the value yourself.