Spatialite & KNN in Python

59 views
Skip to first unread message

Pedro Camargo

unread,
Mar 23, 2021, 6:49:48 PMMar 23
to Spatialite Users
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

unread,
Mar 24, 2021, 3:16:08 AMMar 24
to spatiali...@googlegroups.com
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

unread,
Mar 24, 2021, 8:40:15 PMMar 24
to spatialite-users, afurieri
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.
>
Reply all
Reply to author
Forward
0 new messages