Spatialite & KNN in Python

[Pedro Camargo]

Hello!

A bit of background:

I (and 99% of my team) work on Windows.

I have been working with Spatialite for a little while with quite a bit of Success (Thanks, Sandro), but now that QGIS has incorporated Spatialite 5, it is time to incorporate KNN into my work, which consists of manipulating spatial databases in QGIS, Python & Sqlite Browser with mod_spatialite loaded.

The issue:

The setup usually goes as I describe in a blog post a while back (Spatialite and Python in 2020 – Xl-Optim), and it was working flawlessly until now. However, my KNN triggers are only working inside QGIS, while crashing Python and the sqlite browser silently (when I managed to get into the debugger it seemed to point to the sqlite3 DLL)

I have tried using the DLLs shipped with QGIS in a desperate attempt to make things work, but no luck.

I saw this discussion on the list (NEXTGEN RC1 - loadable modules for Windows (google.com), but I am not sure how/if it relates to my problem and what I need to do to fix it.

Does anybody have any pointers on this?

Example:

An example of the type of trigger I am using

create trigger if not exists transit_stops_enforces_zone_field after update of "zone" on Transit_Stops

begin

    update Transit_Stops

        set zone = (select zone from Zone

                        where Zone.ROWID=(

                            select k.fid from knn k

                                WHERE f_table_name = 'zone'

                                AND ref_geometry = new.geo

                                AND max_items = 1));

end;

Thanks,

Pedro

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

On Wed, 24 Mar 2021 08:49:40 +1000, Pedro Camargo wrote:
> The setup usually goes as I describe in a blog post a while back
> (Spatialite and Python in 2020 – Xl-Optim), and it was working
> flawlessly until now. However, my KNN triggers are only working
> inside
> QGIS, while crashing Python and the sqlite browser silently (when I
> managed to get into the debugger it seemed to point to the sqlite3
> DLL)
>

Hi Pedro,

KNN is affected by what seems to be a flaw in the SQLite architecture.

just a little technical insight: the loadable module mod_spatialite
will usually receive a copy of "mirror APIs" directly pointing to
the corresponding APIs internally provvided by the calling library
libsqlite3.

KNN is fully based on sqlite3_rtree_query_callback() [1], a very
advanced API allowing to directly inspect the R*Tree, but unhappily
this one is one of the very few SQLite's API not reflected by the
"mirror APIs" supplied to loadable modules.
So, in order to support KNN in mod_spatialite we are necessarily
forced to explicitly link libsqlite3.

[1] https://sqlite.org/rtree.html#customquery

My personal interpretation: until the binary code of mod_spatialite
points to the same identical copy of sqlite3.dll anything runs smoothly
(as it seems confirmed in your all QGIS environment).

but when the "mirror APIs" and the direct link to
sqlite3_rtree_query_callback()
point to two different DLLs (possibly of different versions, or
built by different compilers may be using different C runtimes)
some spectacular crash is almost certainly ensured.
(as it happens in your standalone Python environment not based
on QGIS itself)

short conclusion: mixing mod_spatialite.dll with a sqlite3.dll
someway different from the original one used at build time will
very probably be a cause of severe instability.
said in different words: when you absolutely need to use
mod_spatialite in different Python environments you should
always be very carefull in ensuring to load a copy of
mod_spatialite specifically built for that environment
and surely referencing the same sqlite3.dll loaded by
Python.

bye Sandro

[Pedro Camargo]

Thanks for the thorough explanation, Sandro!

Cheers,
Pedro


---- On Wed, 24 Mar 2021 17:16:04 +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/cb5f4c5b549554a20b704bb2208ed9e8%40lqt.it.
>