6

I have an existing SQLITE Database and I want to create several additional geometry Layers with Python.

Within QGIS 2.x I used the following library

from pyspatialite import dbapi2

According to this issue Pyspatialite in QGIS 3 crash QGIS 2.99 does not have the pyspatialite library anymore. With the sqlite3 library there is obviously no spatialite-extension included.

So now I am stuck. Is there another library I can use?

  • You can connect to Spatialite databases directly using pysqlite and I would assume also pyspatialite. You can also add spatial data of most types using ogr2ogr. Is there a reason you need to involve QGIS in your workflow? – eric s Nov 2 '17 at 17:47
  • Yes, it is part of a QGIS plugin! The whole workflow (creating new polygons, points, topology, etc.) is done in QGIS. – AndreasK Nov 2 '17 at 20:59
6

Just opened a PR to fix this issue in QGIS v3 (on Windows). Remember that this is a dev version.

In the meantime, you could use this installer and adjust it by yourself (free software rocks!) after installation. You just need to change line 602 of C:\\Program Files\\QGIS 2.99\\apps\\qgis-dev\\python\\qgis\\utils.py by:

    ("spatialite", "spatialite_init_ex"),

Restart QGIS and now you'll be able to use sqlite3 connections with SpatiaLite support by using the following line from the QGIS Python Console or from your plugin:

con = qgis.utils.spatialite_connect("/path/to/your/spatialite_db.sqlite")

Now, you can create a new point layer in your database in this way (see the docs):

cur = con.cursor()
# Run next line if your DB was just created, it may take a while...
cur.execute("SELECT InitSpatialMetaData()") 
cur.execute("""CREATE TABLE test_geom (
  id INTEGER NOT NULL
    PRIMARY KEY AUTOINCREMENT,
  name TEXT NOT NULL,
  measured_value DOUBLE NOT NULL);""")
cur.execute("""SELECT AddGeometryColumn('test_geom', 'the_geom',
  4326, 'POINT', 'XY');""")
cur.close()
con.close()

This little fix also makes db_manager able to open SpatiaLite databases.

  • Thank you very much, changing line 602 does the trick! (And of course you are right, it is a dev-version!) – AndreasK Nov 6 '17 at 11:26
  • Great! FYI, Jürgen F. has commited another change to make sure SpatiaLite module gets loaded on Windows, which according to him is not happening, even with the change I proposed. So in newer QGIS versions you shouldn't need this code tweak I told you. – Germán Carrillo Nov 6 '17 at 12:37
  • In the last QGIS 2.99 weekly, this works perfectly (without any tweak) : import qgis.utils ... con = qgis.utils.spatialite_connect("/path/to/your/spatialite_db.sqlite") – sigeal Dec 4 '17 at 13:15
  • Yeap! That's now fixed in QGIS master! – Germán Carrillo Dec 4 '17 at 14:03
2

As Jürgen Fischer said on the bug tracker feed here

Use sqlite3 instead of pyspatialite for SpatiaLite connections

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.