Getting from wkt to shapefile in Python?

Question

I am querying tables from a SQL-Server database and would like to convert the table into a shapefile. It has wkt geometries and wkb as columns in the table, along with other attributes that need to be fields in the dbf. I'm wondering what the most efficient way to convert these returned results into shapefiles is. I'm not sure ogr2ogr is the best method because I already store the attributes in a list of dictionaries in my script (for sorting and filtering purposes). I've been looking at pysal and fiona, but can't find any examples showing exactly what I'm trying to do, and I'm rather new to Python. Also, I do have access to arcpy.

Update after @fenris comments:

I've tried this method but am still getting errors.

Here's what I'm doing:

from shapely.geometry import mapping
from shapely.wkb import loads
from fiona import collection
outDir = r'C:/Users/x/Documents/'
outFile = 'Output_CIM'
outCellShp = outDir + outFile + '.shp'

schema = {'geometry': 'Point', 'properties': {'veh' : 'str',
               'id' : 'str',
               'imStart' : 'datetime.datetime',
               'imEnd' : 'datetime.datetime',
               'area' : 'Decimal',
               'c' : 'Decimal'} }

with collection(outCellShp, "w", "ESRI ShapeFile", schema) as output:
    print('here')
    for row in images:
        geometry = loads(row['wkb'])
        output.write({'properties': {'veh' : row['veh'],
               'catid' : row['id'],
               'imStart' : row['imStart'],
               'imEnd' : row['imEnd'],
               'area' : row['area'],
               'cc' : row['c']},
               'geometry' : mapping(geometry)})

And am getting the following error: Error: class 'fiona.errors.DriverError'

It doesn't appear that it's making it passed the collection() call. Am I allowed to use 'datetime.datetime' as a properties type? I'd actually prefer to have that as a string because Arc cuts it off at day anyway. Right now I'm using wkb as a bytearray, but also have AIS_Geometry and AIS_Geography in the database, if either of these would yield better results.

---

I was able to get the above working with the following:

schema = {'geometry': 'Polygon', 'properties': {'veh' : 'str',
               'id' : 'str',
               'imStart' : 'str',
               'imEnd' : 'str',
               'area' : 'float',
               'c' : 'float'} }

with collection("outCellShp.shp", "w", "ESRI Shapefile", schema) as output:
    for row in images:
        geometry = loads(bytes(row['wkb']))
        output.write({'properties': {'veh' : row['veh'],
               'id' : row['id'],
               'imStart' : str(row['imStart']),
               'imEnd' : str(row['imEnd']),
               'area' : float(row['area']),
               'c' : float(row['c']}),
               'geometry' : mapping(geometry)})

I had to make sure there was a polygon in the schema as opposed to a point. Additionally, I had to convert the bytearray into a bytestring, the decimals into floats, and the datetime.datetime into str.

Answer 1

Since you've already got a list of dicts, you can use Shapely (to manage the geometry) and Fiona (to write the shape file).

from shapely.geometry import mapping
from shapely.wkt import loads
from fiona import collection

schema = {'geometry': 'Point', 'properties': {'atribute1':'value', 'atribute2':'value'}}

with collection("output.shp", "w", "ESRI Shapefile", schema) as output:
    for point in points:
        geometry = loads(point['wkt'])
        output.write({'properties':{'atribute1': point['atribute1'],
                                    'atribute2': point['atribute2']},
                      'geometry': mapping(geometry)

Largely cribbed from GIS with Python, Shapely, and Fiona by Tom MacWright, but I found the Shapely WKT info in the Shapely docs

Edit in response to @Mathew edit in original question: There are three different errors that I find I'm getting, and your version of Fiona may be calling them slightly different.

1. If 'datetime.datetime' is used instead of 'datetime' I get a 'ValueError' as 'datetime.datetime' is not in the list of valid property types.
2. If I'm trying to write somewhere outside my working directory, I'll get ERROR:Fiona:OGR Error 1: Failed to create file .shp file. Try without specifying a directory in the file path.
3. I'm getting a Warning:Fiona:OGR Error 6: Field time create as date field, though DateTime requested. Which looks to be because shapefiles don't specifically allow a datetime field. You can separate out the datetimes into 2 separate properties, or write them as a string.

Answer 2

First, if you don't have a geometry/geography column in the table yet, add it by converting WKT or WKB to geometry/geography using SQL Server Spatial (STLineFromText, STPOlyFromText, STPointFromWKT, similarily works from WKB).

Then, simply connect to the database (Database Connection) and create a query layer. At this point, you'll have a layer in the ArcMap that you can export to shape.