Use spatialite extension for SQLite on Windows

Question

I know that this has been asked in some similar ways before. However, all questions I found on this dealt with some very specific system setups which were not applicable for me (because so is mine).

System:

• Windows 7 64bit
• Python 3.4 64bit
• sqlite3 2.6.0 (shipped with Python I guess)
• Spatialite Windows binaries 2.3.1 (anything else of importance?)

How can I activate the spatialite extension for the ´sqlite3´ module?

What I tried (the way that other people in similar questions say it works):

• Downloading from https://www.gaia-gis.it/spatialite-2.3.1/binaries.html :
  • libspatialite-win-x86-2.3.1.zip
  • proj-win-x86-4.6.1.zip
  • geos-win-x86-3.1.1.zip
  • libiconv-win-x86-1.9.2.zip
• unzipping all of them into the same folder on C:\
• (also tried only putting the DLLs into that folder)
• putting that folder into my system PATH variable

Then, running

import sqlite3

conn = sqlite3.connect(":memory:")
conn.enable_load_extension(True)
conn.execute('SELECT load_extension("libspatialite-2.dll")')

gives

conn.execute("SELECT load_extension('libspatialite-2.dll')")
sqlite3.OperationalError: The specified module could not be found.

What more can I try to make this work?

Seise

As a suggestion, put Spatialite folder as first in your PATH environment variable. I was having troubles because Spatialite path was after GDAL. – Guzman

Answer 1

you probably don't have the folder in which libspatialite-2.dll is placed in your PATH. Perhaps you can add the folder from within your Python script (I don't know any Python). Or else you could add it from the Windows properties interface.

BTW you are using a very old version of spatialite: have a look here for newer versions: https://www.gaia-gis.it/fossil/libspatialite/index

Overabundance

The old version of spatialite was the issue! Google confused me with subpages of old spatialite versions on https://www.gaia-gis.it when searching for Windows binaries, that's why I thought there were no more recent ones. Besides, the library/binaries have been renamed from spatialite-2(/3/4).dll to mod_spatialite.dll which made me think that this is something different. Downloading mod_spatialite-4.2.0-win-amd64.7z from this download page and putting all DLLs into c:\Windows\system32 solved the issue. – Seise

By the way, this download page on Gaia GIS provides Windows 32bit binaries. I lost almost a whole workday by trying to compile mod_spatialite myself (don't do this on Windows :-( ) before I finally found the compiled binaries -_- – Seise

As a suggestion, put Spatialite folder as first in your PATH environment variable. I was having troubles because Spatialite path was after GDAL. – Guzman

Answer 2

I have recently faced this problem with mod_spatialite.dll with Spatialite 5.0.1, Python 3.8 (with Anaconda) and Windows 10. I fixed the problem with the following steps:

1. Installed OSGeo4W;
2. Copied all the files and folders in OSGeo4W64\bin to anaconda3\Library\bin (without replacing the existing files, as this may break other things in the Python installation). I backed up this folder beforehand so I could undo everything, just in case things go wrong;
3. Copied mod_spatialite.dll (which I had already downloaded) into anaconda3\Library\bin.

The reason why I think I had this problem is because of missing recursive dll dependencies; since just getting the direct dependencies (through dumpbin /dependents mod_spatialite.dll) and copying them to the anaconda3\Library\bin folder did not solve the problem, so I came to the conclusion that I was missing a dependency of some dependency. Given that all dependencies are already neatly placed in OSGeo4W64\bin, copying everything from there solved it for me.

Calvaria