SQLite Forum

RTree on ESP32 SQLite Version
Login
Read the documentation found at <https://sqlite.org/rtree.html> and pay particular attention to the **BOLD** text.

\3. Using the R\*Tree Module

The SQLite R\*Tree module is implemented as a 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 pairs, one pair per dimension, containing the minimum and maximum values for that dimension, respectively. A 1-dimensional R\*Tree thus has 3 columns. A 2-dimensional R\*Tree has 5 columns. A 3-dimensional R\*Tree has 7 columns. A 4-dimensional R\*Tree has 9 columns. And a 5-dimensional R\*Tree has 11 columns. The SQLite R\*Tree implementation does not support R*Trees wider than 5 dimensions.

**The first column of an SQLite R\*Tree is similar to an integer primary key column of a normal SQLite table. It may only store a 64-bit signed integer value. Inserting a NULL value into this column causes SQLite to automatically generate a new unique primary key value. If an attempt is made to insert any other non-integer value into this column, the r-tree module silently converts it to an integer before writing it into the database.**

The min/max-value pair columns are stored as 32-bit floating point values for "rtree" virtual tables or as 32-bit signed integers in "rtree_i32" virtual tables. Unlike regular SQLite tables which can store data in a variety of datatypes and formats, the R\*Tree rigidly enforce these storage types. If any other type of value is inserted into such a column, the r-tree module silently converts it to the required type before writing the new record to the database.