How to convert PostGIS WKB to Shapefile - Fiona 'Polygon' object not iterable error

Question

I am relatively new to Python and programming and trying to create a shapefile using multiple WKBs queried from a PostGIS database. I am using psycopg2 to query the database and using shapely and fiona for format conversion. The output I am looking for is a shapefile with multiple polygons (rows) based on how many WKBs were queried. However, I am getting an error: "TypeError: 'Polygon' object is not iterable".

import psycopg2
from shapely.wkb import loads
from shapely.geometry import MultiPolygon, mapping
import fiona
from fiona.crs import from_epsg

def main():
    try:
        print("Connecting to  database...\n")
        db_connection = psycopg2.connect(host = "server.com", dbname = "database", user = "postgres", password = "postgres")
        print("Connected to database\n")
    except:
        print("Unable to connect to database")

    db_cursor = db_connection.cursor()

    try:
        db_cursor.execute("SELECT feature FROM table LIMIT 10")
    except:
        print("Unable to query")

    def wkb_to_shapefile(db_wkb, db_wkb_list, schema):
        for row in db_wkb:
            db_wkb_list.append(row)
            image_wkb = db_wkb_list[0][10]
            load_wkb = loads(image_wkb, hex = True)
            print(load_wkb)
            geometry = list(load_wkb)
            print(geometry)
        with fiona.open("output/shapefile.shp", "w", crs = from_epsg(4326), driver = "ESRI Shapefile", schema = schema) as output:
            output.write({"properties": {"feature": ""},
                          "geometry": mapping(geometry)})

    db_rows_list = []

    db_rows = db_cursor.fetchall()

    shapefile_schema = {"geometry": "MultiPolygon", "properties": {"feature": ""}}

    wkb_to_shapefile(db_rows, db_rows_list, shapefile_schema)

    db_cursor.close()
    db_connection.close()

if __name__ == "__main__":
    main()

Answer 1

Nearly there.. couple of things to note

• the rows returned by psycopg2 are tuples, even if they only have one column (as here). You need to get the first (0th) entry using row[0]
• you need to write one geometry per feature, so put the fiona write inside the loop

Rewriting your function like this does the trick...

def wkb_to_shapefile(db_wkb, db_wkb_list, schema):
    with fiona.open("/tmp/shapefile.shp", "w", crs=from_epsg(4326), driver="ESRI Shapefile",
                    schema=schema) as output:
        for row in db_wkb:
            image_wkb = row[0] # <-- each row is a tuple with one thing in it
            geometry = loads(image_wkb, hex = True) # <- now have a usable geometry
            print(geometry.wkt)
            output.write({"properties": {"feature": ""},
                          "geometry": mapping(geometry)})

Also, you need to change the following line to specify the type of the field feature in your schema. You may be able to leave that as an empty dictionary as you're not setting it to anything ;-)

shapefile_schema = {"geometry": "MultiPolygon", "properties": {"feature": "str"}}

You're also loading the whole table in one go. If your database table is large, you might run out of memory (or not fetch all of the data). Something like this should be better.

def wkb_to_shapefile(cursor, sql, schema):
    with fiona.open("/tmp/shapefile.shp", "w", crs=from_epsg(4326), driver="ESRI Shapefile",
                    schema=schema) as output:
        cursor.execute(sql)
        for row in cursor.fetchall():
            image_wkb = row[0] # <-- each row is a tuple with one thing in it
            geometry = loads(image_wkb, hex = True) # <- now have a usable geometry
            print(geometry.wkt)
            output.write({"properties": {"feature": ""},
                          "geometry": mapping(geometry)})