spatialindex table is empty and MATCH RtreeIntersects not available

[antonio valanzano]

I am using spatialite GUI 1.8 and i have created 2 spatial indexes  using the GUI menu entry "Build spatial index".
When I try to query the Spatialindex table
with the simple query SELECT ROWID  FROM "SpatialIndex"
the result is an empty ResultSet and therefore I cannot use spatial indexes for my query.

If I try to use the old syntax ... MATCH Rtreeintersects (..)  the gui returns a message "no such function RTreeIntersects"

Is there something wrong with the way I have created the indexes with the GUI ?


Antonio Valanzano

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

On Thu, 5 Mar 2015 14:55:11 -0800 (PST), antonio valanzano wrote:
> I am using spatialite GUI 1.8 and i have created 2 spatial indexes
> using the GUI menu entry "Build spatial index".

Ciao Antonio,

> When I try to query the Spatialindex table
> with the simple query SELECT ROWID FROM "SpatialIndex"
> the result is an empty ResultSet
>

certainly yes; this is the expected normal behavior

> and therefore I cannot use spatial indexes for my query.
>

this is a completely wrong assumption ;-)

rationale: SpatialIndex isn't an ordinary table, it's a VIRTUAL table.
i.e. it's a driver implementing a complex internal logic.
you can query a VirtualTable as any other table, and the returned
resultset will always be an ordinary SQL resultset.

anyway a VirtualTable by itself does not physically contain any row
at all; it will create on-the-fly the rows to be returned depending
on the corresponding WHERE clause.
in order to effectively get some resultset you should test an SQL
query like this one:

SELECT rowid FROM SpatialIndex
WHERE f_table_name = 'some_table' AND search_frame = geom;

any VirtualSpatialIndex query omitting to specify a valid
WHERE f_table_name = ... AND search_frame = ...
is always expected to return an empty resultset.

> If I try to use the old syntax ... MATCH Rtreeintersects (..) the gui
> returns a message "no such function RTreeIntersects"
>

yes, because in recent versions MATCH RTreeIntersects is deprecated
and is no longer enabled; you are expected to use SpatialIndex as
a full replacement.

bye Sandro