ATTACH and spatialite_gui question

[Jim]

I ran the following query on the database 'us_uk_ca_shapes.db' and got the expected result:   (Note: the database and table do have the same name).

SELECT name
FROM us_uk_ca_shapes
WHERE rowid IN (SELECT ROWID FROM SpatialIndex
   WHERE f_table_name = 'us_uk_ca_shapes'
         AND search_frame = BuildCircleMBR(-122.365680,47.763610, .00001));

Then I started over and connected to database "CAETravel.db'.

I then attached the database 'us_uk_ca_shapes.db'  (Using either the attach menu function or by an ATTACH statement and ran this query:

SELECT name
FROM a.us_uk_ca_shapes
WHERE rowid IN (SELECT ROWID FROM SpatialIndex
   WHERE f_table_name = 'a.us_uk_ca_shapes'
         AND search_frame = BuildCircleMBR(-122.365680,47.763610, .00001));

And got no result, just an empty result set.

I've tried several variations of this where I qualified 'name' and 'rowid' but got 'no such column' errors as expected.

Is this not possible or have I missed something?

Regards,

Jim

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

On Mon, 8 Apr 2024 20:28:59 -0700 (PDT), Jim wrote:
> Then I started over and connected to database "CAETravel.db'.
> I then attached the database 'us_uk_ca_shapes.db'  (Using either the
> attach menu function or by an ATTACH statement and ran this query:
>

> ---------------

>
> And got no result, just an empty result set.
>
> I've tried several variations of this where I qualified 'name' and
> 'rowid' but got 'no such column' errors as expected.
>
> Is this not possible or have I missed something?
>

Hi Jim,

yes, you've missed something ;-)

querying a Spatial Index located on some ATTACHED DB requires
a special syntax: the "f_table_name" argument must explicitly
reference not only the mother table table name but also the
DB prefix where this table is stored.

your syntax is wrong:
... WHERE f_table_name = 'a.us_uk_ca_shapes' ...

the correct systax is:
... WHERE f_table_name = 'DB=a.us_uk_ca_shapes' ...

you'll find more usefull informations here:

https://www.gaia-gis.it/fossil/libspatialite/wiki?name=SpatialIndex

bye Sandro