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

### Describe what you want sqlite implements geospatial queries using the R*Tree extension ([https://www.sqlite.org/rtree.html](url)), and full text search using the FTS5 extension ([https://www.sqlite.org/fts5.html](url)). Both extensions are enabled by default on Turso. ([https://docs.turso.tech/extensions](url)) Use of these modules requires the CREATE VIRTUAL TABLE statement. ([https://www.sqlite.org/vtab.html](url)) 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`

Posted by aaroned on Mar 20, 2024, 5:08 AM

Tags

• github (Other)

Related issues

• struct's lifetime requirements can be bypassed via covariant field assignment
• Proposal to Reorganize the Moderation Team and Review Moderation Policies
• Progressive FPS degradation appears to be related to dota2.com — blocking the domain prevents the performance loss
• proposal: container/...: generic collection types
• Better Custom Endpoint BYOK
• A concrete policy for backward compatibility until Godot 5.0