Can't create RTREE spatial index using new CRS-scoped GEOMETRY column type

[mhkeller]

When trying to create a RTREE spatial index with a geometry field that has a CRS, I get an error that I can only do this on plain GEOMETRY columns. Here's a reproduction (view example csv)

INSTALL spatial;
LOAD spatial;

.print '=== DuckDB version (need v1.5.0 or newer) ==='
SELECT version() AS duckdb_version;

.print '=== 1. Control: plain GEOMETRY + RTREE ==='

CREATE OR REPLACE TABLE lighthouses_plain AS
  SELECT
    name,
    country,
    longitude,
    latitude,
    height_m,
    year_built,
    ST_Point(longitude, latitude)::GEOMETRY AS geom
  FROM read_csv_auto('lighthouses.csv');

CREATE INDEX IF NOT EXISTS lighthouses_plain_geom_idx
  ON lighthouses_plain USING RTREE (geom);

.print '=== 2. Bug repro: GEOMETRY(''OGC:CRS84'') + RTREE ==='

CREATE OR REPLACE TABLE lighthouses_crs AS
  SELECT
    name,
    country,
    longitude,
    latitude,
    height_m,
    year_built,
    ST_Point(longitude, latitude)::GEOMETRY('OGC:CRS84') AS geom
  FROM read_csv_auto('lighthouses.csv');

-- Binder Error: RTree indexes can only be created over GEOMETRY columns.
CREATE INDEX IF NOT EXISTS lighthouses_crs_geom_idx
  ON lighthouses_crs USING RTREE (geom);

[Maxxen]

Hey! Thanks for reporting this issue!
This seems like an oversight indeed and should be a relatively easy fix.

[mhkeller]

Just wanted to check in on what you think a timeline might be for this. I'm coordinating a dependency update (has a few moving parts so I try to space them out) and want to see if I should do it now or wait a little longer. Thanks again!

[Maxxen]

Sorry about that, got lost in the weeds. Ill have a fix ready for v1.5.5

[mhkeller]

Thanks!