Similar issues have come up with non-GIS functions. For example, the DATEDIFF function [1]. Snowflake and MSSQL have ‘DATEDIFF(timeUnit, datetime, datetime2)’, whereas MySQL has ‘DATEDIFF(date, date2)’. We document which specification we implement, and potentially we could implement both specifications, as either functions with different names, or by selectively enabling the behavior that the user wants.
The most important thing is to document which specification we are implementing (when there is ambiguity). Then if someone would prefer the other specification, they can contribute an implementation, and we can selectively enable it. Julian [1] https://github.com/apache/calcite/blob/ae228f64347cc7620d28eff04f5869583c6bf9d5/core/src/main/java/org/apache/calcite/sql/fun/SqlLibraryOperators.java#L98 > On Feb 6, 2023, at 3:39 AM, Bertil Chapuis <bchap...@gmail.com> wrote: > > Hello Everyone, > > I continue to make progress on the implementation of the Spatial Type (ST_) > extension for calcite [1] and wanted to exchange about the current design. > > When implementing spatial functions, we usually refer to the OpenGIS Simple > Features Implementation Specification for SQL [2] or the documentation of > other databases, such as the PostGIS extension for PostgreSQL or the H2GIS > extension for H2. The documentation has a compatibility column that indicates > which reference was used to guide the implementation. > > In practice, the implementation of the spatial type functions is not always > the same. For instance, the ST_AddPoint[3] function uses indices in PostGIS > and a tolerance parameter in H2GIS[4]. Therefore, some choices have to be > made when implementing the functions and at the end, the implementation will > neither be compatible with PostGIS nor H2GIS. > > Additionally, most of the functions maps well to the Java Topology Suite > (JTS) [5] library, however, some other are not so straightforward. For > instance, the ST_Buffer [6] function in PostGIS takes a style parameter > (e.g., "join=mitre mitre_limit=5.0") that must be parsed, whereas the JTS > library exposes typed buffer parameters [6]. > > In this context, I would design the ST_ functions in the following way: > - Follow the OpenGIS Simple Features Implementation Specification for SQL as > much as possible; > - Map the functions to the JTS library as much as possible (priority over > PostGIS and H2GIS); > - Use PostGIS and H2GIS documentation for inspiration and to create test > cases; > - Remove the compatibility column from the documentation or limit it to > compatibility with the OpenGIS standard. > > What do you think about these ideas? Would some of you be interested to > exchange/brainstorm on this topic? I feel that this will help to make the > implementation more consistent and easier to maintain in the future. > > Thanks a lot for your feedback. > > Best, > > Bertil > > > [1] https://github.com/apache/calcite/pull/3060 > [2] > https://portal.ogc.org/files/?artifact_id=829#:~:text=A%20simple%20feature%20is%20defined,with%20linear%20interpolation%20between%20vertices. > [3] https://postgis.net/docs/ST_AddPoint.html > [4] http://www.h2gis.org/docs/dev/ST_AddPoint/ > [5] https://postgis.net/docs/ST_Buffer.html > [6] > https://locationtech.github.io/jts/javadoc/org/locationtech/jts/operation/buffer/BufferParameters.html >
