SQL: Add initial geo support

[elasticmachine]

Introduction

This is a meta issue for tracking initial iteration of adding geo features to SQL. Our implementation will be based on the OpenGIS® Implementation Standard for Geographic information - Simple feature access, which is the current standard for GIS system implementation. This effort will concentrate on SQL option AKA ISO 19125-2. Later on we might also consider adding support for SQL/MM (ISO 13249-3), which extends Simple Feature Access with circular interpolations.

The current work can be found in the geosql branch.

Initial Implementation plan

This effort will be divided in the following sub-issues

• Identify a minimal viable set of SQL queries that can already be supported by ES with minimal or no changes
• Figure out the best way to load Blue Lake dataset into elasticsearch. This dataset is a part of the standard and can be used for integration testing. LINK REDACTED
• Make sure that WKT (Well-known text) parsing functions are available to SQL plugin. Simple Features are using WKT for geo shape representation and SQL will need to be able to translate between different shape representations
• Add support for returning geo shapes columns in SELECT clauses
• Whitelist WKT and other geo function so they can be called from painless in case they appear in the WHERE clause expressions
• Add support for basic geometry property functions such as ST_X and ST_Y SQL: Add Misc Geometry Property functions #35698
• Add support for geo queries in WHERE clauses SQL: Convert ST_Distance into query when possible #40595
• Add support for geo aggregations (aggregation are not supported for geo-shapes, so they are out of the initial scope)
• Upgrade H2 to 1.5.0 if it is ready or move all geo tests into a separate project where we can use JTS 1.14 as dependency SQL: Upgrade H2GIS library to 1.5.0 #40461
• Add support for Geometry to JDBC client SQL: representation of geo objects in JDBC #35767
• Switch server side processing from ShapeBuilder to libs/geo SQL: Switch server side for geosql from ShapeBuilder to libs/geo #41692
• Add support for ST_Z SQL: Add ST_Z function #41772
• Add ODBC support Introduce geo support elasticsearch-sql-odbc#154

Queries that will be supported in the initial implementation

Initial minimal release should support the following commands:

Metadata commands

• DESCRIBE table - returns the correct column type GEOMETRY for geo shapes
• SYS_SPATIAL_REF - returns a table with a single spatial reference system WGS84
• SYS_GEOMETRY_COLUMNS - returns a list of all geometry columns in all tables

Returning geoshapes and geopoints from elasticsearch

• SELECT * FROM table - returns the geoshapes in their _source representation;
• SELECT ST_AsWKT(geom) FROM table; and SELECT ST_AsText(geom) FROM table;- returns the geoshapes in their WKT representation;

Using geopoints to elasticsearch

• The following functions will be supported for geopoints in queries and aggregations: ST_GeomFromText, "ST_X, ST_Y, ST_Z, ST_Distance`.
• SELECT * FROM table WHERE ST_Distance(ST_GeomFromText(POINT(1 2), point) < 10; - returns all records for which point is located within 10m from the POINT(1 2) by using query.

Limitations:

Geoshapes cannot be used in queries, sorting and aggregations as part of this initial effort. In order to fully take advantage of geoshapes we would need to have access to geoshape doc values, which is coming in #37206. ST_Z cannot be used on geopoints in queries, sorting and aggregations since we don't store altitude in geo_point doc values.