---
title: "Query SQLite R*Tree bounding boxes in C#"
url: https://recursiveai.org/onelite/cookbook/search-spatial-data-with-sqlite-rtree/
description: "Create a SQLite R*Tree virtual table and query overlapping bounding boxes in C# with OneLite, including containment and coordinate-rounding notes."
version: "1.0.0"
---

# Query SQLite R*Tree bounding boxes in C#

**OneLite** is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/search-spatial-data-with-sqlite-rtree/search-spatial-data-with-sqlite-rtree.zip)

An R*Tree indexes bounding boxes so you can find rectangles that overlap a search area. This finds box candidates, not exact polygon intersections.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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);
```

```text
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](https://recursiveai.org/onelite/cookbook/search-spatial-data-with-sqlite-rtree/search-spatial-data-with-sqlite-rtree.zip), then `dotnet run`.

## Variations

**Only boxes fully contained in the search area.** Replace the SELECT inside `QueryAsync<long>` with:

```sql
SELECT id FROM boxes
WHERE minX>=0 AND maxX<=10
  AND minY>=0 AND maxY<=10
ORDER BY id
```

```text
1
```

For decimal coordinates, outward rounding can exclude an edge-aligned box from a containment query; see the explanation below.

## Related recipes

- [Query SQLite with a recursive CTE in C#](https://recursiveai.org/onelite/cookbook/query-sqlite-with-recursive-cte/)
- [Calculate a SQLite running total in C#](https://recursiveai.org/onelite/cookbook/sqlite-running-total-window-function/)

## 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](https://recursiveai.org/onelite/docs/rtree/#rtree) 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](https://www.sqlite.org/rtree.html#roundoff) 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](https://www.sqlite.org/rtree.html#using_r_trees_effectively) 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.
