calcite [CALCITE-6239, CALCITE-6263] Add PostGIS Dialect to Apache Calcite

This PR introduces a new PostgisSqlDialect to Apache Calcite, providing initial support for PostGIS functions. It primarily addresses CALCITE-6239 and follows the architectural approach proposed by @julianhyde in CALCITE-6263.

Summary of the changes:

New Dialect: PostgisSqlDialect class extends PostgresqlSqlDialect, specializing SQL generation for PostGIS functions.
ST_ Function Support: Adds SQL translation for selected ST_ prefixed spatial functions (e.g., ST_Contains, ST_Within, etc.), enabling Calcite to generate syntactically correct PostGIS SQL from relational algebra expressions using these functions.
Function Mapping: Introduced mapping from Calcite’s standard operator representations to PostGIS-compatible SQL functions.
Test Coverage: Added unit tests to validate the translation logic for supported spatial functions under the new dialect.

Coupling with Java Topology Suite (JTS): The main concern regarding this PR was about avoiding excessive coupling between Calcite’s core and third-party spatial libraries like JTS as described in CALCITE-6263. We now have a Built-in GEOMETRY Type independant from JTS. The coupling with JTS occurs in JavaTypeFactoryImpl which is better than in RelDataTypeFactoryImpl.

Feb 02 '24 22:02 bchapuis

@YiwenWu @JiajunBernoulli What would be the best way to test these changes against a postgis database? Ideally, I'd like to add an integration test that executes queries against postgis (e.g. with testcontainers). But I havn't been able to find such tests in calcite.

Feb 03 '24 10:02 bchapuis

@YiwenWu @JiajunBernoulli What would be the best way to test these changes against a postgis database? Ideally, I'd like to add an integration test that executes queries against postgis (e.g. with testcontainers). But I havn't been able to find such tests in calcite.

I remember that testContainer has been used in redis apapter test.

Feb 04 '24 05:02 macroguo-ghy

@YiwenWu @JiajunBernoulli What would be the best way to test these changes against a postgis database? Ideally, I'd like to add an integration test that executes queries against postgis (e.g. with testcontainers). But I havn't been able to find such tests in calcite.

There are some integration tests for Druid: https://github.com/zabetak/calcite-druid-dataset Here is CI config in calcite: https://github.com/apache/calcite/blob/2aabf210dc1918c6ca20e63b39661ff445535eb8/.github/workflows/main.yml#L440