bug: reading geospatial data fails with duckdb>=1.5.1

[knaaptime]

What happened?

when reading geospatial data from a parquet file, ibis throws a schema error. For example this call

con = ibis.duckdb.connect()
t = con.read_parquet('s3://spatial-ucr/census/blocks_2020/11.parquet')

fails with the output below

this is probably related to the recent changes in duckb>=1.5. Yet if i read the data in directly with duckdb (e.g. using:)

import duckdb

con = duckdb.connect()
con.execute("INSTALL spatial;")
con.execute("LOAD spatial;")

query = """
SELECT * FROM read_parquet('s3://spatial-ucr/census/blocks_2020/11.parquet');
"""
df = con.execute(query).fetchdf()

the read executes fine (using duckdb 1.5.1; that call fails on 1.5)

What version of ibis are you using?

12.0.0

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

duckdb

Relevant log output

Traceback (most recent call last):
  File "<python-input-27>", line 2, in <module>
    t = con.read_parquet('s3://spatial-ucr/census/blocks_2020/11.parquet')
  File "/Users/knaaptime/miniforge3/envs/geosnap/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py", line 840, in read_parquet
    return self.table(table_name)
           ~~~~~~~~~~^^^^^^^^^^^^
  File "/Users/knaaptime/miniforge3/envs/geosnap/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py", line 286, in table
    table_schema = self.get_schema(name, catalog=catalog, database=database)
  File "/Users/knaaptime/miniforge3/envs/geosnap/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py", line 341, in get_schema
    name: type_mapper.from_string(typ, nullable=null == "YES")
          ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/knaaptime/miniforge3/envs/geosnap/lib/python3.14/site-packages/ibis/backends/sql/datatypes.py", line 195, in from_string
    return cls.to_ibis(sgtype, nullable=nullable)
           ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/knaaptime/miniforge3/envs/geosnap/lib/python3.14/site-packages/ibis/backends/sql/datatypes.py", line 161, in to_ibis
    dtype = method(*typ.expressions, nullable=nullable)
  File "/Users/knaaptime/miniforge3/envs/geosnap/lib/python3.14/site-packages/ibis/backends/sql/datatypes.py", line 360, in _from_sqlglot_GEOMETRY
    typeclass = _geotypes[arg.this.this]
                ~~~~~~~~~^^^^^^^^^^^^^^^
KeyError: '{"$schema":"https://proj.org/schemas/v0.7/projjson.schema.json","type":"GeographicCRS","name":"NAD83","datum":{"type":"GeodeticReferenceFrame","name":"North American Datum 1983","ellipsoid":{"name":"GRS 1980","semi_major_axis":6378137,"inverse_flattening":298.257222101}},"coordinate_system":{"subtype":"ellipsoidal","axis":[{"name":"Geodetic latitude","abbreviation":"Lat","direction":"north","unit":"degree"},{"name":"Geodetic longitude","abbreviation":"Lon","direction":"east","unit":"degree"}]},"scope":"Geodesy.","area":"North America - onshore and offshore: Canada - Alberta; British Columbia; Manitoba; New Brunswick; Newfoundland and Labrador; Northwest Territories; Nova Scotia; Nunavut; Ontario; Prince Edward Island; Quebec; Saskatchewan; Yukon. Puerto Rico. United States (USA) - Alabama; Alaska; Arizona; Arkansas; California; Colorado; Connecticut; Delaware; Florida; Georgia; Hawaii; Idaho; Illinois; Indiana; Iowa; Kansas; Kentucky; Louisiana; Maine; Maryland; Massachusetts; Michigan; Minnesota; Mississippi; Missouri; Montana; Nebraska; Nevada; New Hampshire; New Jersey; New Mexico; New York; North Carolina; North Dakota; Ohio; Oklahoma; Oregon; Pennsylvania; Rhode Island; South Carolina; South Dakota; Tennessee; Texas; Utah; Vermont; Virginia; Washington; West Virginia; Wisconsin; Wyoming. US Virgin Islands. British Virgin Islands.","bbox":{"south_latitude":14.92,"west_longitude":167.65,"north_latitude":86.45,"east_longitude":-40.73},"id":{"authority":"EPSG","code":4269}}'

Code of Conduct

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

[NickCrews]

Can you give a more detailed lockfile or other steps to repro the exact package versions? eg uv pip install ibis-framework[duckdb] && uv pip install duckdb==1.5.1

[NickCrews]

JK I can figure this out.

[knaaptime]

(in a fresh environment that line you gave will reproduce. I use conda environments so its taking me a second to figure out how to export the uv lock)

[knaaptime]

similarly, if you run the example

import ibis
ibis.options.interactive = True
t = ibis.examples.zones.fetch()
t.geom.area()

if fails with

Traceback (most recent call last):
  File "<python-input-0>", line 4, in <module>
    t = ibis.examples.zones.fetch()
  File "/Users/knaaptime/miniforge3/envs/ibistesst/lib/python3.14/site-packages/ibis/examples/__init__.py", line 107, in fetch
    return backend.read_geo(path)
           ~~~~~~~~~~~~~~~~^^^^^^
  File "/Users/knaaptime/miniforge3/envs/ibistesst/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py", line 797, in read_geo
    return self.table(table_name)
           ~~~~~~~~~~^^^^^^^^^^^^
  File "/Users/knaaptime/miniforge3/envs/ibistesst/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py", line 286, in table
    table_schema = self.get_schema(name, catalog=catalog, database=database)
  File "/Users/knaaptime/miniforge3/envs/ibistesst/lib/python3.14/site-packages/ibis/backends/duckdb/__init__.py", line 341, in get_schema
    name: type_mapper.from_string(typ, nullable=null == "YES")
          ~~~~~~~~~~~~~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/knaaptime/miniforge3/envs/ibistesst/lib/python3.14/site-packages/ibis/backends/sql/datatypes.py", line 195, in from_string
    return cls.to_ibis(sgtype, nullable=nullable)
           ~~~~~~~~~~~^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/knaaptime/miniforge3/envs/ibistesst/lib/python3.14/site-packages/ibis/backends/sql/datatypes.py", line 161, in to_ibis
    dtype = method(*typ.expressions, nullable=nullable)
  File "/Users/knaaptime/miniforge3/envs/ibistesst/lib/python3.14/site-packages/ibis/backends/sql/datatypes.py", line 360, in _from_sqlglot_GEOMETRY
    typeclass = _geotypes[arg.this.this]
                ~~~~~~~~~^^^^^^^^^^^^^^^
KeyError: 'EPSG:2263'

duckdb updated their CRS handling in version 1.5, which also broke geospatial reads (down at the duckdb level), but the 1.5.1 patch this week fixes that. If there's a change that needs to happen in _from_sqlglot_GEOMETRY, it may be passing through wkt or something, as suggested here ?

[NickCrews]

It looks like when reading a geo column, the inferred datatype depends on whether or not the spatial extension is loaded, and on the version of duckdb. Tested using:

import duckdb
print(duckdb.__version__)
query = """FROM read_parquet('s3://spatial-ucr/census/blocks_2020/11.parquet');"""
print(duckdb.sql(query).dtypes)
duckdb.execute("INSTALL spatial; LOAD spatial;")
print(duckdb.sql(query).dtypes)

	spatial not loaded	spatial loaded
duckdb < 1.5	BLOB	GEOMETRY
duckdb >= 1.5	GEOMETRY('{"$schema": ...}')	GEOMETRY('EPSG:4269')

In duckdb < 1.5, we never saw the error because the geometry datatype was never paramaterized. In duckdb >1.5, ibis barfs when parsing the type parameter (in both the spatial not loaded and spatial loaded formats) because our conversion is naive and assumes the postgres format for geo datatypes of geography(POINT,4269), eg we assume that the first positional param of a sqlglot datatype is the geotype, and the second is the srid:

ibis/ibis/backends/sql/datatypes.py

Lines 352 to 365 in 1de88cb

	@classmethod
	def _from_sqlglot_GEOMETRY(
	cls,
	arg: sge.DataTypeParam | None = None,
	srid: sge.DataTypeParam | None = None,
	nullable: bool | None = None,
	) -> sge.DataType:
	if arg is not None:
	typeclass = _geotypes[arg.this.this]
	else:
	typeclass = dt.GeoSpatial
	if srid is not None:
	srid = int(srid.this.this)
	return typeclass(geotype="geometry", nullable=nullable, srid=srid)

Ideally we should fix this upstream in sqlglot, so that they parse both to a consistent format, so that we can convert to/from ibis<->sqlglot consistently in a non-backend-specific way. I'm filing an issue there.

Maybe as a workaround we can do something else, but I would prefer to fix it there if possible