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?
STAsText
function (SQL Server Docs - STAsText)