C# ,System.Data.SQLite, KNN query Problem

[MHH Rise]

Hello everyone

I have a console C# app application using spatialite extension via System.Data.SQLite.

It works fine but when i send bellow query the program exits (In debug mode) without throwing  any exception:

SELECT b.idd FROM knn as a Join ilines as b on (b.rowid=a.fid) WHERE f_table_name = 'ilines' AND ref_geometry = MakePoint(539422.0517,3938828.1452);

The SQL query works fine in GUI with replica of same DB.

best regards.

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

On Mon, 24 May 2021 00:15:03 -0700 (PDT), MHH Rise wrote:
> Hello everyone
>
> I have a console C# app application using spatialite extension via
> System.Data.SQLite.
> It works fine but when i send bellow query the program exits (In
> debug
> mode) without throwing any exception:
>

> SELECT B.IDD FROM KNN AS A JOIN ILINES AS B ON (B.ROWID=A.FID) WHERE
> F_TABLE_NAME = 'ILINES' AND REF_GEOMETRY =
> MAKEPOINT(539422.0517,3938828.1452);

> The SQL query works fine in GUI with replica of same DB.
>

Hi,

I think you are experiencing the same situation discussed in this
post about Python:

https://groups.google.com/g/spatialite-users/c/kDMHj1S5Glk

very short summary: your System.Data.SQLite configuration
seems to be based on two different and not fully compatible
versions of libspatialite, one used in order to load the
dynamic extension and the other directly referenced by
mod_spatialite.

the most probable cause seems to be loading a binary
mod_spatialite compiled by MinGW whilst System.Data.SQLite
depends on its own libspatialite (presumably compiled
by Microsoft Visual C, and possibly not supporting the
RTree extension).

bye, Sandro

[MHH Rise]

Thank you for your reply.

I download  the package of dlls from spatialite website

This file: mod_spatialite-5.0.1-win-x86.7z

And used System.Data.SQLite.dll from it as reference in VS 2019 and loading mod_spatialite in runtime.

where is my problem?

Best regards

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

On Tue, 25 May 2021 20:38:32 -0700 (PDT), MHH Rise wrote:
> Thank you for your reply.
> I download the package of dlls from spatialite website
> This file: mod_spatialite-5.0.1-win-x86.7z
> And used System.Data.SQLite.dll from it as reference in VS 2019 and
> loading mod_spatialite in runtime.
> where is my problem?
>

Hi,

you are blindly mixing different copies of sqlite.dll in the same
operational framework, the first one coming from System.Data and
the second one from the mod_spatialite-win-86 binary package.

not only the two DLLs have been built using different compilers
(MSVC vs MinGW) but they probably are of different versions and
possibly different options were configured at build time.

this is a well known cause of problems and unstabilities, and
is commonly found on many complex frameworks as Java, Python,
PHP and obviously System.Data as well.

the real and effective solution should be directly compiling
mod_spatialite on behalf of your framework so to robustly
ensure that the same copy of libsqlite.dll will be referenced
in any case.

the binary distributions of mod_spatialite for Windows supported
by SpatiaLite itself are always assumed to be loaded at run
time by the same identical copy of libsqlite.dll contained
into the binary packages, that is the one targeted at build
time.
counterproof: as yourself confirm anything runs smoothly
when using the GUI tool.

if this prerequisite is not ensured (as it frequently happens
on Java, Python etc frameworks) you should always expect some
kind of malfunction and unstability, and the KNN module is
the first candidate for being a cause of troubles.

bye Sandro

[Duncan]

Hi Sandro

I did some tests with this issue, but the results were a little confusing so I wonder if you could clarify.

Test 1 

A KNN query adapted from the cookbook to my own data which worked fine in the spatialite_gui

Test 2

A similar setup to the OPs, using System.Data.SQLite 1.0.114.0 & mod_spatialitex64

Load_extension - OK

spatialite_version() - 5.0.1

spatial query using RTREE - OK

KNN query as per Test 1 - Exception Attempting to read or write protected memory

Test 3

Using sqlite-net SQLite wrapper, SQLite3.dll (x64) downloaded from sqlite.org & mod_spatialitex64

Results as per Test 2

Test 4

Using sqlite-net SQLite wrapper  & mod_spatialitex64. 

This time directing sqlite-net to use libsqlite3-0.dll provided with mod_spatialite

Load_extension  - Exception Attempting to read or write protected memory

I guess my question would be how to get a suitable libsqlite to work correctly?

Should libsqlite3-0.dll be used like this?

Thanks

Duncan 

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

the discussion about this topic will continue on this post:

https://groups.google.com/g/spatialite-users/c/m0SF7IzVc_Y

bye Sandro