SQL: representation of geo objects in JDBC

[imotov]

When a geo_shape or a geo_point is sent to the JDBC client, we need to represent it as some sort of the object when it is returned by JDBC's JdbcResultSet.getObject(int column) method. There are several possible alternatives here including

• WKT String. Currently, geo points and geo shapes are serialized as WKT-formatted strings and we don't do any additional transformations on the JDBC side, so we are returning them as string. This is the simplest and the most straightforward solution, however, it provides minimal benefit for the users since they will have to load this string into some other object in order to do anything with it.

• JTS Geometry. This is what H2 is currently using. One problem with this solution is that we will have to require or ship JTS library and we will not be able to release GEOSQL until H2GIS 1.5.0 is released. We are using H2GIS for testing it and it is still using JTS 1.14 at the moment, the rest of ES is using JTS 1.15. So, if we cannot ship JDBC with JTS 1.14, but we cannot test it with JTS 1.15.

• Lucene's geo objects. Lucene now has its own set of Geometry objects that we could use. However, there are 2 missing pieces that prevent me from going ahead with this solution. 1) We will need to include these objects into our JDBC library and while the objects themselves are quite lean they have some dependencies on org.apache.lucene.geo.GeoUtils that pulls tons of other stuff. So, we would need to do some cleanup on the Lucene level to remove unnecessary dependencies. 2) we will need a way to deserialize the objects on the JDBC side and currently all reasonable parser (for WKT and GeoJSON) are integrated with ShapeBuilders that also have tons of dependencies including JTS. So, if we go with this solution, we need to start a massive refactoring of GeoShape to extract WKT or GeoJSON parser from it.

• lib/geo minimum dependency Geometry class hierarchy. After additional discussions with @nknize another option came up with adding a minimal class hierarchy to lib/geo with just data classes and parsers (WKT and WKB). It might be possible to also reuse this hierarchy in other places, but that requires further investigation.

[elasticmachine]

Pinging @elastic/es-search

[elasticmachine]

Pinging @elastic/es-analytics-geo

[imotov]

After multiple discussions, we are going with the last option. The first PR #36477 should add the set of classes and in the follow up PR it will be replace the current String that is returned by JDBC driver when somebody asks for a geoshape.

[polyfractal]

@imotov since #36477 merged into geosql, can/should this be closed? Or are we waiting until geosql merges into master?