[jira] [Commented] (CALCITE-6239) Add a postgis dialect that supports ST functions

["Julian Hyde (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-6239?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17816823#comment-17816823 ] 

Julian Hyde commented on CALCITE-6239:
--------------------------------------

I reviewed the PR, looks pretty good.

However, the change to {{RelDataTypeFactoryImpl}} makes me worry that we are deepening our dependency on the JTS {{Geometry}} class. Is that what we want?

> Add a postgis dialect that supports ST functions
> ------------------------------------------------
>
>                 Key: CALCITE-6239
>                 URL: https://issues.apache.org/jira/browse/CALCITE-6239
>             Project: Calcite
>          Issue Type: New Feature
>          Components: core
>            Reporter: Bertil Chapuis
>            Assignee: Bertil Chapuis
>            Priority: Minor
>              Labels: pull-request-available
>
> 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



--
This message was sent by Atlassian Jira
(v8.20.10#820010)