3

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)

  • QGIS 2.4. I edit my initial post to add SQLITE and spatialite version I work with. I created the "database" using the QgsVectorFileWriter.writeAsVectorFormat from the QGIS API, no gui, it's part of a QGis Standalone application I'm building. This function write as whatevername.sqlite file on my filesystem. I can connect and perform sql using standart shell connection to that file (ie: sqlite3 myfile.sqlite) – Peter Peterson Aug 5 '14 at 9:36
  • I should mention I just try to complete some optional statements for the gsVectorFileWriter.writeAsVectorFormat without success. QgsVectorFileWriter.writeAsVectorFormat(layer, layer.name() + ".sqlite", "utf-8", None, "SQLite", False, "0" ,"METADATA=YES SPATIALITE=YES", "FORMAT=SPATIALITE") – Peter Peterson Aug 5 '14 at 9:45
  • SQLite v3.7.17 Spatialite v3.1.0-RC2 – Peter Peterson Aug 5 '14 at 9:58
2

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

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.