[BigQuery] How can I make st_geogfromwkb work as a UDF?

[Riezebos]

I have a table with a geometry column in duckdb and I'm trying to upload this to bigquery. I tried:

• Just putting the table object into bq.create_table (this doesn't work, but I wasn't expecting it to work)
• ddb_table.to_parquet() --> bq.read_parquet() (This almost works but Bigquery wants an SRID and DuckDB doesn't do SRIDs afaict)
• Converting to WKB in DuckDB and then converting to Geography in BigQuery

The last option works, but only if I do it in SQL, not if I try to do it in Ibis. As you can see in the compiled query, Ibis puts the converted column into a subquery and then converts it back to binary for some reason. Did I create the udf incorrectly?

import ibis
import ibis.expr.datatypes as dt
from ibis import _

ibis.options.interactive = True

@ibis.udf.scalar.builtin
def st_aswkb(column: dt.GeoSpatial) -> dt.Binary: ...

@ibis.udf.scalar.builtin
def st_geogfromwkb(column: dt.Binary) -> dt.GeoSpatial: ...

# Create DuckDB connection with sample data
ddb = ibis.duckdb.connect()
ddb.raw_sql("INSTALL spatial; LOAD spatial;")
sample_data = ddb.sql("""
    SELECT 
        1 as id, 
        ST_GeomFromText('POINT(1 1)') as location
    UNION ALL
    SELECT 
        2 as id, 
        ST_GeomFromText('POINT(2 2)') as location
""")

sample_data_arrow = sample_data.mutate(location=st_aswkb(_.location)).to_pyarrow()

# Create BigQuery connection
bq = ibis.bigquery.connect(project_id="x", dataset_id="dev", location="europe-west1")

# Create table in BigQuery
bq_table = bq.create_table("test_geom", sample_data_arrow, overwrite=True)

# Attempt to use st_geogfromwkb (this doesn't work)
result = bq_table.select(location=st_geogfromwkb(_.location))
print(result.compile(pretty=True))

# Workaround: Use raw SQL to convert WKB to geography
sql = """
SELECT id, ST_GEOGFROMWKB(location) as geog
FROM `dev.test_geom`
"""
bq.sql(sql)

This is the output of result.compile:

SELECT
  *
  REPLACE (st_asbinary(`location`) AS `location`)
FROM (
  SELECT
    st_geogfromwkb(`t0`.`location`) AS `location`
  FROM `x`.`dev`.`test_geom` AS `t0`
)

I first thought maybe the bigquery backend doesn't work with geodata, but this is the output of bq.sql(sql):

┏━━━━━━━┳━━━━━━━━━━━━━━━━━━━━━━━━━━━┓
┃ id    ┃ geog                      ┃
┡━━━━━━━╇━━━━━━━━━━━━━━━━━━━━━━━━━━━┩
│ int64 │ geospatial:geography;4326 │
├───────┼───────────────────────────┤
│     1 │ <POINT (1 1)>             │
│     2 │ <POINT (2 2)>             │
└───────┴───────────────────────────┘