SQLite R*Tree (Virtual Table)

[ainsleyclark]

Version

1.27.0

What happened?

I'm trying to create a virtual table using SQLite's R*Tree functionality described here: https://www.sqlite.org/rtree.html But I'm getting an error when trying to run generate. Is it supported?

Relevant log output

store/query.sql:24:1: relation "weather_rtree" does not exist

Database schema

-- Create a simple weather table for storing latitude and longitude
CREATE TABLE weather (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    latitude REAL NOT NULL,
    longitude REAL NOT NULL,
    description TEXT
);

-- Create a virtual R-Tree table for spatial indexing
CREATE VIRTUAL TABLE weather_rtree USING rtree(
    id,          
    min_latitude,  
    max_latitude,  
    min_longitude,
    max_longitude  
);

SQL queries

-- name: InsertWeather :one
INSERT INTO weather (
    latitude, longitude, description
) VALUES (
    ?, ?, ?
)
RETURNING id;

-- name: GetWeatherByID :one
SELECT
    id, latitude, longitude, description
FROM weather
WHERE id = ?;

-- name: GetWeatherByLocation :many
SELECT
    w.id, w.latitude, w.longitude, w.description
FROM weather AS w
JOIN weather_rtree AS r
ON w.id = r.id
WHERE r.min_latitude <= ? AND r.max_latitude >= ?
AND r.min_longitude <= ? AND r.max_longitude >= ?;

Configuration

version: "1"
packages:
  - name: "store"
    path: "store"
    queries: "./queries.sql"
    schema: "./schema.sql"
    engine: "sqlite"
    sql_package: "sqlc"

Playground URL

No response

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go