Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

I'm trying to interact with a SpatiaLite database via Python on my Windows machine.

I've managed to connect to the database and load the SpatiaLite extension, which means that I can pass SQL queries in from my Python code e.g:

from pysqlite2 import dbapi2 as sqlite
myPath = r"C:\SpatiaLite\test-2.3.sqlite"
conn = sqlite.connect(myPath)
conn.enable_load_extension(True)
curs = conn.cursor()
curs.execute("select load_extension('libspatialite-1.dll')")    
curs.execute("""SELECT name, peoples, AsText(Geometry) from Towns where
                peoples > 350000 order by peoples DESC""")
# Print the selected records
for row in curs:
    print row

So far so good. I'd now like to know how to export a table as a shapefile. Using the command prompt in spatialite.exe I can do this using .dumpshp, but I can't figure out how to access this command from Python. Any ideas, please?

I was hoping for something like this:

curs.execute("select dumpshp('Towns Geometry shape_towns CP1252 POINT')")

but perhaps it's more complicated than that?

Thanks very much!

share|improve this question

2 Answers 2

up vote 5 down vote accepted

You can use the command line spatialite_tool and embed it in python with subprocess module.

In command line

spatialite_tool -e -shp shape_towns -d test-2.3.sqlite -t Towns -g Geometry -c CP1252 --type POINT

In Python

import subprocess

subprocess.call(["spatialite_tool", "-e", "-shp", "shape_towns", "-d", "test-2.3.sqlite", "-t", "Towns", "-g", "Geometry", "-c", "CP1252", "--type", "POINT"])
share|improve this answer
    
Thanks! That works really well. Do you know if there's some documentation for spatialite_tools anywhere? I've not had much luck Googling, but it would be interesting to know what else it can do. Thanks again! –  JamesS Jun 30 '11 at 12:19
    
Type in a command line shell spatialite_tool and you will get the options –  ThomasG77 Jun 30 '11 at 12:47
    
Great - thanks Thomas! As you can probably tell, I'm not very good at this yet... –  JamesS Jun 30 '11 at 13:00
    
We all have been beginners so not a problem :) –  ThomasG77 Jun 30 '11 at 13:03

I don't think you can use dumpshp because it is a macro command not a SQL command. I don't know if it is the best solution but you could process row-by-row and export it into shp file.

For example, something like this:

from osgeo import ogr
from pysqlite2 import dbapi2 as sqlite


myPath = r"D:\testSpatiaLite.sqlite"
conn = sqlite.connect(myPath)
conn.enable_load_extension(True)
curs = conn.cursor()
curs.execute("select load_extension('libspatialite-1.dll')")
curs.execute("""SELECT attribute1, AsText(the_geom) FROM points""")

output = "d:\\points.shp"

out_driver = ogr.GetDriverByName( 'ESRI Shapefile' )
out_ds = out_driver.CreateDataSource(output)
out_srs = None
out_layer = out_ds.CreateLayer("point", out_srs, ogr.wkbPoint)
fd = ogr.FieldDefn('name',ogr.OFTString)
out_layer.CreateField(fd)

for row in curs:
    feature = ogr.Feature(out_layer.GetLayerDefn())
    wkt = row[1]

    point = ogr.CreateGeometryFromWkt(wkt)
    feature.SetGeometry(point)
    feature.SetField('name',row[0])
    out_layer.CreateFeature(feature)
    feature.Destroy()

conn.close()
out_ds.Destroy()
share|improve this answer
    
Thanks, Mario, this is a good answer too. I've accepted @ThomasG77's answer as it's simpler, but I'm just starting to use GDAL/OGR and your code will definitely help me in the future. Cheers! –  JamesS Jun 30 '11 at 12:23
    
Thank you. :) Yes it is. Its much simpler than mine. –  Mario Miler Jun 30 '11 at 12:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.