Re: geometry type support problem

[tonytao]

Thanks for your reply.
I had create a jira: CALCITE-4662 
<https://issues.apache.org/jira/browse/CALCITE-4662>



On 6/23/21 4:02 AM, Julian Hyde wrote:
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