It’s possible that the problem is with the JDBC adapter. Calcite would need to connect to PostGIS via its JDBC driver, read the column types, and deduce that the column is of GEOMETRY type. JDBC does not have a type-id for GEOMETRY, so this process would be a little ad hoc. (Probably using ResultSet.getColumnClassName in some way.)
As far as I know, you are the first person to connect to PostGIS via Calcite’s JDBC adapter, so it’s not surprising that there are some interoperability issues. Can you please log a JIRA case for this issue? Julian > On Jun 22, 2021, at 2:47 AM, tonytao <tonytao0...@outlook.com> wrote: > > hi folks, > > I'm using calcite with postgis, and found the support of geometry type is not > very good. > > Firstly, geometry was not added to enum "SqlTypeName" as a jdbc type, > geometry type read from postgresql jdbc will convert to ANY type. > > Since 1.27.0, the hepPlanner will add a cast-to-geometry function to the ANY > type as below: > > table: > > \d cities > Table "public.cities" > Column | Type | Collation | Nullable | Default > ----------+-----------------------+-----------+----------+--------- > id | integer | | | > name | character varying(50) | | | > the_geom | geometry(Point,4326) | | | > x | double precision | | | > y | double precision | | | > > query: > > select > ST_POINT(ST_x(the_geom),ST_Y(the_geom)),ST_MAKELINE(the_geom,the_geom),ST_AsWKT(the_geom),ST_AsText(\"the_geom\"),ST_x(the_geom),ST_y(the_geom) > from pg.cities t > > logical plan of 1.26: > > LogicalProject(EXPR$0=[ST_POINT(ST_X($2), ST_Y($2))], EXPR$1=[ST_MAKELINE($2, > $2)], EXPR$2=[ST_ASWKT($2)], EXPR$3=[ST_ASTEXT($2)], EXPR$4=[ST_X($2)], > EXPR$5=[ST_Y($2)]) > JdbcTableScan(table=[[pg, cities]]) > > logical plan of 1.27: > > LogicalProject(EXPR$0=[ST_POINT(ST_X(CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom)), > ST_Y(CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom)))], > EXPR$1=[ST_MAKELINE(CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom), CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom))], > EXPR$2=[ST_ASWKT(CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom))], > EXPR$3=[ST_ASTEXT(CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom))], > EXPR$4=[ST_X(CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom))], > EXPR$5=[ST_Y(CAST($2):JavaType(interface > org.apache.calcite.runtime.Geometries$Geom))]) > JdbcTableScan(table=[[pg, cities]]) > > > rel2sql couldn't convert logical plan of 1.27 to sql for: > > java.lang.UnsupportedOperationException: Unsupported type when > convertTypeToSpec: GEOMETRY > at > org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1065) > at > org.apache.calcite.sql.type.SqlTypeUtil.convertTypeToSpec(SqlTypeUtil.java:1087) > at org.apache.calcite.sql.SqlDialect.getCastSpec(SqlDialect.java:800) > at > org.apache.calcite.sql.dialect.PostgresqlSqlDialect.getCastSpec(PostgresqlSqlDialect.java:92) > at > org.apache.calcite.rel.rel2sql.SqlImplementor$Context.callToSql(SqlImplementor.java:772) > > I think this problem is not only a bug.For the spatial type ,different > database has different implements and definitions.It's hard to write a > general solution.In my opinion it could add a hook to support a specific > spatial database type convert? > > Many thanks for your work,looking forward for your reply. > > Thanks again! > > Tony Tao > >
