Parsing WKB with python geos: ParseException: Unknown WKB type 16

Question

I am pulling a WKB strings from the MYSQL table in pandas / geopandas, Python 2.7. As it is MySQL, the only way to read geometry is to parse it on the client.

However, when parsing, geos raises an Exception, and I am not sure what is wrong with it.

ParseException: Unknown WKB type 16. At the same time, I know that production team uses geos binding for Ruby and everything works fine.

In particular, this is what is happening:

from shapely import geos
import binascii
geos.WKBWriter.defaults['include_srid'] = True
from shapely import wkb

connection = pymysql.connect(host=***,
                         user=***,
                         password=***,
                         db=***
                         )

df = pd.read_sql_query('SELECT geometry, building_count, created_at  FROM boundaries LIMIT 10', con=connection)
df.head(3) 

    geometry    building_count  created_at
0   �\t+~��}R�)�Ǻ\D@�ʡE�}R�'�UH�]...    2892    2011-03-24 01:59:15
1   �.�o�}R�R�8�cD@�'�X}R��ǵ�bd...  1317    2011-03-30 15:02:45
2   �\r-��燎R�����]D@{�/L��R��?�Ŋ^D@...  5810    2011-03-30 23:05:02



z = df.loc[0, 'geometry']
zh = z.encode('hex')
zh

'e6100000010300000001000000090000002b137ea99f7d52c029cb10c7ba5c4440c1caa145b67d52c027da5548f95d444081b22957787d52c0d1e80e62675e44403a92cb7f487d52c0ad86c43d965e4440c959d8d30e7d52c0ca54c1a8a45e4440bc3fdeab567c52c075594c6c3e5e4440aa436e861b7c52c07767edb60b5d444094a46b26df7c52c09bc937dbdc5c44402b137ea99f7d52c029cb10c7ba5c4440'

wkb.loads(zh, hex=True)

-> ParseException: Unknown WKB type 16

I am sure that there is something with encoding or WKB specification, but can't figure out how exactly to resolve this.

Answer 1

So, in a nutshell: this is actually a MySQL binary blob of wkt. With AsText(column) I was able to get a normal wkt format, and then parse it with shapely/geopy:

d = pd.read_sql_query('''SELECT AsText(boundaries.geometry) as geometry, center_lat, center_lon, building_count, boundaries.created_at, boundaries_users.boundary_name
                         FROM boundaries LEFT JOIN boundaries_users ON boundaries_users.boundary_id = boundaries.id
                         WHERE building_count >0;''', connection)

def tryParseWKT(x):
    try:
        return wkt.loads(x)
    except:
        return None

d['geometry'] = d['geometry'].apply(lambda x: tryParseWKT(x))
d = d[pd.notnull(d['geometry'])]

boundaries = gp.GeoDataFrame(d)