Hello Everyone, Calcite implements support for spatial types (geometry, point, etc.) and spatial functions (ST_), and it can connect to PostGIS via a JdbcSchema. However, the Postgresql dialect does not currently handle spatial types and functions. As a result, Calcite tries to execute the spatial functions at the level of the JVM instead of pushing them down to postgis.
As a result, the following query gets executed, but the type of the geom column is incorrect: SELECT id, geom FROM public.spatial_table The following query fails with a ClassCastException as Calcite tries to use the java implementation of the ST_SRID function: SELECT id, ST_SRID(geom) FROM public.spatial_table java.lang.ClassCastException: class org.postgresql.util.PGobject cannot be cast to class org.locationtech.jts.geom.Geometry (org.postgresql.util.PGobject and org.locationtech.jts.geom.Geometry are in unnamed module of loader 'app') In my current understanding, this issue could be addressed with a new PostgisSqlDialect that extends PostgresqlSqlDialect and adds support for spatial types and functions. Here is a tentative roadmap: - Add all the spatial functions to the SqlKind class - Create a PostgisSqlDialect class that extends PostgresqlSqlDialect - Add support for the spatial types (geometry) by overriding the getCastSpec method of the SqlDialect class - Add support for the spatial functions by overriding the supportsFunction method of the SqlDialect class - Add support for the spatial aggregate functions by overriding the supportsAggregateFunction method of the SqlDialect class Could someone confirm that overall this approach makes sense? If so, I will create a JIRA issue and submit pull requests. Something that I am not sure about, is how to test the dialect. In my understanding, when it comes to niche dialects such as PostGIS, Calcite relies on its users to report issues. Is that correct? Best, Bertil
signature.asc
Description: Message signed with OpenPGP
