bug: Loading geometry from postgres with SRID gives error

[Riezebos]

What happened?

Very similar to #8807. When trying to load a table with a geometry that has an SRID set, I get the following error:

TypeError: SqlglotType._from_sqlglot_GEOMETRY() takes from 1 to 2 positional arguments but 3 were given

Here is a reproducible example:

drop table if exists v_test;
CREATE TABLE v_test (
    id int8 NULL,
    lonlat geometry(Point, 4326) NULL
);

INSERT INTO v_test (id, lonlat)
VALUES 
    (2, st_setsrid(st_makepoint(5.7, 53.2),4326));

import ibis

ibis.options.interactive = True

pg_con = ibis.connect(
    "postgresql://blablabla",
)

pg_con.sql("select * from v_test").execute()

If it helps, loading this table with geopandas does not give errors.

What version of ibis are you using?

ibis-framework[postgres,geospatial]==9.1.0

(Also tried --pre)

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

Postgres

Relevant log output

No response

Code of Conduct

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

[ncclementi]

@Riezebos thank you for reporting this. I think I know where the problem is, but I need to investigate a bit more.

@cpcloud looks like the tests that we have checking if srid work is not catching this problem.
I'm not sure I follow exactly, why...

ibis/ibis/backends/postgres/tests/test_geospatial.py

Lines 247 to 262 in 5a9d026

	@pytest.mark.parametrize(
	("condition", "expected"),
	[
	(lambda _: point_geom_2.srid(), 4326),
	(lambda _: point_geom_0.srid(), 4326),
	(lambda t: t.geo_point.srid(), 0),
	(lambda t: t.geo_linestring.srid(), 0),
	(lambda t: t.geo_polygon.srid(), 0),
	(lambda t: t.geo_multipolygon.srid(), 0),
	],
	)
	def test_srid(geotable, condition, expected):
	"""Testing for geo spatial srid operation."""
	expr = geotable[geotable.id, condition(geotable).name("tmp")]
	result = expr.execute()["tmp"][[0]]
	assert np.all(result == expected)

Looks, it's failing here, because there is no way to pass the srid at this stage, however I don't see any references in the code to this method, so I'm not sure how we get there. Do you have any clues?

ibis/ibis/backends/sql/datatypes.py

Lines 298 to 304 in 5a9d026

	@classmethod
	def _from_sqlglot_GEOMETRY(
	cls, arg: sge.DataTypeParam | None = None
	) -> sge.DataType:
	if arg is not None:
	return _geotypes[str(arg).upper()](nullable=cls.default_nullable)
	return dt.GeoSpatial(geotype="geometry", nullable=cls.default_nullable)

[cpcloud]

From triage earlier today: from the TypeError in the initial report, this likely is us not threading the second parameter (that contains the SRID) through our sqlglot -> ibis type converter.

[cpcloud]

Would be nice to get this in for 9.2!