spatialite extension cannot be loaded (Win10)

[noschinl]

Issue: Loading the spatialite extension either in sqlite3 or in Python's sqlite3 module fails

I installed the libspatialite package and tried to use it either in the sqlite3 command line client or with the python sqlite3 modile:

>sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load spatialite
Error: Die angegebene Prozedur wurde nicht gefunden.

sqlite> .load libspatialite
Error: Das angegebene Modul wurde nicht gefunden.

(Sorry for the german error messages, I don't know how to change them to english).

The error messages for the python case are the same.

Environment (conda list):

``` $ conda list (base) C:\Users\Lars Noschinski\IdeaProjects\poi_converter>conda list # packages in environment at C:\Users\Lars_Ohne_Leerzeichen\Miniconda3: # # Name Version Build Channel asn1crypto 1.3.0 py37_0 ca-certificates 2019.11.28 hecc5488_0 conda-forge certifi 2019.11.28 py37_0 conda-forge cffi 1.14.0 py37h7a1dbc1_0 chardet 3.0.4 py37_1003 conda 4.8.2 py37_0 conda-forge conda-package-handling 1.6.0 py37h62dcd97_0 console_shortcut 0.1.1 3 cryptography 2.8 py37h7a1dbc1_0 freexl 1.0.5 hd288d7e_1002 conda-forge geos 3.8.0 he025d50_1 conda-forge idna 2.8 py37_0 libiconv 1.15 hfa6e2cd_1005 conda-forge libspatialite 4.3.0a hed33574_1035 conda-forge libxml2 2.9.10 h9ce36c8_0 conda-forge menuinst 1.4.16 py37he774522_0 openssl 1.1.1d hfa6e2cd_0 conda-forge pip 20.0.2 py37_1 powershell_shortcut 0.0.1 2 proj 6.3.1 ha7a8c7b_1 conda-forge pycosat 0.6.3 py37he774522_0 pycparser 2.19 py37_0 pyopenssl 19.1.0 py37_0 pysocks 1.7.1 py37_0 python 3.7.4 h5263a28_0 pywin32 227 py37he774522_1 requests 2.22.0 py37_1 ruamel_yaml 0.15.87 py37he774522_0 setuptools 46.0.0 py37_0 six 1.14.0 py37_0 sqlite 3.31.1 he774522_0 tqdm 4.42.1 py_0 urllib3 1.25.8 py37_0 vc 14.1 h0510ff6_4 vs2015_runtime 14.16.27012 hf0eaf9b_1 wheel 0.34.2 py37_0 win_inet_pton 1.1.0 py37_0 wincertstore 0.2 py37_0 yaml 0.1.7 hc54c509_2 zlib 1.2.11 h2fa13f4_1006 conda-forge ```

Details about conda and system ( conda info ):

``` $ conda info active environment : base active env location : C:\Users\Lars_Ohne_Leerzeichen\Miniconda3 shell level : 1 user config file : C:\Users\Lars Noschinski\.condarc populated config files : conda version : 4.8.2 conda-build version : not installed python version : 3.7.4.final.0 virtual packages : __cuda=10.1 base environment : C:\Users\Lars_Ohne_Leerzeichen\Miniconda3 (writable) channel URLs : https://repo.anaconda.com/pkgs/main/win-64 https://repo.anaconda.com/pkgs/main/noarch https://repo.anaconda.com/pkgs/r/win-64 https://repo.anaconda.com/pkgs/r/noarch https://repo.anaconda.com/pkgs/msys2/win-64 https://repo.anaconda.com/pkgs/msys2/noarch package cache : C:\Users\Lars_Ohne_Leerzeichen\Miniconda3\pkgs C:\Users\Lars Noschinski\.conda\pkgs C:\Users\Lars Noschinski\AppData\Local\conda\conda\pkgs envs directories : C:\Users\Lars_Ohne_Leerzeichen\Miniconda3\envs C:\Users\Lars Noschinski\.conda\envs C:\Users\Lars Noschinski\AppData\Local\conda\conda\envs platform : win-64 user-agent : conda/4.8.2 requests/2.22.0 CPython/3.7.4 Windows/10 Windows/10.0.18362 administrator : False netrc file : None offline mode : False ```

[noschinl]

I also tried the instructions on https://github.com/conda-forge/libspatialite-feedstock/issues/25#issuecomment-387051781, but without success

[noschinl]

Reading https://www.gaia-gis.it/fossil/libspatialite/wiki?name=mod_spatialite, I understand that this cannot work, as this package only ships libspatialite.dll on Windows, but not mod_spatialite.dll. Considering that the packages for OS X and Linux contain a mod_spatialite build, as there a reason this package does not ship mod_spatialite on Windows?

[gillins]

The Linux package does not contain mod_spatialite either... I don't think we are building it at all, but finding the documentation very hard to understand. Can you find instructions on how to build mod_spatialite?

[noschinl]

@gillins, are you sure about the Linux package not containing mod_spatialite? At least the newest linux file in the conda-forge channel on anaconda contains a mod_spatialite.so?

As for building mod_spatialite: I haven't set up a build environment on Windows yet, but on linux ./configure && make builds mod_spatialite.

[noschinl]

So, I was able to to build libspatialite on windows using mingw. ./configure && make causes mod_spatialite.dll to be build, too. make install-strip copies mod_spatialite.dll to $PREFIX/lib, as expected.

Having tested that, it seems that you are using MSVC instead of mingw, using makefile.vc instead of configure -- this may be the source of the problem (as I don't see any instructions for mod_spatialite.dll in there).

I assume that the compiler chain for conda-forge ist fixed?

[gillins]

We have to use MSVC to be compatible with Python. Can you see anyway to build mod_spatialite.dll with makefile.vc? Might be a question for upstream.

[noschinl]

In theory, this should be possible. The current development versions also contain appropriate makefiles (see the mailing list).

Given that the last release candidate is from 2018, the best way forward would probably to backport these changes. I'll have a look whether this is feasible for me.

[johntruckenbrodt]

Might this help?
NEXTGEN RC1 - loadable modules for Windows

[PMeira]

Hi, all, Following the discussion on https://github.com/ContinuumIO/anaconda-issues/issues/10926 (cc @BigBaaadBob), I'd say the best option would be to update the recipe to the 5.0.0 RC1 (@johntruckenbrodt linked the binaries a few days ago) or wait for the full 5.0.0 release soon. I didn't know the official Anaconda package was forked from the conda-forge version (or even it's existence), otherwise I would have posted here from the start.

(EDIT: 5.0.0 has been released: https://groups.google.com/u/2/g/spatialite-users/c/K3gcP9_C1Zo )

As @noschinl mentioned, the latest dev versions of Spatialite do contain the updated scripts for Microsoft Visual Studio, including makefiles for the required mod_spatialite on Windows instead of the plain libspatialite.

Compared to Spatialite 4.3.0, Spatialite 5 introduces RTTopo (RT Topology Library) as a dependency (licensed under the GPL) replacing LWGEOM (another GPL library), which is not included the current recipe. Per https://github.com/conda-forge/libspatialite-feedstock/issues/53, RTTopo is not yet available on conda-forge. The build instructions on Windows seem simple enough, hopefully it should be easy to build. Building without RTTopo is possible but would require a larger but still simple patch on Windows (that would keep Spatialite LGPL). For the other systems, --enable-rttopo is required to enable it (defaults to "no").

I also noticed on https://github.com/conda-forge/freexl-feedstock/pull/18 that for the latest FreeXL the patches need updating.

On the issue per se ("spatialite extension cannot be loaded"), I'd suggest adding a test for loading the SQLite module to ensure it's available, based on the current tests something like:

import os
import sys
import sqlite3

if sys.platform == 'win32':
    path = os.path.join(sys.prefix, 'Library', 'bin', 'mod_spatialite.dll')
elif sys.platform == 'darwin':
    # LD_LIBRARY_PATH not set on OS X or Linux.
    path = os.path.expandvars('$PREFIX/lib/mod_spatialite.dylib')
elif sys.platform.startswith('linux'):
    path = os.path.expandvars('$PREFIX/lib/mod_spatialite.so')
else:
    raise Exception('Cannot recognize platform {!r}'.format(sys.platform))

# Test loading the resulting SQLite3 extension
db = sqlite3.connect(':memory:')
db.enable_load_extension(True)
db.execute("SELECT load_extension(?)", [path])
db.enable_load_extension(False)
db.close()

I could try to work on this if nobody is available, but I'm not sure about the RTTopo situation (adding it to conda-forge and as a dependency here). Note that I never handled a conda-forge package before either.

[theroggy]

It would be great if the conda-forge package of libspatialite would be upgraded to 5.0.0 including rttopo. The current package doesn't seem to include LWGEOM, which cripples it somewhat...

So an upgrade to 5.0.0 with rttopo would solve quite some issues...

[xylar]

@theroggy, @PMeira, @noschinl and others I am working on building 5.0.0 without rttopo here: #56. That will be a start. But someone has to be willing to add rttopo to conda-forge before spatialite can be built with it (see #53).

[xylar]

I've put in the time I can give in #56. If someone has an idea of how to get mod_spatialite working in Windows (see https://github.com/conda-forge/libspatialite-feedstock/pull/56#discussion_r500929888), We can move forward with 5.0.0. Or if someone has time to put in a ticket: https://www.gaia-gis.it/fossil/libspatialite/reportlist

I've given what I can but I'm going to have to drop this a move on to my own work unless and until I get some suggestions for what else to try.

[xylar]

Okay, libspatialite 5.0.0 is in.

[xylar]

We added tests that check that mod_spatialite can now be loaded in Windows, so I think this issue is addressed. Please re-open if not.

[digital-idiot]

I am still having this problem in MacOS.

❯ sqlite3
SQLite version 3.46.0 2024-05-23 13:25:27
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> .load libspatialite
Error: dlsym(0x8bb10240, sqlite3_spatialite_init): symbol not found

My environment:

micromamba list libspatialite
List of packages in environment: "~/micromamba/envs/PyDev"

  Name           Version  Build       Channel
───────────────────────────────────────────────────
  libspatialite  5.1.0    hf7a34df_8  conda-forge

[xylar]

@digital-idiot, could you open a new issue and provide the full requested information about your conda environment? This would help us to better help you out. The information you provided above isn't quite enough on its own.