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(WKB) # gives the above byte sequence
    geomString = wkb.loads(WKB) # crashes see error below

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?

  • 1
    The binary might be corrupted somehow; you might want to try asking SQL Server for WKT directly. – Vince Feb 14 '20 at 11:47
  • I agree with @Vince, just to add my two cents, check STAsText function (SQL Server Docs - STAsText) – cabesuon Feb 14 '20 at 13:33

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.

  • Thank you so much for your input. I have managed to finally migrate all the data from sql server to postgres. Only thinng is, i seem to have lost my projection somwhere along the way. In PGadmin all my geometries are stacked on each other and their string representation is exaclty equal. – jdoe Feb 14 '20 at 14:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.