WL#10734: Spatial index type

Affects: Server-8.0   —   Status: Complete   —   Priority: Medium

Support creation of Spatial Index of GeoJSON data stored in JSON documents

Spatial indexes are important in any application where storage of geographical 
information like coordinates is needed. In such cases, efficient query of geo 
data implies use of R-TREE / spatial indexes. 

For encoding of geographical data in a collection, the GeoJSON format is  a 
suitable standard is also supported by MySQL. GeoJSON data is to be converted to 
the GEOMETRY datatype, which can then be indexed.
FR1
:   for the client it MUST be possible to create spatial indexes on GeoJSON data

FR2
:   for the client it MUST be possible to specify parameters for the GeoJSON parsing function (ST_GeomFromGeoJSON), such as options and srid

FR3
:   if the client provides a docpath that does not refer to a valid GeoJSON sub-document, a error MUST be returned

FR4
:   if the client doesn't provide a column-type for a GEOJSON index, it defaults to GEOMETRY
Background
==========

The `create_collection_index` `StmtExecute` command of the `mysqlx` namespace is used to create indexes for collections. 

See: `admin_cmd_handler.cc`

It currently accepts the following list of arguments

* schema: string
* collection: string
* name: string
* unique: bool
* constraint: object or list-of-object
  * member: docpath
  * required: bool
  * type: string [optional]

Adding SPATIAL support
======================

## new top-level field "type"

Up to now only plain INDEXes could be created.

A new field 'type' shall be added which:

* allows "INDEX"
* defaults to "INDEX" if it isn't set (backward compatibility)

## SPATIAL index-type

To allow SPATIAL indexes, the `.type` additionally allows the value:

* `SPATIAL`

## new constraint.type

If `type` is `SPATIAL` the `constraint.type` must accept a single value:

* GEOJSON

The `GEOJSON` leads to:

* extraction function: ST_GeomFromGeoJson(doc->${.member})
* column-type: GEOMETRY

## default constraint.type

If index-type is SPATIAL and no constraint.type is specified,
it defaults to "GEOJSON"

## new constraint fields

If `constraint.type` is `GEOJSON` then new, optional `constraint` fields

* options: int, default: 1
* srid: int, default: 4326

shall be accepted.

accepted schema
===============

* schema: string
* collection: string
* name: string
* type: string [optional, default="INDEX", allowed=INDEX|SPATIAL]
* unique: bool
* constraint: object or list-of-object
  * member: docpath
  * required: bool
  * type: string [optional]
  * if type == GEOJSON:
    * options: int [optional, default: 1]
    * srid: int [optional, default: 4326]


SQL generation
==============

ALTER TABLE, ADD COLUMN part:

    <col> GEOMETRY 
        GENERATED ALWAYS AS (ST_GeomFromGeoJSON(doc->'$.geo', 1, 4326))
        STORED NOT NULL

Notes:

* the data type for the generated column MUST be GEOMETRY
* SPATIAL indexes can currently only be created on STORED generated columns, not 
VIRTUAL
* SPATIAL indexes require NOT NULL, so any request that does not have the field 
set as required shall generate an error


ALTER TABLE, ADD INDEX part:

    <tbl> ADD SPATIAL INDEX <name> (<col>);

Example
-------

* schema: 'foo'
* collection: 'bar'
* name: 'geoidx'
* unique: FALSE
* type: 'SPATIAL'
* constraint: object
  * member: '$.geo'
  * required: TRUE
  * type: GEOJSON
  * options: 1
  * srid: 4326

Generated SQL:

    alter table foo.bar
      add column geo geometry generated always as
        (st_geomfromgeojson(doc->'$.geo', 1, 4326)) stored not null,
      add spatial index geoidx (geo);

Error Handling
==============

Currently, generating a SPATIAL index without NOT NULL results in:

    ERROR 1252 (42000): All parts of a SPATIAL index must be NOT NULL

This error must be handled and translated into:

    GEOJSON index requires 'constraint.required: TRUE'

Spatial indexes require NOT UNIQUE, so any request that does 
have the argument set shall generate an error:

    "Unique spatial index is not supported"

Creating indexes require (an optional) type of index, so any request
that does have the argument set on an unsupported value 
shall generate an error: 

    "Argument value '%s' for index type is invalid"