Documentation Source Text

Check-in [7404688990]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Documentation on how to configure R*Tree for integer-only operation.
Downloads: Tarball | ZIP archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7404688990c374ba2ea7b7c03d2702bdec10f383
User & Date: drh 2012-04-03 02:04:21.826
Context
2012-04-19
15:19
Fix a typo on the download page. (check-in: 9b450ad866 user: drh tags: trunk)
2012-04-03
02:04
Documentation on how to configure R*Tree for integer-only operation. (check-in: 7404688990 user: drh tags: trunk)
2012-04-02
15:49
Updates to the atomic commit document in order to reference WAL and PSOW and to improve clarity of presentation. (check-in: aaf3ea1155 user: drh tags: trunk)
Changes
Unified Diff Ignore Whitespace Patch
Changes to pages/changes.in.
51
52
53
54
55
56
57



58
59
60
61
62
63
64
<li>Report the name of specific [CHECK] constraints that fail.
<li>In the command-line shell, use popen() instead of fopen() if the first
    character of the argument to the ".output" command is "|".
<li>Make use of OVERLAPPED in the windows [VFS] to avoid some system calls
    and thereby obtain a performance improvement.
<li>More aggressive optimization of the AND operator when one side or the
    other is always false.



<li>Bug fix: Fix the [RELEASE] command so that it does not cancel pending
    queries.  This fixes a problem introduced in 3.7.11.

}

chng {2012 March 20 (3.7.11)} {
<li>Enhance the [INSERT] syntax to allow multiple rows to be inserted







>
>
>







51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
<li>Report the name of specific [CHECK] constraints that fail.
<li>In the command-line shell, use popen() instead of fopen() if the first
    character of the argument to the ".output" command is "|".
<li>Make use of OVERLAPPED in the windows [VFS] to avoid some system calls
    and thereby obtain a performance improvement.
<li>More aggressive optimization of the AND operator when one side or the
    other is always false.
<li>Add the [SQLITE_RTREE_INT_ONLY] compile-time option to force the
    [rtree | R*Tree Extension Module] to use integer instead of
    floating point values for both storage and computation.
<li>Bug fix: Fix the [RELEASE] command so that it does not cancel pending
    queries.  This fixes a problem introduced in 3.7.11.

}

chng {2012 March 20 (3.7.11)} {
<li>Enhance the [INSERT] syntax to allow multiple rows to be inserted
Changes to pages/compile.in.
469
470
471
472
473
474
475







476
477
478
479
480
481
482
  subject to certain operating constraints.
}

COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the
  [rtree | R*Tree index extension].
}








COMPILE_OPTION {SQLITE_ENABLE_STAT2} {
  This option used to cause the [ANALYZE] command to collect
  index histogram data in the <b>sqlite_stat2</b> table.  But that
  functionality was superceded by [SQLITE_ENABLE_STAT3] as of
  SQLite version 3.7.9.  The SQLITE_ENABLE_STAT2 compile-time option
  is now a no-op.







>
>
>
>
>
>
>







469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
  subject to certain operating constraints.
}

COMPILE_OPTION {SQLITE_ENABLE_RTREE} {
  This option causes SQLite to include support for the
  [rtree | R*Tree index extension].
}

COMPILE_OPTION {SQLITE_RTREE_INT_ONLY} {
  If this option is used together with [SQLITE_ENABLE_RTREE] then the
  [rtree | R*Tree extension] will only store 32-bit signed integer
  coordinates and all internal computations will be done using integers
  instead of floating point numbers.
}

COMPILE_OPTION {SQLITE_ENABLE_STAT2} {
  This option used to cause the [ANALYZE] command to collect
  index histogram data in the <b>sqlite_stat2</b> table.  But that
  functionality was superceded by [SQLITE_ENABLE_STAT3] as of
  SQLite version 3.7.9.  The SQLITE_ENABLE_STAT2 compile-time option
  is now a no-op.
Changes to pages/rtree.in.
1
2

3
4
5
6
7
8
9
10
11
<title>The SQLite R*Tree Module</title>
<tcl>hd_keywords *rtree *RTREE {R-Tree extension} {R-Trees}</tcl>


<h1>1.0 Overview</h1>

<p>
An [http://en.wikipedia.org/wiki/R-tree | R-Tree] is a special
index that is designed for doing range queries.  R-Trees are most commonly
used in geospatial systems where each entry is a rectangle with minimum and
maximum X and Y coordinates.  Given a query rectangle, an R-Tree is able
to quickly find all entries that are contained within the query rectangle


>

|







1
2
3
4
5
6
7
8
9
10
11
12
<title>The SQLite R*Tree Module</title>
<tcl>hd_keywords *rtree *RTREE {R-Tree extension} {R-Trees}</tcl>
<h1 align="center">The SQLite R*Tree Module</h1>

<h2>1.0 Overview</h2>

<p>
An [http://en.wikipedia.org/wiki/R-tree | R-Tree] is a special
index that is designed for doing range queries.  R-Trees are most commonly
used in geospatial systems where each entry is a rectangle with minimum and
maximum X and Y coordinates.  Given a query rectangle, an R-Tree is able
to quickly find all entries that are contained within the query rectangle
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
The implementation found in SQLite is a refinement of Guttman's original
idea, commonly called "R*Trees", that was described by
Norbert Beckmann, Hans-Peter Kriegel, Ralf Schneider, Bernhard Seeger:
<em>The R*-Tree: An Efficient and Robust Access Method for Points
and Rectangles.</em> SIGMOD Conference 1990: 322-331.
</p>

<h1>2.0 Compiling The R*Tree Module</h1>

<p>
The source code to the SQLite R*Tree module is included as part
of the [amalgamation] but is disabled by default.  To enable the
R*Tree module, simply compile with the [SQLITE_ENABLE_RTREE] 
C-preprocessor macro defined.  With many compilers, this is accomplished
by adding the option "-DSQLITE_ENABLE_RTREE=1" to the compiler
command-line.
</p>

<h1>3.0 Using the R*Tree Module</h1>

<p>
The SQLite R*Tree module is implemented as a
[sqlite3_create_module | virtual table].  ^Each R*Tree index is a
virtual table with an odd number of columns between 3 and 11.
^The first column is always a 64-bit signed integer primary key.
^The other columns are minimum- and maximum-value pairs (stored as







|










|







29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
The implementation found in SQLite is a refinement of Guttman's original
idea, commonly called "R*Trees", that was described by
Norbert Beckmann, Hans-Peter Kriegel, Ralf Schneider, Bernhard Seeger:
<em>The R*-Tree: An Efficient and Robust Access Method for Points
and Rectangles.</em> SIGMOD Conference 1990: 322-331.
</p>

<h2>2.0 Compiling The R*Tree Module</h2>

<p>
The source code to the SQLite R*Tree module is included as part
of the [amalgamation] but is disabled by default.  To enable the
R*Tree module, simply compile with the [SQLITE_ENABLE_RTREE] 
C-preprocessor macro defined.  With many compilers, this is accomplished
by adding the option "-DSQLITE_ENABLE_RTREE=1" to the compiler
command-line.
</p>

<h2>3.0 Using the R*Tree Module</h2>

<p>
The SQLite R*Tree module is implemented as a
[sqlite3_create_module | virtual table].  ^Each R*Tree index is a
virtual table with an odd number of columns between 3 and 11.
^The first column is always a 64-bit signed integer primary key.
^The other columns are minimum- and maximum-value pairs (stored as
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
can store data in a variety of datatypes and formats, the R*Tree
indices rigidly enforce these two storage types.  ^Attempts to insert
something other than an integer into the first column, or something
other than a floating point value into the other columns, will result
in an error.
</p>

<h2>3.1 Creating An R*Tree Index</h2>

^(<p>
A new R*Tree index is created as follows:
</p>

<blockquote><pre>
CREATE VIRTUAL TABLE <em>&lt;name&gt;</em> USING rtree(<em>&lt;column-names&gt;</em>);







|







67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
can store data in a variety of datatypes and formats, the R*Tree
indices rigidly enforce these two storage types.  ^Attempts to insert
something other than an integer into the first column, or something
other than a floating point value into the other columns, will result
in an error.
</p>

<h3>3.1 Creating An R*Tree Index</h3>

^(<p>
A new R*Tree index is created as follows:
</p>

<blockquote><pre>
CREATE VIRTUAL TABLE <em>&lt;name&gt;</em> USING rtree(<em>&lt;column-names&gt;</em>);
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
CREATE VIRTUAL TABLE demo_index USING rtree(
   id,              -- Integer primary key
   minX, maxX,      -- Minimum and maximum X coordinate
   minY, maxY       -- Minimum and maximum Y coordinate
);
</pre></blockquote>)^

<h2>3.2 Populating An R*Tree Index</h2>

<p>
^The usual [INSERT], [UPDATE], and [DELETE] commands work on an R*Tree
index just like on regular tables.  ^(So to insert some data into our sample
R*Tree index, we can do something like this:
</p>








|







114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
CREATE VIRTUAL TABLE demo_index USING rtree(
   id,              -- Integer primary key
   minX, maxX,      -- Minimum and maximum X coordinate
   minY, maxY       -- Minimum and maximum Y coordinate
);
</pre></blockquote>)^

<h3>3.2 Populating An R*Tree Index</h3>

<p>
^The usual [INSERT], [UPDATE], and [DELETE] commands work on an R*Tree
index just like on regular tables.  ^(So to insert some data into our sample
R*Tree index, we can do something like this:
</p>

140
141
142
143
144
145
146
147
148
149
150
151
152
153
154

<p>
The entries above might represent (for example) a bounding box around
the offices for SQLite.org and bounding box around the
12th Congressional District of North Carolina in which SQLite.org is located.  
</p>

<h2>3.3 Querying An R*Tree Index</h2>

<p>
^Any valid query will work against an R*Tree index.  But the R*Tree
implementation is designed to make two kinds of queries especially
efficient.  ^(First, queries against the primary key are efficient:
</p>








|







141
142
143
144
145
146
147
148
149
150
151
152
153
154
155

<p>
The entries above might represent (for example) a bounding box around
the offices for SQLite.org and bounding box around the
12th Congressional District of North Carolina in which SQLite.org is located.  
</p>

<h3>3.3 Querying An R*Tree Index</h3>

<p>
^Any valid query will work against an R*Tree index.  But the R*Tree
implementation is designed to make two kinds of queries especially
efficient.  ^(First, queries against the primary key are efficient:
</p>

206
207
208
209
210
211
212
213
214
215
216
217
218
219
220

<p>
But, generally speaking, the more constraints that the R*Tree module
has to work with, and the smaller the bounding box, the faster the
results will come back.
</p>

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

<p>
The only information that an R*Tree index stores about an object is
its integer ID and its bounding box.  Additional information needs to
be stored in separate tables and related to the R*Tree index using
the primary key.  ^(For the example above, one might create an auxiliary
table as follows:







|







207
208
209
210
211
212
213
214
215
216
217
218
219
220
221

<p>
But, generally speaking, the more constraints that the R*Tree module
has to work with, and the smaller the bounding box, the faster the
results will come back.
</p>

<h2>4.0 Using R*Trees Effectively</h2>

<p>
The only information that an R*Tree index stores about an object is
its integer ID and its bounding box.  Additional information needs to
be stored in separate tables and related to the R*Tree index using
the primary key.  ^(For the example above, one might create an auxiliary
table as follows:
289
290
291
292
293
294
295
296






















297
298
299
300
301
302
303
304
305

<p>The problem with this latter query is that it must apply the
contained_in() function to millions of entries in the demo_data table.
The use of the R*Tree in the penultimate query reduces the number of
calls to contained_in() function to a small subset of the entire table.
The R*Tree index did not find the exact answer itself, it merely
limited the search space.</p>























<tcl>hd_fragment {customquery} {custom r-tree queries}</tcl>
<h1>5.0 Custom R-Tree Queries</h1>

<p>By using standard SQL expressions in the WHERE clause of a SELECT query,
a user may query for all r-tree entries that intersect a specified
bounding-box, or for all entries that are completely encapsulated by a
specified bounding-box. Custom r-tree queries, which use the special MATCH
operator in the WHERE clause of a SELECT, allow the user to query for the set
of r-tree entries that intersect any arbitrarily defined region.








>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>

|







290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328

<p>The problem with this latter query is that it must apply the
contained_in() function to millions of entries in the demo_data table.
The use of the R*Tree in the penultimate query reduces the number of
calls to contained_in() function to a small subset of the entire table.
The R*Tree index did not find the exact answer itself, it merely
limited the search space.</p>

<tcl>hd_fragment {intrtree} {integer-valued r-trees}</tcl>
<h2>5.0 Integer-Valued R-Trees</h2>

<p>
The default virtual table ("rtree") normally stores coordinates as
single-precision (4-byte) floating point numbers.  If integer coordinates
are desired, declare the table using "rtree_i32" instead:

<blockquote><pre>
CREATE VIRTUAL TABLE intrtree USING rtree_i32(id,x0,x1,y0,y1,z0,y1);
</pre></blockquote>

<p>
An rtree_i32 stores coordinates as 32-bit signed integers.  But it still
using floating point computations internally as part of the r-tree algorithm.
For applications running on processors without hardware floating point,
it might be desirable to have a pure integer implementation of r-trees.
This is accomplished by compiling with the [SQLITE_RTREE_INT_ONLY] option.
When SQLITE_RTREE_INT_ONLY is used, both the "rtree" and the "rtree_i32"
virtual tables store 32-bit integers and only integer values are used for
internal computations.

<tcl>hd_fragment {customquery} {custom r-tree queries}</tcl>
<h2>6.0 Custom R-Tree Queries</h2>

<p>By using standard SQL expressions in the WHERE clause of a SELECT query,
a user may query for all r-tree entries that intersect a specified
bounding-box, or for all entries that are completely encapsulated by a
specified bounding-box. Custom r-tree queries, which use the special MATCH
operator in the WHERE clause of a SELECT, allow the user to query for the set
of r-tree entries that intersect any arbitrarily defined region.