PyQGis - Working with Spatialite

Question

I wrote a Vector layer to a spatialite file/db using the dedicated API tool, using valid driver names supported formats by OGR. When connecting to that db, I can't retrieve properly the geometry objects when using SQL command over the created db (other regular SELECT/INSERT/... SQL commands are perfectly working)

Creating the Spatialite File using API Tool

QgsVectorFileWriter.writeAsVectorFormat(layer, layer.name() + ".sqlite",
                                                        "utf-8", None, "SQLite")

I noticed it gives a default "GEOMETRY" name to the geometry column within my table

Then, when trying to execute some SQL comman over that file:

from pyspatialite import dbapi2 as db

db_filename = 'db.sqlite'
conn = db.connect(db_filename)
c = conn.cursor()
ex = c.execute

rs = c.execute('SELECT sqlite_version(), spatialite_version()')
for row in rs:
    msg = "> SQLite v%s Spatialite v%s" % (row[0], row[1])
    print msg

rs = c.execute('SELECT IsValid(GEOMETRY) FROM table LIMIT 10' )
for row in rs:
    msg = row[0]
    print msg

returns:

SQLite v3.7.17 Spatialite v3.1.0-RC2

-1 for every selected row, which mean that the geometry is invalid. Other function such as AStext(GEOMETRY), etc return None or various error code

Of course, if I reload that file wthin Qgis using the API command, geometry is correct. I suspect I misunderstand what QgsVectorFileWriter is actually doing, please advice.

(note: I'm using GDAL 1.9)

Answer 1

Solved!

I was wrong when using the optional parameters syntax. SO, creating the spatialite database using the Spatialite format for the geometry column was my problem as it was defaulted to WKB format that was not appropriate for what I wanted to do.

QgsVectorFileWriter.writeAsVectorFormat(layer, layer.name() + ".sqlite",
                             "utf-8", None, "SQLite", False, None ,["SPATIALITE=YES",])

interresting links:

http://www.gdal.org/ogr_formats.html

http://www.gdal.org/drv_sqlite.html