On Tue, 2 Aug 2022 17:33:04 -0700 (PDT), Jason Williams wrote:
> Thank you for the ideas! Got it! I have to add "and"...
> BTW, I am using spatialite5, there is no f_table_name in my index,
> I do something wrong?
if you notice, you are querying the SpatialIndex table.
this one is a very special table, that actually is a VIRTUAL TABLE,
that is kind of a driver simulating to be a table but being in
reality just a sw component.
said in other words: there is no table SpatialIndex at all
into the database, it's just a symbolic interface that you
can query using standard SQL syntax.
SpatialIndex virtually defines the following columns:
1. f_table_name [input]
name of the main Geometry table
2. f_column_name [input]
name of the Geometry column supported by a corresponding
it can be omitted if the main Geometry table defines
just a single Geometry (will be automatically detected).
3. search_frame [input]
a Geometry acting as a Spatial Filter
4. rowid [output[
the unique IDs of the rows within f_table_name satisfying
the given Spatial Filter.
all together now:
WHERE f_table_name = 'mytable' AND
search_frame = a.geometry
explained in a verbose form this practically means:
"please SpatialIndex interface, search for a table named
mytable that is expected to be supported by a corresponding
R*Tree. Query this Spatial Index using a.geometry as a
Spatial Filter and finally return back to the caller a list
of all ROWIDs satisfying this filter."
a short rationale:
SQLite supports a very effective form of Spatial Index
represented by R*Trees, but differently from any other
SpatialDBMS they are not at all an "index" because
they actually are separate tables with no explicit
relation with their parent table.
SpatiaLite on its own adds the "syntactic sugar" of
virtual SpatialIndex which allows for an easier
access to spatial indexing completely masking all
low level details of the R*Trees implementation.