Spatialite on anaconda

[Robert Withrow]

I'm having problem loading the spatialite extension using conda, and I've notice other folks have too.  Example: https://github.com/ContinuumIO/anaconda-issues/issues/10926

I'm wondering if the extension is being properly built...

Does anyone have any ideas?

Thanks!

[Brad Hards]

It looks like the sqlite extension loading mechanism may be disabled. A bad build of the extension seems less likely given that specific error message (per the issue).

 

I think you should treat this as an anaconda-specific issue.

 

Brad

--
You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/f7282e80-dc3f-440e-b04d-d18577f3ffd5n%40googlegroups.com.

[a.fu...@lqt.it]

On Wed, 19 Aug 2020 19:26:27 -0700 (PDT), Robert Withrow wrote:
> I'm having problem loading the spatialite extension using conda, and
> I've notice other folks have too. Example:

> https://github.com/ContinuumIO/anaconda-issues/issues/10926 [1]

>
> I'm wondering if the extension is being properly built...
>
> Does anyone have any ideas?
>

Hi Robert,

please note than on Win10 an error message like:

load_extension(mod_spatialite) failed, Error: The specified module
could not be found

doesn't simply means that the system was unable to locate nowhere
the mod_spatialite.dll module.
it will more probably intend that some further DLL required by
mod_spatialite.dll itself couldn't be succesfully loaded.

the rules adopoted by Windows for locating and loading any
DLL are really messy and confusing; please carefully check
the MS own doc:

https://docs.microsoft.com/en-us/windows/win32/dlls/dynamic-link-library-search-order#standard-search-order-for-desktop-applications

few golden rules:

1. carefully check to never mix 32 and 64 bit DLLs
an app running on Python 32 bit always requires 32 bit DLLs,
exactly as Python 64 bit requires 64 bit DLLs

2. use the CLI front end sqlite3.exe from the command shell
so to check if mod_spatialite.dll could be succesfully
loaded

3. use the smart DependencyWalker in order to chek if
mod_spatialite.dll has any broken dependency
https://www.dependencywalker.com/

4. properly set the PATH environment variable so to
include all folders where your DLLs have been
installed

final considerations: properly configuring all
DLLs required by mod_spatialte.dll for dynamic
loading on Windows (and more notably on Win10)
isn't at all impossible.
but for sure it will be a not-so-easy task, and
will probably cause you some headache :-P

bye Sandro

[Pedro Camargo]

A couple of months ago I wrote a blog post covering this in detail



---- On Thu, 20 Aug 2020 16:32:37 +1000 <a.fu...@lqt.it> wrote ----

> --
> You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

> To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/dc9124f291a9a9c77296d4a08c6eb666%40lqt.it.
>

[Pedro Camargo]

I'm sorry,  pressed send by mistake. 

The blog post is here:

https://xl-optim.com/spatialite-and-python-in-2020/ 

Cheers, 

Pedro 

---- On Thu, 20 Aug 2020 20:06:04 +1000 c...@margo.co wrote ----

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/1740b5528ff.5c8ab5d428586.6541078022411261261%40margo.co.

[Robert Withrow]

Thanks for the replies! Here is what I've learned since yesterday.

First, note that I'm trying to have spatialite work in an Anaconda environment for reasons dictated by company police I have do deal with.... :-) As a result, if something is broken in Anaconda, I want to see if I can get it fixed.

As far as I can tell sqlite extension loading is enabled in current conda builds. I don't know of another simple extension to load to prove that (but let me know of one and I'll try it).

Also, the error message is "specified procedure could not be found", not "specified module could not be found", which I think means that the module was found and loaded but the DDL initialization procedure in the nodule could not be found. The fact that it found and tried to load the module also seems to show that extension loading is enabled.

Pedro's article is very helpful and almost seems to work. When I follow his lead, I get the following results:

Code:

import os
print('current directory:',os.getcwd())
spatialite_path = os.path.join(os.getcwd(),'mod_spatialite-4.3.0a-win-amd64')
os.environ['PATH'] = spatialite_path + ';' + os.environ['PATH']
print('PATH:')
paths = os.environ['PATH'].split(';')
for p in paths:
    print('  ',p)

from ctypes import util
lib = util.find_library('mod_spatialite')
print('spatialite library:', lib)

import sqlite3
connection = sqlite3.connect('geotest')
connection.enable_load_extension(True)
connection.load_extension('mod_spatialite')

Results (it sure seems like it is trying to load the extension):

current directory: C:\Users\witr\Documents\Python Scripts\geo
PATH:
   C:\Users\witr\Documents\Python Scripts\geo\mod_spatialite-4.3.0a-win-amd64
   C:\Users\witr\miniconda3\envs\geo
   C:\Users\witr\miniconda3\envs\geo\Library\mingw-w64\bin
   C:\Users\witr\miniconda3\envs\geo\Library\usr\bin
   C:\Users\witr\miniconda3\envs\geo\Library\bin
   C:\Users\witr\miniconda3\envs\geo\Scripts
   C:\Users\witr\miniconda3\envs\geo\bin
   C:\Users\witr\miniconda3\condabin
   C:\Windows\system32
   C:\Windows
   C:\Windows\System32\Wbem
   C:\Windows\System32\WindowsPowerShell\v1.0
   C:\Windows\System32\OpenSSH
   C:\Program Files\Wolfram Research\WolframScript
   C:\Users\witr\AppData\Local\Microsoft\WindowsApps
   .
spatialite library: C:\Users\witr\Documents\Python Scripts\geo\mod_spatialite-4.3.0a-win-amd64\mod_spatialite.dll
Traceback (most recent call last):
  File "geotest_dl.py", line 19, in <module>
    connection.load_extension('mod_spatialite')
sqlite3.OperationalError: A dynamic link library (DLL) initialization routine failed.

[Robert Withrow]

I'm kinda floundering around here but I can duplicate this problem outside of python by using the sqlite3.exe binaries from the miniconda environments. It turns out if I use 32 bit spatialite libraries and sqlite.exe, I get expected (good) results. If I use the 64 bit libraries and sqlite.exe I get the same  "A dynamic link library (DLL) initialization routine failed." error I reported earlier.

Incidentally, the sqlite.exe I'm using is version 3.33, whereas the one that ships with the spatialite libraries is 3.8.11.1 from 2015. I can't find a 64 bit conda sqlite.exe version of that later vintage to try and I'm not equipped to build one.

I was sort of hoping someone who knows more about might duplicate my results and verify there is a problem with the 64 bit 4.3.0a libs and current sqlite.exe versions.

32 bit:

C:\Users\witr\Documents\Python Scripts\geo\mod_spatialite-4.3.0a-win-x86>sqlite3_33.exe
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select st_distance();
Error: no such function: st_distance
sqlite> .load mod_spatialite
sqlite> select st_distance();
Error: wrong number of arguments to function st_distance()

64 bit:

C:\Users\witr\Documents\Python Scripts\geo\mod_spatialite-4.3.0a-win-amd64>sqlite3_33.exe
SQLite version 3.33.0 2020-08-14 13:23:32
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> select st_distance();
Error: no such function: st_distance
sqlite> .load mod_spatialite

Error: A dynamic link library (DLL) initialization routine failed.

[David Anderson]

I am also have problems loading in the latest version of Anaconda 3.  2020-07.  All of the DLLs and paths are configured correctly as I can load it with the latest version of the WinPython distribution as well as DB-Browser for SQLite.

[br...@frogmouth.net]

What debugging have you done? Are you trying to load something you built yourself or something out of an updated feedstock?

 If it works with a rebuilt version then I see no reason why 5.0 shouldn't be released. If some distributors need to build with particular compilers or dependences, that isn't an upstream responsibility. 

--

You received this message because you are subscribed to the Google Groups "SpatiaLite Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to spatialite-use...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/spatialite-users/85d432ae-cedd-4414-a19d-aae1b69b12ffn%40googlegroups.com.

[Paulo Meira]

Hi, all,
I posted my observations on the original link at https://github.com/ContinuumIO/anaconda-issues/issues/10926#issuecomment-678581014
and on a related conda-forge issue at https://github.com/conda-forge/libspatialite-feedstock/issues/49#issuecomment-678592688

I guess I should post some notes here too:

1. The old Spatialite 4.3.0a DLLs fail with "A dynamic link library (DLL) initialization routine failed." -- I believe there's a conflict of the old GCC DLLs and the modern Visual Studio runtimes. Attaching the Visual Studio debugger to the process (e.g. sqlite3.exe from Anaconda) and running "select load_extension('mod_spatialite.dll')" with the old 4.3.0a DLLs in the executable PATH, I could see a lot of access violation exceptions like:

        Exception thrown at 0x000000006A5DFE90 (libstdc++_64-6.dll) in sqlite3.exe: 0xC0000005: Access violation reading location 0xFFFFFFFFDEF16FF7.

   I've been running Spatialite with custom build scripts for Visual Studio for several years without issues (4.3.0a + check-picked patches from the the Fossil repo), recently using Anaconda too.

2. The official DLLs for Spatialite 5.0.0-RC1 at http://www.gaia-gis.it/gaia-sins/windows-bin-NEXTGEN-amd64/spatialite-loadable-modules-NG-RC1-win-amd64.7z *do work* for me -- remember they need to be in a folder listed in the PATH environment variable after activating the environment. I haven't done extensive testing but they load fine (both Anaconda's sqlite3.exe and Python sqlite3 module), and I'm able to open and manipulate previous DB files. I'm certain the correct DLLs were loaded since "select spatialite_version();" returns '5.0.0-RC1' and I inspected the process to confirm which DLLs were open.

3. On Windows, both the official Anaconda and the conda-forge packages for Spatialite don't contain mod_spatialite. This is the root cause of issues for many users. That's, of course, not a responsibility of the upstream Spatialite project. Anaconda is not a typical Python distribution; it uses the conda package manager and related building tools as the preferred way to build/install everything, not only Python modules (the DLLs end up in "$ENVIRONMENT_ROOT/Library/bin"). As such, the preferred way to use Spatialite on Anaconda would be to fix/update the distributed package there.

For completeness, here's the result of "conda list" for my minimal test environment ("conda create -p c:\test\spatialite_test python") -- there's nothing special about it:

    # packages in environment at c:\test\spatialite_test:
    #
    # Name                    Version                   Build  Channel
    ca-certificates           2020.6.24                     0
    certifi                   2020.6.20                py38_0
    openssl                   1.1.1g               he774522_1
    pip                       20.2.2                   py38_0
    python                    3.8.5                he1778fa_0
    setuptools                49.6.0                   py38_0
    sqlite                    3.33.0               h2a8f88b_0
    vc                        14.1                 h0510ff6_4
    vs2015_runtime            14.16.27012          hf0eaf9b_3
    wheel                     0.34.2                   py38_0
    wincertstore              0.2                      py38_0
    zlib                      1.2.11               h62dcd97_4


Since this is my first post here, I'd like to take the opportunity to thank Sandro for Spatialite :)

Regards,

Paulo Meira

---

[Robert Withrow]

From my dumb user point of view, I tested the 5.0 RC 64 bit libraries with the sqlite3 cli version 33 as distributed by conda and they work correctly enough based on my minimal tests.  So I've duplicated Paulo's results. Thanks Paulo!

I can make some suggestions regarding spatialite versus downstreams:

1. It's the downstream's responsibility to build things right, or course, and Paulo has contributed tons of good info on the Anaconda site regarding that. I assume they (Anaconda) will eventually work on that.
2. As part of  the pre-release process it would be good to test that the 64 bit libraries work with a generically built sqlite3 image (like the one in Anaconda). I don't understand why the sqlite3 folks don't provide a 64 bit version of the CLI or I would suggest using that one. The reason I suggest this is because unsophisticated users, like me, will likely grab the binaries from Gaia-SINS page and try to use them with the sqlite3 image we get elsewhere (lacking an existing working package for the Python we are using).
3. It would be awesome if the spatialite folks could address the 64 bit problem with the 4.3.0a binaries on Gaia-SINS, because that will become the "previously stable version" that people might revert to. By address, I suggest it could be documenting the problem, or maybe even rebuild them to avoid the segfault.

Anyway, thanks for all the help folks!

[Paulo Meira]

A quick update on trying to load the 4.3.0a binaries with recent Pythons:

• Anaconda's Python 2.7, which uses Visual Studio 2008, is able to load mod_spatialite v4.3.0a.
• To test the GCC vs. MSVC runtime conflict hypothesis, I replaced a couple of DLLs (libgcc_s_seh-1.dll, libstdc++_64-6.dll) from http://www.gaia-gis.it/gaia-sins/windows-bin-amd64/mod_spatialite-4.3.0a-win-amd64.7z with MSYS2/Mingw-w64's GCC 9.1.0 versions I had around. Doing that, I was able to load the mod_spatialite v4.3.0. With that, the basics work fine (loading the extension, simple queries). Of course I don't recommend doing this in production since there could be some incompatibilities due to potential API/ABI breakages.
  
• I'd conclude that just recompiling with newer GCCs would be enough to use mod_spatialite v4.3.0a with Pythons built with modern Visual Studio runtimes. You aren't required to build using Visual Studio.

[a.fu...@lqt.it]

just few final considerations about dynamically loading mod_spatialite

1. mod_spatialite isn't an elementary simple object, it depends on
several other libraries that need to be loaded in turn.
the list includes PROJ, GEOS, RTTOPO (if Topology is enabled),
XML2, ICONV and CURL, just to cite the most relevant ones.

2. a failure in loading any one of them will cause a general
failure thus forbidding to load mod_spatialite itself.

3. last but not least, all these modules require to be
supported by some specific version of the C or C++
runtime. it strictly depends on the compiler make
and version used in order to build each module.
just to be more precise, all Windows DLLs distributed
by SpatiaLite itself are built using MinGW, a port
for Windows of the well known GCC compiler almost
universally adopted on Linux/Unix.
MinGW has a remarkably high compatibility with the
Microsoft native runtime implementations available
on Windows, but sometimes some ill-fated combinations
couldn't work, most easily when mixing at random
modules built in different years thus being based
on strongly different versions of runtime.

4. the final complexity touch: the search rules adopted
by Windows for dynamically loading external modules
are really chaotic.
and to make matters worst, the error messages returned
by this Operating System in the case of failure simply
is a meaningless "Unable to load" completely lacking
any other useful information about the real cause
forbidding the operation.
note that recent changes introduced by Win 8 and 10
have made this unpleasant situation even worst of
what it was on XP and 7.


short conclusions:
==================

A. dynamically loading mod_spatialite on Windows is quite
easy and simple if you are using all component of the
same age/version built by the same compiler.
this is the classic case of loading mod_spatialite
on the top of sqlite3.exe using the binaries shipped
into the SpatiaLite own distribution.
it's a stable and robust configuration, and is expected
to correctly work everywhere.

B. unhappily, this _IS NOT_ the case of loading mod_spatialite
on the top of Python, Java and so on.
all them are highly complex frameworks using their own
libraries and runtime support; many unexpected conflicts
and compatibility issues may easily arise.

C. the robust and safe solution to this problem is rather
obvious; you should always build a specific make of
mod_spatialite carefully checking to use the appropriate
compiler and libraries (possibly, the same used for
building the whole Python or Java stack).
this is not a task you can expect to be fulfilled by
SpatiaLite itself, because there are so many different
flavors and versions of these popular languages that
supporting all them would require an extraordinay effort
well beyond our practical possibilities.
however, it seems reasonable asking the maintainers of
your specific distro (as e.g. Anaconda) to directly
support their own version of mod_spatialite (as it
usually happens in the most popular Linux distros).

D. as practical experience in the field shows, a clever
hacking could actually solve many apparently desperate
situations.
unhappily such a solution surely requires a thorough
comprehension about the internal mechanisms of the
Operating System, which the average user usually lacks.

E. final hint.
learn to use the nice Depenndency Walker in order to
debug this kind of Windows issues.
it's the unique tool capable to shed some light on
such issues, and it's not to much complex to be used.

https://www.dependencywalker.com/

bye Sandro

[Paulo Meira]

To complement Sandro, I recommend trying the alternative called Dependencies available on GitHub if you find any issues with the old Dependency Walker:

https://github.com/lucasg/Dependencies/

      "Dependencies is a rewrite of the legacy software Dependency Walker which was shipped along Windows SDKs, but whose development stopped around 2006. Dependencies can help Windows developers troubleshooting their dll load dependencies issues."

And if you ever need more details besides which DLL failed to load, try the debugging procedure through Visual Studio. You don't need any code or even debug symbols, just start or attach to a process which tries to load the DLL. Remember to enable stopping on all exceptions (especially Win32 exceptions) for more info besides the generic error messages.