PS Regarding which specification we choose to implement. The four principles you outline sound good to me. It’s always better to follow the standard. If leading implementations (e.g. PostGIS and H2GIS) diverge from the standard, we can make a note, and possibly support them as secondary implementations.
Sometimes there are compatible extensions. E.g. the OpenGIS function has two arguments, and the PostGIS version accepts two or three arguments. If someone calls Calcite's implementation of the function with three arguments, it is reasonable that they get the PostGIS behavior. > On Feb 6, 2023, at 5:00 PM, Julian Hyde <[email protected]> wrote: > > 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 <[email protected]> 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 >> >
