bug: geospatial literals don't work out of the box with the default backend

[ncclementi]

What happened?

When trying to do an operation on a geospatial literal object, it fails due to missing "spatial" extension. Tried creating a connection, and loading the extension, but problem persists:

Minimal reproducible example

In [1]: import ibis

In [2]: import shapely

In [3]: a = shapely.LineString([[0, 0], [1, 0], [1, 1]])

In [4]: al = ibis.literal(a, type="geometry")

In [5]: al
Out[5]: LINESTRING (0 0, 1 0, 1 1)

In [6]: al.length()
Out[6]: GeoLength(LINESTRING (0 0, 1 0, 1 1)): GeoLength(LINESTRING (0 0, 1 0, 1 1))

In [7]: from ibis.interactive import *

In [8]: al.length()

ProgrammingError: (duckdb.duckdb.CatalogException) Catalog Error: Scalar Function with name "st_length" is not in the catalog, but it exists in the spatial extension.

Please try installing and loading the spatial extension:
INSTALL spatial;
LOAD spatial;


[SQL: SELECT ST_Length('LINESTRING (0 0, 1 0, 1 1)'::geometry) AS "GeoLength(LINESTRING (0 0, 1 0, 1 1))"]
(Background on this error at: https://sqlalche.me/e/14/f405)

In [9]: con = ibis.duckdb.connect()

In [10]: con.load_extension("spatial")

In [11]: al.length()

ProgrammingError: (duckdb.duckdb.CatalogException) Catalog Error: Scalar Function with name "st_length" is not in the catalog, but it exists in the spatial extension.

Please try installing and loading the spatial extension:
INSTALL spatial;
LOAD spatial;


[SQL: SELECT ST_Length('LINESTRING (0 0, 1 0, 1 1)'::geometry) AS "GeoLength(LINESTRING (0 0, 1 0, 1 1))"]
(Background on this error at: https://sqlalche.me/e/14/f405)

What version of ibis are you using?

Ibis main branch

What backend(s) are you using, if any?

duckdb

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct

[ncclementi]

This came up while creating the examples for #8128, but I noticed that it wasn't failing in CI. The reason why in CI things worked is because some of the tests run before load the spatial extension.

However, if you were to try to do directly what's on the reproducer, you hit the error.

I was trying to see how we would go about it, but so far no luck. It thought here might be a good place to load the extension but at this point we don't have a connection.

https://github.com/ibis-project/ibis/blob/4fb0aad941029b6fbc47342297b47305f5366d1c/ibis/backends/duckdb/registry.py#L202-L203

@cpcloud any words of wisdom on how we can make this happen?

[cpcloud]

What's happening is that the default backend is used to execute the expressions (where you define al)

In the second block of code you initialize a duckdb connection and load the necessary extensions.

However: the connection used for the default backend is a different connection than the one you're constructing.

One option is to call 'ibis.set_backend(con), and then al.length()` again. That should work.

If we definitely want to make this work out of the box, then we probably need to add some code to duckdb's execute method to find any operations in the executing expression using a pattern match on p.Value(dtype=dt.GeoSpatial) and subsequently calling load_extension if any are found.

We should probably wait until after the-epic-split is merged.

[ncclementi]

Then we can punt this one for after the merge of the-epic-split

Given that's only three cases using literals in the docs, we can patch the examples for now, to load the extension in the spot.

I can do:

import ibis
from ibis.interactive import *
import shapely

a = shapely.LineString([[0, 0], [1, 0], [1, 1]])
al = ibis.literal(a, type="geometry")

con = ibis.get_backend()
con.load_extension("spatial")
al.length()

2.0