Ref. bpo-43440

Now that both Windows and macOS builds compile SQLite with R*Tree support, we should consider adding support for R*Tree callbacks.

SQLite has two API's:

• sqlite3_rtree_query_callback() for SQLite 3.8.5 and newer.
• sqlite3_rtree_geometry_callback() for SQLite pre 3.8.5.

I suggest using the new API only, because it is more flexible, and it is also the one recommended by SQLite.

See https://sqlite.org/rtree.html

Python API:
sqlite3.Connection.create_rtree_query_function()

Too long function name?

As for the callback spec, I'm not sure what's the most pythonic approach?
callback(coords, *params, **query_info):
coords # array of coordinates of node or entry to check
*params # parameters passed to the SQL function
**query_info # the rest of the relevant sqlite3_rtree_query_info members
return (visibility, score)

FYI, PoC patch attached. Lacks tests and some #ifdefs. Let me know if I should create a PR out of it.

Test run output (see attached test file):

$ ./python.exe test_rtree.py
ARGS: ((-80.77490234375, -80.77469635009766, 35.377593994140625, 35.377803802490234), (45.3, 22.9, 5.0))
KWARGS: {'num_queued': [0, 1], 'context': None, 'level': 0, 'max_level': 1, 'rowid': 1, 'parent_score': 0.0, 'parent_visibility': 1}
ARGS: ((-81.0, -79.5999984741211, 35.0, 36.20000076293945), (45.3, 22.9, 5.0))
KWARGS: {'num_queued': [1, 1], 'context': ['stuff'], 'level': 0, 'max_level': 1, 'rowid': 2, 'parent_score': 0.0, 'parent_visibility': 1}

Unfortunately, there's no way to detect R*Tree support in sqlite3.h. We could run a script that dumps the compile options, and generate a config.h file from that:

$ for L in $(sqlite3 ":memory:" "pragma compile_options"); do echo #define SQLITE_$L >> config.h; done
$ cat config.h
#define SQLITE_COMPILER=clang-12.0.0
#define SQLITE_ENABLE_DBSTAT_VTAB
#define SQLITE_ENABLE_FTS4
#define SQLITE_ENABLE_FTS5
#define SQLITE_ENABLE_GEOPOLY
#define SQLITE_ENABLE_JSON1
#define SQLITE_ENABLE_RTREE
#define SQLITE_ENABLE_STMTVTAB
#define SQLITE_THREADSAFE=1

For Windows and macOS builds, we've already got the SQLite library compile options, so we can just reuse them in CFLAGS when we're building the sqlite3 module.

Attached is a patch that auto-detects R*Tree support. Tested on macOS only, but I'd guess it should work fine on any OS.

I have no bandwidth for driving this forward. Until there is a real feature request for this, I prefer to close this issue as not planned.