SQLite 3 itself is included in official Python 3 distribution, so it would be nice to add spatial functionality. Alas, attempts to use SpatiaLite with Python 3 on x64 Windows 7 turned out to be a very messy task. Python had an extension for SpatiaLite, PySpatiaLite, but it has not been updated since 2013 and project seems to be inactive, and throws errors during installation. Probably, these errors could be solved, but I wanted to test SpatiaLite, not debug installation package.
There are plenty of stories how SpatiaLite extension does not load, throws errors at installation etc. After some research and testing, the solution turned out to be relatively simple – just use SQLite’s built-in functionality to load extensions.
Step 1. Dependencies
It is assumed Python 3 is already installed. Download mod_spatialite from SpatiaLite binaries page; x86 or x64, according to your platform. Be sure to download the archive that starts with mod_spatialite, this is the library intended to be loaded dynamically.
Extract it somewhere on the disk from where it will be referenced in your script. For example, C:/projects/proj1/bin.
Step 2. Standard stuff
In python script, import sqlite3 that comes with Python distribution, create an empty or open an existing SQLite database. Enable extension loading for the connection.
import sqlite3 connection = sqlite3.connect('C:/temp/test.db') connection.enable_load_extension(True)
Step 3. Set up dll seach paths
In the script, add the path where mod_spatialite is located, to the dll search path. This is very important. mod_spatialite dll itself uses some other dlls that come with it in the distribution package.
os.environ['PATH'] = 'C:/projects/proj1/bin' + ';' + os.environ['PATH']
If some dll cannot be fould, you will get error, something like this:
Traceback (most recent call last): File <pyshell#10>, line 1, in <module> connection.load_extension('C:/projects/proj1/bin/mod_spatialite') sqlite3.OperationalError: The specified module could not be found.
Step 4. Load SpatiaLite dll and initialise spatial database
Load extension using sqlite3 function. DO NOT specify file extension. If the database is freshly created, initialize spatial database structures.
connection.load_extension('C:/projects/proj1/bin/mod_spatialite') cur = connection.cursor() # parameter 1 means execute everything in one transaction. It is faster. cur.execute('SELECT InitSpatialMetaData(1)') connection.commit() connection.close()
Sample code with x64 mod_spatialite binaries included can be found at GitHub.
Test if there are no memeory leaks, sudden hangs etc.