[FEATURE]: sqlite CREATE VIRTUAL TABLE and R*Tree extension

[aaroned]

Describe what you want

sqlite implements geospatial queries using the R*Tree extension (https://www.sqlite.org/rtree.html), and full text search using the FTS5 extension (https://www.sqlite.org/fts5.html). Both extensions are enabled by default on Turso. (https://docs.turso.tech/extensions)

Use of these modules requires the CREATE VIRTUAL TABLE statement. (https://www.sqlite.org/vtab.html)

The schema for an R*Tree virtual table might look like:

export const suburbSpatialIndex = sqliteRTree('suburbSpatialIndex', 'id', ['minLon', 'maxLon'], ['minLat', 'maxLat'])

this would create a migration:

CREATE VIRTUAL TABLE suburbSpatialIndex USING rtree(id, minLon, maxLon, minLat, maxLat);

Querying the virtual table would be just like querying a regular table:

db.select().from(suburbSpatialIndex).where(and(lte(suburbSpatialIndex.minLon, 144.5), gte(suburbSpatialIndex.maxLon, 144.5), lte(suburbSpatialIndex.minLat, -37.5), gte(suburbSpatialIndex.maxLat, -37.5)))

this would generate the sql:

SELECT * FROM suburbSpatialIndex WHERE minLon <= 144.5 AND maxLon >= 144.5 AND minLat <= -37.5 AND maxLat >= -37.5

[jamiehaywood]

Would be great to have this feature. How do Drizzle recommend we query spatial data at the moment?