A technical blog concerning Python programming and GIS applications

Python and Spatialite (32-bit) on 64-bit Windows

For some reason it is insanely difficult to use Spatialite from Python on Windows. In theory the following should create and connect to an in-memory database, then load the Spatialite extension (after mod_spatialite and friends have been put somewhere and added to your system Path):

However, this fails stating: OperationalError: The specified module could not be found.

Numerous posts on StackExchange referring to this error(1, 2,3 and 4) suggest that this is something to do with 64-bit Windows DLL Hell. Interestingly if you point a 32-bit Python/SQLite at a 64-bit mod_spatialite (or vice versa) the error is: OperationalError: %1 is not a valid Win32 application., suggesting that in the initial case the module was in fact found, but some other error is occurring along the line and the error message is a lie.

After a lot of head scratching and some horrific workarounds I did manage to find a simple solution that works for the following setup (all 32-bit on 64-bit Windows 10):

Python 2.7.10

SQLite 3.9.2

Spatialite 4.3.0a

Clean up

First: make sure to clean up and restore any bits of the above that you may have interfered with while trying to get things working (system Path, overwritten DLLs, etc.).

Download

Download clean libraries of mod_spatialite and SQLite (I would strongly suggest actually using cyqlite, which is SQLite compiled for Windows with some useful options enabled – notably R-Tree, meaning that you can use Spatial Indexes):

mod_spatialite: mod_spatialite-[version]-win-x86.7z from http://www.gaia-gis.it/gaia-sins/ – scroll down to the bottom (pink box) for MS Windows binaries > current stable version > x86

Extract files and add to Path

Extract files from the archives you have downloaded and put them all into a single folder in a handy location – I chose C:\Program Files (x86)\Spatialite. Extract the SQLite archive first then mod_spatialite, and overwrite any files if prompted. Now add that folder to the System Path (which is just a list of folders that Windows checks out to try and find files).

WARNING: You could break your system if you are not careful here: don’t delete anything unnecessarily!

To bring up the Environment Variables dialogue in Windows 10:

click on the Start Menu

type (starts searching) “SystemPropertiesAdvanced”

then click the Environment Variables button at the bottom

You have two options regarding the kind of variable you want to create:

User variables: are applied to your login only (preferred option)

System variables: will be applied for all users of the operating system

In either the User or System variables pane, select the ‘Path’ (or ‘PATH’) variable, then hit Edit. Click New and then paste the folder path, i.e.:

C:\Program Files (x86)\Spatialite

If there is no ‘Path’ variable under User variables you will need to create one by clicking New (set the name to Path and then you can paste or browse to the directory).

Override old sqlite3.dll

The final step is to convince Python to use our new sqlite3.dll, rather than the one it came with. To do this, open the Python DLLs folder (i.e. C:\Python27\DLLs), find sqlite3.dll and rename it to something like sqlite3_old.dll. As the new sqlite3.dll and mod_spatialite are all on the system Path, these will now be loaded by default.

Test it out

The same code we ran above can be used to check that everything works:

Bonus

To actually use a spatial database, spatial metadata must first be initialised, otherwise you will receive a AddGeometryColumn() error: unexpected metadata layout error when trying to add geometry columns. The command for doing this is SELECT InitSpatialMetaData(), run directly after the extension is loaded, which works fine for in-memory databases but is very slow for physical databases as it consists of a large number of transactions. The best way around this is to pass the function the value 1, i.e. in Python:conn.execute("SELECT InitSpatialMetaData(1)")

If you are doing Python development, you may be interested in my Windows Dev Stack, which describes my development environment from high level technologies down to specific apps, and how they all work together.

5 thoughts on “Python and Spatialite (32-bit) on 64-bit Windows”

This made me appreciate Docker even more 🙂 I’d just write a minimal Dockerfile (with Alpine Linux and Python 3.4, this would be only about 250 MB) mount your data directory and away you go. Takes a lot less time than sorting out all this Windows DLL business.

I haven’t tested this, but since trying out Docker, I can’t see anyway I’d ever stop using it. spatialite-bin probably has other dependencies, like GEOS and PROJ4, and may need to be compiled from source, but there is probably something on Dockerhub with this ready to go, and if not it’s still only one more command. Then spin it up:

I’ve done my fair share of wrestling with getting Spatialite to run, ever since the 2.X versions. Now I am juggling 32 and 64 bit versions. The error you report is what I see when the SQLite version is behind the Spatialite version. Sandro (the developer) tends to use the latest version of SQLite when compiling. One trick is that the SQLite dll that Spatialite is compiled against is included in the Spatialite distribution.

The trick with using the (1) in the InitSpatialMetaData call is great. Database creation when from a couple of minutes to almost instantaneous.

I’m finding an odd problem. When I use IDLE, I can import arcpy and load mod_spatialite.dll. It all works fine. But, when I try and load mod_spatialite.dll from inside the Arcpy console in ArcGIS, it says it can’t find the mod_spatialite module. I’ve even tried it with Geany, and that works too. For some reason, it appears to be blocked when trying to run it from the Arcpy console. Have you ever come across this?

I’m not able to test this at the moment, sorry, but I think it might be to do with the path order. Python loads its modules from the Pythonpath (a list of folders where modules should be loaded from) – ArcGIS adds some other stuff to this path to get it’s own libraries in there. In the past I remember seeing this as Desktop10.pth in the ArcGIS Python install directory. You might be able to find a solution by editing this file or doing your own path setting after to point to the directory which contains spatialite.dll?