3

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.

  • 1
    It seems the data is in WKB type that stores TINs (Triangulated Irregular Networks) which might not be supported by Shapely/GEOS... en.wikipedia.org/wiki/Well-known_text#Well-known_binary I'd suggest converting the data to points or whatever you want to use it as first. – Jan Šimbera Nov 7 '16 at 21:48
  • Why do you think of TINs? Theoretically It should be flat 2d polygons in my case... – Philipp_Kats Nov 7 '16 at 21:58
  • Because the WKB type 16 corresponds to TINs. No other clue. – Jan Šimbera Nov 9 '16 at 12:51
2

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)

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.