4

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.

  • Is OGR an option? You can export the shape to WKT (or WKB) using the geometry class gdal.org/python/osgeo.ogr.Geometry-class.html there's a good example on this page gdal.org/ogr_apitut.html (skip over c/c++) to nearly the end. Or do you have your heart set on arcpy? – Michael Stimson Oct 29 '14 at 4:50
  • OGR is an option, but I've only seen it used when bringing over entire sql-server tables as a shapefile. Can I write data I've already imported to python to a shapefile using ogr? – Mathew Oct 29 '14 at 14:57
  • both osgeo.ogr.Geometry and arcpy.Geometry have a method to create a geometry object from WKT or WKB; for OGR it's in the constructor, for arcpy use FromWKT resources.arcgis.com/en/help/main/10.1/index.html#//… but if you're new to python that might be a little advanced - it's only a fraction of what's involved in creating a shapefile and inserting geometries (features). Either shapely, arcpy or OGR are great geospatial libs to learn but it's probably best to pick one and stick to it until you're better at python, then by all means learn the others! – Michael Stimson Oct 29 '14 at 21:46
3

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.
  • I removed the date properties for debugging and am trying to run: collection("outCell.shp","w","ESRI ShapeFile", schema) I get the following error: fiona.errors.DriverError: unsupported driver: 'ESRI ShapeFile' – Mathew Oct 29 '14 at 20:42
  • @Mathew Try without the CamelCaps, e.g.: 'ESRI Shapefile' – Alex Kerney Oct 29 '14 at 21:03
1

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.

  • I have been able to export the layers in ArcMap to shapefiles, but I eventually want to automate this process, so that's why I'm looking for a method to do this in Python. – Mathew Oct 29 '14 at 15:01
  • 2
    To automate the above workflow you can use arcpy. But there are also other approaches like using shapely and ogr – Matej Oct 29 '14 at 15:13
  • That's what I've found searching the web: There are multiple approaches to do this. Do anyone have any recommendations? Or, better yet, examples? Based on what I've seen from Ogr2Ogr, it can be accomplished with 1 command line, but it looks like it takes an entire script in python. – Mathew Oct 29 '14 at 16:07

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.