I'm writing a tcl script that uses spatialite. There's a file of
coordinates and an sqlite database of polygons that represent land
parcels. Which parcel is the point in? Note that I'm not a novice at
this, it's just the latest software is not working on windows. I have
bawt which provides the sqlite3 package and spatialite comes with a qgis install.
Here's a query using sqlite3 without tcl that works fine on windows and
linux using a spatial index

select load_extension('mod_spatialite');
.version
select spatialite_version();
select lotplan,segpar from lotslite as d,
(select makepoint(492842.0,6981883.0,28356) as point) as p
where within(p.point, d.shape) and d.rowid in
(select rowid from spatialindex
where f_table_name='lotslite'
and search_frame=p.point);

output is
SQLite 3.41.0 2023-02-21 18:09:37 .....
zlib version 1.2.13
gcc-12.2.1 20230201
5.0.1
9RP175453|62902002

Here's the tcl script with both the indexed and unindexed query. This
works fine on linux but wanders off into the weeds on the spatialindex
query.

package require sqlite3
sqlite3 db lots.sqlite
db enable_load_extension true
db eval {SELECT load_extension('mod_spatialite')}
puts [db version]
puts [db eval {select spatialite_version()}]
set x 492842
set y 6981883

puts [db eval {
select lotplan,segpar from lotslite as d,
(select makepoint(casttodouble(:x),casttodouble(:y),28356)
as point) as p
where within(p.point, d.shape)
}]

puts [db eval {
select lotplan,segpar from lotslite as d,
(select makepoint(casttodouble(:x),casttodouble(:y),28356)

as point) as p
where within(p.point, d.shape) and d.rowid in
(select rowid from spatialindex
where f_table_name='lotslite'
and search_frame=p.point)}]

outputs on linux
3.41.0
5.0.1
9RP175453 62902002
9RP175453 62902002

but on windows I only get

3.41.0
5.0.1
9RP175453 62902002

I've tried this also with a tclkit that comes with bawt and also a kit
from Ashok. What to blame? It's looking like the sqlite which comes
with bawt. Is there somewhere else I can get a compiled sqlite package
for windows. Ultimately this will be wrapped up as a starpack to give
to a client so the sqlite package needs to be compatible with whatever kit.

TIA for any tips
Peter

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 1/03/2023 4:20 pm, Peter Dean wrote:

I'm writing a tcl script that uses spatialite.  There's a file of coordinates and an sqlite database of polygons that represent land
parcels. Which parcel is the point in?  Note that I'm not a novice at
this, it's just the latest software is not working on windows. I have
bawt which provides the sqlite3 package and spatialite comes with a qgis install.
Here's a query using sqlite3 without tcl that works fine on windows and
linux using a spatial index

select load_extension('mod_spatialite');
.version
select spatialite_version();
select lotplan,segpar from lotslite as d,
    (select makepoint(492842.0,6981883.0,28356) as point) as p
        where within(p.point, d.shape) and d.rowid in
          (select rowid from spatialindex
             where f_table_name='lotslite'
                and search_frame=p.point);

output is
SQLite 3.41.0 2023-02-21 18:09:37 .....
zlib version 1.2.13
gcc-12.2.1 20230201
5.0.1
9RP175453|62902002

Here's the tcl script with both the indexed and unindexed query.  This
works fine on linux but wanders off into the weeds on the spatialindex
query.

package require sqlite3
sqlite3 db lots.sqlite
db enable_load_extension true
db eval {SELECT load_extension('mod_spatialite')}
puts [db version]
puts [db eval {select spatialite_version()}]
set x 492842
set y 6981883

puts [db eval {
select lotplan,segpar from lotslite as d,
    (select makepoint(casttodouble(:x),casttodouble(:y),28356)
        as point) as p
        where within(p.point, d.shape)
}]

puts [db eval {
        select lotplan,segpar from lotslite as d,
            (select makepoint(casttodouble(:x),casttodouble(:y),28356)
               as point) as p
                where within(p.point, d.shape) and d.rowid in
                    (select rowid from spatialindex
                         where f_table_name='lotslite'
                            and search_frame=p.point)}]

outputs on linux
3.41.0
5.0.1
9RP175453 62902002
9RP175453 62902002

but on windows I only get

3.41.0
5.0.1
9RP175453 62902002

I've tried this also with a tclkit that comes with bawt and also a kit
from Ashok.  What to blame?  It's looking like the sqlite which comes
with bawt.  Is there somewhere else I can get a compiled sqlite package
for windows.  Ultimately this will be wrapped up as a starpack to give
to a client so the sqlite package needs to be compatible with whatever kit.

TIA for any tips
Peter

correction, it's actually 3.40.0 on windows. Could that be it?

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 1/03/2023 5:22 pm, Christian Gollwitzer wrote:

Am 01.03.23 um 07:29 schrieb Peter Dean:

outputs on linux
3.41.0
5.0.1
9RP175453 62902002
9RP175453 62902002

but on windows I only get

3.41.0
5.0.1
9RP175453 62902002

I've tried this also with a tclkit that comes with bawt and also a
kit from Ashok.  What to blame?  It's looking like the sqlite which
comes with bawt.  Is there somewhere else I can get a compiled sqlite
package for windows.  Ultimately this will be wrapped up as a
starpack to give to a client so the sqlite package needs to be
compatible with whatever kit.

correction, it's actually 3.40.0 on windows. Could that be it?

Most likely. I can't believe it comes from the Tcl driving it, so it
must be a pure SQLite problem. 3.40.0 is bundled with Tcl 8.6.13. 3.41.0 appeared on 2023-02-24 (one week ago!), so probably your linux distro is
one of those with instant rolling release.

Can't you compile it on your own?

             Christian

I'd try but I'm only comfortable with msys2 and I don't believe that's supported.
Yes I'm on arch linux that's why I like msys2. Pacman.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am 01.03.23 um 07:29 schrieb Peter Dean:

outputs on linux
3.41.0
5.0.1
9RP175453 62902002
9RP175453 62902002

but on windows I only get

3.41.0
5.0.1
9RP175453 62902002

I've tried this also with a tclkit that comes with bawt and also a kit
from Ashok.  What to blame?  It's looking like the sqlite which comes
with bawt.  Is there somewhere else I can get a compiled sqlite
package for windows.  Ultimately this will be wrapped up as a starpack
to give to a client so the sqlite package needs to be compatible with
whatever kit.

correction, it's actually 3.40.0 on windows. Could that be it?

Most likely. I can't believe it comes from the Tcl driving it, so it
must be a pure SQLite problem. 3.40.0 is bundled with Tcl 8.6.13. 3.41.0 appeared on 2023-02-24 (one week ago!), so probably your linux distro is
one of those with instant rolling release.

Can't you compile it on your own?

Christian

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 1/03/2023 5:22 pm, Christian Gollwitzer wrote:

Am 01.03.23 um 07:29 schrieb Peter Dean:

outputs on linux
3.41.0
5.0.1
9RP175453 62902002
9RP175453 62902002

but on windows I only get

3.41.0
5.0.1
9RP175453 62902002

I've tried this also with a tclkit that comes with bawt and also a
kit from Ashok.  What to blame?  It's looking like the sqlite which
comes with bawt.  Is there somewhere else I can get a compiled sqlite
package for windows.  Ultimately this will be wrapped up as a
starpack to give to a client so the sqlite package needs to be
compatible with whatever kit.

correction, it's actually 3.40.0 on windows. Could that be it?

Most likely. I can't believe it comes from the Tcl driving it, so it
must be a pure SQLite problem. 3.40.0 is bundled with Tcl 8.6.13. 3.41.0 appeared on 2023-02-24 (one week ago!), so probably your linux distro is
one of those with instant rolling release.

Can't you compile it on your own?

             Christian

Thanks Christian, it was quite straight forward to compile 3.41.0 on
windows and it solved the problem. 3.40.0 is buggy on this particular
problem.

Peter

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Am 01.03.23 um 09:28 schrieb Peter Dean:

On 1/03/2023 5:22 pm, Christian Gollwitzer wrote:

Most likely. I can't believe it comes from the Tcl driving it, so it
must be a pure SQLite problem. 3.40.0 is bundled with Tcl 8.6.13.
3.41.0 appeared on 2023-02-24 (one week ago!), so probably your linux
distro is one of those with instant rolling release.

Can't you compile it on your own?

              Christian

Thanks Christian, it was quite straight forward to compile 3.41.0 on
windows and it solved the problem. 3.40.0 is buggy on this particular problem.

NP, you actually solved the problem yourself ;) Anyway, keep in mind,
when you compile something on Windows using gcc, you need to include
these flags in your linker line:

-static-libgcc

otherwise, the result will depend on the libgcc_s library and not run on computers, where gcc is not installed. I don't know if the sqlite
Makefiles do it on their own, if not, simply pass this as a "LIBS" or
"LDFLAGS" to configure. Probably easiest to find out by trying the
library on another Windows machine; maybe also "ldd" from the msys2
prompt could work, and if it shows any library inside your msys2
install, then it won't work outside of it.

Christian

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 2/03/2023 2:23 am, Christian Gollwitzer wrote:

Am 01.03.23 um 09:28 schrieb Peter Dean:

On 1/03/2023 5:22 pm, Christian Gollwitzer wrote:

Most likely. I can't believe it comes from the Tcl driving it, so it
must be a pure SQLite problem. 3.40.0 is bundled with Tcl 8.6.13.
3.41.0 appeared on 2023-02-24 (one week ago!), so probably your linux
distro is one of those with instant rolling release.

Can't you compile it on your own?

              Christian

Thanks Christian, it was quite straight forward to compile 3.41.0 on
windows and it solved the problem. 3.40.0 is buggy on this particular
problem.

NP, you actually solved the problem yourself ;) Anyway, keep in mind,
when you compile something on Windows using gcc, you need to include
these flags in your linker line:

-static-libgcc

otherwise, the result will depend on the libgcc_s library and not run on computers, where gcc is not installed. I don't know if the sqlite
Makefiles do it on their own, if not, simply pass this as a "LIBS" or "LDFLAGS" to configure. Probably easiest to find out by trying the
library on another Windows machine; maybe also "ldd" from the msys2
prompt could work, and if it shows any library inside your msys2
install, then it won't work outside of it.

    Christian

I will store that info away for future reference. In the long distant
past I remember having to include libgcc with the installer. There's a
lot of dlls that spatialite depends on that I have to include.

I ran a dependency walker on the sqlite3410.dll. It only needs 2 built
in windows dlls, kernel32 and msvcrt.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)