[CALCITE-6239] Add a PostGIS dialect that supports ST_ functions

• Type: New Feature
• Status: Open
• Priority: Minor
• Resolution: Unresolved
• Component/s: core, spatial
• Labels: pull-request-available
• Assignee: Bertil Chapuis
• Reporter: Bertil Chapuis
• Created: 02/Feb/24 22:56
• Updated: 09/Apr/24 07:47

Description

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

Issue Links

• is related to: CALCITE-6263 Discuss the coupling of Calcite with JTS
• links to: GitHub Pull Request #3668