Python, Spatialite

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):

import sqlite3

with sqlite3.connect(':memory:') as conn:

	conn.enable_load_extension(True)

	conn.execute("SELECT load_extension('mod_spatialite')")

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):

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: Don’t make any mistakes here (like deleting things or adding unnecessary spaces), or your system might not work…

To bring up the Environment Variables dialogue (Windows 7 and 8):

  • open Control Panel
  • go into System
  • click on Advanced system settings on the left
  • then click Environment Variables at the bottom

To bring up the Environment Variables dialogue (Windows 10):

  • open Start Menu
  • right click on the File Explorer icon, then into More then click Properties
  • click on Advanced system settings on the left
  • then click Environment Variables at the bottom

 

In the System variables pane, scroll the box down, select the Path variable, then go to Edit, hit End to get the cursor at the end, add a semi-colon (;) and then enter the path to your SQLite/mod_spatialite DLLs, in my case:

;C:\Program Files (x86)\Spatialite

Click Ok.

Override old sqlite3.dll

The final step is to make Python 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:

import sqlite3

with sqlite3.connect(':memory:') as conn:

	conn.enable_load_extension(True)

	conn.execute("SELECT load_extension('mod_spatialite')")

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)")

Advertisements

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

  1. 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.


    FROM frolvlad/alpine-python3
    RUN apk add sqlite3
    RUN apk add spatialite-bin
    RUN rm -rf /var/cache/apk/*

    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:

    docker build -t [image]:[tag] . && docker run -it -v path/to/data:/data -v /path/to/source:/source [image]:[tag]

    Unfortunately, it seems it’s not trivial to install and use Docker itself on Windows… https://docs.docker.com/windows/step_one/

  2. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s