Replacing WKT with WKB in PostGIS query & Shapely

Question

I am extracting a geometry (type geometry(Geometry,4326)) from a PostGIS database. I managed to plot them from a GeoPandas GeoDataFrame using the following code :

cursor.execute("SELECT id, ST_asText(wkb_geometry) FROM mydb")

rows=[]
for id, geom in cursor:
    data = {'id': id, 'geometry': shapely.wkt.loads(geom)}
    rows.append(data)

geopandas.GeoDataFrame(rows,crs='epsg:4326').plot()

Figuring WKB would be more efficient than WKT, I was expecting querying with ST_Binary()/ST_AsEWKB()/ST_AsTWKB() and loading with shapely.wkb.loads(geom) to be equivalent to the above, but it throws a

TypeError: bytes or integer address expected instead of memoryview instance

Why ?

Answer 1

For some reason, the psycopg2 database driver returns blob values as memoryview objects.

You can convert them into bytes objects with bytes(geom) or geom.tobytes().

---

This post on the psycpg mailing list shows how to change the returned type of blobs by registering a new type:

def bytea2bytes(value, cur):
    m = psycopg2.BINARY(value, cur)
    if m is not None:
        return m.tobytes()

BYTEA2BYTES = psycopg2.extensions.new_type(
    psycopg2.BINARY.values, 'BYTEA2BYTES', bytea2bytes)
psycopg2.extensions.register_type(BYTEA2BYTES)

cur.execute("select 'abcdef'::bytea")
cur.fetchone()[0]    # returns b'abcdef'