Future of the Spatial Type Extension

[Bertil Chapuis <bc...@gmail.com>]

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

Re: Future of the Spatial Type Extension

[Julian Hyde <jh...@gmail.com>]

> On Mar 6, 2023, at 3:37 PM, Bertil Chapuis <bc...@gmail.com> wrote:
> 
> Thank you for your answers and for the pointers.
> 
>> 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.
> 
> My feeling is that spatial is a rather small niche and that developers do not necessarily seek compatibility with Postgis or H2GIS. Personally, I almost always have to refer to the documentation of the RDBMS when writing queries that use spatial functions and I have few expectations regarding their behaviour beyond what is stated in the documentation. In this regard, I feel that a good standalone documentation is preferable to a documentation with references to other implementations that may differ in subtle ways.

It is a truism that the majority of SQL is generated by programs, not people. Those programs have already been written for industry-standard dialects (e.g. Oracle, Postgres) and therefore the best semantics is to be consistent with those standards.

For geospatial SQL, I suspect that a somewhat higher percentage of queries are written by people. Still, the principle of least surprise dictates that we should comply with the de facto standard unless there are compelling reasons not to.

If we depart from the standard for particular functions, let’s do it consciously, explicitly, and rarely.

Calcite does not currently have detailed documentation. We have benefited hugely from products that do, such as Postgres and H2GIS, because our users can safely assume that our semantics is consistent with the other products. Documenting our functions exhaustively - say, moving from one page for all functions to one page per function - would be a huge endeavor and I can’t see it happening with the current community. The best we could reasonably achieve would be separate pages for a very small number of exceptional functions.

> 
>>> 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. 
> 
> Do you think we should eventually add a @LibraryOperator for spatial type functions (i.e. OpenGIS, Postgis, H2GIS)? There is still a TODO (prior to the changes I made) in the SpatialTypeFunctions class asking the following question: Should we create aliases for functions in upper-case [1]?  My current understanding of this question is that a possibility may be to have a method named “buffer” in the class and an alias named “ST_Buffer” registered somewhere else. Appart from resolving lots of sonar code smells related to method names, this approach would allow to more easily introduce new methods that don’t have ST_ counterparts in other RDBMS.

It would be good to move all spatial functions out of the “core” module at some point. If we did that, it would probably make sense to declare the geospatial functions in a class with similar structure to SqlLibraryOperators so that they can be selectively enabled.

At this point I don’t see much benefit for adding separate libraries for OpenGIS, Postgis, H2GIS. But that might change. As far as I can tell (correct me if I am wrong) they are mostly the same functions, with slightly different specifications. If so, a ‘compliance’ flag might be more appropriate.

I wouldn’t worry about code smells. Something done for a good reason isn’t a code smell. It is a false negative from a robot.

> After merging CALCITE-5367 [2], I think we will have a relatively decent support for spatial types in core. At this stage, I think it would be good to see if the organisation of the code can be improved before investigating support for more advanced spatial functions (e.g. 3D, ST_AsMVT, ST_AsFlatGeobuf, etc.).

Sounds good. Identify problems and propose solutions. Algebraic approaches - e.g. decomposing functions into smaller composable units, like how AVG decomposes into SUM and COUNT - are well worth considering.

Julian

Re: Future of the Spatial Type Extension

[Bertil Chapuis <bc...@gmail.com>]

Thank you for your answers and for the pointers.

> 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.

My feeling is that spatial is a rather small niche and that developers do not necessarily seek compatibility with Postgis or H2GIS. Personally, I almost always have to refer to the documentation of the RDBMS when writing queries that use spatial functions and I have few expectations regarding their behaviour beyond what is stated in the documentation. In this regard, I feel that a good standalone documentation is preferable to a documentation with references to other implementations that may differ in subtle ways.

>> 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. 

Do you think we should eventually add a @LibraryOperator for spatial type functions (i.e. OpenGIS, Postgis, H2GIS)? There is still a TODO (prior to the changes I made) in the SpatialTypeFunctions class asking the following question: Should we create aliases for functions in upper-case [1]?  My current understanding of this question is that a possibility may be to have a method named “buffer” in the class and an alias named “ST_Buffer” registered somewhere else. Appart from resolving lots of sonar code smells related to method names, this approach would allow to more easily introduce new methods that don’t have ST_ counterparts in other RDBMS.

After merging CALCITE-5367 [2], I think we will have a relatively decent support for spatial types in core. At this stage, I think it would be good to see if the organisation of the code can be improved before investigating support for more advanced spatial functions (e.g. 3D, ST_AsMVT, ST_AsFlatGeobuf, etc.).

[1] https://github.com/apache/calcite/blob/ee9b80b0b68d442991dfaa142722e3488ec73e79/core/src/main/java/org/apache/calcite/runtime/SpatialTypeFunctions.java#L94 <https://github.com/apache/calcite/blob/ee9b80b0b68d442991dfaa142722e3488ec73e79/core/src/main/java/org/apache/calcite/runtime/SpatialTypeFunctions.java#L94>
[2] https://github.com/apache/calcite/pull/3088 <https://github.com/apache/calcite/pull/3088>
[3] https://issues.apache.org/jira/browse/CALCITE-5309 <https://issues.apache.org/jira/browse/CALCITE-5309>

Re: Future of the Spatial Type Extension

[Julian Hyde <jh...@gmail.com>]

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 <jh...@gmail.com> 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 <bc...@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
>> 
> 

Re: Future of the Spatial Type Extension

[Julian Hyde <jh...@gmail.com>]

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 <bc...@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
>