drizzle-team / drizzle-team/drizzle-orm
[FEATURE]: sqlite CREATE VIRTUAL TABLE and R*Tree extension
- Dominant language
- TypeScript
- Stars
- 35.8k
- Forks
- 1.6k
- Avg merge
- 2d 7h
- Merged PRs (30d)
- 4
Description
### 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`
Contributor guide
Assessment
This issue has not been assessed yet.