Documentation Source Text

<p>However, for a "contained-within" style query, rounding the bounding
boxes outward might cause some entries to be excluded from the result set
if the edge of the entry bounding box corresponds to the edge of the query
bounding box.  To guard against this, applications should expand their
contained-within query boxes slightly (by 0.000012%) by rounding down the
lower coordinates and rounding up the top coordinates, in each dimension.
































































<h1>Using R*Trees Effectively</h1>

<p>
For SQLite versions prior to 3.24.0 ([dateof:3.24.0]),
the only information that an R*Tree index stores about an object is
its integer ID and its bounding box.  Additional information needs to

<p>However, for a "contained-within" style query, rounding the bounding
boxes outward might cause some entries to be excluded from the result set
if the edge of the entry bounding box corresponds to the edge of the query
bounding box.  To guard against this, applications should expand their
contained-within query boxes slightly (by 0.000012%) by rounding down the
lower coordinates and rounding up the top coordinates, in each dimension.

<tcl>hd_fragment readwrite {reading and writing an rtree at the same time}</tcl>
<h2>Reading And Writing At The Same Time</h2>

<p>
It is the nature of the Guttman R-Tree algorithm that any write might
radically restructure the tree, and in the process change the scan order
of the nodes.  For this reason, it is not generally possible to modify
the R-Tree in the middle of a query of the R-Tree.  Attempts to do so
will fail with a [SQLITE_LOCKED] "database table is locked" error.

<p>
So, for example, suppose an application runs one query against an R-Tree like
this:

<codeblock>
SELECT id FROM demo_index
 WHERE maxY&gt;=35.0  AND minY&lt;=35.0;
</codeblock>

<p>
Then for each "id" value returned, suppose the application creates an 
UPDATE statement like the following and binds the "id" value returned against
the "?1" parameter:

<codeblock>
UPDATE demo_index SET maxY=maxY+0.5 WHERE id=?1;
</codeblock>

<p>
Then the UPDATE might fail with an SQLITE_LOCKED error.  The reason is that
the initial query has not run to completion.  It is remembering its place
in the middle of a scan of the R-Tree.  So an update to the R-Tree cannot
be tolerated as this would disrupt the scan.

<p>
It is also possible to express this kind of simultaneous read and write
on an R-Tree within a single query, for example if an UPDATE statement
tries to change the value of one row of the R-Tree based on a complicated
query from another row of the same R-Tree, perhaps something like this:

<codeblock>
UPDATE demo_index 
   SET maxY = (SELECT max(maxX) FROM demo_index AS x2
                WHERE x2.maxY&gt;demo_index.x2)
 WHERE maxY&gt;=35.0  AND minY&lt;=35.0;
</codeblock>

<p>
This is a limitation of the R-Tree extension only.  Ordinary tables in
SQLite are able to read and write at the same time.  Other virtual tables
might (or might not) also that capability.  And R-Tree can appear to read
and write at the same time in some circumstances, if it can figure out how
to reliably run the query to completion before starting the update.  But
you shouldn't count on that for every query.  Generally speaking, it is
best to avoid running queries and updates to the same R-Tree at the same
time.

<p>
If you really need to update an R-Tree based on complex queries against
the same R-Tree, it is best to run the complex queries first and store
the results in a temporary table, then update the R-Tree based on the values
stored in the temporary table.

<h1>Using R*Trees Effectively</h1>

<p>
For SQLite versions prior to 3.24.0 ([dateof:3.24.0]),
the only information that an R*Tree index stores about an object is
its integer ID and its bounding box.  Additional information needs to