Skip to content

Windows installation notes, issues and error message explanations

Jump to bottom
Robin Wilson edited this page Feb 19, 2020 · 1 revision

Working out the instructions for setting up pepys-import on Windows took quite a while, with a lot of 'wrong-turns' along the way, so this document aims to describe some of the rationale behind issues that were experienced and explain some of the issues along the way.

Useful tools

The Rapid Environment Editor is very useful for editing environment variables like PATH and PYTHONPATH and allows you to easily re-arrange entries in these variables. Make sure you run it with Administrator privileges or it won't be able to access the system variables.

Pure Python code

The pepys-import module itself works fine on Windows, as it is all pure Python code. It just needs cloning from the git repository, and the code itself works - it's just it's dependencies that are the issue.

Spatialite

Spatialite is the extension for the SQLite database that provides spatial functionality. Python includes support for SQLite as default, but unfortunately the version of the sqlite3.dll file included with Python has been compiled without RTree support. This is essential for using the full functionality of spatialite (note: without RTree support, spatialite will install correctly and seem to work properly until you carry out certain operations, at which point it will fail with some strange errors - see section below).

Therefore, to get spatialite to work properly with Python we need to replace the sqlite3.dll file that Python is using. There are a few ways of doing this:

  1. The simplest, but ugliest way is to replace the sqlite3.dll file in the Python DLLs directory (something like c:\Python37\DLLs with a new sqlite3.dll downloaded directly from the SQLite website (make sure you get the 64-bit version).

  2. The next simplest, but still unpleasant, way is to rename sqlite3.dll in the Python DLLs directory to something like sqlite3OLD.dll and then put the new sqlite3.dll file somewhere on the path (eg. C:\sqlite and then add that directory to the PATH).

  3. The nicest way to do this is to change the PYTHONPATH so that Python uses different directories in a different priority order to try and find DLLs. However, Python actually imports a file called _sqlite3.pyd which is a 'Python DLL' that links to the standard Windows DLL. This file is also kept in the Python DLLs directory. To get Python to pick up a new DLL you need to copy _sqlite3.pyd and put it and the new sqlite3.dll file in a new folder and add that folder to the PYTHONPATH environment variable. This will prepend this folder to the Python search path (accessible inside a Python program as sys.path) meaning it searches there before other folders (if you look at sys.path you will see that the DLLs folder of the Python installation is shown later in the path, therefore this new folder has priority)

We then need to install spatialite itself. Development on spatialite seems to have been rather slow recently, and to work with Python 3.7 we need to use the 'Next Generation' spatialite release. Finding an up-to-date download link for spatialite is rather difficult, so go here and download the 64-bit Next Generation zip file. Extract this to a folder and add this to the PATH.

Testing spatialite is set up correctly

You can use the code below to test that Spatialite is set up correctly. This tests both that a simple spatial operation works and that the RTree module is compiled in:

import sqlite3
import sys
from ctypes import util, cdll
import time

import faulthandler; faulthandler.enable()

# Verify some of the loaded paths and libraries:
lib = util.find_library('mod_spatialite')

print('loaded spatialite library: ', lib)

connection = sqlite3.Connection(':memory:')
connection.enable_load_extension(True)

print("About to load")
try:
    connection.load_extension("mod_spatialite")
    print("Loaded")
except:
    print("load_extension(mod_spatialite) failed, Error: ", sys.exc_info()[1], "occured.")
    print('\n\nTry loading using the name of the dll (spatialite.dll)')
    connection.load_extension("spatialite")

SQL = """select AsWKT(ST_LineFromText('LINESTRING(540000 220000, 540000 221609)', 32123))"""

with connection as conn:
    cur = conn.execute(SQL)
    results = cur.fetchall()

    for result in results:
        wkt = result[0]
        print(wkt)

print("Spatial functions working")
SQL = """CREATE VIRTUAL TABLE demo_index USING rtree(
   id,              -- Integer primary key
   minX, maxX,      -- Minimum and maximum X coordinate
   minY, maxY       -- Minimum and maximum Y coordinate
);"""

with connection as conn:
    cur = conn.execute(SQL)

print("If no errors above, then RTree working")

PostGIS

Installation Postgres and PostGIS on Windows is pretty easy (see Windows installation instructions) as the StackBuilder tool helps install the PostGIS extension.

pepys-import uses the testing.postgresql library to run tests on PostgreSQL. This actually sets up its own Postgres server for each test, then creates the databases and works with them, and then deletes them all at the end of the test. There isn't much documentation for this library, but it requires having the postgres.exe and initdb.exe files on the PATH so that the library can run them to create new databases and start new servers.

To make this work, you need to add the PostgreSQL bin directory (something like c:\Program Files\PostgreSQL\12\bin) to your PATH. However you need to make sure the directories are in the right order in your PATH or this will conflict with the spatialite install. This is because spatialite and PostGIS both use some of the same DLL files for some of their functionality (eg. geos and proj for dealing with geometries and projections), but they have different versions of these DLLs. This can lead to very strange errors. Therefore, you must make sure that your PATH is in the following order:

  • mod_spatialite folder (eg. c:\mod_spatialite)
  • PostgreSQL bin folder (eg. c:\Program Files\PostgreSQL\12\bin)
  • Rest of the PATH here

It seems that mod_spatialite will pick up DLLs from wherever it can find them, but PostgreSQL will by default just pick up DLLs from its own directory. Having the PATH this way around means that both will work (this took a lot of experimentation and head-scratching to work out).

Errors and what they mean

A number of the error messages that I've seen while setting this up make no sense at all - so hopefully this section can help translate them.

Segmentation fault

Generic failure - not very helpful! Often caused by incompatible SQLite and spatialite versions - make sure you're using the Next Generation version of spatialite.

The procedure entry point DllMain could not be located in the dynamic link library C:\mod_spatialite-NG-win-amd64\mod_spatialite.DLL.

This is nothing to do with the file that is given in the error message - it actually means that the incorrect copies of some of the DLLs that mod_spatialite.dll requires are being used. This happens if the PostgreSQL bin directory is higher up the PATH and some DLLs (like ligeos and libproj) are being picked up from the incorrect place.

%1 is not a valid Win32 application

This means you're mixing 64-bit and 32-bit DLLs and programs: make sure everything is 64-bit.

no such table: main.idx_States_location

This comes as a Python error from one of our tests, and looks like this:

    def do_execute(self, cursor, statement, parameters, context=None):
>       cursor.execute(statement, parameters)
E       sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such table: main.idx_States_location
E       [SQL: INSERT INTO "States" (time, sensor_id, location, heading, course, speed, source_id, privacy_id, created_date) VALUES (?, ?, GeomFromEWKT(?), ?, ?, ?, ?, ?, ?)]
E       [parameters: ('2020-02-18 10:36:22.976277', 5, None, None, None, None, 9, None, '2020-02-18 10:36:22.981480')]
E       (Background on this error at: http://sqlalche.me/e/e3q8)
..\venv\lib\site-packages\sqlalchemy\engine\default.py:580: OperationalError

This means that RTree support is not configured properly in the sqlite3.dll file that is being used. Sometimes this comes with a message below it stating something about RTree support not being available - but sometimes it seems like that message is missing (or gets hidden somehow). The RTree module creates these various index tables, and then the INSERT INTO command can't find the tables. To fix this, use a version of sqlite3.dll with RTree support available - and make sure that Python is using that specific DLL.

Other hints

  • Don't try and test sqlite DLLs by using the sqlite3 command-line interface and the .load function to load modules. Depending on the sqlite3.exe file that you have, it can look in strange places for the sqlite3.dll file and therefore use ones that aren't available to other programs running on the system, giving results inconsistent with what you're finding from Python.
Clone this wiki locally