An R*Tree indexes bounding boxes so you can find rectangles that overlap a search area. This finds box candidates, not exact polygon intersections.
dotnet add package RecursiveAI.OneLiteRequires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.
using RecursiveAI.OneLite; await using var db = await Database.OpenAsync(":memory:"); await db.ExecuteAsync($""" CREATE VIRTUAL TABLE boxes USING rtree(id, minX, maxX, minY, maxY) """); await db.ExecuteAsync($""" INSERT INTO boxes VALUES (1,1,2,1,2),(2,9,11,9,11), (3,20,21,20,21) """); var ids = await db.QueryAsync<long>($""" SELECT id FROM boxes WHERE minX<=10 AND maxX>=0 AND minY<=10 AND maxY>=0 ORDER BY id """); foreach (var id in ids) Console.WriteLine(id);
1 2
The search area runs from 0 to 10 on both axes. Box 1 is inside, box 2 crosses the upper-right boundary, and box 3 is outside. If a minimum exceeds its maximum, OneLite reports an R*Tree constraint failure through OneLiteException.
The query methods require interpolated strings: values inserted with {value} become bound SQL parameters.
Unzip the example, then dotnet run.
Variations
Only boxes fully contained in the search area. Replace the SELECT inside QueryAsync<long> with:
SELECT id FROM boxes WHERE minX>=0 AND maxX<=10 AND minY>=0 AND maxY<=10 ORDER BY id
1For decimal coordinates, outward rounding can exclude an edge-aligned box from a containment query; see the explanation below.
Related recipes
The short answer
Create a virtual table using rtree(id, minX, maxX, minY, maxY), insert each object’s bounding box, and compare its bounds with the query rectangle. For overlap, require the stored minimum to be no greater than the query maximum and the stored maximum to be no less than the query minimum on each axis. The C# example executes those statements with OneLite and prints the matching IDs.
The test is inclusive: touching an edge counts as overlap.
How do you query a SQLite R*Tree bounding box?
Store coordinates as ordered minimum/maximum pairs. In this example X is the horizontal coordinate and Y is the vertical coordinate. The search rectangle has lower bounds zero and upper bounds ten on each axis. A stored rectangle is disjoint if it sits entirely beyond one of those bounds; the overlap predicates exclude exactly those cases.
Use the same units and coordinate system for the stored boxes and the query. The SQL here performs coordinate comparisons, not coordinate conversion or distance measurement. If you use geographic data, deciding how to handle a region that crosses a coordinate discontinuity is an application concern; this example uses an ordinary flat grid.
OneLite’s R*Tree documentation says coordinate constraints using =, <, <=, > and >= prune subtrees. It also says row order is not promised. The explicit ORDER BY id makes the sample output reproducible without relying on tree traversal order. An equality on the ID uses the R*Tree row-ID lookup instead of a spatial range search.
How do you find boxes contained within a SQLite R*Tree query?
Reverse which bounds you compare: the stored minimum must be at least the query minimum, and the stored maximum must be at most the query maximum, on both axes. The variation returns only box 1. Box 2 overlaps the search area but extends past it, so it is excluded.
Be careful with fractional coordinates. OneLite documents rtree coordinates as binary32 values with outward rounding: lower bounds move down and upper bounds move up when necessary. The SQLite roundoff explanation describes why overlap queries may gain extra edge candidates while containment queries may lose edge-aligned entries. Use a deliberately expanded containment search area when that matches your application’s tolerance; do not assume decimal literals survive storage exactly. The integer coordinates in this example avoid that particular ambiguity.
Does an R*Tree query return exact spatial matches?
It returns matches for the stored boxes. A polygon can have a bounding box that overlaps the search rectangle while the polygon itself does not. Use the IDs as candidates for an exact geometry check when the actual shapes matter. The SQLite R*Tree usage guide explains this two-stage approach.
OneLite does not support R*Tree geometry callbacks or MATCH geometry queries, according to its pinned R*Tree documentation. This recipe therefore uses coordinate comparisons only. It also does not modify an R*Tree while scanning it: finish collecting the query results before changing the same tree. Keep application metadata in a related table or documented auxiliary columns rather than editing the internal shadow tables that store the index.